How to Exclude Tables from MySQL Dump?

MySQL dump is a utility that significantly aids in database backups, creating a text file full of SQL statements. It’s particularly beneficial when you want to exclude specific tables from the dump, which can be essential for omitting large or sensitive tables. The guide below will help you streamline the backup process but also ensures that critical or unnecessary information is not included in your backups, reinforcing the security and efficiency of your data management practices.

How to exclude tables in a MySQL dump?

To exclude tables from a MySQL dump, employ the --ignore-table option. This option omits the specified tables from the backup. Apply the following syntax:

mysqldump -u [username] -p[password] [database_name] --ignore-table=[database_name].[table_to_exclude] > [output_file].sql

Substitute [username], [password], [database_name], [table_to_exclude], and [output_file] with your respective MySQL credentials, database name, the table you wish to exclude, and your desired output file name.

Example

To exclude the logs and temporary_data tables from the app_database, use:

mysqldump -u user -ppassword app_database --ignore-table=app_database.logs --ignore-table=app_database.temporary_data > backup.sql

How to exclude multiple tables?

Repeat the --ignore-table option for each table you wish to exclude when backing up multiple tables:

mysqldump -u user -ppassword mydatabase --ignore-table=mydatabase.table1 --ignore-table=mydatabase.table2 > mydatabase_dump.sql

This command produces a dump of mydatabase, leaving out table1 and table2.

Tips for large databases

Excluding tables from backups can reduce the backup time and file size for large databases, enhancing the overall efficiency. However, ensure you have backup solutions for the excluded tables if they contain important data.