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
add comment

5 Answers

up vote 81 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
9  
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
add comment

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
add comment

You can try some GUI clients like SQLyog. Where you can select the tables which you want to dump.

share|improve this answer
add comment

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=Table1 --ignore-table=Table2 > dump_file.sql
share|improve this answer
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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