MySQLdump is perhaps the most commonly used to tool to backup MySQL databases. By default, MySQLdump exports all tables in a database. But sometimes you may need to ignore tables or exclude tables in MySQLdump command. In this article, we will learn how to skip tables in MySQLdump.
How to Skip Tables in MySQLDump
You can easily skip tables in MySQLdump using –ignore-table option. Here is the command for it. Replace USERNAME, PASSWORD, DATABASE and table_name with the username, password, database name and table name respectively. Please note, there is no space after -p option below.
$ mysqldump -u USERNAME -pPASSWORD DATABASE --ignore-table=DATABASE.table_name > filename.sql
For example if you have a database sales with tables t1, t2, t3 then here is the command to exclude table t1 from MySQLdump command.
mysqldump -u USERNAME -pPASSWORD sales --ignore-table=sales.t1 > database.sql
If you want to exclude multiple tables, say, t1 and t2 then you need to either provide each table name as a separate –ignore-table option, or provide them in an array.
mysqldump -u USERNAME -pPASSWORD sales --ignore-table=sales.t1 --ignore-table=sales.t2 > database.sql
Alternatively, you can provide these table names as an array.
mysqldump -u USERNAME -pPASSWORD sales --ignore-table={sales.t1, sales.t2} > database.sql
Here is a simple shell script to help you easily exclude multiple tables in MySQLdump command. Replace my_db.table1, my_db.table2 and my_db.tablex with the names of tables that you want to exclude, one per line. Also replace USER and PASSWORD with the username and password of MySQL database.
#!/bin/bash #tables to ignore _TIGNORE=( my_db.table1 my_db.table2 my_db.tablex ) #create text for ignore tables _TDELIMITED="$(IFS=" "; echo "${_TIGNORE[*]/#/--ignore-table=}")" #don't forget to include user and password /usr/bin/mysqldump -uUSER -pPASSWORD --events ${_TDELIMITED} --databases my_db
In the above code, we first define the execution environment, then mention the tables to be ignored in _TIGNORE variable. Then iterate through this list of tables and construct an array or table names to be passed to MySQLdump command. Finally, we call the MySQLdump command with username, password, database and tables.
In this article, we have learnt how to skip tables in MySQLdump command. You can customize it as per your requirement.
Also read:
How to Reset MySQL Query Cache Without Restarting
How to Delete All Rows in MySQL table Except Some
How to Fix Unknown Column in Field List in MySQL
How to Loop Through All Table Rows in MySQL
How to Combine Columns in MySQL