Managing & Using MySQL, 2nd Edition

mysqldump

mysqldump is the MySQL utility provided for dumping databases. It basically generates an SQL script containing the commands (CREATE TABLE, INSERT, etc.) necessary to rebuild the database from scratch. The main advantage of this approach over the direct copy (mysqlhotcopy) is that output is in a portable ASCII format that can be used across hardware and operating systems to rebuild a database. Also, because the output is an SQL script, it is possible to recover individual tables.

To use mysqldump to back up your database, we recommend that you use the -opt option. This turns on -quick, --add-drop-table, --add-locks, --extended-insert, and --lock-tables. This option should give you the fastest possible dump of your database. The option --lock-tables locks all the tables in the database, so the database will essentially be offline while you are doing this.

Your command will look something like this:

$ mysqldump  --opt test > /usr/backups/testdb

This command dumps the test database into the file /usr/backups/testdb. If you are using binary logging, you will also want to specify --flush-logs, so the binary logs get checkpointed at the time of the backup:

$ mysqldump  --flush-logs  --opt test > /usr/backups/testdb

mysqldump has a number of other options for customizing your backup. For a list of all the available options for mysqldump, type mysqldump --help.