Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysqldump entire structure but only data from selected tables in a single command

My database has 3 tables: table1, table2 and table3

I would like to do a mysqldump on this database with the following conditions:

  • Dump structure for all tables
  • Only dump data for table1 and table2, ignore data in table3

Currently, I do this with 2 mysqldump statements

mysqldump -u user -p -d db > db_structure.sql
mysqldump -u user -p db --ignore-table=db.table3 > table1_and_table2_data.sql

Import them in the same order they were dumped (structure, then data from table1 and table2)

Is there a way to combine this into a single mysqldump command?

like image 990
Trevor Avatar asked Nov 19 '09 16:11

Trevor


People also ask

What is single transaction in Mysqldump?

Mysqldump with Transactions The --single-transaction flag will start a transaction before running. Rather than lock the entire database, this will let mysqldump read the database in the current state at the time of the transaction, making for a consistent data dump.

How do I export a specific table in MySQL?

Open the MySQL database wizard & select the table you want to export. Right-click on the table name & select the table data export wizard option.

How do I dump a specific table?

In order to dump only a specific set of tables using mysqldump , you need to specify the database name followed by the name of the tables you want to include in the dump. After running the command from the example above, the output file my_backup.


3 Answers

You can't combine them in one command but you can execute both commands at the same time and output to the same file.

mysqldump -u user -p --no-data db > structure.sql; mysqldump -u user -p db table1 table2 >> structure.sql

to avoid having to enter the password twice you can do -ppassword (note the lack of space!). Also use --no-data in the first command or you end up with the data as well. -d isn't needed when you're doing just one database.

like image 170
Cfreak Avatar answered Oct 02 '22 10:10

Cfreak


Given you may want to pipe the output to another command, as I did, instead of just redirecting to a file and appending to that file in the next command, you could try (modified from the example of stask):

(mysqldump -u $1 -p$2 -d db && mysqldump -u $1 -p$2 db --ignore-table=db.table3) |\
your_command

... in my case:

(mysqldump -u $1 -p$2 -d db && mysqldump -u $1 -p$2 db --ignore-table=db.table3) |\
gzip -9 > filename.sql.gz

Enclosing the two mysqldump commands in parentheses creates a subshell whose output we pipe into gzip and then redirect that into a file.

PS: I've also been unable to combine it into one single mysqldump invocation, though.

like image 29
0xC0000022L Avatar answered Oct 02 '22 10:10

0xC0000022L


I don't think you can do it in one command. But you definitely can merge the output to one file. Why not to wrap it in some shell script that does following:

mysqldump -u $1 -p$2 -d db > dump.sql && mysqldump -u $1 -p$2 db --ignore-table=db.table3 >> dump.sql

You will run this script with two parameters: username and password.

like image 23
stask Avatar answered Oct 02 '22 10:10

stask