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
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.
