Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Does mysqldump -all-databases include all objects ?

I have to migrate all databases to new server.

share|improve this question
add comment

1 Answer

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 ?

share|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
    
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 Jul 11 '13 at 10:56
    
The generic way to dump the SQL Grants looks familiar. +1 !!! –  RolandoMySQLDBA Jul 11 '13 at 13:54
    
I changed the post URL to go directly to my post. –  RolandoMySQLDBA Jul 11 '13 at 13:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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