Binlog2SQL tool is not working for MySQL 8.0

[ponsureshpandian]

Hi Guys,

The Binlog2sql tool is really great tool for DBA's.This tool is reducing the recovery time.

This tool is worked for both mysql 5.6 & 5.7.At the same I have tested for mysql 8.0.17

I am getting below python error.

MySQL Client Version :

mysql-community-client-8.0.17-1.el7.x86_64

[mysqld]

server_id = 100

log_bin = mysql-bin

max_binlog_size = 1G

binlog_format = row

binlog_row_image = full

Error :

[root@mydbopslabs210 binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p 'R00t@123' -dmydbops -t t2 --start-file ='mysql-bin.000007' Traceback (most recent call last): File "binlog2sql.py", line 149, in back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type) File "binlog2sql.py", line 46, in init self.connection = pymysql.connect(**self.conn_setting) File "/usr/lib/python2.7/site-packages/pymysql/init.py", line 90, in Connect return Connection(*args, **kwargs) File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 706, in init self.connect() File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 931, in connect self._get_server_information() File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1269, in _get_server_information self.server_charset = charset_by_id(lang).name File "/usr/lib/python2.7/site-packages/pymysql/charset.py", line 38, in by_id return self._by_id[id] KeyError: 255

Kindly let me know how to fix this error ?

[ponsureshpandian]

Hi Guys,

I found the issue.If you are using mysql 8.0 need to install below packages on latest version.

1). pymysql - 0.9.3

pip install pymysql (or) pip install pymysql --upgrade

2. mysql-replication - 0.21

pip install mysql-replication

Logs:-

[root@mydbopslabs208 binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p 'R00t@123' -dmydbops -t t2 --start-file='mysql-bin.000003' INSERT INTO mydbops.t2(id) VALUES (4); #start 4 end 452 time 2020-03-10 12:20:40 INSERT INTO mydbops.t2(id) VALUES (4); #start 483 end 779 time 2020-03-10 12:37:56 INSERT INTO mydbops.t2(id) VALUES (5); #start 810 end 1106 time 2020-03-10 12:37:59 INSERT INTO mydbops.t2(id) VALUES (6); #start 1137 end 1433 time 2020-03-10 12:38:01 INSERT INTO mydbops.t2(id) VALUES (7); #start 1464 end 1760 time 2020-03-10 12:38:04

[BongBongBang]

That works! Thx~

[thaygiaoth]

it doesn't work for me ( MySQL 8.0.19)

Traceback (most recent call last): File "/root/binlog2sql/binlog2sql/binlog2sql.py", line 150, in binlog2sql.process_binlog() File "/root/binlog2sql/binlog2sql/binlog2sql.py", line 74, in process_binlog for binlog_event in stream: File "/usr/lib/python2.7/site-packages/pymysqlreplication/binlogstream.py", line 455, in fetchone self.__fail_on_table_metadata_unavailable) File "/usr/lib/python2.7/site-packages/pymysqlreplication/packet.py", line 139, in init fail_on_table_metadata_unavailable=fail_on_table_metadata_unavailable) File "/usr/lib/python2.7/site-packages/pymysqlreplication/row_event.py", line 557, in init self.table = self.packet.read(self.table_length).decode() UnicodeDecodeError: 'ascii' codec can't decode byte 0xe1 in position 1: ordinal not in range(128)

[thaygiaoth]

More info: when using python3

It's not show any insert, delete, update statements and any errors

Tks

[madonhe]

[root@newdba2-node01 binlog2sql]# mysql --version mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)

[root@newdba2-node01 binlog2sql]# python binlog2sql.py -hlocalhost -udba_gary -p'123456' -dtest -t test_demo2 --start-file='mgr-binlog.000021' Traceback (most recent call last): File "binlog2sql.py", line 7, in from pymysqlreplication import BinLogStreamReader File "/usr/lib/python2.7/site-packages/pymysqlreplication/init.py", line 23, in from .binlogstream import BinLogStreamReader File "/usr/lib/python2.7/site-packages/pymysqlreplication/binlogstream.py", line 10, in from .packet import BinLogPacketWrapper File "/usr/lib/python2.7/site-packages/pymysqlreplication/packet.py", line 7, in from pymysqlreplication import constants, event, row_event File "/usr/lib/python2.7/site-packages/pymysqlreplication/row_event.py", line 9, in from pymysql.charset import charset_to_encoding ImportError: cannot import name charset_to_encoding

[madonhe]

[root@newdba2-node01 binlog2sql]# mysql --version mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)

[root@newdba2-node01 binlog2sql]# python binlog2sql.py -hlocalhost -udba_gary -p'123456' -dtest -t test_demo2 --start-file='mgr-binlog.000021' Traceback (most recent call last): File "binlog2sql.py", line 7, in from pymysqlreplication import BinLogStreamReader File "/usr/lib/python2.7/site-packages/pymysqlreplication/init.py", line 23, in from .binlogstream import BinLogStreamReader File "/usr/lib/python2.7/site-packages/pymysqlreplication/binlogstream.py", line 10, in from .packet import BinLogPacketWrapper File "/usr/lib/python2.7/site-packages/pymysqlreplication/packet.py", line 7, in from pymysqlreplication import constants, event, row_event File "/usr/lib/python2.7/site-packages/pymysqlreplication/row_event.py", line 9, in from pymysql.charset import charset_to_encoding ImportError: cannot import name charset_to_encoding

解决： pip uninstall PyMySQL pip install PyMySQL==0.9.3 必须是这个版本，不可以使用0.10.0！

[justbeyourself]

-- pip install PyMySQL==0.9.3 必须是这个版本，不可以使用0.10.0！

it works! TKs

[raybon-lee]

python3 可以 使用 pip3 install -r requirements.txt

PyMySQL==0.9.3
wheel==0.29.0
mysql-replication==0.21

问题解决，看代码像是版本参数不兼容