【MySQL运维】使用binlog2sql工具实现数据回滚
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日志内容的查看
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处
#也支持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; #从指定位置开始显示日志
2、mysqlbinlog
该工具是解析binlog的利器,可以同时解析多个binlog,在使用binlog还原时也需要依赖它,包含选项如下:
· -v |-vv:如果不加-v的话只能看到行格式本身但是看不到SQL,-v可以显示出执行过的SQL但是不显示binlog_rows_query_log_events参数所附带的内容,加 -vv则可以打印binlog_rows_query_log_events参数效果
· --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 语句
三、如何正确删除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;
发表评论
暂时没有评论,来抢沙发吧~