mysqldump -all-databases include all objects ?
I have to migrate all databases to new server.
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 only takes a minute to sign up.Sign up to join this community
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 ?