Installation, use and problem summary of binlog2sql tool

1、 Environmental preparation ( Version is the version I installed , For reference )：

        Git
        Python 3.9.5
        Pip 22.0.4

2、 download binlog2sql Source code ：

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

3、 Dependencies required by the installation tool ：

       cd binlog2sql  // Enter the downloaded source directory

       pip install -r requirements.txt

 4、 Use demo

binlog2sql Usually in mysql Use... When rolling back , This article mainly introduces the installation and troubleshooting of the tool , If you want to know the specific rollback process , You can refer to this article ：https://blog.csdn.net/Interest1_wyt/article/details/115028429

Generate rollback sql command 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

More usage can refer to binlog2sql In the source readme.md file

5、 Summary of problems encountered in installation and use

Question 1 ：Access denied for user 'root'@'localhost' (using password:YES)

This problem is because of my root The account can only be accessed locally , So it needs to be modified here root The account can be accessed remotely ：

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

flush privileges;

notes ： If you use another account , Be sure to use mysql Account information should have SELECT, REPLICATION SLAVE, REPLICATION CLIENT Such as permissions

Question two ：python Connect mysql The database appears 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

The main reason for this problem is MySQL8.0 Updated a lot of character sets , But these character sets are longer than 255 了 , So the old version PyMySQL Length greater than... Is not supported 255 The characters of . The solution is to update the toolkit ：

pip install --upgrade PyMySQL

Question 3 ：ModuleNotFoundError: No module named 'pymysql.util'

This is because there is no 'pymysql , Here we prevent local packet conflicts , So uninstall first and then install ：

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

Question 4 ：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

According to the error information , This heel binlog2sql In the tool binlog2sql.py The code is about , There are several solutions online , Here's a solution that I can use ： I.e. modification binlog2sql In the source binlog2sql_util.py The code that specifies the character encoding （ Notice that there are several lines that involve coding , But we don't have to revise it all , Carefully compare with the figure below , Don't make a mistake ）, The revised contents are as follows ：

Reference link ：

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

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