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


4 Answers 4


If you are dumping tables t1, t2, and t3 from mydb, list them after the database name (here mydb), separated with spaces:

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

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.

  • 57
    To exclude just a few tables you can use --ignore-table=Table1 --ignore-table=Table2 --ignore-table=Table3 etc.
    – codewaggle
    Commented Dec 13, 2012 at 13:06
  • @codecowboy, you can change SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')" to SQL="${SQL} AND table_name NOT LIKE 'foo\_%'". I just tested it and it works. You could change the condition to '%foo%' to get all tables containing 'foo' anywhere in their names (including 'food', 'fool', etc). Commented Dec 23, 2014 at 2:55
  • 1
    Isn't the approach for excluding tables here redundant and overkill given the existence of the --ignore-table argument? And if so, wouldn't it be better to scrap your script from the answer and just recommend --ignore-table instead?
    – Mark Amery
    Commented Sep 26, 2016 at 17:34
  • 1
    @codewaggle That gives the error Illegal use of option --ignore-table=<database>.<table> unless I specify the table as schemaname.tablename.
    – WAF
    Commented Jan 10, 2017 at 14:33
  • cool stuff, any reason SQL variable is not multi-line?
    – user127354
    Commented Jun 23, 2017 at 4:32

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

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]

Remember that those commands must be typed in one line only.


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
  • 6
    --ignore-table is to exclude certain tables from mysqldump, not to include . :) Commented Sep 16, 2013 at 7:02
  • 5
    Yet one could list all the tables to exclude and it would dump just the ones remaining :-) Commented Jan 7, 2015 at 18:17

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