本站所有文章均为原创,如您有所获益,恳请帮忙点击站内广告支持一下,您的帮助是我持续更新的动力!
  • 首页
  • MySQL
  • 【MySQL运维】binlog日志管理与数据恢复

【MySQL运维】binlog日志管理与数据恢复

发布:TangLu2020-6-20 0:52分类: MySQL 标签: mysql mysqlbinlog

一、binlog内容简单解析

在部署MySQL主从的时候已经知道MySQL的binlog文件记录了数据的每次变动,详细到了哪个主机在哪个时间点执行了哪些语句,有了这些语句就可以对数据进行还原。由于binlog是二进制文件,所以无法使用VI等编辑器直接打开,如果需要查看日志内容需要使用mysqlbinlog工具或者MySQL客户端show binlog events命令。经过解析后的日志格式与内容如下:

binlog.png


二、mysqlbinlog工具介绍

1、mysqlbinlog命令常用选项

-d | --database:从二进制日志文件中过滤出某个库的日记信息

--base64-output=decode-rows:将row格式日志进行解码,否则看到的是乱码,因为rows格式是加密的
--start-datetime=:指定时间节点,同理还有--stop-datetime指定结束时间,但并不包含结束位置的事件
--start-position=:指定位置节点,同理还有--stop-position指定结束位置,但并不包含结束位置的事件

-v|-vv|-vvv:详细的信息更详细


2、mysqlbinlog命令示例

· 显示某binlog或relaylog所有内容,并且对rows格式的日志解码

mysqlbinlog -vvv --base64-output=decode-rows  master.bin.000003  #将rows的日志内容解码,可用于数据恢复时查找正确的操作


· 显示指定日期范围内的binlog或relaylog,并且对rows格式的日志解码

mysqlbinlog -vvv --base64-output=decode-rows --start-datetime='2018-02-13 11:37:00'  --stop-datetime='2018-02-15 12:00:00' master-bin.000001


· 显示指定position范围内的binlog或relaylog,并且对rows格式的日志解码

mysqlbinlog  -vvv --base64-output=decode-rows --start-position=3052 master-bin.000002    #显示从3052开始的所有内容  
mysqlbinlog -vvv --base64-output=decode-rows --start-position=3052 --stop-position=3849 master-bin.000002    #显示3052-3849的内容


· 显示指定某个库的binlog或relaylog,并且对rows格式的日志解码

mysqlbinlog  -vvv --base64-output=decode-rows  --start-datetime='2018-10-12 14:42:00'  --stop-datetime='2018-10-12 14:45:00' --database=test_database mysql-bin.000008 > /tmp/bin.sql


· gtid模式下,导出多个binlog或relaylog文件中的数据

mysqlbinlog --include-gtids='yourgtid:1-100' mysql-bin.00001 mysql-bin.00002 mysql-bin.00003 mysql-bin.00004 > /tmp/gtid.sql  #假设101的GTID为drop table,就截取到100即可


3、mysqlbinlog命令结果

建议设置 binlog_rows_query_log_events 参数,用于输出原生的 DML 语句,如果不设置则不能显示 SQL 语句

mysqlbinlog.png


三、使用show binlog events查看事件,同理也有show relaylog events命令适用于relaylog

mysql > show master logs;    #显示当前主库所有二进制日志情况
mysql > show binary logs;    #和上面作用一样
mysql > flush logs;    #刷新并生成新的二进制日志文件
mysql > show binlog | relaylog events in 'master-bin.000002';  #显示二进制日志的内容
mysql > show binlog | relaylog events in 'master-bin.000002' limit5;    #只显示5条
mysql > show binlog | relaylog events in 'master-bin.000002' from 3390 limit5;    #从3390位置开始显示5条
mysql > purge master logs to 'master-bin.000002';  #删除指定的二进制日志
mysql > purge master logs before '2018-02-17 10:30:00';

使用show binlog event命令时关注Event_type为Query的语句,这些就是引起数据库内容变化的SQL,Pos和End_log_pos字段内容是语句的开始和结束位置,具体的SQL在Info字段中。由于每个DDL本身就是一个事件,所以能直接在Info中看到,而DML语句都是由BEGIN开头,COMMIT结束,所以Pos信息应该以Begin为准,End_log_pos则是看COMMIT的。

企业微信截图_20181012115349.png


四、删除binlog日志的正确方法

由于二进制日志会随着数据库操作的增多而不断增长,在一段时间后需要删除一些无用的二进制日志,由于直接rm删除可能会破坏binlog文件索引文件,所以需要用以下方式进行清除

· reset master虽然可以清空所有binlog文件,但是会导致从库异常,不建议使用

· 配置expire_logs_days参数实现自动删除,删除的那一刻会有瞬间IO过高问题,可能导致性能抖动

· purge命令(推荐)

mysql > PURGE MASTER LOGS TO 'mysql-bin.000003'  #删除binlog到指定的文件为止
mysql > PURGE MASTER LOGS BEFORE '2020-12-21 00:00:00'  #删除指定日期之前的文件


五、使用binlog进行数据恢复

如果不小心删错了库或表,可以使用历史备份与备份之后的binlog日志进行数据的恢复。建议找一台机器恢复数据后再导出到生产环境中

· 模拟事故过程

1、建立一个数据库,并在其中创建一张表并插入数据

mysql > create database student;
mysql > use student;
mysql > create table student(id int,name varchar(10),age int);
mysql > insert into student values ('1','tanglu','28'),('2','zouxiaolu','28'),('3','beibei','28');


2、进行完整的备份,这样之前的数据已经有了备份

mysqldump -u root -p  --master-data=2 student > student.sql


3、再次插入三条数据
mysql> insert into student values('4','doudou','8');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values('5','mengmeng','28');      
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values('6','haha','10');
Query OK, 1 row affected (0.01 sec)


4、模拟误操作,删除之前表格中的所有数据

mysql> delete from student ;


· 恢复数据过程

1、重启数据库或者执行flush-logs来获得一个新的二进制日志文件,这样故障操作日志都在之前的binlog文件里

mysql > flush logs;


2、用完整备份恢复数据,这样前面3条数据就恢复成功
mysql -u root -p student < student.sql

3、由于备份开启了master-data=2,所以通过备份文件中的change master信息可以查找出binlog的起始点,假设为355。然后分析binlog或者show events in语句查找误操作的SQL的起始点,通常误操作都在最后一个binlog文件中去查找。这里假设误操作的起始点为1294,所以只需要导出这之前的语句用于恢复

mysqlbinlog --database=linuxe --start-positon=355 --stop-position=1294 linuxe-binlog.000003 > server2.sql
#  mysqlbinlog --database=linuxe --start-datetime="" --stop-datetime="" mysql-bin.000001 mysql-bin.000002 mysql-bin.000003  > xxxx.sql #以时间为维度一次性从多个binlog中恢复数据的办法,如果同一秒有多个的话需要将datetime改为position

4、恢复数据

mysql > set sql_log_bin=0  #临时关闭binlog,避免导入时生成重复binlog
mysql > source /tmp/binlog.sql


温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
版权所有:《Linux运维技术学习站点
文章标题:《【MySQL运维】binlog日志管理与数据恢复
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-393.html  [生成短网址]
et_highlighter51

已有 0/3773 人参与

发表评论:

欢迎分享Linux运维技术学习站点

欢迎使用手机扫描访问本站,还可以关注微信哦~