Backup and Recovery - High Performance MySQL - page 653

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 pro-
cesses for your needs. Remember to script and rehearse anything you think you'll need
to do during recovery.