9.3 Tools and Techniques

With an understanding of the various backup-related issues you need to consider, let's move on to examining the tools available. If you have a complex configuration or unusual needs, there's a chance that none of these alone will do the job for you. Instead, you'll need to build a custom solution—possibly using one or more of the tools described here.

This section isn't intended to be a comprehensive reference for each tool. Instead, it focuses on presenting the relevant features of each one so that you better understand your choices. Once you've selected a tool, be sure to consult the documentation for it. There's a good chance that the tool has options that didn't exist when this book was written. We will post news about available tools at our web site: http://highperformancemysql.com (see the Preface for more information).

9.3.1 mysqldump

For a long time, mysqldump was the only backup tool available for MySQL. It is a command-line utility for dumping tables of any type into SQL flat files. It even handles foreign-key constraints properly. mysqldump comes with MySQL, so you're guaranteed to have it installed already.

Using mysqldump to perform dumps is ideally suited to backing up small databases. The resulting files are large compared to the data being dumped, and it's not a very efficient process.

To back up all the databases on a server, execute the following command:

$ mysqldump -u root -pPassword -x --all-databases > dump.sql

The -x flag tells mysqldump to lock all tables during the backup to ensure consistency.

There are a few drawbacks to that method. Most importantly, the entire dump will go to a single file, which can result in a very large file if you have a lot of data to back up. mysqldump doesn't have an option to split the output into separate files based on database or table name.

If you need to back up a subset of all the databases, you can provide a list of database names on the command line:

$ mysqldump -u root -pPassword -x --databases db1 db2 db3 > dump.sql

This creates a dump file with the information necessary to recreate the db1, db2, and db3 databases.

If you need to back up only a few tables from a single database, you can provide the database and table names:

$ mysqldump -u root -pPassword -x db1 table1 table2 table3 > dump.sql

mysqldump works well over the network, too. By adding a -h argument, you tell it to connect to a remote MySQL host instead:

$ mysqldump -h db.example.com -u root -pPassword -x --all-databases > dump.sql

9.3.1.1 Restoring

No matter which options you use, restoring a dump is always straightforward. Simply feed the dump file back through the mysql command-line tool:

$ mysql -u root -pPassword < dump.sql

If you find yourself restoring dump files frequently (perhaps on a test server), consider using mysqldump's --extended-insert option. It tells mysqldump to bundle many insert statements together using MySQL's bulk insert syntax:

INSERT INTO mytable (col1, col2, col3)

VALUES (val1, val2, val3) (val1, val2, val3) ...

This makes the restore run far faster than the default method, which uses one insert statement per row. It also results in much smaller dump files.

Normally, mysqldump requests all the rows for the table it is dumping, buffers them in memory, and writes the data to disk. It does this to minimize the amount of time tables are locked on the server. However, when dumping large tables, you need to use the --quick option; it prevents the buffering, instead telling mysqldump to fetch rows from the server one at a time. While it's a bit slower^[5] than the default method, it's the only option when your tables are too big to fit in memory on the host that's running the dump.

^[5] That's not a typo. The --quick option causes the dump process to take a bit more time.

In fact, you might consider using the --opt option. It enables several useful options at once, including --quick and --extended-insert.

Windows users should use the --result-file option to specify an output file:

$ mysqldump -u root -pPassword --all-databases --result-file=dump.sql

Otherwise, Windows converts all newline characters (\n) to a carriage return plus newline (\r\n). The silent conversion will cause endless frustration when you need to restore a table in a hurry.

9.3.2 mysqlhotcopy

