binlog2sql 工具安装使用及问题汇总

1、环境准备(版本是我安装的版本，供参考)：

        Git
        Python 3.9.5
        Pip 22.0.4

2、下载binlog2sql源码：

       git clone https://github.com/danfengcao/binlog2sql.git

3、安装工具需要的依赖：

       cd binlog2sql  //进入下载的源码目录里

       pip install -r requirements.txt

 4、使用demo

binlog2sql一般是在mysql回滚的时候使用，本篇文章主要介绍该工具的安装和排错，如果想了解具体的回滚流程，可以参考这篇文章：https://blog.csdn.net/Interest1_wyt/article/details/115028429

生成回滚sql命令demo:

python E:\IdeaProjects\binlog2sql\binlog2sql\binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'123456' -d test -t t1  --start-file E:\Program Files (x86)\mysql-8.0.23-winx64\data\binlog.000069 --start-position 1105 --stop-position 1343 -B

更多用法可以参考binlog2sql源码中的readme.md文件

5、安装使用中遇到的问题汇总

问题一：Access denied for user 'root'@'localhost' (using password:YES)

这个问题是因为我的root账户只能本地访问，所以这里需要修改下root账户可以远程访问：

update user set host = '%' where user = 'root';

flush privileges;

注：如果使用其它账户，要确保使用的mysql账户信息要有SELECT, REPLICATION SLAVE, REPLICATION CLIENT等权限

问题二：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指定字符编码的代码（注意有好几行都涉及到编码，但是我们不用全修改，仔细对照下图，千万别改错了），修改的内容如下图：

参考链接：

https://blog.csdn.net/Evan_Hsu/article/details/80182679

https://blog.csdn.net/weixin_43288999/article/details/119104027