Databases Reference
In-Depth Information
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 defini-
tion 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 state-
ments, 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.
