O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required