自适应网站开发资源,云南网站建设专业品牌,高端手表品牌排行榜前十名,建筑工程网络计划编制软件基于binlog二进制日志的MySQL恢复笔记刚好复习到这里#xff0c;顺手做个小实验#xff0c;记录下。总的操作流程#xff1a;step0、关掉数据库的对外访问【防止用户操作继续写入这个库】step1、mysqlbinlog 导出相关时间段数据库的二进制日志step2、编辑today.sql找到误操作…基于binlog二进制日志的MySQL恢复笔记刚好复习到这里顺手做个小实验记录下。总的操作流程step0、关掉数据库的对外访问【防止用户操作继续写入这个库】step1、mysqlbinlog 导出相关时间段数据库的二进制日志step2、编辑today.sql找到误操作的那几条数据删除并保存。step3、执行全备份恢复 mysql -e source /root/backup.sql;step4、用二进制日志恢复今天的修改 mysql -e source /root/today.sql;step5、登录mysql验证数据是否回来了。原始数据库某表的内容如下MariaDB [hellodb] select * from students;-------------------------------------------------------| StuID | Name | Age | Gender | ClassID | TeacherID |-------------------------------------------------------| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL |-------------------------------------------------------备份了下数据备份为backup.sql模拟当做前一天的全备份文件。然后随便进行了一些操作如下INSERT INTO students VALUES(30,lee,21,M,2,1);DELETE FROM students where StuID10;update students set age10 where StuID1;执行后效果如下MariaDB [hellodb] select * from students;-------------------------------------------------------| StuID | Name | Age | Gender | ClassID | TeacherID |-------------------------------------------------------| 1 | Shi Zhongyu | 10 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL || 30 | lee | 21 | M | 2 | 1 |-------------------------------------------------------假如突然DBA发现刚才的DELETE不应该执行必需要恢复这条数据。step0、这时候首先我们要关掉数据库对外访问的权限防止有用户写入数据干扰恢复操作。(可以修改前端的web连接数据库的文件将其改到其它从节点虽然用户无法写但最起码比网站无法访问要强些)step1、先提取出误操作这段时间的二进制日志取名为today.sql如下mysqlbinlog --start-datetime2016-07-12 23:51:48 --stop-datetime2016-07-12 23:53:00 \/usr/local/mariadb/var/mysql-bin.000033 /root/today.sqlstep2、编辑这个today.sql找到刚才的那条DELETE操作剔除DELETE语句保存退出。step3、用全备份backup.sql恢复数据mysql -e source backup.sql;恢复完的效果如下MariaDB [hellodb] select * from students;-------------------------------------------------------| StuID | Name | Age | Gender | ClassID | TeacherID |-------------------------------------------------------| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL |-------------------------------------------------------15 rows in set (0.00 sec)可以看到第一天数据又恢复了最原始的状态第10条数据又恢复回来了但是INSERT的那条数据却没有了因此我们还要使用二进制日志继续恢复。step4、继续用二进制日志恢复mysql -e source today.sql;step5、查看恢复后的结果恢复完的效果如下MariaDB [hellodb] select * from students;-------------------------------------------------------| StuID | Name | Age | Gender | ClassID | TeacherID |-------------------------------------------------------| 1 | Shi Zhongyu | 10 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL || 30 | lee | 21 | M | 2 | 1 |-------------------------------------------------------16 rows in set (0.00 sec)可以看到第一条数据的Age已经改为我们要求的值了INSERT的最后一条数据也恢复回来了 误删除的StuID为10的数据也恢复了。至此我们的恢复就完成了。
