19

Does mysqldump -all-databases include all objects ?

I have to migrate all databases to new server.

2 Answers 2

26

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 ?

2
  • 1
    You forgot --events which is needed in addition to --routines and --triggers and you also omitted --single-transaction to avoid locking tables unnecessarily. Jul 11, 2013 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. Jul 11, 2013 at 10:56
0

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.

1
  • This doesn't seem to add any relevant information to the existing answer.
    – mustaccio
    May 6 at 19:09

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.