Does mysqldump -all-databases include all objects

Question

Does mysqldump -all-databases include all objects ?

I have to migrate all databases to new server.

Answer 1

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 ?

Answer 2

I use

       mysqldump 
--defaults-file=/something/backup.conf 
--single-transaction 
--opt 
--default-character-set=utf8  
--events 
--routines 
--triggers 
--all-databases 
| gzip -9 -c 
| sudo tee /backup/mysql/db_backup_ALL_`date +%Y%m%d_%H%M%S`.sql.gz 
> /dev/null

I added single transaction, opt, default charset, compress in gzip and a timestamp to the file.

You can also replace -h -u -p by:

--defaults-file=/something/backup.conf

and then inside the file:

[mysqldump]
user=YOUR_USER
password=YOUR_PASSWORD

Also, gzip 9 for max compression, and then sudo tee to avoid permissions issues (err 32 for e.g.). Dev null to avoid the tee to console.