binlog2sql数据恢复常见错误处理

1. binlog2sql数据恢复

目录

不管你什么架构，一切都是为数据服务的，数据是一个企业最核心的价值。不过出现问题的话，数据一旦被删除，每过一秒，都将对企业造成巨大的损失。最近，还真的发生了一起删库跑路事件。

watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VhZ2xlODk=,size_16,color_FFFFFF,t_70

watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VhZ2xlODk=,size_16,color_FFFFFF,t_70

watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VhZ2xlODk=,size_16,color_FFFFFF,t_70

1.1. 安装binlog2sql报错总结

1.1.1. 常见报错：pymysql.err.OperationalError: (1045, u'Unknown error 1045')

format,png

解决：不能用root用户，改成普通用户就可以了

1.1.2. 常见报错：ValueError: parameter error: start_file /opt/mysql-bin.000007 not in mysql server

format,png
解决：复制mysql-binlog.index和对应的binlog到测试环境数据库，注意测试环境数据库binlog要指定到生产环境复制过来的binlog位置
比如测试库里面的binglog文件为

[root@centos7 binary]# ll
mybinlog.000029
mybinlog.000030
mybinlog.000031

[root@centos7 binary]# cat mybinlog.index
/data/mysql/logs3307/binary/mybinlog.000029
/data/mysql/logs3307/binary/mybinlog.000030
/data/mysql/logs3307/binary/mybinlog.000031

将生产环境的binglog传到当前目录下，并将名称改为当前binglog中的一个，不要改最新的binglog，
比如

mv mybinlog.000029 mybinlog.000029_bak
mv 生产的binglig mybinlog.000029

并且要创建服务器上一样的表结构和数据库

create database crushzhengqi;
create table sys_staff;

然后执行分析

/opt/dbtools/python /opt/dbtools/binlog2sql/binlog2sql.py -h127.0.0.1 -P3307 -uroot -p'root' -dcrushzhengqi -t sys_staff --start-file='mybinlog.000029' > old2.sql

1.1.3. 常见报错：AttributeError: exit

format,png

解决：binlog2sql文件有问题

1.1.4. 常见报错：binlog2sql.py: error: argument -p/--password: expected one argument

format,png

解决：在-p参数后加上密码

UnicodeDecodeError: 'ascii' codec can't decode byte 0xe9 报错解决方法

.py文件中添加如下内容

import sys
defaultencoding = 'utf-8'
if sys.getdefaultencoding() != defaultencoding:
    reload(sys)
    sys.setdefaultencoding(defaultencoding)

