首页 1 2 3 4 5 6 7

mysql 在线备份_MySQL实时在线备份恢复方案

点击关注上方“kubernetes中文社区”，

设为“置顶或星标”，第一时间送达干货。

快照和复制技术的结合可以保证我们得到一个实时的在线MySQL备份解决方案

当主库发生误操作时，只需要恢复备库上的快照，然后再根据binlog执行point-in-time的恢复即可

下面假定一个场景：

主从架构，没有延迟，某DBA误操作：drop database

接下来我们按照以上场景进行备份恢复模拟测试

1.主库准备测试数据

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: 0mysql> 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)

2.加个全局读锁在备库

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

3.为备库所在分区创建快照

[root@localhost ~]# lvcreate --size 1G --snapshot --name backup_mysql /dev/vg/mysqlLogical volume "backup_mysql" created[root@localhost ~]# lvsLV VG Attr LSize Origin Snap% Move Log Copy% Convertbackup_mysql vg swi-a- 1.00G mysql 0.00mysql vg owi-ao 2.00G

4.获取二进制日志坐标

解锁在备库：

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

6.挂载快照

[root@localhost ~]# mount /dev/vg/backup_mysql /mnt/backup[root@localhost ~]# cd /mnt/backup/mysql/data/cnfol/ && ls -alh总计 32Kdrwx------ 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.主库某无经验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)

8.备份快照

[root@localhost backup]# pwd/mnt/backup[root@localhost backup]# tar -jcv -f /mnt/snapshot/mysql.tar.bz2 *

这里做备份的原因有2点

* 其一，昂贵的IO，因为磁头要在快照区和系统区来回跑

* 其二，快照区空间不足，因为是COW原理

9.删除快照

[root@localhost ~]# umount /mnt/backup[root@localhost ~]# lvremove --force /dev/vg/backup_mysqlLogical volume "backup_mysql" successfully removed

10.格式化备库所在分区

[mysql@localhost ~]$ mysqladmin -uroot -poracle shutdown131014 10:32:40 mysqld_safe mysqld from pid file /mnt/lvm/mysql/data/localhost.localdomain.pid ended[1]+ Done mysqld_safe[root@localhost ~]# umount /mnt/lvm[root@localhost ~]# mkfs -t ext3 /dev/vg/mysql[root@localhost ~]# mount /dev/vg/mysql /mnt/lvm[root@localhost ~]# lvsLV VG Attr LSize Origin Snap% Move Log Copy% Convertmysql vg -wi-ao 2.00G[root@localhost ~]# vgsVG #PV #LV #SN Attr VSize VFreevg 4 1 0 wz--n- 3.81G 1.81G

11.解压缩快照到备库所在分区

# tar -jxv -f /mnt/snapshot/mysql.tar.bz2 -C /mnt/lvm/[root@localhost lvm]# pwd/mnt/lvm[root@localhost lvm]# lslost+found mysql

12.启动MySQL

13.利用binlog执行point-in-time恢复

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

14.确认数据