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.
