mysql

关于binlog的知识点参考其他文章。

从binlog中提取数据前提:

  • 配置中打开了binlog
  • 产生错误语句的binlog日志文件并没有被清理,不管是人为还是系统自动清理(到期或过大),必须完整
  • 合适的binlog格式

binlog日志可以选择三种模式,分别是 STATEMENT、 ROW、 MIXED,下面简单介绍下这三种模式:

  • STATEMENT:基于SQL语句的复制,每一条会修改数据的sql语句会记录到binlog中。该模式下产生的binlog日志量会比较少,但可能导致主从数据不一致。
  • Mixed:混合模式复制,以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。
  • ROW:基于行的复制,不记录每一条具体执行的SQL语句,仅需记录哪条数据被修改了,以及修改前后的样子。该模式下产生的binlog日志量会比较大,但优点是会非常清楚的记录下每一行数据修改的细节,主从复制不会出错。

binlog模式在MySQL 5.7.7之前,默认为 STATEMENT,在之后的版本中,默认为ROW。这里建议采用ROW模式,因为ROW模式更安全,可以清楚记录每行数据修改的细节。

找到相关定位点:

-- 查看bin-log是否开启
show variables like '%log_bin%';

-- 查看所有binlog日志文件列表
show master logs;

-- 刷新日志,此刻开始产生一个新编号的binlog文件
flush logs;

刷新是为了减少影响,实际可以不刷新,不中断mysql服务,只要你好找到相应语句的位置

-- 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值:
show master status; 

--查看binlog模式
show variables like 'binlog_format';

-- 查询日志事件
SHOW BINLOG EVENTS IN 'binlog.000008';
会列出所有,数据量可能比较大

-- 指定位点查询,比如从pos为746开始查询
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746;
-- 指定位点分页查询
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746 LIMIT 0,5

关于找相关日志文件,尽量在产生错误时,立刻来修复,此时一般存在于最新的日志文件里。也可以通过错误的时间来比对binlog日志文件的修改时间来大致确定是哪个日志文件。

ll -h /var/lib/mysql
......
-rw-r----- 1 mysql mysql 1.1G Jan 25 12:53 mysql-log-bin.001582
-rw-r----- 1 mysql mysql 1.1G Jan 25 15:23 mysql-log-bin.001583
-rw-r----- 1 mysql mysql 488M Jan 25 17:40 mysql-log-bin.001584
.....
对于binlog日志的名字可能不同,可查找my.cnf配置查看

对应到某个日志文件后,再找出具体的事件坐标

1.通过导出语句来一一定位

导出全部语句:
mysqlbinlog --set-charset=utf-8 /var/lib/mysql/mysql-log-bin.001583>1583.sql
到本地的1583.sql

开始和结束时间段的:
mysqlbinlog --base64-output=decode-rows -v --database=数据库名称 --start-datetime="2024-01-25 16:00:00" --stop-datetime="2024-01-25 17:00:00" /var/lib/mysql/mysql-log-bin.001583>1583.sql

某个坐标段的:
mysqlbinlog --base64-output=decode-rows -v --database=数据库名称 --start-position=xxx --stop-position=yyy /var/lib/mysql/mysql-log-bin.001583>1583.sql

翻翻记录,查看可能的事件、表、操作等,找到相关定位坐标

# at 1058234925
#240125 15:21:24 server id 2  end_log_pos 1058234990 CRC32 0xb3fb8fee   GTID    last_committed=2801367  sequence_number=2801369 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '30686326-f8ea-11ec-b5a2-00163e010e7a:4525672909'/*!*/;
# at 1058234990
#240125 15:21:24 server id 2  end_log_pos 1058235060 CRC32 0xcb11b702   Query   thread_id=404400754     exec_time=0     error_code=0
SET TIMESTAMP=1706167284/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at 1058235060
#240125 15:21:24 server id 2  end_log_pos 1058235131 CRC32 0x65221c88   Table_map: `gj`.`device_old_datas` mapped to number 121
'/*!*/;
# at 1059116255
#240125 15:21:24 server id 2  end_log_pos 1059116286 CRC32 0x5f665c78   Xid = 1759230262
COMMIT/*!*/;

