Export Tables with and without Data

Posted on January 13th 2012 3:38pm Friday, by Blaine

Mysqldump does not export tables with data and without data at the same time. It does however support exporting the two sets of data separately.

My problem was that I needed to find a way to exclude certain tables’ data because they contained information that was not important to the dump. I did not want to exclude the tables completely because I wanted the truncated tables in the dump. The solution was to make two dumps and combine them into one dump.

The first dump we use the ignore-table parameter to exclude tables that we do not want data for.

mysqldump -u USERNAME -p DATABASE_NAME --ignore-table=DATABASE_NAME.TABLE_NAME > quick_dump.sql

The second dump makes use of the no-data and tables parameters. The no-data parameter specifies that the dump should not include any table data. The tables parameter limits the dump to the tables you supply. The final special thing we use here is “>> quick_dump.sql” to concatenate the two dumps together.

mysqldump -u USERNAME -p DATABASE_NAME --no-data --tables TABLE_NAME >> quick_dump.sql

Leave a Reply