Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

How can I dump a specific table or set of tables without including the rest of the db tables?

share|improve this question

marked as duplicate by Paul White Aug 28 at 17:07

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

4 Answers 4

up vote 212 down vote accepted

If you are dumping tables t1, t2, and t3 from mydb

mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

If you have a ton of tables in mydb and you want to dump everything except t1, t2, and t3, do this:

SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')"
TBLIST=`mysql -u... -p... -AN -e"${SQL}"`
mysqldump -u... -p... ${DBTODUMP} ${TBLIST} > mydb_tables.sql

Give it a Try !!!

UPDATE 2014-03-06 10:15 EST

@RoryDonohue pointed out to me that the GROUP_CONCAT function needs to have its max length extended. I added the session variable group_concat_max_len to my answer with a length max of 10K. Thanks, @RoryDonohue.

share|improve this answer
To exclude just a few tables you can use --ignore-table=Table1 --ignore-table=Table2 --ignore-table=Table3 etc. – codewaggle Dec 13 '12 at 13:06
@codewaggle You are right. You should submit that as an answer. – RolandoMySQLDBA Dec 13 '12 at 15:09
If the output of the mysql command is limiting to 1024 characters (in the case of many many tables), you need to augment the command line SQL statement: SET SESSION group_concat_max_len = 10000; – Rory Donohue Mar 6 '14 at 11:08
@RoryDonohue I had seen your answer (moderator converted it to a comment) and I had just acknowledged it in my answer. Thank you for your eagle eye, and welcome the the DBA StackExchange. – RolandoMySQLDBA Mar 6 '14 at 15:17
@RolandoMySQLDBA How about if you want to exclude all tables whose names begin with 'foo_' – codecowboy Jun 11 '14 at 12:20

When you have more than a few tables it is much better running something like this:

mysql databasename -u [user] -p[password] -e 'show tables like "table_name_%"' | grep -v Tables_in | xargs mysqldump [databasename] -u [root] -p [password] > [target_file]

or somethink like this:

mysqldump -u [user] -p[password] databasename `echo "show tables like 'table_name_%';" | mysql -u[user] -p[password] databasename | sed '/Tables_in/d'` > [target_file]
share|improve this answer
This is a fresh approach to gather tables using bash/sed logic rather than direct SQL requests from the information_schema. +1 !!! – RolandoMySQLDBA Jul 17 '14 at 16:36
Superb man!! Best answer i have seen so far!! – thekosmix Sep 26 '14 at 10:57

A note to expand on the answer by RolandoMySQLDBA.

The script he included is a great approach for including (and table_name in) or excluding (and table_name NOT in) a list of tables.

If you just need to exclude one or two tables, you can exclude them individually with the --ignore-table option:

mysqldump -u -p etc. --ignore-table=Database.Table1 --ignore-table=Database.Table2 > dump_file.sql
share|improve this answer

You can do it simply using below command:

mysqldump -uusername -ppassword dbname \
  --ignore-table=schema.tablename1    \
  --ignore-table=schema.tablename2    \
  --ignore-table=schema.tablename3 > mysqldump.sql
share|improve this answer
--ignore-table is to exclude certain tables from mysqldump, not to include . :) – Praveen Prasannan Sep 16 '13 at 7:02
Yet one could list all the tables to exclude and it would dump just the ones remaining :-) – Colin 't Hart Jan 7 at 18:17

protected by Paul White Jul 18 '14 at 7:22

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.