How can I dump a specific table or set of tables without including the rest of the db tables?
asked Dec 16, 2011 at 20:39
-
1See also: Any option for mysqldump to ignore databases for backup?– Paul White ♦Commented Mar 19, 2016 at 12:32
-
mysqldump --defaults-group-suffix=db your_db table_name | gzip > table_name-$(date +%Y%m%d-%H%M).sql.gz– Bang NguyenCommented Oct 4, 2023 at 7:10
Add a comment
|
4 Answers
If you are dumping tables t1, t2, and t3 from mydb, list them after the database name (here mydb), separated with spaces:
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.
answered Dec 16, 2011 at 20:46
-
58To exclude just a few tables you can use --ignore-table=Table1 --ignore-table=Table2 --ignore-table=Table3 etc. Commented Dec 13, 2012 at 13:06
-
@codecowboy, you can change
SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')"toSQL="${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). Commented Dec 23, 2014 at 2:55 -
1Isn't the approach for excluding tables here redundant and overkill given the existence of the
--ignore-tableargument? And if so, wouldn't it be better to scrap your script from the answer and just recommend--ignore-tableinstead? Commented Sep 26, 2016 at 17:34 -
1@codewaggle That gives the error
Illegal use of option --ignore-table=<database>.<table>unless I specify the table asschemaname.tablename.– WAFCommented Jan 10, 2017 at 14:33 -
cool stuff, any reason SQL variable is not multi-line?– user127354Commented Jun 23, 2017 at 4:32
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
answered Dec 15, 2012 at 2:06
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.
-
Why is the
'/Tables_in/d'needed resp. how does it work? Forsed(stream editor) I found geeksforgeeks.org/sed-command-in-linux-unix-with-examples and for$dthis stackoverflow.com/q/39600392/1066234 with "delete line"?– AvatarCommented Dec 10, 2023 at 11:15 -
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
-
6--ignore-table is to exclude certain tables from mysqldump, not to include . :) Commented Sep 16, 2013 at 7:02
-
5Yet one could list all the tables to exclude and it would dump just the ones remaining :-) Commented Jan 7, 2015 at 18:17