MySQL Backups

MySQL Backups


There are two common ways of performing MySQL backups: performing SQL dumps and backup up the binary data files. Both ways produce reliable backups, but each has some advantages over the other. For more information about performing backups, please see the database backup documentation.
Written by Ziba R. Scott and Adam D. Gorski
Table of Contents
Generating SQL dump using mysqldump Restoring from SQL dump using mysql Backing up database files Restoring database files
Generating SQL dump using mysqldump
MySQL comes with a command line utility called mysqldump. The utility generates database dumps in various formats, depending on what options are selected. The most common method of dumping MyISAM tables (the MySQL default table type) looks as follows:
mysqldump -u root -p --opt db_name > backup.sql
The command above will dump the database called db_name into the file called backup.sql. The dump will be performed as the user root with the password supplied at the prompt that will come up. The single option --opt is short hand for a number of commonly used dump options. For more information about the mysqldump tool, please see the mysqldump documentation.
Restoring from SQL dump using mysql
Restoring a database using a SQL dump is very easy. Let's assume that we have already created the database to restore into and called it db_name. The command to restore the data would look as follows:
mysql -u root -p db_name < backup.sql
The command above uses the mysql command line client. It restores the data in the file backup.sql to the database named db_name. The restore is performed as the user root with the password supplied at the prompt that will come up. For more information about the mysql tool, please see the mysql documentation.
Backing up database files
By default, MySQL uses MyISAM tables. The tables and their data are stored as files on the file system. Each database is represented as a directory with the table files located inside. To back up a whole database, it suffices to save the directory with the files somewhere safe. For instance, assume that the database hrdi is to be backed up, and the MySQL data is stored in /var/lib/mysql. The following command could be used to create a compressed archive of the database:.
tar jcvf hrdi-backup.tar.bz2 /var/lib/mysql/hrdi
This will create a bzip2 compressed tar archive named hrdi-backup.tar.bz2 of the whole hrdi database.
Restoring database files
Restoring a database is as simple as restoring the files to the right location. Assuming the hrdi-backup.tar.bz2 file from above, a restore would consist of uncompressing the archive in the right place. The following command would accomplish the goal:
tar -C /var/lib/mysql -jxvf hrdi-backup.tar.bz2
This will restore the hrdi directory into /var/lib/mysql and restore the hrdi database.