High Performance MySQL (2012)

---

^[217^] Please do not use Maatkit’s mk-parallel-dump and mk-parallel-restore tools. They are not safe.

Recovering from a Backup

How you recover your data depends on how you backed it up. You might need to take some or all of the following steps:

§ Stop the MySQL server.

§ Take notes on the server’s configuration and file permissions.

§ Move the data from the backup into the MySQL data directory.

§ Make configuration changes.

§ Change file permissions.

§ Restart the server with limited access, and wait for it to start fully.

§ Reload logical backup files.

§ Examine and replay binary logs.

§ Verify what you’ve restored.

§ Restart the server with full access.

We demonstrate how to do each of these steps as needed in the following sections. We also add notes specific to certain backup methods or tools in sections about those methods or tools later in this chapter.

WARNING

If there’s a chance you’ll need the current versions of your files, don’t replace them with the files from the backup. For example, if your backup includes the binary logs, and you need to replay binary logs for point-in-time recovery, don’t overwrite the current binary logs with older copies from the backup. Rename them or move them elsewhere if necessary.

During recovery, it’s often important to make MySQL inaccessible to everything except the recovery process. We like to start MySQL with the --skip-networking and --socket=/tmp/mysql_recover.sock options to ensure that it is unavailable to existing applications until we’ve checked it and brought it back online. This is especially important for logical backups, which are loaded in pieces.

Restoring Raw Files

Restoring raw files tends to be pretty straightforward—which is another way of saying there aren’t many options. This can be a good or a bad thing, depending on your recovery requirements. The usual procedure is simply to copy the files into place.

Whether you need to shut down MySQL depends on the storage engine. MyISAM’s files are generally independent from one another, and simply copying each table’s .frm, .MYI, and .MYD files works well, even if the server is running. The server will find the table as soon as anyone queries it or otherwise makes the server look for it (for example, by executing SHOW TABLES). If the table is open when you copy in these files, it’ll probably cause trouble, so before doing so you should either drop or rename the table, or use LOCK TABLES and FLUSH TABLES to close it.

InnoDB is another matter. If you’re restoring a traditional InnoDB setup, where all tables are stored in a single tablespace, you’ll have to shut down MySQL, copy or move the files into place, and then restart. You also need to ensure that InnoDB’s transaction log files match its tablespace files. If the files don’t match—for example, if you replace the tablespace files but not the transaction log files—InnoDB will refuse to start. This is one reason it’s crucial to back up the transaction log along with the data files.

If you’re using the InnoDB file-per-table feature (innodb_file_per_table), InnoDB stores the data and indexes for each table in a .ibd file, which is like a combination of MyISAM’s .MYI and .MYD files. You can back up and restore individual tables by copying these files, and you can do it while the server is running, but it’s not as simple as with MyISAM. The individual files are not independent from InnoDB as a whole. Each .ibd file has internal information that tells InnoDB how the file is related to the main (shared) tablespace. When you restore such a file, you have to tell InnoDB to “import” the file.

There are many restrictions on this process, which you can read about in the MySQL manual section on using per-table tablespaces. The biggest is that you can only restore a table to the server from which you backed it up. It’s not impossible to back up and restore tables in this configuration, but it’s trickier than you might think.

NOTE

Percona Server and Percona XtraBackup have some enhancements that lift some of the restrictions on this process, such as the same-server restriction.

All this complexity means that restoring raw files can be very tedious, and it’s easy to get it wrong. A good rule of thumb is that the harder and more complex your recovery procedure becomes, the more you need to protect yourself with logical backups as well. It’s always a good idea to have a logical backup, in case something goes wrong and you can’t convince MySQL to use your raw backups.

Starting MySQL after restoring raw files

There are a few things you’ll need to do before you start the MySQL server you’re recovering.

The first and most important thing, and one of the easiest to forget, is to check your server’s configuration and make sure the restored files have the correct owner and permissions, before you try to start the MySQL server. These attributes must be exactly right, or MySQL might not start. The attributes vary from system to system, so check your notes to see exactly what you’ll need to set. You typically want the mysql user and group to own the files and directories, which you want to be readable and writable by that user and group but no others.

We also suggest watching the MySQL error log while the server starts. On a Unix-style system, you can watch the file like this:

$ tail -f /var/log/mysql/mysql.err

The exact location of the error log will vary. Once you’re monitoring the file, you can start the MySQL server and watch for errors. If all goes well, you’ll have a nicely recovered server once MySQL starts.

