快照和复制技术的结合可以保证我们得到一个实时的在线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: 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)
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/mysql
- Logical volume "backup_mysql" created
- [root@localhost ~]# 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.获取二进制日志坐标
在备库:
- 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)
5.挂载快照
- [root@localhost ~]# mount /dev/vg/backup_mysql /mnt/backup
- [root@localhost ~]# 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
6.主库某无经验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)
7.备份快照
- [root@localhost backup]# pwd
- /mnt/backup
- [root@localhost backup]# tar -jcv -f /mnt/snapshot/mysql.tar.bz2 *
这里做备份的原因有2点 * 其一,昂贵的IO,因为磁头要在快照区和系统区来回跑 * 其二,快照区空间不足,因为是COW原理
8.删除快照
- [root@localhost ~]# umount /mnt/backup
- [root@localhost ~]# lvremove --force /dev/vg/backup_mysql
- Logical volume "backup_mysql" successfully removed
9.格式化备库所在分区
- [mysql@localhost ~]$ 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
- [root@localhost ~]# umount /mnt/lvm
- [root@localhost ~]# mkfs -t ext3 /dev/vg/mysql
- [root@localhost ~]# mount /dev/vg/mysql /mnt/lvm
- [root@localhost ~]# lvs
- LV VG Attr LSize Origin snap% Move Log Copy% Convert
- mysql vg -wi-ao 2.00G
- [root@localhost ~]# vgs
- VG #PV #LV #SN Attr VSize VFree
- vg 4 1 0 wz--n- 3.81G 1.81G
10.解压缩快照到备库所在分区
- # tar -jxv -f /mnt/snapshot/mysql.tar.bz2 -C /mnt/lvm/
- [root@localhost lvm]# pwd
- /mnt/lvm
- [root@localhost lvm]# ls
- lost+found mysql
11.启动MySQL
12.利用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
13.确认数据
- 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)
在大三的时候,一直就想搭建属于自己的一个博客,但由于各种原因,最终都不了了...
首先到这里下载其源码。里面东西挺多的,我们基本上可以把它放到两个文件夹就是...
本文实例为大家分享了javascript实现倒计时提示框的具体代码,供大家参考,具体...
今天看到个不错的网页播放器,感觉不错,大家可以测试 我写的一个播放器网页: ...
MFC项目在vs2017编译正常无报错,但是升级vs2019后一打开项目就报如下错误。 项...
由于固态驱动器(SSD)的速度比传统的硬盘驱动器(HDD)快得多,并且价格越来越便宜...
本文实例为大家分享了vue实现按钮切换图片的具体代码,供大家参考,具体内容如下...
这5个PHP编程中的不良习惯,一定要改掉 PHP世界上最好的语言! 测试循环前数组是...
目录 1. C语言文件接口(库函数) 1.1 fopen 1.2 fclose 1.3 fread 1.4 fwrite 1.5...
目录 读者基础 ?微服务架构梳理 https://www.coder4.com/homs_online/ ? ? 读者...