332 Technique 44: Making a MySQL Server Your SQL Server

Choosing a Method to Back Up MySQL Data

Archiving (or backing up) the data in a MySQL database can be a bit tricky. The archive method that you choose is primarily influenced by three factors:

Database availability (hot-backup or shutdown)

Table type (MyISAM, InnoDB, and so on)

Archive size

If your database must be online 24/7, you must use a hot-backup technique. If you can afford to take your MySQL server down for a while, you can use a filesystem backup technique.

If you can take your server offline, you can use normal filesystem backup tools (like tar). If you need to use a hot-backup technique, you can use mysqlhotcopy or mysqldump. Or, for the ultimate in availability, use replication to archive your data. We explain how each of these backup tools works in the sections that follow.

Backing Up and Restoring with mysqldump

To back up a table using mysqldump, follow these steps:

1. Open a terminal window.

2. Execute the following command:

$ mysqldump database > backup.sql

Replace database with the name of the database that you want to archive.

3. Copy the resulting file (backup.sql) to CD (or

The mysqldump program creates an archive in the form of a series of SQL commands that will re-create the data should you restore from the archive. As mysqldump processes each table, it writes a CREATE TABLE command to re-create the table and all indexes defined for that table. Then it writes an INSERT command for each row in the table.

Listing 44-1 shows a snippet from a typical mysqldump archive.

LISTING 44-1: MYSQLDUMP ARCHIVE

...

--

-- Table structure for table `customer`

--
CREATE TABLE customer		(
id	int(11)	NOT NULL default ‘0’,
name	varchar(40)	default NULL,
address	varchar(40)	default NULL,
city	varchar(40)	default NULL,
state	varchar(20)	default NULL,
zip	varchar(9)	default NULL,

PRIMARY KEY (id), KEY cc_name (name),

KEY cc_city_state (city,state) ) TYPE=MyISAM;

--

-- Dumping data for table `customer`

--

INSERT INTO customer VALUES (1,’TrixieWare’, ‘200 Snack Street’, ‘Beltsville’,’MD’,’25525’);

INSERT INTO customer VALUES (2,’Franklin Books’,’157 Literary Ave.’,’Seattle’, ‘WA’,’97745’);

...

mysqldump backup options

tape) or move it to a different host to safeguard	The mysqldump command supports a variety of com-
the data.	mand line options, many of which affect the format
	of the data in the archive itself. One particularly

Backing Up and Restoring with mysqldump 333

handy option is --add-drop-table. This option tells mysqldump to include a DROP TABLE command

before each CREATE TABLE command. If you don’t use --add-drop-table, the resulting script will fail if tablename already exists when you run the script (which is usually the case).

You should also consider using the --all option. --all writes all the table options (character set, comment, row format, and so on) used when each table was originally created. If you don’t use --all, you’ll lose all those table options if you restore from an archive script. Table 44-1 lists the mysqldump options that affect the format of the resulting archive.

It’s a good idea to use the --opt option in most scenarios. --opt produces an archive that saves all data (even the extra table options normally saved with the --all flag) in a form that’s optimized for quick restores.

Backing up multiple databases

To archive all databases, add the --all-databases option to your command line when you create the archive. Or you can archive a set of databases like this:

$ mysqldump --databases db1 db2 db3

Compressing the archive

The archive produced by mysqldump can get very big very fast. Instead of storing the raw archive on disc, you can compress the script that mysqldump produces by using gzip or bzip2.

Because mysqldump writes the script to its standard output stream, you can easily pipe the script into the standard input stream of a compression tool, like this:

$ mysqldump database | gzip > backup.sql.gz

The --complete-insert option is useful when you suspect that you may restructure a table between the time you create the archive and the time you restore from that archive.

TABLE 44-1: MYSQLDUMP ARCHIVE OPTIONS

This command archives the given database, pipes the resulting script to gzip, and saves the compressed script to backup.sql.gz.

Option

Description

--all --complete-insert --extended-insert --add-drop-table --add-locks --disable-keys --no-autocommit --no-create-db --quote-names

--xml --where=where-clause

--opt

Force mysqldump to archive all table options (TYPE, COMMENT, and so on). Include column names in every INSERT command.

Insert multiple rows with a single INSERT command.

Insert a DROP TABLE command before each CREATE TABLE.

Lock each table for WRITE access before inserting new rows.

Update indexes after all data has been inserted.

Restore each table in a single transaction.

Don’t write CREATE DATABASE commands to the script.

Quote all column and table names in the script. Use this option only if you’ve created a table (or column) whose name conflicts with a reserved keyword.

Produce the archive in the form of an XML document rather than an SQL script. Only archive rows that satisfy the where-clause.

Optimize the archive for best restore performance.

334 Technique 44: Making a MySQL Server Your SQL Server

Restoring a mysqldump archive

After you’ve created an archive with mysqldump, it’s easy to restore your data if something goes wrong. Because mysqldump created an SQL script for you, all you have to do to recover lost data is to run the script.

For example, if you’ve created an archive named backup.sql, simply feed that script back into the mysql client, like this:

$ mysql database < backup.sql

Note that you must provide a database name if you archived a single database.

If you back up multiple databases in the same archive file with the --all-databases option or the --databases option, you don’t have to specify a database when you restore (the database names are stored in the archive).

To restore from a compressed archive, use the zcat program to decompress the script and pipe the result into mysql:

$ zcat backup.sql.gz | mysql database

If you normally back up your data to CD or DVD, see Technique 50 for a handy way to stream your MySQL archive directly onto a set of one or more discs.

Backing Up with File

System Tools

If you can afford to take your MySQL server offline occasionally, you can use normal Linux backup tools, such as tar and gzip. (See Technique 50 for information about using file system backup tools to archive your data.)

MySQL tables are stored in normal file system data files. If you have a MyISAM table named customer (in a database named acctg), you see three customerrelated files in /var/lib/mysql/acctg:

customer.frm: Contains the metadata for the customer table (that is, the table, column, and index definitions)

customer.MYD: Contains the rows in the customer table

customer.MYI: Contains the indexes defined for the customer table (if any)

You can back up an entire database by archiving the files in the database directory. For example, use the following command to create a file system backup of the acctg database:

$ tar -zcvf acctg.tgz /var/lib/mysql/acctg

Remember to be safe and shut down your MySQL server before you copy the table files.

If you can’t shut down your MySQL server on a regular basis, you can still use file system archive tools, but you have to ensure that the tables that you’re backing up are not being modified at the time you archive them.

Making a mysqlhotcopy of Your Database

The mysqlhotcopy program creates a copy of MyISAM table files from a running server. When mysqlhotcopy archives a table, it acquires a READ lock on the table, creates a copy of the table’s data files, and then releases the READ lock. The READ lock prevents updates but allows other users to read the table at the same time.

To back up the acctg database using mysqlhotcopy, use the following command:

$ mysqlhotcopy acctg

The table files are copied to a directory named

/var/lib/mysql/acct_copy. You can use a file system archive tool to back up the copy.