Watching the error log is even more important in newer MySQL versions. Older versions wouldn’t start if InnoDB had an error, but in newer versions the server will start anyway and just disable InnoDB. Even if the server seems to start without trouble, you should run SHOW TABLE STATUSin each database, then check the error log again.

Restoring Logical Backups

If you’re restoring logical backups instead of raw files, you need to use the MySQL server itself to load the data back into the tables, as opposed to using the operating system to simply copy files into place.

Before you load that dump file, however, take a moment to consider how large it is, how long it’ll take to load, and anything you might want to do before you start, such as notifying your users or disabling part of your application. Disabling binary logging might be a good idea, unless you need to replicate the restoration to a replica: a huge dump file is hard enough for the server to load, and writing it to the binary log adds even more (possibly unnecessary) overhead. Loading huge files also has consequences for some storage engines. For example, it’s not a good idea to load 100 GB of data into InnoDB in a single transaction, because of the huge rollback segment that will result. You should load in manageable chunks and commit the transaction after each chunk.

There are two kinds of restoration you might do, which correspond to the two kinds of logical backups you can make.

Loading SQL files

If you have a SQL dump, the file will contain executable SQL. All you need to do is run it. Assuming you backed up the Sakila sample database and schema into a single file, the following is a typical command you might use to restore it:

$ mysql < sakila-backup.sql

You can also load the file from within the mysql command-line client with the SOURCE command. Although this is mostly a different way of doing the same thing, it makes some things easier. For example, if you’re an administrative user in MySQL, you can turn off binary logging of the statements you’ll execute from within your client connection, and then load the file without needing to restart the MySQL server:

mysql> SET SQL_LOG_BIN = 0;

mysql> SOURCE sakila-backup.sql;

mysql> SET SQL_LOG_BIN = 1;

If you use SOURCE, be aware that an error won’t abort a batch of statements, as it will by default when you redirect the file into mysql.

If you compressed the backup, don’t separately decompress and load it. Instead, decompress and load it in a single operation. This is much faster:

$ gunzip -c sakila-backup.sql.gz | mysql

If you want to load a compressed file with the SOURCE command, see the discussion of named pipes in the next section.

What if you want to restore only a single table (for example, the actor table)? If your data has no line breaks, it’s not hard to restore the data if the schema is already in place:

$ grep 'INSERT INTO `actor`' sakila-backup.sql | mysql sakila

Or, if the file is compressed:

$ gunzip -c sakila-backup.sql.gz | grep 'INSERT INTO `actor`'| mysql sakila

If you need to create the table as well as restore the data, and you have the entire database in a single file, you’ll have to edit the file. This is why some people like to dump each table into its own file. Most editors can’t deal with huge files, especially if they’re compressed. Besides, you don’t want to actually edit the file itself—you just want to extract the relevant lines—so you’ll probably have to do some command-line work. It’s easy to use grep to pull out only the INSERT statements for a given table, as we did in the previous commands, but it’s harder to get the CREATE TABLE statement. Here’s a sed script that extracts the paragraph you need:

$ sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `actor`/!d;q' sakila-backup.sql

That’s pretty cryptic, we admit. If you have to do this kind of work to restore data, your backups are poorly designed. With a little planning, it’s possible to prevent a situation in which you’re panicked and trying to figure out how sed works. Just back up each table into its own file, or, better yet, back up the data and schema separately.

Loading delimited files

If you dumped the data via SELECT INTO OUTFILE, you’ll have to use LOAD DATA INFILE with the same parameters to restore it. You can also use mysqlimport, which is a wrapper around LOAD DATA INFILE. It relies on naming conventions to determine where to load a file’s data.

We hope you dumped your schema, not just your data. If so, it’s a SQL dump, and you can use the techniques outlined in the previous section to load it.

There’s a great optimization you can use with LOAD DATA INFILE. It must read directly from a file, so you might think you have to decompress the file before loading it, which is very slow and disk-intensive. However, there’s a way around that, at least on systems that support FIFO “named pipe” files, such as GNU/Linux. First, create a named pipe and stream the decompressed data into it:

$ mkfifo /tmp/backup/default/sakila/payment.fifo

$ chmod 666 /tmp/backup/default/sakila/payment.fifo

$ gunzip -c /tmp/backup/default/sakila/payment.txt.gz

> /tmp/backup/default/sakila/payment.fifo

Notice we’re using a greater-than character (>) to redirect the decompressed output into the payment.fifo file—not a pipe symbol, which creates anonymous pipes between programs. The payment.fifo file is a named pipe, so there’s no need for an anonymous one.

