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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 269 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

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

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
Excellent answer. – Wade Williams Dec 21 '15 at 17:26

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 '15 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 or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

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.