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?

See also: Any option for mysqldump to ignore databases for backup? — Paul White, Mar 19 '16 at 12:32
mysqldump --defaults-group-suffix=db your_db table_name | gzip > table_name-$(date +%Y%m%d-%H%M).sql.gz — Bang Nguyen, Oct 04 '23 at 07:10

score 735 · Accepted Answer · edited May 26 '23 at 17:35

735

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

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.

edited May 26 '23 at 17:35

Mehdi Charife

131
1
11

answered Dec 16 '11 at 20:46

RolandoMySQLDBA

182,700
33
317
520

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=<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? – 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

score 92 · Answer 2 · edited Apr 13 '17 at 12:42

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

score 37 · Answer 3 · edited Apr 28 '17 at 12:19

37

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.

edited Apr 28 '17 at 12:19

Jorge Campos

183
1
2
15

answered Sep 18 '12 at 07:35

grosshat

471
4
5

Why is the '/Tables_in/d' needed resp. how does it work? For sed (stream editor) I found https://www.geeksforgeeks.org/sed-command-in-linux-unix-with-examples/ and for $d this https://stackoverflow.com/q/39600392/1066234 with "delete line"? – Avatar Dec 10 '23 at 11:15

score 18 · Answer 4 · edited Jun 19 '15 at 19:21

18

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

edited Jun 19 '15 at 19:21

Community

1

answered Sep 13 '13 at 05:57

Vaibhav

191
1
2

6

--ignore-table is to exclude certain tables from mysqldump, not to include . :) – Praveen Prasannan Sep 16 '13 at 07:02
5

Yet one could list all the tables to exclude and it would dump just the ones remaining :-) – Colin 't Hart Jan 07 '15 at 18:17

How do you mysqldump specific table(s)?

4 Answers4

UPDATE 2014-03-06 10:15 EST

Linked

Related