The pipe will wait until some program opens it for reading from the other end. Here’s the neat part: the MySQL server can read the decompressed data from the pipe, just like any other file. Don’t forget to disable binary logging if appropriate:

mysql> SET SQL_LOG_BIN = 0; -- Optional

-> LOAD DATA INFILE '/tmp/backup/default/sakila/payment.fifo'

-> INTO TABLE sakila.payment;

Query OK, 16049 rows affected (2.29 sec)

Records: 16049 Deleted: 0 Skipped: 0 Warnings: 0

Once MySQL is done loading the data, gunzip will exit, and you can delete the named pipe. You can use this same technique to load compressed files from within the MySQL command-line client with the SOURCE command. The pt-fifo-split program in Percona Toolkit can help you load large files in chunks, rather than one large transaction, which can be a lot more efficient.

YOU CAN’T GET THERE FROM HERE

One of the authors once changed a column from DATETIME to TIMESTAMP to save space and make processing faster, as recommended in Chapter 3. The resulting table definition looked like the following:

CREATE TABLE tbl (

col1 timestamp NOT NULL,

col2 timestamp NOT NULL default CURRENT_TIMESTAMP

on update CURRENT_TIMESTAMP,

... more columns ...

);

This table definition causes a syntax error on MySQL 5.0.40, the server version from which it was created. You can dump it, but you can’t reload it. Odd, unforeseen errors such as this one are among the reasons why it’s important to test your backups. You never know what will prevent you from restoring your data!

Point-in-Time Recovery

The most common way to do point-in-time recovery with MySQL is to restore your last full backup and then replay the binary log from that time forward (sometimes called “roll-forward recovery”). As long as you have the binary log, you can recover to any point you wish. You can even recover a single database without too much trouble.

The main drawback is that binary log replay can potentially be a slow process. It’s essentially equivalent to replication. If you have a replica, and you have measured how heavily utilized the SQL thread is, you’ll have a good gauge of how quickly you can replay binary logs. For example, if your SQL thread is about 50% utilized, recovering a week’s worth of binary logs is probably going to take between three and four days.

A common scenario is undoing the effects of a harmful statement, such as a DROP TABLE. Let’s look at a simplified example of how to do that, using only MyISAM tables. Suppose that at midnight, the backup job ran the equivalent of the following statements, which copied the database elsewhere on the same server:

mysql> FLUSH TABLES WITH READ LOCK;

-> server1# cp -a /var/lib/mysql/sakila /backup/sakila;

mysql> FLUSH LOGS;

-> server1# mysql -e "SHOW MASTER STATUS" --vertical > /backup/master.info;

mysql> UNLOCK TABLES;

Then, later in the day, suppose someone ran the following statement:

mysql> USE sakila;

mysql> DROP TABLE sakila.payment;

For the sake of illustration, we assume that we can recover this database in isolation (that is, that no tables in this database were involved in cross-database queries). We also assume that we didn’t notice the offending statement until some time later. The goal is to recover everything that’s happened to the database, except for that statement. That is, we must also preserve all the modifications that have been made to other tables, including after that statement was run.

This isn’t all that hard to do. First, we stop MySQL to prevent further modifications and restore just the sakila database from the backup:

server1# /etc/init.d/mysql stop

server1# mv /var/lib/mysql/sakila /var/lib/mysql/sakila.tmp

server1# cp -a /backup/sakila /var/lib/mysql

We disable normal connections by adding the following to the server’s my.cnf file while we work:

skip-networking

socket=/tmp/mysql_recover.sock

Now it’s safe to start the server:

server1# /etc/init.d/mysql start

The next task is to find which statements in the binary log we want to replay, and which we want to skip. As it happens, the server has created only one binary log since the backup at midnight. We can examine this file with grep and find the offending statement:

server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215

| grep -B3 -i 'drop table sakila.payment'

# at 352

#070919 16:11:23 server id 1 end_log_pos 429 Query thread_id=16 exec_time=0

error_code=0

SET TIMESTAMP=1190232683/*!*/;

DROP TABLE sakila.payment/*!*/;

The statement we want to skip is at position 352 in the log file, and the next statement is at position 429. We can replay the log up to position 352, and then from 429 on, with the following commands:

server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215

--stop-position=352 | mysql -uroot -p

server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215

--start-position=429 | mysql -uroot -p

Now all we have to do is check the data just to be sure, stop the server and undo the changes to my.cnf, and restart the server.

More Advanced Recovery Techniques

