How to use Replication and LVM Snapshot to make MySQL real-time online backup recovery?

The combination of snapshot and replication technology ensures that we get a MySQL real-time online backup solution.

When the main library misunderstands, you only need to restore the snapshot on the standby database, and then perform the point-in-time recovery according to the binlog.

The following assumes a scenario:

Master-slave architecture, no delay, a DBA misoperation: drop database

Then, we follow the above scenario for backup and recovery simulation tests.

(1) The main library prepares test data

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


mysql> create table cnfol.t (id int primary key);
Query OK, 0 rows affected (0.02 sec)


mysql> insert into cnfol.t select 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> insert into cnfol.t select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Confirm to the stocking library:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cnfol              |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> select * from cnfol.t;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

(2) Add a global read lock

In the preparation database:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

(3) Create a snapshot of the partition where the standby library is located

[[email protected] ~]# lvcreate --size 1G --snapshot --name backup_mysql /dev/vg/mysql
  Logical volume "backup_mysql" created

[[email protected] ~]

# lvs LV VG Attr LSize Origin Snap% Move Log Copy% Convert backup_mysql vg swi-a- 1.00G mysql 0.00 mysql vg owi-ao 2.00G

(4) Get binary log coordinates

In the preparation database:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      727 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

(5) Unlock

In the preparation database:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

(6) Mount the snapshot

[[email protected] ~]# mount /dev/vg/backup_mysql  /mnt/backup

[[email protected] ~]

# cd /mnt/backup/mysql/data/cnfol/ && ls -alh sum 32K drwx—— 2 mysql dba 4.0K 10-14 09:57 . drwx—— 5 mysql dba 4.0K 10-14 09:57 .. -rw-rw—- 1 mysql dba 61 10-14 09:57 db.opt -rw-rw—- 1 mysql dba 8.4K 10-14 09:57 t.frm -rw-rw—- 1 mysql dba 14 10-14 09:57 t.MYD -rw-rw—- 1 mysql dba 2.0K 10-14 10:06 t.MYI

(7) The main library has an inexperienced DBA misoperation

mysql> drop database cnfol;
Query OK, 1 row affected (0.05 sec)

Record the time at this time: 2013-10-14 10:17:10

The standby database confirms whether the library cnfol exists:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.01 sec)

(8) Backup snapshot

[[email protected] backup]# pwd
/mnt/backup

[[email protected] backup]

# tar -jcv -f /mnt/snapshot/mysql.tar.bz2 *

There are 2 reasons for making backups here.

  • First, expensive IO, because the head needs to run back and forth between the snapshot area and the system area.
  • Second, the space in the snapshot area is insufficient because it is the COW principle.

(9) Delete the snapshot

[[email protected] ~]# umount /mnt/backup

[[email protected] ~]

# lvremove –force /dev/vg/backup_mysql Logical volume “backup_mysql” successfully removed

(10) Format the partition where the standby library is located

[[email protected] ~]$ mysqladmin -uroot -poracle shutdown
131014 10:32:40 mysqld_safe mysqld from pid file /mnt/lvm/mysql/data/localhost.localdomain.pid ended
[1]+  Done                    mysqld_safe

[[email protected] ~]

# umount /mnt/lvm

[[email protected] ~]

# mkfs -t ext3 /dev/vg/mysql

[[email protected] ~]

# mount /dev/vg/mysql /mnt/lvm

[[email protected] ~]

# lvs LV VG Attr LSize Origin Snap% Move Log Copy% Convert mysql vg -wi-ao 2.00G

[[email protected] ~]

# vgs VG #PV #LV #SN Attr VSize VFree vg 4 1 0 wz–n- 3.81G 1.81G

(11) Decompress the snapshot to the partition where the standby library is located

# tar -jxv -f /mnt/snapshot/mysql.tar.bz2 -C /mnt/lvm/

[[email protected] lvm]

# pwd /mnt/lvm

[[email protected] lvm]

# ls lost+found mysql

(12) Start MySQL

(13) Perform point-in-time recovery using binlog

[[email protected] ~]$ mysqlbinlog --stop-datetime="2013-10-14 10:17:10" /mnt/lvm/mysql/data/mysql-bin.000003 | mysql -uroot -poracle

(14) Confirmation data

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cnfol              |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> select * from cnfol.t;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

Now, we have use Replication and LVM Snapshot to realize real-time online backup and recovery.