How do you mysqldump specific table(s)?

Question

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

Answer 1

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
TBLIST=`mysql -u... -p... -AN -e"select group_concat(table_name separator ' ') from information_schema.tables where table_schema='${DBTODUMP}' and table_name not in ('t1','t2','t3')"`
mysqldump -u... -p... ${DBTODUMP} ${TBLIST} > mydb_tables.sql

Give it a Try !!!

Answer 2

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

Answer 3

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]

Answer 4

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

Answer 5

Just add the table name with database name in the mysqldump command

mysqldump -u<username> -p<password> <databasename> <tablenames separated by ,> > output.sql