Replication and point-in-time recovery use the same mechanism: the server’s binary log. This means replication can be a helpful tool during recovery, in some not-so-obvious ways. We show you some of the possibilities in this section. This isn’t an exhaustive list, but it should give you some ideas about how to design recovery processes for your needs. Remember to script and rehearse anything you think you’ll need to do during recovery.

Delayed replication for fast recovery

As we mentioned earlier in this chapter, having a delayed replica can make point-in-time recovery much faster and easier if you notice the accident before the replica executes the offending statement.

The procedure is a little different from that outlined in the preceding section, but the idea is the same. You stop the replica, then use START SLAVE UNTIL to replay events until just before the statement you want to skip. Next, you execute SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 to skip the bad statement. Set it to a value higher than 1 if you want to skip several events (or simply use CHANGE MASTER TO to advance the replica’s position in the log).

All you have to do then is execute START SLAVE and let the replica run until it is finished executing its relay logs. Your replica has done all the tedious work of point-in-time recovery for you. Now you can promote the replica to master, and you’ve recovered with very little interruption.

Even if you don’t have a delayed replica to speed recovery, replicas can be useful because they fetch the master’s binary logs onto another machine. If your master’s disk fails, a replica’s relay logs might be the only place you’ll have a reasonably up-to-date copy of the master’s binary logs.

Recovering with a log server

There’s another way to use replication for recovery: set up a log server. We feel that replication is more trustworthy than mysqlbinlog, which might have odd bugs or edge cases that cause unexpected behavior. A log server is also more flexible and easier to use for recovery than mysqlbinlog, not only because of the START SLAVE UNTIL option, but also because of the replication rules you can apply (such as replicate-do-table). With a log server, you can do much more complex filtering than you’d be able to do otherwise.

For example, a log server lets you recover a single table easily. This is a lot harder to do correctly with mysqlbinlog and command-line tools—in fact, it’s hard enough that we advise you not to try.

Let’s suppose our careless developer dropped the same table as before, and we want to recover it without reverting the whole server to last night’s backup. Here’s how to do this with a log server:

1. Let the server you need to recover be called server1.

2. Recover last night’s backup to another server, called server2. Run the recovery process on this server to avoid the risk of making things worse if you make a mistake in recovery.

3. Set up a log server to serve server1’s binary logs, following the directions in Chapter 10. (It might be a good idea to copy the logs away to another server and set up the log server there, just to be extra careful.)

4. Change server2’s configuration file to include the following:

replicate-do-table=sakila.payment

5. Restart server2, then make it a replica of the log server with CHANGE MASTER TO. Configure it to read from the binary log coordinates of last night’s backup. Don’t run START SLAVE yet.

6. Examine the output of SHOW SLAVE STATUS on server2 and verify that everything is correct. Measure twice, cut once!

7. Find the binary log position of the offending statement, and execute START SLAVE UNTIL to replay events until just before that position on server2.

8. Stop the replica process on server2 with STOP SLAVE. It should now have the table as it existed just before it was dropped.

9. Copy the table from server2 to server1.

This process is possible only if the table isn’t the target of any multitable UPDATE, DELETE, or INSERT statements. Any such statements will execute against a different database state than the one that existed when the binary log events were logged, so the table will probably end up containing different data than it should. (This applies only if you’re using statement-based binary logging; if you use row-based logging, the replay process won’t be prone to this error.)

InnoDB Crash Recovery

InnoDB checks its data and log files every time it starts to see whether it needs to perform its recovery process. However, InnoDB’s recovery isn’t the same thing we’ve been talking about in the context of this chapter. It’s not recovering backed-up data; instead, it’s applying transactions from the logs to the data files and rolling back uncommitted modifications to the data files.

Exactly how InnoDB recovery works is a little too complicated to describe here. We focus instead on how to actually perform recovery when InnoDB has a serious problem.

Most of the time InnoDB is very good at fixing problems. Unless there is a bug in MySQL or your hardware is faulty, you shouldn’t have to do anything out of the ordinary, even if your server loses power. InnoDB will just perform its normal recovery upon startup, and all will be well. In the log file, you’ll see messages like the following:

InnoDB: Doing recovery: scanned up to log sequence number 0 40817239

InnoDB: Starting an apply batch of log records to the database...

InnoDB prints progress messages in percents into the log file. Some people report not seeing these until the whole process is done. Be patient; there is no way to hurry the process. Killing and restarting will just make it take longer.

