【MySQL运维】binlog的查看、删除与数据恢复

tanglu 7305 2020-06-20

一、MySQL binlog介绍

MySQL的binlog文件记录了数据的每次变动,事务执行过程中会先把日志写到binlog cache中,等事务提交的时候再把cache写到binlog文件,最后fsync落盘并清空cache(redolog也有着同样的落盘机制,所以也叫做双写)。一个事务的 binlog不能被拆开,不论事务多大也要确保一次性写入binlog。每个线程都会申请一块binlog cache,内存空间大小由参数 binlog_cache_size控制。如果超过参数设置的大小就要暂存到磁盘。而write和fsync的时机由参数sync_binlog决定(双1参数之1)可以详细到哪个时间点执行了哪些语句,有了这些语句就可以对数据进行还原。binlog格式分为了三种:

1、statement(标准模式,简称SBR)

保存的是每一条修改数据的SQL语句本身,然后在从服务器上会执行相同的语句来同步数据。日志记录量比较少,但是对于一些执行结果不明确的语句可能会造成主从数据不一致


2、row(行模式,简称RBR)

最安全的复制模式,将数据的具体改变记录在日志中,记录的日志量大。row不会因为某些会产生随机数的SQL而让主从数据不一致(比如包含了时间函数的语句),但是如果有对BLOB这样的大字段进行操作,row会比statement更耗时,因为row会记录BLOB字段的具体值,而不单单是生成数据的语句。row格式的二进制日志无法直接阅读,需要通过mysqlbinlog --base64-output=decode-rows转码


3、mixed(混合模式,简称MBR)

statement和row的结合模式,当基于statement的语句无法精确复制时,就自动采用行模式进行复制

delete * from test; #假设这条SQL语句删除了100万条数据
# STATEMENT语句模式仅记录这一条SQL语句
# ROW行模式会记录执行删除时的每一个语句,会记录100万次


由于binlog是二进制文件,必须使用mysqlbinlog工具或者登录MySQL客户端后使用show binlog events命令才可查看,不能使用vi等文本工具进行查看和修改。每个Binlog开头都会有4个字节的魔术数,所以对Binlog进行解析的时候都是从#at4 开始的,经过解析后的日志内容如下图:

binlog.png


二、binlog日志内容的查看

1、show binlog events

使用show binlog event命令可以比较快速的看到每个position的大概操作,在查看的时候Event_type为Query的语句就是引起数据库内容变化的SQL,Pos和End_log_pos字段内容是语句的开始和结束位置,具体的SQL在Info字段中。由于每个DDL本身就是一个事件,所以能直接在Info中看到,而DML语句都是由BEGIN开头,COMMIT结束,所以Pos信息应该以Begin为准,End_log_pos则是看COMMIT的。如下图1596处的Position执行了truncate语句,要使用该binlog进行数据恢复的话就需要恢复到1512处

企业微信截图_20210813092635.png

#也支持show relaylog events命令查看relaylog
mysql > show binlog | relaylog events in 'master-bin.000002';                     #显示指定二进制日志内容
mysql > show binlog | relaylog events in 'master-bin.000002' limit5;              #显示N条指定二进制日志内容
mysql > show binlog | relaylog events in 'master-bin.000002' from 3390 limit5;    #从指定位置开始显示日志


企业微信截图_20181012115349.png


2、mysqlbinlog

该工具是解析binlog的利器,可以同时解析多个binlog,在使用binlog还原时也需要依赖它,包含选项如下:

· -v |-vv如果不加-v的话只能看到行格式本身但是看不到SQL,-v可以显示出执行过的SQL但是不显示binlog_rows_query_log_events参数所附带的内容,加 -vv则可以打印binlog_rows_query_log_events参数效果

mysqlbinlog_20211227.png

· --base64-output=decode-rows:如果binlog为row格式必须使用该选项对日志进行解析,恢复数据时不能加该选项

· --include-gtids:只解析指定的 GTID 的事务,比如--include-gtids='143c2270-3b22-11ea-9dea-14feb5dc2c77:2-8'就是2-8的事务

· --exclude-gtids:不解析指定的 GTID 的事务

· --start-datetime | --stop-datetime:指定时间节点,需要注意的是--stop-datetime所指定的时间节点并不包含该时间执行的事务,而是前一个事务,在进行还原的时候需要注意正确的位置

· --start-position | --stop-position:指定binlog位置节点,需要注意的是--stop-position所指定的位置节点并不包含该位置本身,而是前一个事务,在进行还原的时候需要注意正确的位置

· --skip-gtids=true:是否保留GTID信息,如果为true表示恢复数据时将这些事务看作新事务,会生成新的GTID。常用于目标数据库和源数据库有相同GTID信息的情况,需要指定为true跳过这些GTID,否则会因为这些GTID已经在源数据库执行过了而不会执行(根据GTID的特性,同一个GTID只能执行一次)。如果是恢复到其他实例并且不包含源实例的 GTID 信息,那么使用或者不使用都可以恢复成功

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


mysqlbinlog命令示例

· 解析指定binlog或relaylog所有内容

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


· 解析指定日期范围内的binlog或relaylog

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 > /tmp/master.bin.000001.txt


· 解析指定position范围内的binlog或relaylog

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 > /tmp/gtid.sql  #假设101的GTID为drop table,就截取到100即可


mysqlbinlog命令结果

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

mysqlbinlog.png


三、如何正确删除binlog日志

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

· reset master命令:虽然可以清空所有binlog文件,但是会导致从库异常,主从架构下无法使用

· expire_logs_days变量:通过该变量可以指定自动删除日期,如果日志过多,在删除时会有IO过高问题,可能导致性能抖动

· purge命令:推荐方法,可以快速删除指定binlog

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、通过备份文件中的change master信息可以查找出全备时刻position位点,这里假设为355。通过mysqlbinlog或者show events 命令查找出误操作SQL的位点或GTID信息(通常误操作都在最后一个binlog文件中)。假设误操作的起始点为1294,所以只需要导出1294之前的语句用于恢复即可,通常是恢复到上一个事务COMMIT之后的位置。需要注意的是在用binlog进行数据恢复时,不能用mysqlbinlog工具解析出日志然后生成SQL文件来直接执行,因为有些语句的执行结果是依赖上下文的,直接执行的结果很可能是错误的。标准做法是用mysqlbinlog工具解析后通过管道传给MySQL执行,如果存在多个二进制日志,不建议一个一个恢复,而是一起导入。如果分两次操作就是开启两个session,如果刚好用到一个临时表,一个session退出,另一个session就会出错。示例如下: 

mysqlbinlog --database=linuxe --start-positon=355 --stop-position=1294 mysql-binlog.000003 | mysql -uroot -p123456;
版权声明
本站所有文章均为原创,转载请注明出处!小站维护不易,如果对您有所帮助,希望能点击一下站内广告,谢谢!
上一篇:【MySQL运维】记一次线上主从延迟故障
下一篇:使用ifconfig命令为网卡绑定多个IP(VIP)
相关文章

 发表评论

暂时没有评论,来抢沙发吧~

微信二维码