MySQL Logo

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
Profile Photo for Lowell Heddings Lowell Heddings
Lowell is the founder and CEO of How-To Geek. He’s been running the show since creating the site back in 2006. Over the last decade, Lowell has personally written more than 1000 articles which have been viewed by over 250 million people. Prior to starting How-To Geek, Lowell spent 15 years working in IT doing consulting, cybersecurity, database management, and programming work.
Read Full Bio »