Skip certain tables with mysqldump

Question

Is there a way to restrict certain tables from the mysqldump command?

For example, I'd use the following syntax to dump only table1 and table2:

mysqldump -u username -p database table1 table2 > database.sql

But is there a similar way to dump all the tables except table1 and table2? I haven't found anything in the mysqldump documentation, so is brute-force (specifying all the table names) the only way to go?

Solution 1

You can use the --ignore-table option. So you could do

mysqldump -u USERNAME -pPASSWORD DATABASE --ignore-table=DATABASE.table1 > database.sql

There is no whitespace after -p (this is not a typo).

To ignore multiple tables, use this option multiple times, this is documented to work since at least version 5.0.

If you want an alternative way to ignore multiple tables you can use a script like this:

#!/bin/bash
PASSWORD=XXXXXX
HOST=XXXXXX
USER=XXXXXX
DATABASE=databasename
DB_FILE=dump.sql
EXCLUDED_TABLES=(
table1
table2
table3
table4
tableN   
)
 
IGNORED_TABLES_STRING=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

echo "Dump structure"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data --routines ${DATABASE} > ${DB_FILE}

echo "Dump content"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} --no-create-info --skip-triggers ${IGNORED_TABLES_STRING} >> ${DB_FILE}

Solution 2

Building on the answer from @Brian-Fisher and answering the comments of some of the people on this post, I have a bunch of huge (and unnecessary) tables in my database so I wanted to skip their contents when copying, but keep the structure:

mysqldump -h <host> -u <username> -p <schema> --no-data > db-structure.sql
mysqldump -h <host> -u <username> -p <schema> --no-create-info --ignore-table=schema.table1 --ignore-table=schema.table2 > db-data.sql

The resulting two files are structurally sound but the dumped data is now ~500MB rather than 9GB, much better for me. I can now import these two files into another database for testing purposes without having to worry about manipulating 9GB of data or running out of disk space.

Solution 3

for multiple databases:

mysqldump -u user -p --ignore-table=db1.tbl1 --ignore-table=db2.tbl1 --databases db1 db2 ..

Solution 4

Another example for ignoring multiple tables

/usr/bin/mysqldump -uUSER -pPASS --ignore-table={db_test.test1,db_test.test3} db_test> db_test.sql

using --ignore-table and create an array of tables, with syntaxs like

--ignore-table={db_test.table1,db_test.table3,db_test.table4}

Extra:

Import database

 # if file is .sql
 mysql -uUSER  -pPASS db_test < backup_database.sql
 # if file is .sql.gz
 gzip -dc < backup_database.sql.gz | mysql -uUSER -pPASSWORD db_test

Simple script to ignore tables and export in .sql.gz to save space

#!/bin/bash

#tables to ignore
_TIGNORE=(
my_database.table1
my_database.table2
my_database.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_database | gzip -v > backup_database.sql.gz

Links with information that will help you

• mysqldump & gzip
• mysqldump to a tar.gz
• How do I load a sql.gz file to my database?

Note: tested in ubuntu server with mysql Ver 14.14 Distrib 5.5.55

Solution 5

To exclude some table data, but not the table structure. Here is how I do it:

Dump the database structure of all tables, without any data:

mysqldump -u user -p --no-data database > database_structure.sql

Then dump the database with data, except the excluded tables, and do not dump the structure:

mysqldump -u user -p --no-create-info \
    --ignore-table=database.table1 \
    --ignore-table=database.table2 database > database_data.sql

Then, to load it into a new database:

mysql -u user -p newdatabase < database_structure.sql
mysql -u user -p newdatabase < database_data.sql

View more solutions

Comments

[Zac]

Thank you! Worked perfectly... I don't know how I missed that.

[Andres SK]

is there any way to just skip the table contents? the structure i want to backup.

[Brian Fisher]

You can use the --no-data=true option, but I don't know if you can do that on a per table level.

[supajb]

if the database name is not set for each --ignore-table then you will get a 'Illegal use of option --ignore-table=<database>.<table>' error. Make sure you always declare the database!

[carpii]

if you want to ignore some tables data, but still dump their structure, you can run mysqldump again fo those tables, and concatenate it onto the backup you just created

[alxgb]

There's already a reply to this thread that says that, better.

[Alex]

Thats right, but this solution works with multiple databases.

[Martin Thoma]

Interesting - I first thought mysqld and mysqldump would be the same programs.

[Trevor]

I've been constantly backing up a table that stores deleted info and it's taking up gigabytes of space... the deleted info tables aren't a necessity to backup and when I discovered this trick just now the backups went down to about 2 Megs total. BEST... TRICK... EVER!

[aland]

Is there a reason --single-transaction is used on the structure but not data dump?

[Abdel]

Tested and used under MySQL 5.5.43 (for debian-linux-gnu (x86_64)) Thanks

[rubo77]

note: it is ok, if you add a --ignore-table=db.table where the table does not exist

[Greg Glockner]

Simple and elegant. A great solution.

[pvolyntsev]

According to stackoverflow.com/questions/5109993/mysqldump-data-only dump content (second command) should be mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --no-create-info ${DATABASE} ${IGNORED_TABLES_STRING} >> ${DB_FILE}

[jrosell]

Document you share says gzip is faster than lzop.

[jebbie]

that's what i'm looking for - a one-liner answer with the solution which doesn't force me to read something for 2-3mins :P

[DarckBlezzer]

maybe you will use this instead loop IGNORED_TABLES_STRING="$(IFS=" "; echo "${EXCLUDED_TABLES[*]/#/--ignore-table=$DATABASE.}")"

[Rainer Mohr]

great solution. I had to add --skip-triggers to the second statement for the dump to work later (assuming you have triggers), but otherwise: perfect

[wuan]

compressed version, use | gzip -c > ${DB_FILE}.gz instead of > ${DB_FILE}

[Sławomir Lenart]

where is that AI? grep man mysqldump by except, then by exclude, but I forgot about ignore...

[svfat]

Great way to avoid using a script when you want to ignore multiple tables. That answer should receive more "+1"

[tremby]

Instead of building up a bunch of --ignore-table options with a loop, it's much neater to use your shell's expansion. In Bash you could do --ignore-table=mydb.{table1,table2,table3}. Note for this to work you need to use = between option name and value rather than a space.

[alant]

Thanks. Works for me as well. My use case is that I need to dump a db to a file and import it to my local sql server. The db has a few special tables that cannot be directly imported together with the rest in one go. I had to remove --single-transaction and --routines to get it to work. But it worked. Thanks!

[Anse]

I recommend passing --no-create-db to the second command for data, so the database itself is not tried to be created a second time. Although this is just cosmetic with the IF NOT EXISTS clause.

[Pawan Tiwari]

Finally I've come to an end of my searching. Thanks

[user2342558]

For security reasons, It's better to leave empty the -p parameter so it'll ask that in the console, in this way the password will not be saved in the log of the console

[Matthew Wilcoxson]

Doesn't look like this is available in later versions. But --ignore-table=database.table is.