You want to do a MySQL dump. You want the entire structure of the database but you want to exclude some tables because they are too big, have sensitive data, or other reasons. Your MySQL database has triggers, routines, and all that good stuff because it’s 2016.
When I went looking for a solution I read a tutorial that wrongly suggested dumping triggers and schema together in the first step. The problem with this approach is when you import your data, the ON INSERT triggers are executed, and this can lead to primary key conflicts or other weird issues. I learned the hard way.
A better way:
- Schema first
- Data next
- Triggers and routines last
mysqldump --no-data --skip-triggers DATABASE > FILE.sql mysqldump --no-create-db --no-create-info --skip-triggers --ignore-table=TABLE1--ignore-table=TABLE2 DATABASE >> FILE.sql mysqldump --no-create-db --no-create-info --no-data --routines --triggers --skip-opt DATABASE >> FILE.sql
Good times.