2.通过SHOW BINLOG EVENTS IN相关日志文件来查找具体到某个表及其事件:

mysql> SHOW BINLOG EVENTS IN 'mysql-log-bin.001583' FROM 1058234925 LIMIT 0,100;
+----------------------+------------+-------------+-----------+-------------+----------------------------------------------------------------------------+
| Log_name             | Pos        | Event_type  | Server_id | End_log_pos | Info                                                                       |
+----------------------+------------+-------------+-----------+-------------+----------------------------------------------------------------------------+
| mysql-log-bin.001583 | 1058234925 | Gtid        |         2 |  1058234990 | SET @@SESSION.GTID_NEXT= '30686326-f8ea-11ec-b5a2-00163e010e7a:4525672909' |
| mysql-log-bin.001583 | 1058234990 | Query       |         2 |  1058235060 | BEGIN                                                                      |
| mysql-log-bin.001583 | 1058235060 | Table_map   |         2 |  1058235131 | table_id: 121 (gj.device_old_datas)                                        |
| mysql-log-bin.001583 | 1058235131 | Delete_rows |         2 |  1058243326 | table_id: 121                                                              |
| mysql-log-bin.001583 | 1058243326 | Delete_rows |         2 |  1058251521 | table_id: 121                                                              |
| mysql-log-bin.001583 | 1058251521 | Delete_rows |         2 |  1058259716 | table_id: 121                                                              |
| mysql-log-bin.001583 | 1058259716 | Delete_rows |         2 |  1058267911 | table_id: 121                                                              |
| mysql-log-bin.001583 | 1058267911 | Delete_rows |         2 |  1058276106 | table_id: 121                                                              |
......
| mysql-log-bin.001583 | 1059103801 | Delete_rows |         2 |  1059111996 | table_id: 121                                                              |
| mysql-log-bin.001583 | 1059111996 | Delete_rows |         2 |  1059116255 | table_id: 121 flags: STMT_END_F                                            |
| mysql-log-bin.001583 | 1059116255 | Xid         |         2 |  1059116286 | COMMIT /* xid=1759230262 */    
......

语句执行起始位置由BEGIN开始:

以及其结束COMMIT提交位置:

如此由它们的pos组成开始和结束定位坐标。

恢复数据:

拿到定位后,即可恢复相关数据:

mysqlbinlog --start-position=xxx --stop-position=yyy --database=zzz /var/lib/mysql/mysql-log-bin.001583 | mysql -uroot -p123456
注意数据库名称,日志地址,mysql数据库用户名和密码是执行

也可以通过时间段来恢复数据:

mysqlbinlog --start-datetime="2024-01-25 16:00:00" --stop-datetime="2024-01-25 17:00:00" --database=zzz /var/lib/mysql/mysql-log-bin.001583 | mysql -uroot -p123456

以上未验证,请自行测试再使用,需要搞清楚相关段数据是否是自己需要的。可能会产生执行错误,对应处理,或使用下面的整理数据进行恢复的方法。

恢复后必须进行数据验证,以免产生纰漏。

整理数据进行恢复:

当上一步有问题时,或更直观的恢复,或处理后再恢复,则可以将原来的binlog中的语句拿出来,整理成自己需要的,执行相关sql语句来恢复。

1.通过上部的导出语句命令,来导出相关日志信息,假设导出到a.txt

使用sed清洗数据

2 提取对应的删除sql

sed -n '/^###/'p a.txt >b.txt

这个命令的作用是将文件a.txt中以###开头的行复制到文件b.txt中。

sed是一个流编辑器 ,-n选项表示关闭默认的输出 ,只输出经过处理的行。

  • ‘/^###/’是一个正则表达式 ,表示匹配以###开头的行。
  • p命令表示输出匹配的行。

a.txt是要处理的文件。
>b.txt表示将输出结果重定向到文件b.txt中。

3 替换“### ”

sed 's/### //g 'b.txt >c.txt

