1. What is binlog2sql?
It is an open source tool for parsing binary logs. It has the capability to extract raw SQL statements from binary logs. It has the capability to generate rollback SQL from binary logs for point-in-time recovery.
2. Install binlog2sql
cd git clone https://github.com/danfengcao/binlog2sql.git & amp; & amp; cd binlog2sql pip install -r requirements.txt
3. Change the mysql configuration file
[root@test4 binlog2sql]# cat /etc/my.cnf datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin server-id=3 #gtid_mode=ON #enforce_gtid_consistency=1 binlog_format = row binlog_row_image = full log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Increase log-bin server-id=3 binlog_format = row binlog_row_image = full
reason
The MySQL server must be active for two reasons: Binlog2SQL is based on the BINLOG_DUMP protocol to obtain binary log content. binlog2sql is required to read INFORMATION_SCHEMA. Gets the table's COLUMNS table from the server to get the table's metadata. (Servers with a large number of tables may incur significant overhead querying the INFORMATION_SCHEMA.list.)
4. Create a table and insert data
mysql> create table xp
-> (id int,name varchar(50),sex enum('n','f') default 'n');
Query OK, 0 rows affected (0.02 sec)
mysql> desc xp;
+ ------- + --------------- + ------ + ----- + ---------- + -- ----- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + --------------- + ------ + ----- + ---------- + -- ----- +
| id | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| sex | enum('n','f') | YES | | n | |
+ ------- + --------------- + ------ + ----- + ---------- + -- ----- +
3 rows in set (0.00 sec)
mysql> insert into xp values(1,'dog','n'),(2,'cat','f'),(3,'fox','n');
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from xp;
+ ------ + ------ + ------ +
| id | name | sex |
+ ------ + ------ + ------ +
| 1 | dog | n |
| 2 | cat | f |
| 3 | fox | n |
+ ------ + ------ + ------ +
3 rows in set (0.00 sec)
5. Enter the binlog2sql directory
cd /root/binlog2sql/binlog2sql
6. Run the script to obtain the contents of the database
[root@test4 binlog2sql]# ./binlog2sql.py -uroot -pAdmin@123 -P3306 -d xp -t xp --start-file=test4-bin.000005 --stop-file=test4-bin. 000006
Traceback (most recent call last):
File "./binlog2sql.py", line 149, in <module>
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
If the above error occurs, update PyMySQL to solve the problem. For mysql8 version issues, install the corresponding binlog2sql tool version.
pip uninstall PyMySQL pip install PyMySQL==0.9.3
7. Run again
[root@test4 binlog2sql]# ./binlog2sql.py -uroot -pAdmin@123 -P3306 -d xp -t xp --start-file=test4-bin.000006
USE xp;
create table xp
(id int,name varchar(50),sex enum('n','f') default 'n');
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 1, 'dog'); #start 428 end 702 time 2023-09-14 11:39: twenty three
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('f', 2, 'cat'); #start 428 end 702 time 2023-09-14 11:39: twenty three
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 3, 'fox'); #start 428 end 702 time 2023-09-14 11:39: twenty three
You can save it to the specified file
[root@test4 binlog2sql]# ./binlog2sql.py -uroot -pAb@123456 -P3306 -d xp -t xp --start-file=test4-bin.000006 > /root/xp1.md
8. Parameter explanation:
-d specifies the library name
-t specifies table
--start-file starts parsing the file, only the file name is needed, no full path is required
This path can be viewed in mysql
mysql> show master status \G
*************************** 1. row ********************* *******
File: test4-bin.000006
Position: 733
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 0cd27570-4564-11ee-86d0-2a26553b6ba7:1-60
1 row in set (0.00 sec)
9.How does binlog2sql support PITR?
Binlog2sql tool has option "--flashback" which will help generate ROLLBACK statements. We can recover data from DELETE and UPDATE statements. It will not support DDL (DROP, TRUNCATE) because the actual row events are not available in the binary log.
10. Test
1. Let’s delete a record mysql> select * from xp; + ------ + ------ + ------ + | id | name | sex | + ------ + ------ + ------ + | 1 | dog | n | | 2 | cat | f | | 3 | fox | n | + ------ + ------ + ------ + 3 rows in set (0.00 sec) mysql> delete from xp where id=3; Query OK, 1 row affected (0.00 sec)
11. Let’s check again using the command
[root@test4 binlog2sql]# ./binlog2sql.py -uroot -pAdmin@123 -P3306 -d xp -t xp --start-file=test4-bin.000006
USE xp;
create table xp
(id int,name varchar(50),sex enum('n','f') default 'n');
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 1, 'dog'); #start 428 end 702 time 2023-09-14 11:39: twenty three
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('f', 2, 'cat'); #start 428 end 702 time 2023-09-14 11:39: twenty three
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 3, 'fox'); #start 428 end 702 time 2023-09-14 11:39: twenty three
DELETE FROM `xp`.`xp` WHERE `sex`='n' AND `id`=3 AND `name`='fox' LIMIT 1; #start 733 end 987 time 2023-09-14 13:36:05
At this time, there is a clear delete statement in it
12. The DELETE above is the exact statement to perform deletion of records. Now I will generate ROLLBACK statement using option “–flashback”
[root@test4 binlog2sql]# ./binlog2sql.py -uroot -pAdmin@123 -P3306 -d xp -t xp --start-file=test4-bin.000006 --flashback
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 3, 'fox'); #start 733 end 987 time 2023-09-14 13:36: 05
DELETE FROM `xp`.`xp` WHERE `sex`='n' AND `id`=3 AND `name`='fox' LIMIT 1; #start 428 end 702 time 2023-09-14 11:39:23
DELETE FROM `xp`.`xp` WHERE `sex`='f' AND `id`=2 AND `name`='cat' LIMIT 1; #start 428 end 702 time 2023-09-14 11:39:23
DELETE FROM `xp`.`xp` WHERE `sex`='n' AND `id`=1 AND `name`='dog' LIMIT 1; #start 428 end 702 time 2023-09-14 11:39:23
At this time, the delete statement just deleted becomes an insert statement. You only need to copy and paste it into the database.
mysql> INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 3, 'fox');
Query OK, 1 row affected (0.00 sec)
mysql> select * from xp.xp;
+ ------ + ------ + ------ +
| id | name | sex |
+ ------ + ------ + ------ +
| 1 | dog | n |
| 2 | cat | f |
| 3 | fox | n |
+ ------ + ------ + ------ +
3 rows in set (0.00 sec)
13. The update statement is the same as above. If you want to roll back and generate the original data, you also need to add –flashback at the end
Official documentation
https://www.percona.com/blog/binlog2sql-binlog-to-raw-sql-conversion-and-point-in-time-recovery/