If there’s a severe problem with your hardware, such as memory or disk corruption, or if you run into a bug in MySQL or InnoDB, you might have to intervene and either force recovery or prevent the normal recovery from happening.

Causes of InnoDB corruption

InnoDB is very robust. It is built to be reliable, and it has a lot of built-in sanity checks to prevent, find, and fix corrupted data—much more so than other MySQL storage engines, and even more than some other databases. However, it can’t protect itself against everything.

At a minimum, InnoDB relies on unbuffered I/O calls and fsync() calls not returning until the data is safely written to physical media. If your hardware doesn’t guarantee this behavior, InnoDB can’t protect your data, and a crash can cause corruption.

Many InnoDB corruption problems are hardware-related (e.g., corrupted page writes caused by power failures or bad memory). However, misconfigured hardware is a much bigger source of problems in our experience. Common misconfigurations include enabling the writeback cache on a RAID card that doesn’t have a battery backup unit, or enabling the writeback cache on hard drives themselves. These mistakes will cause the controller or drive to lie and say the fsync() completed, when the data is in fact only in the writeback cache, not on disk. In other words, the hardware doesn’t provide the guarantees InnoDB needs to keep your data safe.

Sometimes machines are configured this way by default, because it gives better performance—which might be fine for some purposes, but not for a transactional database server.

You can also get corruption if you run InnoDB on network-attached storage (NAS), because completing an fsync() to such a device might just mean the device received the data. The data is safe if InnoDB crashes, but not necessarily if the NAS device crashes.

Sometimes the corruption is worse than other times. Severe corruption can crash InnoDB or MySQL, but less severe corruption might just mean some transactions are lost because the log files weren’t really synced to disk.

How to recover corrupted InnoDB data

There are three major types of InnoDB corruption, and each requires a different level of effort to recover the data:

Secondary index corruption

You can often fix a corrupt secondary index with OPTIMIZE TABLE; alternatively, you can use SELECT INTO OUTFILE, drop and recreate the table, then use LOAD DATA INFILE. (You can also alter the table to MyISAM and back.) These processes fix the corruption by building a new table, and hence rebuilding the affected index.

Clustered index corruption

In the event of clustered index corruption, you might need to use the innodb_force_recovery settings to dump the table (more on this later). Sometimes the dump process crashes InnoDB; if this happens, you might need to dump ranges of rows to skip the corrupted pages that are causing the crash. A corrupt clustered index is a more severe problem than a corrupt secondary index because it affects the data rows themselves, but it’s still possible to fix just the affected tables in many cases.

Corrupt system structures

System structures include the InnoDB transaction log, the undo log area of the tablespace, and the data dictionary. This type of corruption is likely to require a complete dump and restore, because much of InnoDB’s inner workings might be affected.

You can usually repair a corrupted secondary index without losing any data. However, the other two scenarios often involve at least some data loss. If you have a backup, you’re probably better off restoring that backup rather than trying to extract data from corrupt files.

If you must try to extract the data from the corrupted files, the general process is to try to get InnoDB up and running, then use SELECT INTO OUTFILE to dump the data. If your server has already crashed and you can’t even start InnoDB without crashing it, you can configure it to prevent the normal recovery and background processes from running. This might let you start the server and make a logical backup with reduced or no integrity checking.

The innodb_force_recovery parameter controls which kinds of operations InnoDB will do at startup and during normal operation. The normal value is 0, and you can increase it up to 6. The MySQL manual documents the exact behavior of each option; we won’t duplicate that information here, but we will note that you can increase the value to as high as 4 with little danger. At this setting, you might lose some data on pages that have corruption; if you go higher, you might extract bad data from corrupted pages, or increase the risk of a crash during the SELECT INTO OUTFILE. In other words, levels up to 4 do no harm to your data, but they might miss opportunities to fix problems; levels 5 and 6 are more aggressive at fixing problems but risk doing harm.

When you set innodb_force_recovery to a value greater than 0, InnoDB is essentially read-only, but you can still create and drop tables. This prevents further corruption, and it makes InnoDB relax some of its normal checks so it doesn’t intentionally crash when it finds bad data. In normal operations, this is a safeguard, but you don’t want it when you’re recovering. If you need to force InnoDB recovery, it’s a good idea to configure MySQL not to allow normal connection requests until you’re finished.

If InnoDB’s data is so corrupt that you can’t start MySQL at all, you can use Percona’s InnoDB Recovery Toolkit to extract data directly from the files. These tools are freely available at http://www.percona.com/software/. Percona Server also has an option that allows the server to run even when some tables are corrupted, rather than the default MySQL behavior of hard-crashing the entire server when a single corrupt page is detected.

