1, Foreword

binlog for MySQL(23) data recovery

This article will use binlog2sql tool for data recovery

binlog2sql usage conditions

2, binlog2sql

https://github.com/danfengcao/binlog2sql

1. Installation

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip3 install -r requirements.txt

2. Use

-- End the log file being written and create a new log file for writing
flush logs;

① Database and table creation data

-- Create database-test
create database test;
use test;
-- Create table t_user
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
  `username` varchar(20) DEFAULT NULL COMMENT 'user name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='Test table';
-- insert data
insert into test.t_user values(1,'test'),(2,'admin');
insert into test.t_user values(3,'test'),(4,'admin');
-- Update data
update test.t_user set username='test03' where id=3;

View the latest log file being written

show master status;

② Parse out standard SQL

python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' -dtest -t t_user --start-file='mysql-bin.000002'

③ Resolve rollback SQL

# python3 binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -p'root' -dtest -t t_user --start-file='mysql-bin.000002'
# Resolve the rollback SQL and write it to the 'rollback.sql' file for viewing
python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' -dtest -t t_user --start-file='mysql-bin.000002' -B > rollback.sql | cat

④ Execute rollback statement

mysql -h127.0.0.1 -P3306 -uroot -p'root' < rollback.sql

Here, data recovery is complete... Flashback^_^

3. Command options

option	describe
-h host; -P port; -u user; -p password	mysql connection configuration
Analytical mode
–stop-never	Continue to parse binlog. Optional. The default is False, which synchronizes to the latest binlog location when the command is executed.
-K, --no-primary-key	Remove the primary key from the INSERT statement. Optional. Default False
-B, --flashback	Generate rollback SQL, which can parse large files and is not limited by memory. Optional. The default is False. And stop never or no primary key cannot be added at the same time.
–back-interval	-In mode B, the number of seconds to add a SLEEP sentence for every 1000 lines of rollback SQL printed. If you don't want to add SLEEP, set it to 0. Optional. The default is 1.0.
Analytical range control
–start-file	Starting to parse the file, you only need the file name, not the full path. must.
–start-position/–start-pos	Start resolution location. Optional. The default is the start location of the start file.
–stop-file/–end-file	Terminate parsing file. Optional. The default is start file, the same file. If the parsing mode is stop never, this option is disabled.
–stop-position/–end-pos	End resolution location. Optional. The default is the last position of stop file; If the parsing mode is stop never, this option is disabled.
–start-datetime	Start parsing time, format '% Y -% m -% d% H:% m:% s'. Optional. The default is no filtering.
–stop-datetime	End parsing time, format '% Y -% m -% d% H:% m:% s'. Optional. The default is no filtering.
Object filtering
-d, --databases	Only the sql of the target DB is parsed, and multiple libraries are separated by spaces, such as -d db1 db2. Optional. The default value is empty.
-t, --tables	Only the sql of the target table is parsed. Multiple tables are separated by spaces, such as -t tbl1 tbl2. Optional. The default value is empty.
–only-dml	Only dml is parsed and ddl is ignored. Optional. The default is False.
–sql-type	Only the specified type is resolved. Insert, update and delete are supported. Multiple types are separated by spaces, such as – SQL type insert delete. Optional. The default is to resolve all additions, deletions and modifications. If this parameter is used but no type is filled in, none of the three will be resolved.

Today's sharing statement:
You work hard, keep fit and smile at others. These are not to please others, but to dress up yourself, illuminate your heart and tell yourself that I am an independent and upward force.