379

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

508

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:

DBTODUMP=mydb
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.

  • 42
    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
  • @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). – Buttle Butkus Dec 23 '14 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 Sep 26 '16 at 17:34
  • @codewaggle That gives the error Illegal use of option --ignore-table=<database>.<table> unless I specify the table as schemaname.tablename. – WAF Jan 10 '17 at 14:33
  • cool stuff, any reason SQL variable is not multi-line? – sdkks Jun 23 '17 at 4:32
71

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
28

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.

10

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
  • 4
    --ignore-table is to exclude certain tables from mysqldump, not to include . :) – Praveen Prasannan Sep 16 '13 at 7:02
  • 2
    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.