Mysqldump tables from different databases?

Question

I want to backup two tables: table1 and table2.

table1 is from database database1.

table2 is from database database2.

Is there a way to dump them with a single mysqldump call?

I know I can do:

mysqldump -S unixSocket --skip-comments --default-character-set=utf8 --databases database1 --tables table1 > /tmp/file.sql

But how to dump two tables from different databases?

Answer 1

Use mysqldump twice but second time with redirect to file as append >> /tmp/file.sql.

Answer 2

The syntax is:

mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

Check for reference: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Hope it helps

Answer 3

There are three general ways to invoke mysqldump:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

Only the first one lets you select the database and table name, but doesn't allow multiple databases. If you use the second or third option you'll dump the selected databases (second) or all databases (third).

So, you can do it but you'll need to dump to entire databases.

As Michał Powaga stated in the comments, you might also do it twice.

first time with "> /tmp/file.sql"

second time with ">> /tmp/file.sql to append"

Answer 4

For linux/bash, oneliner:

(mysqldump dbname1 --tables table1; mysqldump dbname2 --tables table2) | gzip > dump.sql.gz

Answer 5

This might be a workaround but you could ignore the other tables you DON'T want to backup.

Such as in your case:

mysqldump --databases database1 database2 --ignore-table=database1.table2 --ignore-table=database2.table1

You need to define each table you DON'T want to dump with each --ignore-table statement.

Good luck!