Detailed explanation of how to use binlog2sql to quickly roll back after MySQL misoperation

Preface

In daily work or study, it is inevitable that you will make mistakes due to "carelessness" when operating the database. If you need to recover quickly, it is unlikely to recover through backup. The following article mainly introduces the method of using binlog2sql to quickly roll back after Mysql misoperation. Without further ado, let's take a look at the detailed introduction:

1. General explanation:

DML (data manipulation language):

They are SELECT, UPDATE, INSERT, and DELETE. As their names suggest, these four commands are used to operate on the data in the database.

DDL（data definition language）：

DDL has more commands than DML. The main commands are CREATE, ALTER, DROP, etc. DDL is mainly used to define or change the structure of a table, data types, links and constraints between tables, and other initialization tasks. They are mostly used when creating a table.

DCL（Data Control Language）：

It is a database control function. It is used to set or change the permissions of database users or roles, including (grant, deny, revoke, etc.) statements. By default, only sysadmin, dbcreator, db_owner, or db_securityadmin have the authority to execute DCL.

2. Installation of binlog2sql

Parse the sql you want from mysql binlog. Depending on the options, you can get the original SQL, rollback SQL, insert SQL without primary key, etc.

2.1. Purpose

 Rapid data rollback (flashback)
Repair of data inconsistency after master-slave switching
Generate standard SQL from binlog, bringing derivative functions

2.2 Installation

# cd /usr/local
# git clone https://github.com/danfengcao/binlog2sql.git
# ls 
binlog2sql games java lib64 mariadb sbin src 
# cd binlog2sql
# pip install -r requirements.txt
-bash: pip: command not found
 -------------Install pip tool-------------
# wget https://bootstrap.pypa.io/get-pip.py 
# python get-pip.py
# pip -V # View pip version pip 9.0.1 from /usr/lib/python2.7/site-packages (python 2.7)
# pip install -r requirements.txt
Requirement already satisfied: PyMySQL==0.7.8 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 1))
Requirement already satisfied: wheel==0.24.0 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 2))
Requirement already satisfied: mysql-replication==0.9 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 3))

2.3. The minimum set of permissions required by the user:

It is recommended to grant select, super/replication client, replication slave permissions

mysql > GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* to flashback@'localhost' identified by 'flashback';
mysql > GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* to flashback@'127.0.0.1' identified by 'flashback';

2.4 Basic Usage

Parse standard SQL

shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -ddatabase -t table1 table2 --start-file='mysql-bin.000002' --start-datetime='2017-01-12 18:00:00' --stop-datetime='2017-01-12 18:30:00' --start-pos=1240

Parse the rollback SQL

shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147

3. Test:

3.1. Create a new table users

create table cope_users like info_users; # Create a new table insert into cope_users select * from info_users limit 500; # Insert 500 rows of data delete from cope_users where id<20; # Delete 20 rows of data

3.2. Parsing standard SQL

# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-datetime='2017-07-11 15:10:00' --stop-datetime='2017-07-11 15:12:00'
DELETE FROM `ttt`.`users` WHERE `uid`='0e8e2609c748bbb052d7' AND `ip`='172.16.208.32' AND `sex`=0 AND `app_ver`='5.2.3' AND `device_type`=2 AND `guides`='' AND `last_login_time`=1481602129 AND `id`=1 AND `latitude`='' AND `add_time`=1481602080 AND `recharge_time`=0 AND `token_change_time`=1481602129 AND `expire_time`=0 AND `nickname`='阿超' AND `device_id`='cc0e154d9b5dd703eccc7d8a0dbc0f67d64b79e8' AND `push_key`='' AND `level`=0 AND `mobile`='18810895535' AND `settings`='' AND `longitude`='' AND `signature`='' AND `os_ver`='' LIMIT 1; #start 79078 end 83053 time 2017-07-11 15:11:50
DELETE FROM `ttt`.`users` WHERE `uid`='b5cfbdb4205b56703a97' AND `ip`='172.16.208.48' AND `sex`=0 AND `app_ver`='5.2.2' AND `device_type`=2 AND `guides`='' AND `last_login_time`=1481602096 AND `id`=2 AND `latitude`='' AND `add_time`=1481602096 AND `recharge_time`=0 AND `token_change_time`=1481602096 AND `expire_time`=0 AND `nickname`='家长091410' AND `device_id`='fedea666076a7906be53523acc7a8b32811354fe' AND `push_key`='7759d6772c9851a2bfc13835a3d7e7da' AND `level`=0 AND `mobile`='13629470521' AND `settings`='' AND `longitude`='' AND `signature`='' AND `os_ver`='' LIMIT 1; #start 79078 end 83053 time 2017-07-11 15:11:50

