524

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

RolandoMySQLDBA
  • 177,694
  • 32
  • 308
  • 507
markdorison
  • 5,375
  • 3
  • 15
  • 6
  • 1
    See also: [Any option for mysqldump to ignore databases for backup?](http://dba.stackexchange.com/q/35081/1192) – Paul White Mar 19 '16 at 12:32

4 Answers4

712

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.

Community
  • 1
RolandoMySQLDBA
  • 177,694
  • 32
  • 308
  • 507
  • 57
    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 02: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
  • 1
    @codewaggle That gives the error `Illegal use of option --ignore-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? –  Jun 23 '17 at 04:32
  • It s just not working `Access denied for user to database t1 when selecting database` – amdev Feb 07 '18 at 12:44
  • How do I apply multiple tables `t1`, `t2`, `t3` using the option `--tables` ? – Ifedi Okonkwo Sep 11 '19 at 13:05
  • You can pipe the output to zip command to compress the dump: mysqldump -u... -p... mydb t1 t2 t3 | zip mydb_tables.sql.zip – Satish Gadhave Mar 18 '20 at 04:51
92

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
Community
  • 1
codewaggle
  • 1,166
  • 2
  • 13
  • 15
35

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.

Jorge Campos
  • 181
  • 1
  • 2
  • 14
grosshat
  • 451
  • 4
  • 5
17

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
Community
  • 1
Vaibhav
  • 181
  • 1
  • 2