Point time in recovery using binlog2sql tool

In this blog, I will explain about the tool binlog2sql,This tool will help to decode the MySQL binary logs and extract the mysqlbinlog, This tool can ROLLBACK statements for point in time recovery (PITR) using the “flashback” feature. This feature is available in MariaDB’s version of “mysqlbinlog” (Check out Flashback: Point-In-Time Recovery in MySQL/MariaDB/Percona Server)

This tool required python for run . so we need to install Python 2.7, 3.4+ versions

yum install python -y

We need to install git for sourced file download .

yum install git

Download binlog2sql file from git source directory.

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

Install python requirements. its required for binloglog2sql tool so we need to execute this command

cd /root/binlog2sql/

pip install -r requirements.txt

EPEL has an ‘epel-release’ package that includes GPG keys for package signing and repository .so we can download package using yum to install their dependencies

yum install epel-release

Binlog2sql tool required python-pip dependencies.

sudo yum install python-pip

Check your mysql configuration .we need to enable binlog. Because we need to capture some data for testing purpose .

vi /etc/my.cnf

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log 

max_binlog_size = 1G 
binlog_format = row
binlog_row_image = full

Testing binlog2sql tool
let’s we try with realtime .so we need to login mysql database server & insert some records in our database .

mysql -uroot -p
password:

mysql> use testdb
Database changed

mysql> create table binlog2sql(id int primary key auto_increment, name varchar(16), status enum('A','NA'), up_date datetime default current_timestamp);
Query OK, 0 rows affected (0.16 sec)

mysql> insert into binlog2sql (name,status) values ('raj','A'),('rani','A'),('ram','A'),('ash','A'),('siva','A');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

change the file location .

cd /root/binlog2sql/binlog2sql

This tool will support rollback only for DELETE/UPDATE and its not support DDL and encrypted/compressed binary logs because the DDL statements do not log any actual data in the binary logs.

We have to choose which binlog need to encrypt

python binlog2sql.py -hlocalhost -P3306 -uroot -ppassword -d databasename -t tablename --start-file='mysql-bin.000005'

UPDATE `testdb`.`binlog2sql` SET `status`='A', `up_date`='2021-06-20 14:54:51', `id`=5 LIMIT 1
UPDATE `testdb`.`binlog2sql` SET `status`='A', `up_date`='2020-06-20 14:54:51', `id`=5 LIMIT 1
UPDATE `testdb`.`binlog2sql` SET `status`='A', `up_date`='2020-06-20 14:54:51', `id`=5 LIMIT 1

Now we recover the query details from binlog.

we need to login MySQL database server & insert retrieve the records in our database .
mysql -uroot -p
password:

UPDATE `testdb`.`binlog2sql` SET `status`='A', `up_date`='2021-06-20 14:54:51', `id`=5 LIMIT 1 UPDATE `testdb`.`binlog2sql` SET `status`='A', `up_date`='2020-06-20 14:54:51', `id`=5 LIMIT 1 UPDATE `testdb`.`binlog2sql` SET `status`='A', `up_date`='2020-06-20 14:54:51', `id`=5 LIMIT 1
insert into binlog2sql (name,status) values ('raj','A'),('rani','A'),('ram','A'),('ash','A'),('siva','A')

All done ! We recover the data from MySQL binlogfile .If any queries please comment here or reach me out in linkdin .Thanks!