3.3. Parse the rollback SQL

# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -h127.0.0.1 -P3306 -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-position=79078 --stop-position=83053

Check the parsed sql, if it is correct, you can print it into the sql file /data/backup/rollback.sql

# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-position=79078 --stop-position=83053> /data/backup/rollback.sql
# cat /data/backup/rollback.sql 
`id`, `latitude`, `add_time`, `recharge_time`, `token_change_time`, `expire_time`, `nickname`, `device_id`, `push_key`, `level`, `mobile`, `settings`, `longitude`, `signature`, `os_ver`) VALUES ('24667530f4b16a446b3e', '172.16.218.75', 0, '5.2.93', 3, '{\"2103\":1,\"2100\":1,\"2101\":1,\"2102\":1,\"2104\":1,\"2105\":1}', 1490239125, 19, '', 1481610680, 0, 1490239125, 0, 'zf', 'da75b093-bd22-48f6-bbb1-d3296e29e9b5', 'be05183f80a96e788e0b0a99d1275392', 0, '15101538925', '', '', '', ''); #start 79078 end 83053 time 2017-07-11 15:11:50
INSERT INTO `ttt`.`users`(`uid`, `ip`, `sex`, `app_ver`, `device_type`, `guides`, `last_login_time`, `id`, `latitude`, `add_time`, `recharge_time`, `token_change_time`, `expire_time`, `nickname`, `device_id`, `push_key`, `level`, `mobile`, `settings`, `longitude`, `signature`, `os_ver`) VALUES ('77e50b4910a9389057ed', '172.16.218.37', 0, '5.2.1.14', 3, '', 1488787835, 18, '39.978212', 1481610517, 0, 1488787835, 0, '陈俊宇', 'ed0a273d-74de-4173-92c6-55d92597bc79', '', 0, '18612482272', '', '116.306826', '', ''); #start 79078 end 83053 time 2017-07-11 15:11:50

mysql connection configuration

 -h host; -P port; -u user; -p password

Parsing Mode

--realtime Sync binlog continuously. Optional. If not added, the data will be synchronized to the latest binlog position when the command is executed.
--popPk Remove primary key from INSERT statements. Optional.
-B, --flashback Generate rollback statements. Optional. Cannot be added simultaneously with realtime or popPk.

Resolution range control

--start-file Start parsing file. must.
--start-pos The starting parsing position of start-file. Optional. The default is the starting position of start-file;
--end-file End parsing file. Optional. The default is the same file as start-file. If the parsing mode is realtime, this option has no effect.
--end-pos The end parsing position of end-file. Optional. The default is the last position of end-file; if the parsing mode is realtime, this option is invalid.

Object filtering

-d, --databases only output the sql of the target db. Optional. The default value is empty.
-t, --tables only output the SQL of target tables. Optional. The default value is empty.

3.4. Start rolling back

# mysql -uroot -p000000 < /data/backup/rollback.sql

3.5. Login database verification

IV. Precautions

4.1. The following parameters are set in the configuration file:

server_id = 1
log_bin = /data/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full # default

4.2. The MySQL service must be started during flashback

Because it obtains binlog content through the BINLOG_DUMP protocol, it needs to read the server-side information_schema.COLUMNS table to obtain the meta information of the table structure before it can be spliced into SQL statements. Therefore, the minimum permissions required to be provided to users are as follows:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'%';

In the source code, python-mysql-replication is mainly used as real-time parsing of MySQL binlog to obtain each EVENT. python-mysql-replication implements the MySQL replication protocol. The client pretends to be a slave to obtain the master's binlog and EVENT.

4.3. Most of the time, standard SQL and rollback SQL can be parsed for insert, update, and delete.

One exception: after insert, update, delete operations, drop/truncate table. Although all events are recorded in binlog at this time, when binlog2sql is used to generate standard SQL and rollback SQL, the corresponding table of DML operation can no longer be found.

4.4. DDL cannot use binlog2sql to flash back data.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:

Detailed installation and configuration tutorial of MySQL flashback tool binlog2sql
Detailed steps for configuring mysql8.0.20 with binlog2sql and simple backup and recovery

<<: Explanation of the new feature of Hadoop 2.X, the recycle bin function

>>: Explanation of the working mechanism of namenode and secondarynamenode in Hadoop