Backup and Recovery - High Performance MySQL - page 654

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