境外注册网站,微分销系统的规范问题,四川外国语大学网站建设,销售网站页面特点mysql利用binlog进行数据恢复最近线上误操作了一个数据#xff0c;由于是直接修改的数据库#xff0c;所有唯一的恢复方式就在mysql的binlog。binlog使用的是ROW模式#xff0c;即受影响的每条记录都会生成一个sql。同时利用了binlog基本配置和格式binlog基本配置binlog需要…mysql利用binlog进行数据恢复最近线上误操作了一个数据由于是直接修改的数据库所有唯一的恢复方式就在mysql的binlog。binlog使用的是ROW模式即受影响的每条记录都会生成一个sql。同时利用了binlog基本配置和格式binlog基本配置binlog需要在mysql的配置文件的mysqld节点中进行配置# 日志中的Serveridserver-id 1# 日志路径log_bin /var/log/mysql/mysql-bin.log# 保存几天的日志expire_logs_days 10# 每个binlog的大小max_binlog_size 1000M#binlgo模式binlog_formatROW# 默认是所有记录可以配置哪些需要记录哪些不记录#binlog_do_db include_database_name#binlog_ignore_db include_database_name查看binlog状态SHOW BINARY LOGS; 查看binlog文件SHOW VARIABLES LIKE %log_bin% 查看日志状态SHOW MASTER STATUS 查看日志文件位置binlog的三种格式1.ROW针对行记录日志每行修改产生一条记录。优点上下文信息比较全恢复某条误操作时可以直接在日志中查找到原文信息对于主从复制支持好。缺点输出非常大如果是Alter语句将产生大量的记录格式如下DELETE FROM back.sys_user WHERE deptid27 AND status1 AND accountadmin AND name张三 AND phone18200000000 AND roleid1 AND createtime2016-01-29 08:49:53 AND sex2 AND emailsn93qq.com AND birthday2017-05-05 00:00:00 AND avatargirl.gif AND version25 AND passwordecfadcde9305f8891bcfe5a1e28c253e AND salt8pgby AND id1 LIMIT 1; #start 4 end 796 time 2018-10-12 17:03:192.STATEMENT针对sql语句的每条语句产生一条记录优点产生的日志量比较小主从版本可以不一致缺点主从有些语句不能支持像自增主键和UUID这种类型的格式如下delete from sys_role;3.MIX结合了两种的优点一般情况下都采用STATEMENT模式对于不支持的语句采用ROW模式转换成sqlmysql自带的mysqlbinlog由于binlog是二进制的所以需要先转换成文本文件一般可以采用Mysql自带的mysqlbinlog转换成文本。mysqlbinlog --no-defaults --base64-outputdecode-rows -d room -v mysql-bin.011012 /root/binlog_2018-10-10参数说明--no-defaults 为了防止报错mysqlbinlog: unknown variable default_character_setutf8mb4--base64-outputdecode-rows 和-v一起使用 进行base64解码其他有很多用来限定范围的参数比如数据库起始时间起始位置等等。这些参数在查找误操作的时候非常有用。binlog的基本块如下# at 417750#181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Querythread_id440109962exec_time0error_code0SET TIMESTAMP1538877038/*!*/;BEGIN# at 417750指明的当前位置相对文件开始的偏移位置这个在mysqlbinlog命令中可以作为--start-position的参数#181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Querythread_id440109962exec_time0error_code0181007 1:50:38指明时间为18年10月7号1:50:38serverid也就是你在配置文件中的配置的end_log_pos 417844这个块在417844结束。thread_id执行的线程idexec_time执行时间error_code错误码SET TIMESTAMP1538877038/!/;BEGIN具体的执行语句一行记录产生的日志如下所示# at 417750#181010 9:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Querythread_id440109962exec_time0error_code0SET TIMESTAMP1539136238/*!*/;BEGIN/*!*/;# at 417844#181010 9:50:38 server id 1630000 end_log_pos 417930 CRC32 0xce36551b Table_map: goods.good_info mapped to number 129411# at 417930#181010 9:50:38 server id 1630000 end_log_pos 418030 CRC32 0x5827674a Update_rows: table id 129411 flags: STMT_END_F### UPDATE goods.good_info### WHERE### 12018:10:07 /* DATE meta0 nullable0 is_null0 */### 29033404 /* INT meta0 nullable0 is_null0 */### 31 /* INT meta0 nullable0 is_null0 */### 48691108 /* INT meta0 nullable0 is_null0 */### 59033404 /* INT meta0 nullable0 is_null0 */### 620 /* LONGINT meta0 nullable0 is_null0 */### 71538877024 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### SET### 12018:10:07 /* DATE meta0 nullable0 is_null0 */### 29033404 /* INT meta0 nullable0 is_null0 */### 31 /* INT meta0 nullable0 is_null0 */### 48691108 /* INT meta0 nullable0 is_null0 */### 59033404 /* INT meta0 nullable0 is_null0 */### 621 /* LONGINT meta0 nullable0 is_null0 */### 71538877024 /* TIMESTAMP(0) meta0 nullable0 is_null0 */# at 418030#181010 9:50:38 server id 1630000 end_log_pos 418061 CRC32 0x468fb30e Xid 212760460521COMMIT/*!*/;# at 418061一行记录产生的日志如上所示。以SET TIMESTAMP1539136238/*!*/;开始以COMMIT/*!*/;结尾。我们可以根据两个at指明的位置来限定范围。注意一条记录开始的SET TIMESTAMP之前的# at 417750和结尾的COMMIT之后的# at 418061利用binlog2sqlbinlog2sql官网介绍从MySQL binlog解析出你要的SQL。根据不同选项你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。基本使用如下python binlog2sql.py -hlocalhost -P3306 -udev -p\* -d room -t room_info --start-filemysql-bin.011012 --start-position 129886892 --stop-position 130917280 rollback.sql具体的使用我就不讲解了github上讲解的十分清楚主要看下很多用来筛选的条件比如起止时间--start-datetime/--stop-datetime表名限定-t数据库限定-d语句限定--sql-type主要说说我遇到的一些问题。mysql的binlog模式这里需要设置为ROW因为ROW模式有原来的信息如果可以直接利用binlog2sql反向生成回滚sql如果是STATEMENT无法生成需要利用的mysql定时备份的文件再去做回滚恢复数据的具体操作因为当时线上执行的是一条update语句没有唯一键索引的。导致有两千多条记录被更新。语句如下update room_info set status1 where status2;根据操作时间先定位对应的binlog文件我记得当时操作的时间大概的是上午9多左右所以去找对应的binlog文件最后修改时间大于9点并且时间最接近的一个文件。使用linux的ll命令查看文件的修改时间。筛选具体的数据库因为一个mysql实例的所有binlog文件是在一个文件中的所以我们先要去除其他不想关的数据库。利用-d参数来指明数据实例。然后在利用开始时间(--start-datetime)和结束时间(--stop-datetime)来进一步筛选mysqlbinlog --no-defaults -v --base64-outputdecode-rows -d room --start-datetime2018-10-10 9:00:00 --stop-datetime2018-10-10 10:00:00 mysql-bin.011012temp.sql压缩取回文件分析zip temp.zip temp.sql sz temp.zip取回文件在本地用文本工具如vscode分析里面有正则匹配根据你改动过的特征比如我有个房间号888888这个不应该被修改你就查看这个房间号的修改记录ROW模式的语句是Where在前set在后。利用正则room_id888888.*show_state1.*AND show_state2很快就能匹配到。我当时的语句影响了两千多条记录你根据找到的语句去找开始的SET TIMESTAMP1539136238的位置之前的at和结尾的COMMIT之后的at。利用binlog2sql生成回滚语句python binlog2sql.py -hlocalhost -P3306 -udev -p* -d room -t room_info -B --start-filemysql-bin.011012 --start-position 129886892 --stop-position 130917280 rollback.sql另外因为我这边是一条update影响多条的情况如果是带唯一键的情况下影响的只有一条记录完全没必要这么麻烦直接利用binlog2sql带上-d和-t参数限定数据库和表然后利用grep来查找直接可以得出对应的sql。mysqlbinlog少了一个限定表和限定语句的功能。比如精确到一张表的Delete语句能减少很多的数据能快速定位。