Backup and Recovery Tools

A variety of good and not-so-good backup tools are available. Our favorites are mylvmbackup for LVM snapshot backups, and Percona XtraBackup (open source) or MySQL Enterprise Backup (proprietary) for hot InnoDB backups. We don’t recommend mysqldump for backing up any significant amount of data, due to the impact on the server and the unpredictably long restore times.

There are a few backup tools that should have disappeared over the years, but unfortunately they’ve stayed around. The most obvious example is Maatkit’s mk-parallel-dump, which never worked right, even though it was redesigned several times. Another is mysqlhotcopy, which sort of worked for MyISAM tables in the olden days. Neither tool is safe to trust with your data in the general case, and can lead you to believe that data is backed up when it isn’t. For example, mysqlhotcopy will copy your .ibd files if you use InnoDB with innodb_file_per_table, which has fooled some people into thinking their InnoDB data was backed up. Both tools can have an adverse impact on a running server in some circumstances.

If you were watching MySQL’s roadmap around 2008 or 2009, you probably heard about MySQL online backup. This was a feature that let you initiate backups and restores from the server, with SQL commands. It was originally planned for MySQL 5.2, then rescheduled for MySQL 6.0, then canceled forever as far as we know.

MySQL Enterprise Backup

This tool, formerly known as InnoDB Hot Backup or ibbackup, is part of a MySQL Enterprise subscription from Oracle. Using it does not require stopping MySQL, setting locks, or interrupting normal database activity (though it will cause some extra load on your server). It supports features such as compressed backups, incremental backups, and streaming backups to another server. It is the “official” backup tool for MySQL.

Percona XtraBackup

Percona XtraBackup is quite similar to MySQL Enterprise Backup in many ways, but it’s open source and free. In addition to the core backup tool, there is also a wrapper script written in Perl that enhances its functionality for more advanced tasks. It supports features such as streaming, incremental, compressed, and multithreaded (parallel) backup operations. It also has a variety of special features to reduce the impact of backups on heavily loaded systems.

Percona XtraBackup works by “tailing” the InnoDB log files in a background thread, then copying the InnoDB data files. This is a slightly involved process, with special checks to ensure that data is copied consistently. When all the data files are copied, the log-copying thread finishes, too. The result is a copy of all the data, but at different points in time. The logs can now be applied to the data files, using InnoDB’s crash recovery routines, to bring all of the data files into a consistent state. This is referred to as the prepare process. Once prepared, the backup is fully consistent and contains all committed transactions as of the ending point of the file copy process. All of this happens completely externally to MySQL, so it doesn’t need to connect to or access MySQL in any way.

The wrapper script adds the ability to recover a backup by copying it back to its original location. There’s also Lachlan Mulcahy’s XtraBackup Manager project for even more functionality; see http://code.google.com/p/xtrabackup-manager/ for more information.

mylvmbackup

