How to Exclude Tables from MySQL Exports Using mysqldump

While setting up a new test environment to help developers make a little less bugs, I was looking for how to transfer the database without restoring a bunch of archive tables including we did not really need it.

Logically, we would have moved these archive tables to a separate archive database, but it would be a lot of work and we did not feel like that. Mmm Hot Pockets.

After looking at our database, we realized that about 90% of the space was data that we did not really need in a test environment, and that most of these data were found in some tables

The solution, of course, is simply to use mysqldump with the –ignore-table option. The only tricky thing you need to keep in mind is that you need to use a dbname.tablename syntax, you can not just put the table name. Why? / shrug of shoulders

mysqldump -uUser -pPass -hHost –ignore-table = databasename.tablename databasename> db.bak

If you want to exclude multiple tables, you can use the same argument several times on the command line, like this:

mysqldump -uUser -pPass -hHost –ignore-table = databasename.table1 –ignore-table = databasename.table2 databasename> db.bak

You might think you could just put exclusions with spaces as you do when you specify only specific tables for export. But no, it would be too coherent.

To import this exported file to another machine again, you will use something like this to execute all commands and insertions in the file:

mysql -uUser -pPass -hHost database name <db.bak

Advertisements

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.