8.12. mysqldump — A Database Backup Program

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump contains SQL statements to create the table or populate it, or both.

If you are doing a backup on the server, and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores. See Section 8.13, “mysqlhotcopy — A Database Backup Program”.

There are three general ways to invoke mysqldump: 

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
shell> mysqldump [options] --all-databases

If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.

To get a list of the options your version of mysqldump supports, execute mysqldump --help.

If you run mysqldump without the --quick or --opt option, mysqldump loads the whole result set into memory before dumping the result. This can be a problem if you are dumping a big database. The --opt option is enabled by default, but can be disabled with --skip-opt.

If you are using a recent copy of the mysqldump program to generate a dump to be reloaded into a very old MySQL server, you should not use the --opt or --extended-insert option. Use --skip-opt instead.

mysqldump supports the following options:

You can also set the following variables by using --var_name=value syntax:

It is also possible to set variables by using --set-variable=var_name=value or -O var_name=value syntax. This syntax is deprecated.

The most common use of mysqldump is probably for making a backup of an entire database: 

shell> mysqldump --opt db_name > backup-file.sql

You can read the dump file back into the server like this: 

shell> mysql db_name < backup-file.sql

Or like this: 

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating databases by copying data from one MySQL server to another: 

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

It is possible to dump several databases with one command: 

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, use the --all-databases option: 

shell> mysqldump --all-databases > all_databases.sql

For InnoDB tables, mysqldump provides a way of making an online backup: 

shell> mysqldump --all-databases --single-transaction > all_databases.sql

This backup just needs to acquire a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If and only if one long updating statement is running when the FLUSH statement is issued, the MySQL server may get stalled until that long statement finishes, and then the dump becomes lock-free. If the update statements that the MySQL server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates.

For point-in-time recovery (also known as “roll-forward,” when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log (see Section 5.12.3, “The Binary Log”) or at least know the binary log coordinates to which the dump corresponds: 

shell> mysqldump --all-databases --master-data=2 > all_databases.sql

Or: 

shell> mysqldump --all-databases --flush-logs --master-data=2
              > all_databases.sql

The simultaneous use of --master-data and --single-transaction provides a convenient way to make an online backup suitable for point-in-time recovery if tables are stored in the InnoDB storage engine.

For more information on making backups, see Section 5.10.1, “Database Backups”, and Section 5.10.2, “Example Backup and Recovery Strategy”.