基于binlog二进制日志的MySQL恢复笔记-阿里云开发者社区

开发者社区> 数据库> 正文

基于binlog二进制日志的MySQL恢复笔记

简介:

基于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,模拟当做前一天的全备份文件。


然后随便进行了一些操作,如下:

1
<span style="font-size:14px;font-family:'楷体', '楷体_GB2312', SimKai;">INSERT INTO students VALUES(30,'lee',21,'M',2,1);<br>DELETE FROM students where `StuID`=10;<br>update students set age=10 where `StuID`=1;<br></span>

执行后效果如下:

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,如下:

1
<span style="font-size:14px;font-family:'楷体', '楷体_GB2312', SimKai;">mysqlbinlog --start-datetime='2016-07-12 23:51:48' --stop-datetime='2016-07-12 23:53:00' \<br>/usr/local/mariadb/var/mysql-bin.000033 > /root/today.sql<br></span>


step2、编辑这个today.sql,找到刚才的那条DELETE操作,剔除DELETE语句,保存退出。


step3、用全备份backup.sql恢复数据:

1
<span style="font-size:14px;font-family:'楷体', '楷体_GB2312', SimKai;">mysql -e 'source backup.sql;'<br></span>

恢复完的效果如下:

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、继续用二进制日志恢复

1
<span style="font-size:14px;font-family:'楷体', '楷体_GB2312', SimKai;">mysql -e 'source today.sql;'<br></span>


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的数据也恢复了。


至此,我们的恢复就完成了。











本文转自 lirulei90 51CTO博客,原文链接:http://blog.51cto.com/lee90/1825904,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章