4

Which databases are included in the dump generated by mysqldump --all-databases?

By my experience, that should be all the user-created databases, plus the mysql database. The database information_schema is backed up only if explicitly mentioned, and performance_schema is never backed up. Is this correct?

| improve this question | | | | |
6

I just did mysqldump --all-databases in MySQL 5.6.22 for Windows.

Neither performance_schema nor information_schema are in the dump.

It says so the the MySQL Documentation

Restrictions

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. You can also name it with the --databases option. Also, use the --skip-lock-tables option.

That's a good thing. Why ?

PERFORMANCE_SCHEMA

The instrumentation tables within performance schema are transient within the DB Server. The metrics from one time period to another can be different or the same (thus, trying to use PERFORMANCE_SCHEMA idempotently would never reveal the same results). Such results would make absolute no sense loaded onto a different server or even back into the source from another time period. Therefore, performance_schema is not dumped by default.

If you simply need to recreate the performance_schema database from backup because you dropped it accidentally, that would be OK. Then, upon using the instrumentation, stuff would get rewritten anyway.

If you wanted to snapshot the instrumentation results to view later on in a text editor or an app, that would OK as well.

INFORMATION_SCHEMA

This is also transient. Furthermore, all information_schema tables are stored in memory and simple hold metadata about the tables in the instance since MySQL started. (See my old post How is INFORMATION_SCHEMA implemented in MySQL?) Dumping it and reloading it into another MySQL Instance is completely useless because mysqld would immediately deny its load (or at the very least, mysqld would start reloading its metadata by re-reading the mysql schema and all table files and tablespaces again if it had to).

If you wanted to capture the state of all tables and their files at a moment in time without touching the tables, then dumping INFORMATION_SCHEMA and viewing the dumps from an editor or some app would be OK.

EPILOGUE

Think of it this way: If you had a slow log from one MySQL instance and copied it to another, how does that benefit you as a Developer or DBA with queries that did not run on the new server ?

In like fashion, having performance metrics and table metadata from one server would not serve any useful purpose being dumped then reloaded back to the original server or another server. mysqld needs to have a clear picture of the data files it currently manages, not what mysqld managed on another server or at some time in the past.

CAVEAT

You should refrain from backing up the mysql schema unless you restore it to the same machine or at least to another machine with the same major release of MySQL.

Please see my old post Backup and restore "mysql" database as to why you should not.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.