pymysql.err.InternalError: (1236, u"Cannot replicate GTID-transaction when @@GLOBAL.GTID_MODE = OFF, at file ./testdb1-bin.000001, position 234.; the first event 'testdb1-bin.000001' at 4, the last event read from './testdb1-

解决办法：
set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

解析完之后，记得把GTID_MOD 关掉
set @@GLOBAL.GTID_MODE = OFF;

1.2. binlog-rollback.pl工具介绍

基于row模式的binlog，生成DML(insert/update/delete)的rollback语句

操作流程

#通过mysqlbinlog -v 解析binlog生成可读的sql文件
#提取需要处理的有效sql
#"### "开头的行.如果输入的start-position位于某个event group中间，则会导致"无法识别event"错误
#将INSERT/UPDATE/DELETE 的sql反转,并且1个完整sql只能占1行
#INSERT: INSERT INTO => DELETE FROM, SET => WHERE
#UPDATE: WHERE => SET, SET => WHERE
#DELETE: DELETE FROM => INSERT INTO, WHERE => SET
#用列名替换位置@{1,2,3}
#通过desc table获得列顺序及对应的列名
#特殊列类型value做特别处理
#逆序

注意事项：

#表结构与现在的表结构必须相同[谨记]
#由于row模式是幂等的，并且恢复是一次性，所以只提取sql，不提取BEGIN/COMMIT
#只能对INSERT/UPDATE/DELETE进行处理
#线上误操作后，一定要立刻对操作的表进行加锁，避免其他数据继续写入

1.2.1. binlog格式介绍

MYSQL binlog复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

1.2.2. binlog格式

一个完整的binlog文件是由一个format description event开头，一个rotate event结尾，中间由多个其他event组合而成。
640a302f
每个event都是由event header 和event data组成。下面简单介绍下几种常见的binlog event。

① formart description event
01f23313
表达的含义是：

170905 01:59:33 server id 10 end_log_pos 123 CRC32 0xed1ec563
Start: binlog v 4, server v 5.7.18-log created 170905 01:59:33
② table map event

问题二：python 连接 mysql 数据库出现 keyerror： 255

Traceback (most recent call last):
  File "E:\IdeaProjects\binlog2sql\binlog2sql\binlog2sql.py", line 145, in <module>
    binlog2sql = Binlog2sql(connection_settings=conn_setting, start_file=args.start_file, start_pos=args.start_pos,
  File "E:\IdeaProjects\binlog2sql\binlog2sql\binlog2sql.py", line 46, in __init__
    self.connection = pymysql.connect(**self.conn_setting)
  File "E:\Program Files\PYTHON3.9.5\lib\site-packages\pymysql\__init__.py", line 90, in Connect
    return Connection(*args, **kwargs)
  File "E:\Program Files\PYTHON3.9.5\lib\site-packages\pymysql\connections.py", line 706, in __init__
    self.connect()
  File "E:\Program Files\PYTHON3.9.5\lib\site-packages\pymysql\connections.py", line 931, in connect
    self._get_server_information()
  File "E:\Program Files\PYTHON3.9.5\lib\site-packages\pymysql\connections.py", line 1269, in _get_server_information
    self.server_charset = charset_by_id(lang).name
  File "E:\Program Files\PYTHON3.9.5\lib\site-packages\pymysql\charset.py", line 38, in by_id
    return self._by_id[id]
KeyError: 255

该问题主要原因是MySQL8.0更新了很多字符集，但是这些字符集长度超过255了，所以旧版的PyMySQL不支持长度超过255的字符。解决方法是更新该工具包：

pip install --upgrade PyMySQL

问题三：ModuleNotFoundError: No module named 'pymysql.util'

这是因为没有'pymysql ，这里我们防止本地有包冲突，所以先卸载再安装：

PS E:\Program Files (x86)\mysql-8.0.23-winx64\data> pip uninstall pymsql
WARNING: Skipping pymsql as it is not installed.
PS E:\Program Files (x86)\mysql-8.0.23-winx64\data> pip install pymysql==0.9.3
Collecting pymysql==0.9.3
  Downloading PyMySQL-0.9.3-py2.py3-none-any.whl (47 kB)
     ---------------------------------------- 47.7/47.7 KB 480.7 kB/s eta 0:00:00
Installing collected packages: pymysql
  Attempting uninstall: pymysql
    Found existing installation: PyMySQL 1.0.2
    Uninstalling PyMySQL-1.0.2:
      Successfully uninstalled PyMySQL-1.0.2
Successfully installed pymysql-0.9.3

问题四：UnicodeDecodeError: 'utf-8' codec can't decode byte 0xbb in position 39: invalid start byte

E:\Program Files\PYTHON3.9.5\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 1")

  result = self._query(query)

Traceback (most recent call last):

  File "E:\IdeaProjects\binlog2sql\binlog2sql\binlog2sql.py", line 150, in <module>

    binlog2sql.process_binlog()

  File "E:\IdeaProjects\binlog2sql\binlog2sql\binlog2sql.py", line 121, in process_binlog

    self.print_rollback_sql(filename=tmp_file)

  File "E:\IdeaProjects\binlog2sql\binlog2sql\binlog2sql.py", line 129, in print_rollback_sql

    for line in reversed_lines(f_tmp):

  File "E:\IdeaProjects\binlog2sql\binlog2sql\binlog2sql_util.py", line 249, in reversed_lines

    block = block.decode("utf-8")

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xbb in position 39: invalid start byte

根据报错信息可以知道，这个跟binlog2sql工具里的binlog2sql.py代码有关，网上有好几种解决方案，这里贴一种我使用没问题的方案：即修改binlog2sql源码中的binlog2sql_util.py指定字符编码的代码（注意有好几行都涉及到编码，但是我们不用全修改，仔细对照下图，千万别改错了），修改的内容如下图：
vim binlog2sql_util.py

常见错误

闪回sql中文乱码问题：

用binlog2sql时，解析出原sql时，sql中的中文正常显示，编码格式也正常
但是解析成回滚sql的时候，中文显示为乱码

经过测试该问题出现在mysql8.0的版本中，
建议回滚sql使用my2sql工具解析，性能更好

posted @ 2026-05-21 10:05  数据库小白（专注）  阅读(13)  评论(0)    收藏  举报