MySQL實時線上備份恢複方案：Replication+LVM Snapshot【上篇】

快照和複制技術的結合可以保證我們得到一個實時的線上MySQL備份解決方案

當主庫發生誤操作時，隻需要恢複備庫上的快照，然後再根據binlog執行point-in-time的恢複即可

下面假定一個場景：

主從架構，沒有延遲，某DBA誤操作：drop database

接下來我們按照以上場景進行備份恢複模拟測試

⑴ 主庫準備測試資料

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

到備庫确認：

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)

⑵ 加個全局讀鎖

在備庫：

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

⑶ 為備庫所在分區建立快照

[[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

⑷ 擷取二進制日志坐标

在備庫：

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 727 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

⑸ 解鎖

在備庫：

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

⑹ 挂載快照

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

[[email protected] ~]# cd /mnt/backup/mysql/data/cnfol/ && ls -alh

總計 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

⑺ 主庫某無經驗DBA誤操作

mysql> drop database cnfol;

Query OK, 1 row affected (0.05 sec)

記錄下此時時間：2013-10-14 10:17:10

備庫确認是否存在庫cnfol:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

+--------------------+

3 rows in set (0.01 sec)

⑻ 備份快照

[[email protected] backup]# pwd

/mnt/backup

[[email protected] backup]# tar -jcv -f /mnt/snapshot/mysql.tar.bz2 *

這裡做備份的原因有2點

其一，昂貴的IO，因為磁頭要在快照區和系統區來回跑

其二，快照區空間不足，因為是COW原理

⑼ 删除快照

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

[[email protected] ~]# lvremove --force /dev/vg/backup_mysql

Logical volume "backup_mysql" successfully removed

⑽ 格式化備庫所在分區

[[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

⑾ 解壓縮快照到備庫所在分區

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

[[email protected] lvm]# pwd

/mnt/lvm

[[email protected] lvm]# ls

lost+found mysql

⑿ 啟動MySQL

⒀ 利用binlog執行point-in-time恢複

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

⒁ 确認資料

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)

參考資料：

http://vbird.dic.ksu.edu.tw/linux_basic/0420quota.php#lvm_snapshot

http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/

By 迦夜

2013-10-14

Good Luck