search

hcymysql / reverse_sql

Binlog数据恢复，生成反向SQL语句
66 stars 28 forks source link

mysql8.0 之前为默认配置BINLOG_ROW_METADATA = MINIMAL有办法使用吗 #7

Open KongDaTua opened 9 months ago

KongDaTua commented 9 months ago

Processing binlogevents: 0event [00:00, ?event/s]WARNING:root: Setting The Variable Value BINLOG_ROW_METADATA = FULL, BINLOG_ROW_IMAGE = FULL. By Applying this, provide properly mapped column information on UPDATE,DELETE,INSERT.

Traceback (most recent call last): File "reverse_sql_mysql8.py", line 445, in File "reverse_sql_mysql8.py", line 287, in main File "pymysqlreplication/binlogstream.py", line 596, in fetchone File "pymysql/connections.py", line 772, in _read_packet File "pymysql/protocol.py", line 221, in raise_for_error File "pymysql/err.py", line 143, in raise_mysql_exception pymysql.err.OperationalError: (1236, 'Could not open log file')

hcymysql commented 9 months ago

Processing binlogevents: 0event [00:00, ?event/s]WARNING:root: Setting The Variable Value BINLOG_ROW_METADATA = FULL, BINLOG_ROW_IMAGE = FULL. By Applying this, provide properly mapped column information on UPDATE,DELETE,INSERT.

Traceback (most recent call last): File "reverse_sql_mysql8.py", line 445, in File "reverse_sql_mysql8.py", line 287, in main File "pymysqlreplication/binlogstream.py", line 596, in fetchone File "pymysql/connections.py", line 772, in _read_packet File "pymysql/protocol.py", line 221, in raise_for_error File "pymysql/err.py", line 143, in raise_mysql_exception pymysql.err.OperationalError: (1236, 'Could not open log file')

MySQL5.7版本用 https://github.com/hcymysql/reverse_sql/releases/download/reverse_sql_json/reverse_sql_mysql57

MySQL8.0版本用 https://github.com/hcymysql/reverse_sql/releases/download/reverse_sql_json/reverse_sql_mysql8

8.0版本需要开启这两个参数： SET GLOBAL BINLOG_ROW_METADATA = FULL; SET GLOBAL BINLOG_ROW_IMAGE = FULL;

KongDaTua commented 9 months ago

之前没开 现在开能靠binlog文件恢复之前的数据吗

hcymysql commented 9 months ago

之前没开 现在开能靠binlog文件恢复之前的数据吗

只要这两个参数： binlog_format = ROW binlog_row_image = FULL 设置了就行。

KongDaTua commented 9 months ago

binlogfile参数配的是开始文件吧 可以指定截止binlog文件吗

hcymysql commented 9 months ago

binlogfile参数配的是开始文件吧 可以指定截止binlog文件吗

可以指定起始binlog文件名，截止是通过时间。假如你想恢复至今的数据，那就

./reverse_sql -ot table1 -op delete -H 主库IP -P 3336 -u admin -p 123456 -d yourDB \
--binlog-file 你的binlog名字 --start-time "2024-01-29 00:00:00" --end-time "2024-01-29 23:59:59"