While setting up a new testing environment to help development make slightly less bugs, I was looking at how to transfer the database without restoring a bunch of archive tables that we really didn’t need on QA.
Logically we’d have moved those archive tables to a separate archive database, but that would be a lot of work and we just haven’t felt like it. Mmm Hot Pockets.
After looking at our database we realized that about 90% of the space was data that we really didn’t need on a testing environment, and most of that data was in a couple of tables.
The solution, of course, is to simply use mysqldump with the
--ignore-table option. The one tricky thing that you need to keep in mind is that you have to use a
dbname.tablename syntax, you can’t simply put the table name. Why? /shrug
mysqldump -uUser -pPass -hHost --ignore-table=dbname.tablename dbname > db.bak
If you want to exclude multiple tables, you can use the same argument more than once on the command line, like this:
mysqldump -uUser -pPass -hHost --ignore-table=dbname.table1 --ignore-table=dbname.table2 dbname > db.bak
You’d think that you could simply put the exclusions with spaces like you do when you specify only particular tables for export in the first place. But no, that would be too consistent.
To import that exported file again on another machine, you’d use something like this to run all the commands and inserts in the file:
mysql -uUser -pPass -hHost databasename < db.bak