backup and point in time recovery with binary logs

MySQL backup and point in time recovery with binary logs

Backing up MySQL data is very important if you run MySQL on any kind of production server. We’ll explore some basic concepts which allows you to do that.
We’re interested in two types of backup: full and incremental.

Full backup can be saved as a collection of queries which can restore a database (logical), or raw copy of directories which contains MySQL data (physical). For large databases (>10GB), you’ll probably want to use the latter option. You can do that by using tools like mysqlhotcopy for MyISAM and InnoDB Hot Backup or Xtrabackup for InnoDB table. In this example we will make the logical full backup with mysqldump command.

Incremental backup can be done by backing up MySQL binary logs.
Let’s make sure that binary logs are enabled. Edit MySQL’s my.cnf config file and check for this lines:

log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10

First option tells MySQL to enable binary logging and save the logs under /var/log/mysql/ directory. Second option means that all logs older that 10 days will be automatically deleted.

First, from the full backup:

root@test1:~# mysql -u root -p

mysql> create database bk_test;
Query OK, 1 row affected (0.00 sec)

root@test1:~# mysql -u root -p bk_test < full_backup.sql

This is the contents of the table now:

mysql> select * from bk_test_t1;
+----+------------+---------------------+
| id | test_field | time_created        |
+----+------------+---------------------+
|  1 | val1       | 2010-09-05 10:15:35 |
|  2 | val2       | 2010-09-05 10:15:35 |
|  3 | val3       | 2010-09-05 10:15:35 |
+----+------------+---------------------+
3 rows in set (0.00 sec)

Now we need to just apply the changes from the binary log. There is a tool called mysqlbinlog which outputs the contents of the logs in text format. Its result can be piped directly to the mysql command.

root@test1:~# mysqlbinlog mysql-bin.000002 | mysql -uroot -p bk_test

And voila! we have restored the original table:

mysql> select * from bk_test_t1;
+----+------------+---------------------+
| id | test_field | time_created        |
+----+------------+---------------------+
|  1 | val1       | 2010-09-05 10:15:35 |
|  2 | val2       | 2010-09-05 10:15:35 |
|  3 | val3       | 2010-09-05 10:15:35 |
|  4 | val4       | 2010-09-05 10:57:43 |
|  5 | val5       | 2010-09-05 10:57:43 |
|  6 | val6       | 2010-09-05 10:57:44 |
+----+------------+---------------------+
6 rows in set (0.00 sec)

Dates are correctly restored because bin log contains SET TIMESTAMP command to adjust the time before the concrete query.

But, binary logs are much more powerful than that. You can, for example, pipe the result from mysqlbinlog to a file, remove bad queries and restore it.

You can also limit the list of returned queries with start and end time. For example, to restore the database to the state as it was on 2010-09-05 10:55:44, you can use following command:

root@test1:~# mysqlbinlog --stop-datetime="2010-09-05 10:55:44" mysql-bin.000002 | mysql -uroot -p bk_test

To include multiple logs, just include them in command, one after another:

root@test1:~# mysqlbinlog mysql-bin.000002 mysql-bin.000003 | mysql -uroot -p bk_test

You can use –start-datetime and –stop-datetime to locate positions in binary logs, and then use that positions in –start-position and –stop-position params to limit returned queries.

It’s a good idea to save binary logs on some SAN storage, or rsync them periodically to another location, so if something goes wrong, you can first restore data from the last incremental backup, and then restore additional data from those new logs, so minimum amount of data is lost. You can also check sync_binlog mysql parameter, which controls when binary logs are synchronized to the disk.