19

Does mysqldump -all-databases include all objects ?

I have to migrate all databases to new server.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Manish Ishwar
  • 209
  • 1
  • 2
  • 3

2 Answers2

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 ?

Community
  • 1
Abdul Manaf
  • 9,677
  • 16
  • 71
  • 84
  • 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 09: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 Lupolt Jul 11 '13 at 10:56
  • It includes databases like mysql which I cannot import them in AWS RDS, how to avoid exporting them? – tom10271 Oct 10 '23 at 04:47
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.

Leandro Bardelli
  • 143
  • 8