Originally created by Tim Bunce (the architect of Perl's DBI), mysqlhotcopy is a Perl script included in the standard MySQL distributions. Its purpose is to automate the process of backing up a database consisting of ISAM and MyISAM tables while the server is running. It's the most popular tool available for performing online raw backups and is best suited to backing up single databases on a live server. It operates by getting a read lock on all the tables to be copied, copying them, and then releasing the lock. This means it doesn't scale very well as traffic or size increase.

To back up a live database, such as the test database, run:

$ mysqlhotcopy -u root -p Password test /tmp

You'll end up with a test subdirectory in /tmp that contains all the tables from the backed up database.

$ ls -l /tmp/test

total 108

-rw-rw----    1 mysql    users        8550 May  3 12:02 archive.frm

-rw-rw----    1 mysql    users          25 May  3 12:02 archive.MYD

-rw-rw----    1 mysql    users        2048 May 23 12:58 archive.MYI

-rw-rw----    1 mysql    users        8924 Mar  4 21:52 contacts.frm

-rw-rw----    1 mysql    users        7500 Mar  5 21:11 contacts.MYD

-rw-rw----    1 mysql    users        5120 May 23 12:58 contacts.MYI

-rw-rw----    1 mysql    users        8550 May  3 12:02 dirty.frm

-rw-rw----    1 mysql    users          25 May  3 12:02 dirty.MYD

-rw-rw----    1 mysql    users        2048 May 23 12:58 dirty.MYI

-rwxr-xr-x    1 mysql    users        8558 Feb 26  2001 maybe_bug.frm*

-rwxr-xr-x    1 mysql    users          45 Feb 26  2001 maybe_bug.MYD*

-rwxr-xr-x    1 mysql    users        2048 May 23 12:58 maybe_bug.MYI*

-rwxr-xr-x    1 mysql    users        8715 Jan 15  2001 test_more_info.frm*

-rwxr-xr-x    1 mysql    users         784 Jan 16  2001 test_more_info.MYD*

-rwxr-xr-x    1 mysql    users        2048 May 23 12:58 test_more_info.MYI*

As you can see, mysqlhotcopy copies the data (.MYD), index (.MYI), and table definition (.frm) files for each table in the test database. To conserve space, you may choose to back up only the .frm and .MYD files in their entirety. Given the --noindices option, mysqlhotcopy copies only the first 2,048 bytes of each .MYI file. That's all MySQL needs to reconstruct the indexes at a later date.

$ mysqlhotcopy -u root -p Password --noindices test /tmp

Because it is written in Perl, mysqlhotcopy has support for regular expressions too. To back up every database that contains the string test in its name, run:

$ mysqlhotcopy -u root -p Password --regexp=test /tmp

In practice, few users use that capability, but it is there.

9.3.2.1 Restoring

To restore one or more tables, simply copy the files into the proper subdirectory of MySQL's data directory. For example, if you need to restore the test_more_info table into the test database, run:

$ cp /tmp/test/test_more_info.* datadir/test

If you used the --noindices option to truncate the .MYI files, you need to repair the tables before you can use them. You can use either the myisamchk -r command:

$ cd datadir/test

$ myisamchk -r test_more_info

or the REPAIR TABLE test_more_info command from within MySQL:

mysql> REPAIR TABLE test_more_info

That's all there is to it. You can then freely use the restored table.

9.3.3 mysqlsnapshot

Jeremy originally wrote mysqlsnapshot to simplify the process of configuring replication slaves at Yahoo! using MySQL 3.23.xx. As the amount of data grew, he realized one day that a better online backup system was needed. After working with the code for mysqlsnapshot, Jeremy realized that if he added one more feature it would do the job quite well. In addition, it would be a much smaller and easier to maintain than mysqlhotcopy.

He hasn't yet submitted mysqlsnapshot for inclusion in the MySQL distribution. It may be there by the time you read this, but if not, you can find it at http://jeremy.zawodny.com/mysql/mysqlsnapshot/.

mysqlsnapshot is best used to back up an entire database server without taking it offline. It has no options for specifying particular databases or tables to include or exclude in the process. It copies everything.

To back up all databases on a server, run:

$ mysqlsnapshot -u root -p Password -s /tmp/snap --split -n

checking for binary logging... ok

backing up db database... done

backing up db jzawodn... done

backing up db mysql... done

backing up db nuke... done

backing up db phplib... done

backing up db prout... done

backing up db test... done

snapshot completed in /tmp/snap/

This results in one tar file for each database, written to the /tmp/snap directory. If you remove the --split option, mysqlsnapshot puts all the data in a single tar file. If you supply the -z argument, it compresses the backup using gzip.

9.3.3.1 Restoring

Restoring a backup created with mysqlsnapshot is just a matter of untarring the files in MySQL's data directory. To restore the prout database, you execute:

$ cd datadir/test

$ tar -xvf /tmp/prout.tar

This illustrates one reason you ought to consider keeping each database in a separate tar file. By doing so, your backups will be more manageable (you can selectively delete them on a per-database basis), and you can be selective about what you restore.

9.3.4 InnoDB Hot Backup

If you're keeping a large amount of data in InnoDB and would like online backups, the InnoDB Hot Backup tool is the best choice. Unlike MySQL, it's not free. Rather, it's a relatively inexpensive commercial tool developed by the makers of InnoDB. See http://www.innodb.com/hotbackup.html for details.

To use the Hot Backup Tool (ibbackup), you create a configuration file that tells ibbackup where to archive the data. Then run it like this:

$ ibbackup /etc/my.cnf /etc/ibbackup.cnf

The backup tool needs to read the MySQL configuration file as well as its own configuration. Recent versions of ibbackup have added the ability to compress the backup (--compress).

It's important to note that ibbackup doesn't back up the .frm files for your tables. So even if you use InnoDB tables exclusively in MySQL, you still need to back up the .frm files separately from using ibbackup. This is slated to change in the future, so check the InnoDB manual for the most recent news.

Restoring a backup is a straightforward process. With MySQL offline, simply run:

$ ibbackup --restore /etc/ibbackup.cnf

Then start MySQL.

9.3.5 Offline Backups

As discussed earlier, there are numerous benefits to shutting down MySQL before performing a backup. To recap:

• There will be no consistency problems.

• You can use existing backup software.

• Backups can be very fast.

If you are using a home-grown backup script of some sort, simply add a call to the mysqladmin command like this:

# Now, shut down MySQL before the backup begins.

mysqladmin -u root -pPassword shutdown



# And start the backup

...



# Then bring MySQL back up

/usr/local/mysql/bin/mysqld_safe &

If you use a prepackaged backup system, you need to ensure that MySQL is down before it starts. If the backup software is run locally on the MySQL server, that's easy. Rather than running the software directly, create a small shell script or batch file that handles the stopping and starting of MySQL around the backup process—much like the previous example.

In larger environments, it is common to run client/server backup software. The backup server contacts a daemon running on a remote server when it is time for the backup process to begin. That daemon (running on your MySQL server) then feeds data to the backup server over the network. It is also common in such environments to let the backup software control the exact starting time of the backup.

In a case like that, you may need to find an alternative approach for backing up MySQL, or you'll need to do some digging in the backup software's manual. There's a good chance that you can find a way to make the backup software start and stop MySQL when it needs to. If not, you may be able to use one of the other backup strategies. If you have sufficient disk space, you can perform the backup directly on the MySQL server and let your normal backup process back up those files.

9.3.5.1 Restoring

Once again, MySQL makes it easy to restore data.^[6] Unless you're restoring the entire MySQL installation, you need to recover the files that make up the tables and databases you need to restore. Once you have them, copy them back into MySQL's data directory and start MySQL.

^[6] Your backup software may not, but there's little we can do about that here.

9.3.6 Filesystem Snapshots

Taking a snapshot of MySQL's data is the fastest and least intrusive method of backing up an online server. While the implementation details vary, a snapshot is an online copy of your data—usually stored on the same filesystem or volume. In fact, most systems use a copy-on-write scheme to minimize the free space required to take a snapshot.

MySQL itself provides no support for taking snapshots, but various free and commercial filesystems and storage solutions do. In the Linux world, LVM (the Linux volume manager) has snapshot capabilities. Veritas sells a filesystem product for most versions of Unix (and Linux) that can take snapshots. FreeBSD 5.x may offer snapshot capabilities too.

In the hardware space, Network Appliance's popular "filers" can be used to take filesystem snapshots. EMC has two ways of doing this: snapshots, which are just like the snapshots described above, and BCVs (business continuance volumes). They are, in effect, additional mirrors of a volume that can be broken off and mounted on other systems. They require double the amount of storage and are therefore expensive.

Snapshots are best used with a more traditional backup solution. By itself, a snapshot doesn't do much to guard against hardware failures. Sure, you can use a snapshot to quickly restore an accidentally dropped table, but all the snapshots in the world won't help if the disk controller catches fire.

Be sure that you have sufficient space reserved on your volume for the number of snapshots you plan to keep online. Most snapshot-capable filesystems require that you reserve a minimum amount of disk space for snapshot data. If your server processes a lot of write queries, you can easily exceed the reserved space. Check your filesystem documentation for complete details.

Just as with the other approach to online backups, you must be careful to flush and obtain a read lock on all ISAM and MyISAM tables before initiating a snapshot. The easiest way to do this is to use MySQL's FLUSH TABLES WITH READ LOCK command. It will hold the lock until you disconnect from MySQL or issue an UNLOCK TABLES command. We'll discuss this in the next section.