Backup and restore MySQL database

2014-05-28T04:52:00Z.

It is highly recommended to backup databases regularly. In case of data corruption, the backup can be used to restore data. This article covers basic usage of the program mysqldump to backup databases. This document assumes running mysqldump on Debian GNU/Linux.

The mysqldump program is installed by default when installing MySQL client. In case the version of MySQL server does not match with MySQL client, mysqldump may fail to work. If the package repository does not provide the matching version of MySQL client, it is possible to install the generic binaries of the desired version. The generic binaries can be downloaded from MySQL website:

http://dev.mysql.com/downloads/mysql/

Download the compressed TAR archive for the appropriate platform (x86 32-bit or x86 64-bit). Extract the downloaded archive by executing:


tar -xzvf /path/to/mysql.tar.gz

The mysqldump program is available under the bin directory.

Backup with single-line command

To backup a database FooBar once, execute the following command:


mysqldump --compact --complete-insert --skip-quote-names \
--quick --single-transaction \
--host localhost \
--password=bar --user=foo \
FooBar > FooBar-`date +%y%m%d-%H%M`.sql

The above command backups a database named FooBar to a file with timestamp in the file name.

Backup periodically with script file

To backup a database FooBar periodically, first prepare a script file with the following content:


# Backup directory.
BACKUP_DIR=/home/foobar/backup

# Backup FooBar database.
mysqldump --compact --complete-insert --skip-quote-names \
--quick --single-transaction \
--host localhost \
--password=bar --user=foo \
FooBar > ${BACKUP_DIR}/FooBar-`date +%Y%m%d-%H%M`.sql

In case of using mysqldump from manually installed MySQL generic binaries, prepare a script file with the following content:


# Home directory of MySQL binaries. Change the path of the bin
# directory when necessary.
MYSQL_BIN_HOME=/home/foobar/applications/MySQL-5.6.14/bin

# Backup directory.
BACKUP_DIR=/home/foobar/backup

# Backup FooBar database.
${MYSQL_BIN_HOME}/mysqldump --compact --complete-insert \
--skip-quote-names \
--quick --single-transaction \
--host localhost \
--password=bar --user=foo \
FooBar > ${BACKUP_DIR}/FooBar-`date +%Y%m%d-%H%M`.sql

Execute the following line to grant the execute permission on the script file:


chmod 700 /path/to/script.sh

Note that only the script owner can read, write and execute the script file because it contains sensitive information, such as the password for connecting to database.

To run the script periodically, add a cron job:


0 5 * * * /path/to/script.sh

The above line invokes the script file to backup database daily at 05:00.

Restore database from a backup

To restore a backup, simply redirect the content of backup file to the mysql program. Assuming a blank database Foobar has been created and the backup file foobar.sql is accessible, execute the following:


echo "SET FOREIGN_KEY_CHECKS=0; source foobar.sql;" | mysql -u root -p Foobar

This command will first disable foreign key constraint in the restore session, so that errors will be ignored if a table is created with foreign key referencing to a table that has not been created yet.

References

Read the official MySQL manual for more details:

Home / Articles