mysqldump: how to exclude or include tables

mysqldump is a command-line tool used for creating database backups in MySQL. By default, mysqldump includes all tables of the specified database when creating the dump. In some cases, it is useful to exclude some of the tables or even include only some of them. For me, this helped to exclude one of the biggest tables to reduce the backup execution time and file size.

Exclude a single table

To exclude a single table, the argument --ignore-table followed by the table name is used:

mysqldump my_database --ignore-table=my_table > my_backup.sql

This will create a file my_backup.sql containing the sql schema and data of all tables in the database my_database except my_table.

Exclude multiple tables

To exclude multiple tables, simply add the argument --ignore-table for all the tables you want to exclude:

mysqldump my_database --ignore-table=my_table --ignore-table=another_table > my_backup.sql

This will create a file my_backup.sql containing the sql schema and data of all tables in the database my_database except my_table and another_table.

Include a single table

To include only a single table into the dump, add the name of the table to the command:

mysqldump my_database my_table > my_backup.sql

This will create a file my_backup.sql containing the sql schema and data of the table my_table from the database my_database.

Include multiple tables

To include multiple tables into the dump, add the names of the tables to the command:

mysqldump my_database my_table another_table > my_backup.sql

This will create a file my_backup.sql containing the sql schema and data of the tables my_table and another_table from the database my_database.

A complete documentation of mysqldump is available at mysql.com.

Photo by William Warby on Unsplash