Does mysqldump -all-databases include all objects ?

I have to migrate all databases to new server.


No mysqldump -all-databases does not include all objects

mysqldump --help

  -A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected.

So mysqldump with --all-databases only dumps all the databases.

In order to migrate all the databases to a new server, you should take a complete backup :

mysqldump an entire mysql instance

mysqldump -h... -u... -p... --events --routines --triggers --all-databases > MySQLData.sql

The drawback is that backups created this way can only be reloaded into the same major release version of mysql that the mysqldump was generated with. In other words, a mysqldump --all-databases from a MySQL 5.0 database cannot be loaded in 5.1 or 5.5. The reason ? The mysql schema is totally different between major releases.

Here is the generic way to dump the SQL Grants for users that is readble and more portable

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql

Have a look at answer from RolandoMySQLDBA on How can I optimize a mysqldump of a large database ?

| improve this answer | |
  • 1
    You forgot --events which is needed in addition to --routines and --triggers and you also omitted --single-transaction to avoid locking tables unnecessarily. – Michael - sqlbot Jul 11 '13 at 9:45
  • 1
    Just to be sure it's clear, --single-transaction won't produce a consistent backup if there are MyISAM tables being written to while the backup runs. However, adding --single-transaction is a good idea if you're using all InnoDB and want to avoid blocking while mysqldump runs. – James L Jul 11 '13 at 10:56

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.