How to skip certain database tables with mysqldump?

The multiple-skips play: Excluding numerous tables

Enjoy a customized backup experience by skipping more than one table:

mysqldump -u username -p --ignore-table=database.table1 --ignore-table=database.table2 database_name > backup.sql


Now, table1 and table2 join the "not-my-type of tables" club and are not included in the dump.

The ultimate script: Automated exclusion

To remember all tables you want to exclude is like remembering all syntax errors you ever had. Use a Bash script that reads table names from a file:

# Let's play hide and seek with tables
while read table; do
    IGNORE_TABLES+=" --ignore-table=$table"
done < tables_to_skip.txt

mysqldump -u username -p database_name $IGNORE_TABLES > backup.sql


The picky backup: Data-and-structure selective dump

Let's make mysqldump a picky dumper, excluding table data or structure:

# For structure, because who needs clutter?
mysqldump -u username -p --no-data database_name > structure.sql

# For data, because structures are too mainstream
mysqldump -u username -p --no-create-info database_name > data.sql


Extra sugar: Advanced tips for smooth dump experience

One-shot picture: Consistent dumps

Just like you wouldn't want to blink during a group photo, use --single-transaction for consistent snapshots:

# Say cheese!
mysqldump --single-transaction -u username -p database_name > backup.sql


Frugal backup: Compression on the fly

Combine mysqldump with gzip to be mighty and frugal:

# Feeling light
mysqldump -u username -p database_name | gzip > backup.sql.gz


Special-case tables: Skip triggers

Sometimes, we want triggers out of this dumping party and --skip-triggers fits the bill:

# Because triggers can be triggering
mysqldump --skip-triggers --routines -u username -p database_name > backup.sql


Privacy first: Password handling

For securing your password, put a -p flag without space:

# Shhh... secret code
mysqldump -u username -p database_name > backup.sql


Effective restoration

Once backed up, we've to set things back to normal:

# Let's get things back in place
mysql -u username -p new_database < backup.sql # for .sql files
gzip -dc backup.sql.gz | mysql -u username -p new_database # for .sql.gz files


Multi-database dump

For multi-database environments, --ignore-table pairs up with --databases:

# Variety is the spice of life
mysqldump -u username -p --databases db1 db2 --ignore-table=db1.table1 > backup.sql