s/### //g是一个替换命令 ,表示将所有的### (三个井号后跟一个空格 )替换为空。

  • s表示执行替换操作。
  • /### /是一个正则表达式 ,表示匹配所有的### 。
  • /g表示全局替换 ,即将所有匹配的字符串都进行替换。

b.txt是要处理的文件。
>c.txt表示将替换后的内容输出到文件c.txt中。

4 删除每行后面的注释

sed 's#/.*#,#g 'c.txt >d.txt

这个命令的作用是将文件c.txt中每一行第一个/及其后面的内容替换为一个逗号 ,并将替换后的内容输出到文件d.txt中。

s#/.*#,#g是一个替换命令 ,表示将每一行第一个/及其后面的内容替换为一个逗号。

  • s表示执行替换操作。
  • /#/是一个正则表达式 ,表示匹配每一行中第一个/。
  • .*表示匹配/后面的任意字符串。
  • /#/,表示替换为一个逗号。
  • /g表示全局替换 ,即将每一行中第一个匹配的字符串都进行替换。

c.txt是要处理的文件。
>d.txt表示将替换后的内容输出到文件d.txt中。

5 DELETE FROM替换为INSERT INTO

sed 's#DELETE FROM#INSERT INTO#g 'd.txt >e.txt

这个命令的作用是将文件d.txt中所有的DELETE FROM替换为INSERT INTO ,然后将替换后的内容输出到文件e.txt中。

s#DELETE FROM#INSERT INTO#g是一个替换命令 ,表示将所有的DELETE FROM替换为INSERT INTO。

  • s表示执行替换操作。
  • #DELETE FROM#是一个正则表达式 ,表示匹配所有的DELETE FROM。
  • #INSERT INTO#表示替换为INSERT INTO。
  • /g表示全局替换 ,即将所有匹配的字符串都进行替换。

d.txt是要处理的文件。
>e.txt表示将替换后的内容输出到文件e.txt中。

6 WHERE替换为SELECT

sed 's#WHERE#SELECT#g 'e.txt >f.txt

这个命令的作用是将文件e.txt中所有的WHERE替换为SELECT ,然后将替换后的内容输出到文件f.txt中。

s#WHERE#SELECT#g是一个替换命令 ,表示将所有的WHERE替换为SELECT。

  • s表示执行替换操作。
  • #WHERE#是一个正则表达式 ,表示匹配所有的WHERE。
  • #SELECT#表示替换为SELECT。
  • /g表示全局替换 ,即将所有匹配的字符串都进行替换。

e.txt是要处理的文件。
>f.txt表示将替换后的内容输出到文件f.txt中。

7 @53 =替换成’,‘ 语句结尾添加’;‘号

sed -r 's#(@53 =.*)(,)#\1;#g 'f.txt >h.txt

这个命令的作用是将文件f.txt中所有的以 @11 =开头、以,结尾的字符串替换为这个字符串加上;,然后将替换后的内容输出到文件h.txt中。

-r选项表示使用扩展正则表达式。
s#(@53 =.*)(,)#\1;#g是一个替换命令 ,表示将所有的以 @11 =开头、以,结尾的字符串替换为这个字符串加上;。

  • s表示执行替换操作。
  • #(@11 =.*)(,)#是一个正则表达式 ,匹配所有以 @11 =开头、以,结尾的字符串。
  • \1;表示将匹配到的字符串替换为匹配到的内容加上;。
  • /g表示全局替换 ,即将所有匹配的字符串都进行替换。

f.txt是要处理的文件。
>h.txt表示将替换后的内容输出到文件h.txt中。

8 生成可执行的sql

sed -r 's#(@.*=)(.*)#\2#g 'h.txt >aa.sql

9 添加commit

sed -i '$a commit;'aa.sql

至此 ,已经从导出binlog文件中清洗除了需要恢复的sql语句 (insert语句 ),可以通过命令或者桌面工具执行sql ,恢复删除的数据了。

10 执行sql

mysql -uroot -p123456 test <aa.sql

第9、10步,可以不操作,拿到aa.sql后,请检查语句是否正确,修缮后再去执行。

作者 赵文明

发表回复