MySQL- realizes the best flashback actual combat based on binlog

1. Background

* Master and slave are built for data security. Real-time master-slave backups can only prevent hardware problems, such as damage to the hard drive of the master library. But for misoperation, there is nothing we can do. For example, a table is mistakenly deleted in the main database, or a update statement does not specify a where condition, resulting in the whole table being updated. When the operation is synchronized to the slave library, the master and slave are "powerless".

* flashback is used to quickly recover data lost due to misoperation. In the event of misoperation of DBA, you can restore the database to a previous point in time (or a pos of a binlog). For example, if you forget the update and delete operations with where conditions, the traditional recovery method is to use full backup + binary log roll forward for recovery. Compared with the traditional full backup + additional operations, flashback is obviously faster and simpler.

two。 Test environment

Mysql > show variables like 'version' +-+-+ | Variable_name | Value | +-+-+ | version | 5.6.36-log | +-+-+ 1 row in set (0.03 sec) mysql > show variables like 'datadir' +-+-+ | Variable_name | Value | +-+-+ | datadir | / data/mysql_data6/ | + -+ 1 row in set (0.00 sec) mysql > show variables like 'log_bin' +-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | +-+-+ 1 row in set (0.00 sec) mysql > show variables like 'binlog_format' +-+-+ | Variable_name | Value | +-+-+ | binlog_format | ROW | +-+-+ 1 row in set (0.00 sec) mysql > show variables like 'binlog_row_p_w_picpath' +-+-+ | Variable_name | Value | +-+-+ | binlog_row_p_w_picpath | FULL | +-+-+ 1 row in set (0.00 sec)

3. Flashback to actual combat

* create databases and test tables and insert data

Mysql > create table users (- > id BIGINT NOT NULL AUTO_INCREMENT,-> name VARCHAR (255th) NOT NULL,-> sex ENUM ('age INT UNSIGNED NOT NULL DEFAULT,' F') NOT NULL DEFAULT 'NOT NULL,-> age INT UNSIGNED NOT NULL DEFAULT' 0mm,-> PRIMARY KEY (id)->) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 Query OK, 0 rows affected (0.04 sec) mysql > insert into users values (null, 'tom',' null, 32), (null, 'ses',' Mises, 45), (null, 'lisea',' sec, 35); Query OK, 4 rows affected (0.13 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > select * from users +-+ | id | name | sex | age | +-+ | 1 | tom | M | 25 | 2 | jak | F | 32 | 3 | ses | M | 45 | 4 | lisea | M | 35 | +-+ 4 rows in set (0.00 sec)

* download the flashback tool binlog2sql [produced by Shanghai Meituan DBA team]

[root@MySQL] # git clone https://github.com/danfengcao/binlog2sql.gitInitialized empty Git repository in / root/binlog2sql/.git/remote: Counting objects: 244, done.remote: Total 244 (delta 0), reused 0 (delta 0), pack-reused 244Receiving objects: 100% (244 KiB), 121.72 KiB | 27 KiB/s, done.Resolving deltas: 100% (124 KiB), done.

* install related dependencies

[root@MySQL] # yum install pip-y [root@MySQL ~] # pip install-- upgrade pip [root@MySQL ~] # pip install-r binlog2sql/requirements.txt

* refresh binlog ahead of time [to distinguish files during testing]

Mysql > flush logs;Query OK, 0 rows affected (0.02 sec)

* View current binlog information

Mysql > show master status +-- + | File | Position | Binlog_Do_DB | Binlog_Ignore_ DB | Executed_Gtid_Set | +-- -+ | bin.000006 | 191 | c7f82640-6b2d-11e7-9316-000c29f0b169:1-22 | +-- -+ 1 row in set (0.01 sec)

* misoperation. Delete does not have where condition.

Mysql > delete from users;Query OK, 4 rows affected (0.01sec)

* the binlog2sql tool outputs operation information through files to locate the start and end locations of the SQL

Time can be located through-- start-datetime and-- stop-datetime.

As a result, the start position is 239 and the end position is 483.

[root@MySQL ~] # python binlog2sql/binlog2sql/binlog2sql.py-hlocalhost-P3306-uroot-prun123'-dmytest-tusers-- start-file='bin.000006' DELETE FROM `mytest`.`users`WHERE `age` = 25 AND `sex` ='M' AND `id` = 1 AND `name` = 'tom' LIMIT 1; # start 239 end 483 time 2017-07-19 01:02:49DELETE FROM `mytest`.`users`WHERE `age` = 32 AND `sex` =' F'AND `id` = 2 AND `name` = 'jak' LIMIT 1 # start 239 end 483 time 2017-07-19 01:02:49DELETE FROM `mytest`.`users`users`age` = 45 AND `sex` ='M 'AND `id` = 3 AND `name` =' ses' LIMIT 1; # start 239 end 483 time 2017-07-19 01:02:49DELETE FROM `mytest`.`users`WHERE `age` = 35 AND `sex` = 'M`AND `id` = 4 AND `name` =' lisea' LIMIT 1; # start 239 end 483 time 2017-07-19

* binlog2sql generates rollback SQL through flashback

[root@MySQL] # python binlog2sql/binlog2sql/binlog2sql.py-hlocalhost-P3306-uroot-pendant 123'-dmytest-tusers-- start-file='bin.000006'-- start-position=239-- stop-position=483-B > rollback.sql

* Import rollback SQL [check whether the SQL statement is normal before import]

[root@MySQL] # mysql-hlocalhost-uroot-pendant 123'

< rollback.sql * 查看 mysql>

Select * from mytest.users +-+ | id | name | sex | age | +-+ | 1 | tom | M | 25 | 2 | jak | F | 32 | 3 | ses | M | 45 | 4 | lisea | M | 35 | +-+ 4 rows in set (0.00 sec)

4. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.