MySQL dump structure of all tables and data of some

Question

I'm trying to dump the structure of all the tables in our database, and then only the data of the ones I specifically want, but i seem to be doing something wrong as I'm not getting the empty tables created for the ones I exclude from the data dump.

I have a text file which specifies which tables I want to dump the data for (called showtables.txt):

SHOW TABLES FROM mydb
WHERE Tables_in_mydb NOT LIKE '%_history'
AND Tables_in_mydb NOT LIKE '%_log';

I am then doing this command to dump the structure of all tables, and then the data of the tables returned by that query in the text file:

mysqldump -u root -pmypassword mydb --no-data > mydump.sql; mysql -u root -pmypassword < showtables.txt -N | xargs mysqldump mydb -u root -pmypassword > mydump.sql -v

I am getting the dump of all the tables included in the results of the showtables query, but I am not getting the structures of the rest of the tables.

If I run just the structure part as a single command, that works fine and I get the structures dumped for all tables. But combining it with the data dump seems to not work.

Can you point me to where I'm going wrong with this?

Thanks.

Answer 1

I think you've got the order of your commandline arguments wrong (the redirection to a file should be the end), and you need an extra parameter for xargs so we can specify the database name to mysqldump.

Additionally, you need to append >> the dump data, otherwise you'd be overwriting the mydump.sql file for each table:

mysqldump -u root -pmypassword mydb --no-data > mydump.sql
mysql -u root -pmypassword -N < showtables.txt | xargs -I {} mysqldump -v -u root -pmypassword mydb {} >> mydump.sql

Sources: http://www.cyberciti.biz/faq/linux-unix-bsd-xargs-construct-argument-lists-utility/