Take the 2-minute tour ×
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.

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

share|improve this question
14  
May i suggest "man mysqldump"? The answer could have between found in less time than it took to write this post. Rolando politely covered the answer, however. –  Aaron Brown Dec 17 '11 at 3:58

5 Answers 5

up vote 125 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:

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.

share|improve this answer
12  
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
2  
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 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 at 15:17
    
@RolandoMySQLDBA How about if you want to exclude all tables whose names begin with 'foo_' –  codecowboy Jun 11 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 at 16:36
    
Superb man!! Best answer i have seen so far!! –  thekosmix Sep 26 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 try some GUI clients like SQLyog. Where you can select the tables which you want to dump.

share|improve this answer

You can do it simply using below command:

mysqldum -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

protected by Paul White Jul 18 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.