Lenz Grimmer’s mylvmbackup (http://lenz.homelinux.org/mylvmbackup/) is a Perl script to help automate MySQL backups via LVM snapshots. It gets a global read lock, creates a snapshot, and releases the lock. It then compresses the data with tar and removes the snapshot. It names the tarball according to the timestamp at which the backup was made. It has a few more advanced options, but in general it’s a straightforward tool for performing LVM backups.

Zmanda Recovery Manager

Zmanda Recovery Manager for MySQL, or ZRM (http://www.zmanda.com), comes in both free (GPL) and commercial versions. The enterprise edition comes with a management console that provides a graphical web-based interface for configuration, backup, verification, recovery, reporting, and scheduling. The open source edition is not crippled in any way, but it doesn’t include some of the extra niceties, such as the web-based console.

True to its name, ZRM is actually a backup and recovery manager, not just a single tool. It wraps its own functionality around standard tools and techniques, such as mysqldump, LVM snapshots, and Percona XtraBackup. It automates much of the tedious work of backups and recovery.

mydumper

Several current and former MySQL engineers created mydumper as a replacement for mysqldump, based on their years of experience. It is a multithreaded (parallel) backup and restore toolset for MySQL and Drizzle with a lot of nice features. Many people will probably find the speed of multithreaded backups and restores to be this tool’s most attractive feature. Although we know of some people using it in production, we don’t have any production experience with it ourselves. You can find out more at http://www.mydumper.org.

mysqldump

Most people use the programs that ship with MySQL, so despite its shortcomings, the most common choice for creating logical backups of data and schemas is mysqldump. It’s a general-purpose tool that can be used for many tasks, such as copying a table from one server to another:

$ mysqldump --host=server1 test t1 | mysql --host=server2 test

We’ve shown several examples of how to create logical backups with mysqldump throughout this chapter. By default, it outputs a script containing all the commands needed to create a table and fill it with data; there are also options to output views, stored routines, and triggers. Here are some more examples of typical usage:

§ To make a logical backup of everything on a server to a single file, with all tables in each database backed up at the same logical point in time:

$ mysqldump --all-databases > dump.sql

§ To make a logical backup of only the Sakila sample database:

$ mysqldump --databases sakila > dump.sql

§ To make a logical backup of only the sakila.actor table:

$ mysqldump sakila actor > dump.sql

You can use the --result-file option to specify an output file, which can help prevent newline conversion on Windows:

$ mysqldump sakila actor --result-file=dump.sql

The default options for mysqldump aren’t good for most backup purposes. You’ll probably want to specify some options explicitly to change the output. Here are options we use frequently to make mysqldump more efficient and make its output easier to use:

--opt

Enables a group of options that disable buffering (which could make your server run out of memory), write more data into fewer SQL statements in the dump so they’ll load more efficiently, and do some other useful things. Read your version’s help for the details. If you disable this group of options, mysqldump will store each table you dump in its memory before writing it to the disk, which is impractical for large tables.

--allow-keywords, --quote-names

Make it possible to dump and restore tables that use reserved words as names.

--complete-insert

Makes it possible to move data between tables that don’t have identical columns.

--tz-utc

Makes it possible to move data between servers in different time zones.

--lock-all-tables

Uses FLUSH TABLES WITH READ LOCK to get a globally consistent backup.

--tab

Dumps files with SELECT INTO OUTFILE.

--skip-extended-insert

Causes each row of data to have its own INSERT statement. This can help you selectively restore certain rows if necessary. The cost is larger files that are more expensive to import into MySQL; you should enable this only if you need it.

If you use the --databases or --all-databases options to mysqldump, the resulting dump’s data will be consistent within each database, because mysqldump will lock and dump all tables a database at a time. However, tables from different databases might not be consistent with each other. Using the --lock-all-tables option solves this problem.

For InnoDB backups, you should add the --single-transaction option, which uses InnoDB’s MVCC features to create a consistent backup at a single point in time, instead of using LOCK TABLES. If you add the --master-data option, the backup will also contain the server’s binary log coordinates at the moment of the backup, which is very helpful for point-in-time recovery and setting up replicas. However, be aware that it will use FLUSH TABLES WITH READ LOCK to freeze the server so it can get the coordinates.

Scripting Backups

It’s pretty standard to need to write some scripts for backups. Rather than showing you a sample program, which would necessarily have a lot of scaffolding that just takes up space on the page, we list the ingredients that go into a typical backup script and show you code snippets for a Perl script. You can view these as building blocks that you can snap together to create your own script. We show them in roughly the order you’ll need to use them:

Sanity checking

Make life easier on yourself and your teammates—turn on strict error checking and use English variable names:

use strict;

use warnings FATAL => 'all';

use English qw(-no_match_vars);

If you script in Bash, you can enable stricter variable checking, too. The following will raise an error when there’s an undefined variable in a substitution or when a program exits with an error:

set -u;

set -e;

Command-line arguments

The best way to add command-line option processing is with the standard libraries, which are included with every Perl installation:

use Getopt::Long;

Getopt::Long::Configure('no_ignore_case', 'bundling');

GetOptions( .... );

Connecting to MySQL

The standard Perl DBI library is nearly ubiquitous, and it provides a lot of power and flexibility. Read the Perldoc (available online at http://search.cpan.org) for details on how to use it. You can connect to MySQL using DBI as follows:

use DBI;

$dbh = DBI->connect(

'DBI:mysql:;host=localhost', 'user', 'p4ssw0rd', {RaiseError => 1 });

For command-line scripting, read the --help text for the standard mysql program. It has a lot of options to make it friendly for scripting. For example, here’s how to iterate over a list of databases in Bash:

mysql -ss -e 'SHOW DATABASES' | while read DB; do

echo "${DB}"

done

Stopping and starting MySQL

The best way to stop and start MySQL is to use your operating system’s preferred method, such as running the /etc/init.d/mysql init script or the service control (on Windows). It’s not the only way, though. You can shut down the database from Perl, with an existing database connection:

$dbh->func("shutdown", 'admin');

You shouldn’t rely on MySQL being shut down when this command completes—it might only be in the process of shutting down. You can also stop MySQL from the command line:

$ mysqladmin shutdown

Getting lists of databases and tables

Every backup script asks MySQL for a list of databases and tables. Beware of entries that aren’t really databases, such as the lost+found directory in some journaling filesystems and the INFORMATION_SCHEMA. Make sure your script is ready to deal with views, too, and be aware thatSHOW TABLE STATUS can take a really long time when you have lots of data in InnoDB:

mysql> SHOW DATABASES;

mysql> SHOW /*!50002 FULL*/ TABLES FROM<database>;

mysql> SHOW TABLE STATUS FROM<database>;

Locking, flushing, and unlocking tables

You’re bound to need to lock and/or flush one or more tables. You can either lock the desired tables by naming them all, or just lock everything globally:

mysql> LOCK TABLES<database.table>READ [, ...];

mysql> FLUSH TABLES;

mysql> FLUSH TABLES<database.table>[, ...];

mysql> FLUSH TABLES WITH READ LOCK;

mysql> UNLOCK TABLES;

Be very careful about race conditions when getting lists of tables and locking them. New tables could be created, or tables could be dropped or renamed. If you lock and back them up one at a time, you won’t get consistent backups.

Flushing binary logs

It’s handy to ask the server to begin a new binary log (do this after locking the tables, but before taking a backup):

mysql> FLUSH LOGS;

It makes recovery and incremental backups easier if you don’t have to think about starting in the middle of a log file. This does have some side effects with regard to flushing and reopening error logs and potentially destroying old log entries, so be careful you’re not throwing away data you need.

Getting binary log positions

Your script should get and record both the master and replica status—even if the server is just a master or just a replica:

mysql> SHOW MASTER STATUS\G

mysql> SHOW SLAVE STATUS\G

Issue both statements and ignore any errors you get, so your script gets all the information possible.

Dumping data

Your best options are to use mysqldump, mydumper, or SELECT INTO OUTFILE.

Copying data

Use one of the methods we showed throughout the chapter.

These are the building blocks of any backup script. The hard part is to script the management and recovery tasks. If you want inspiration for how to do this, you can take a look at the source code for ZRM.

Summary

Everyone knows that they need backups, but not everyone realizes that they need recoverable backups. There are many ways to design backups that contradict your recovery requirements. To help avoid this problem, we suggest that you define and document your recovery point objective and your recovery time objective, and use those requirements when choosing a backup system.

It’s also important to test recovery on a routine basis and ensure that it works. It’s easy to set up mysqldump and let it run every night, without realizing that your data has grown over time to the point where it might take days or weeks to import again. The worst time to find out how long your recovery will take is when you actually need it. A backup that completes in hours can literally take weeks to restore, depending on your hardware, schema, indexes, and data.

Don’t fall into the trap of thinking that a replica is a backup. It’s a less intrusive source for taking a backup, but it’s not a backup. The same is true of your RAID volume, your SAN, and filesystem snapshots. Make sure that your backups can pass the DROP TABLE test (or the “I got hacked” test), as well as the test of losing your datacenter. And if you take backups from a replica, be sure that you verify replication integrity with pt-table-checksum.

Our two favorite ways to take backups are to copy the data from a filesystem or SAN snapshot, or to use Percona XtraBackup. Both techniques let you take nonintrusive binary (raw) backups of your data, which you can then verify by starting a mysqld instance and checking the tables. Sometimes you can even kill two birds with one stone: test recovery every single day by restoring the backup to your development or staging server. You can also dump the data from that instance to create a logical backup. We also like to back up binary logs, and to keep enough generations of backups and binary logs that we can perform recovery or set up a new replica even if the most recent backup is unusable.

There are good commercial backup tools in addition to the open source ones we’ve mentioned, foremost among them MySQL Enterprise Backup. Be careful with “backup” tools that are included with GUI SQL editors, server management tools, and the like. Likewise, be careful with “MySQL backup plugins” from companies who make one-size-fits-all backup tools that claim to support MySQL. You really need a first-class backup tool that’s designed primarily for MySQL, not one that just happens to support MySQL as well as a hundred other things. A lot of backup tool vendors don’t know or acknowledge the impact of practices such as using FLUSH TABLES WITH READ LOCK. The use of this SQL command automatically disqualifies a solution as a “hot” backup in our opinion. If you use only InnoDB tables, you usually don’t need it.