How to make dump of all MySQL databases besides two

Question

this is probably massively simple, however I will be doing this for a live server and don't want to mess it up.

Can someone please let me know how I can do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases?

http://stackoverflow.com/questions/9497869/export-and-import-all-mysql-databases-at-one-time — Grzegorz, Sep 24 '15 at 21:25

Basile · Accepted Answer · 2015-09-24T21:50:12.150

8

Yes, you can dump several schemas at the same time :

mysqldump --user=[USER] --password=[PASS] --host=[HOST] --databases mydb1 mydb2 mydb3 [...] --routines > dumpfile.sql

OR

mysqldump --user=[USER] --password=[p --host=[HOST] --all-databases --routines > dumpfile.sql

concerning the last command, if you don't want to dump performance_schema (EDIT: as mentioned by @Barranka, by default mysqldump won't dump it), mysql, phpMyAdmin schema, etc. you just need to ensure that [USER] can't access them.

edited Sep 24 '15 at 21:50

answered Sep 24 '15 at 21:37

Basile

326
1
7

I like when answers from different users enrich each other – Barranka Sep 24 '15 at 22:18
exactly ! this is what stackoverflow is made for :-) – Basile Sep 24 '15 at 22:51

Eugene Yarmash · Answer 2 · 2015-09-24T22:30:42.803

2

There's no option in mysqldump that you could use to filter the databases list, but you can run two commands:

# DATABASES=$(mysql -N -B -e "SHOW DATABASES" | grep -Ev '(mysql|performance_schema)')
# mysqldump -B $DATABASES

edited Sep 24 '15 at 22:30

answered Sep 24 '15 at 21:24

Eugene Yarmash

142,882
41
325
378

You need to add the `-R` flag to include the stored procedures in the dump (I learned that the hard way :S ) – Barranka Sep 24 '15 at 21:32
@Barranka You can add a lot more, but the OP seems to be asking about skipping some databases. – Eugene Yarmash Sep 24 '15 at 21:37

Barranka · Answer 3 · 2015-09-24T22:19:39.003

As stated in the reference manual:

mysqldump does not dump the INFORMATION_SCHEMA or performance_schema database by default. To dump either of these, name it explicitly on the command line and also use the --skip-lock-tables option. You can also name them with the --databases option.

So that takes care of your concern about dumping those databases.

Now, to dump all databases, I think you should do something like this:

mysqldump -h Host -u User -pPassword -A -R > very_big_dump.sql

To test it without dumping all data, you can add the -d flag to dump only database, table (and routine) definitions with no data.

As mentioned by Basile in his answer, the easiest way to ommit dumping the mysql database is to invoke mysqldump with a user that does not have access to it. So the punch line is: use or create a user that has access only to the databases you mean to dump.

as you mentioned INFORMATION_SCHEMA or performance_schema won't be dumped by default, but `mysql` will be. — Basile, Sep 24 '15 at 21:52

How to make dump of all MySQL databases besides two

3 Answers3