450

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

600

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.

| improve this answer | |
  • 47
    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
81

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
| improve this answer | |
  • And this is the best answer! – unbreak Mar 12 at 10:08
32

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.

| improve this answer | |
15

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

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