MySQL--------Best Practice for flashback based on Binlog

1. Background

* for data security, built master and slave. Real-time Master-slave backup only prevents hardware problems, such as hard disk damage from the main library. But for the wrong operation, there is nothing to do. For example, a table was mistakenly deleted in the main library, or an UPDATE statement did not specify a where condition, causing the whole table to be updated. When the operation is synchronized to the library, the master and slave are "powerless".

* Flash back for fast recovery of lost data due to misoperation. When the DBA mistakenly operates, the database can be restored to a previous point in time (or some binlog pos). For example, forget the update, delete operation with the Where condition, the traditional recovery method is to use the full-standby + binary log roll forward to restore, compared to the traditional full-complement + flashback, obviously faster and simpler.

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/9C/8D/wKioL1lx2fHROzBVAABWHTvREUU212.jpg "title=" Flashback.jpg "alt=" Wkiol1lx2fhrozbvaabwhtvreuu212.jpg "/>

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_image '; +------------------+-------+| variable_name    |  value |+------------------+-------+| binlog_row_image | full  |+---------------- --+-------+1 row in set  (0.00 sec)

3. Flash back to combat

* CREATE DATABASE and test tables, and insert data

Mysql> create table users (    -> id BIGINT NOT  Null auto_increment,    -> name varchar (255)  NOT NULL,     -> sex enum (' m ',  ' F ')  NOT NULL DEFAULT  ' m ',     -> age INT UNSIGNED NOT NULL DEFAULT  ' 0 ',     -> PRIMARY KEY  (ID)     -> ) engine=innodb  default charset=utf8mb4; query ok, 0 rows affected  (0.04 sec) mysql> insert into users  values (null,  ' Tom ',  ' M ',  25),  (null,  ' jak ',  ' F ',  32),  (NULL,   ' ses ',  ' m ',  45 ',  (null,  ' Lisea ',  ' m ',  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 Flashback tool binlog2sql[by Shanghai American Group DBA team

[[email protected] ~]# 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 244Receiv ing objects:100% (244/244), 121.72 KiB | KIB/S, done. Resolving deltas:100% (124/124), done.

* Installation related dependencies

[[email protected] ~]# yum install pip-y[[email protected] ~]# pip install--upgrade pip[[email protected] ~]# pip instal L-r Binlog2sql/requirements.txt

* Pre-refresh Binlog [Good separation of files in test]

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) 

* error operation, delete without where condition

mysql> Delete from users; Query OK, 4 rows affected (0.01 sec)

* binlog2sql tool to position SQL start and end positions via file output operation information

　　 Positioning time via--start-datetime and--stop-datetime

This gets the start position to 239, ending position for 483

[[Email protected] ~]# python binlog2sql/binlog2sql/binlog2sql.py  -hlocalhost  -p3306 -uroot -p ' 123 '  -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 '  WHERE  ' 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 01:02:49

* binlog2sql generate rollback SQL via flashback

[[email protected] ~]# python binlog2sql/binlog2sql/binlog2sql.py-hlocalhost-p3306-uroot-p ' 123 '-dmytest-tusers--st Art-file= ' bin.000006 '--start-position=239--stop-position=483-b > Rollback.sql

* import rollback SQL [Check if SQL statement is normal before import]

[Email protected] ~]# mysql-hlocalhost-uroot-p ' 123 ' < Rollback.sql

* View

Mysql> SELECT * from mytest.users;+----+-------+-----+-----+| ID | name | sex |  Age |+----+-------+-----+-----+| 1 | Tom |  M |  25 | | 2 | Jak |  F |  32 | | 3 | SES |  M |  45 | | 4 | Lisea |  M | |+----+-------+-----+-----+4 rows in Set (0.00 sec)

4. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1949859

MySQL--------Best Practice for flashback based on Binlog