skip tables in mysqldump

How to Skip Tables in MySQLDump

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

Leave a Reply

Your email address will not be published. Required fields are marked *