Skip certain tables with mysqldump


To restrict certain tables from the mysqldump command, we can use the –ignore-table option. So you could do

mysqldump -u username -p database –ignore-table=database.table1 –ignore-table=database.table2 > database.sql

To skip their contents when copying, but keep the structure:

mysqldump -h <host> -u <username> -p <schema> –no-data > db-structure.sql
mysqldump -h <host> -u <username> -p <schema> –ignore-table=schema.table1 –ignore-table=schema.table2 > db-data.sql

The resulting two files are structurally sound but the dumped data is now ~500MB rather than 9GB, much better for me. I can now import these two files into another database for testing purposes without having to worry about manipulating 9GB of data or running out of disk space. Hope this helps someone!

For multiple databases:

mysqldump -u user -p –ignore-table=db1.tbl1 –ignore-table=db2.tbl1 –databases db1 db2 ..