Shulou(Shulou.com)06/01 Report--

Mysql erroneous deletion

The original Meituan open source Mysql data flashback tool used this time

Portal: https://github.com/Meituan-Dianping/MyFlash

First, brief introduction

The predecessor of MyFlash is binlong2, followed by a tool to roll back DML operations developed and maintained by the Technical Engineering Department of Meituan Dianping Company. The tool completes the rollback operation by parsing the V4 version of the binary log. Compared with the existing rollback tools, it adds more filtering options to make rollback easier. This tool has been used internally by Meituan Dianping.

Second, limit the binary log format to [row] line-level mode, and binlog_row_image = full only supports 5.6and 5.7.If it is a lower version of mysql, upgrade mysql 2 and use binlog2.

Download address of binlog2sql tool: you can only roll back DML (add, delete, modify) 3. Details 3.1 installation and deployment of git clone https://github.com/Meituan-Dianping/MyFlash.gitcd MyFlash/ [root@gitlab MyFlash] # yum install glib2* [root@gitlab MyFlash] # gcc-w `pkg-config-- cflags-- libs glib- 2.0` source/binlogParseGlib.c-o binary/flashback# dynamic compilation

3.2 enable mysql-binlog [root@gitlab mysql] # cat / etc/my.cnf | grep "log_bin*" # log_binlog-bin=mysql-bin # name prefix log_bin=/var/lib/mysql/mysql-bin # path log_bin_index=/var/lib/mysql/mysql-bin.index # Index binlog_format=row # binlog mode (row, mixed, Sql statement) max_binlog_size = 512m # single file size expire_logs_day = 30 # keep the last 30 days server-id = 1 # ID number [root@gitlab mysql] # / etc/init.d/mysqld restartRestarting mysqld (via systemctl): [OK] log in to mysql to view binlog open mysql > show variables like'% log_bin%'

Note: this tool must use row row-level mode

Show variables like 'binlog%'; # # row-level mode disadvantage, binlog logs will be increased by three times the original advantage: relative security

3.3.Use the method cd binary./flashback-- helpUsage: flashback [OPTION...] Help Options:-- help Show help optionsApplication Options:-- databaseNames to specify the database name that needs to be rolled back. Multiple databases can be separated by ",". If you do not specify this parameter, it is equivalent to specifying all databases. -- tableNames specifies the name of the table to be rolled back. Multiple tables can be separated by ",". If you do not specify this parameter, it is equivalent to specifying all tables. -- start-position specifies the location where the rollback starts. If not specified, rollback from the beginning of the file. Please specify the correct and valid location, otherwise you cannot roll back-- stop-position specifies where the rollback ends. If not specified, roll back to the end of the file. Please specify the correct and valid location, otherwise you cannot roll back-- start-datetime specifies the start time of the rollback. Note that the format must be "2016-11-17 14:40:00. If not specified, there is no time limit-- stop-datetime specifies the end time of the rollback. Note that the format must be Y-%m-%d H:%M:%S. If not specified, there is no time limit-- sqlTypes specifies the type of sql that needs to be rolled back. Currently, the supported filter types are INSERT, UPDATE and DELETE. Multiple types can be separated by ",". -- once maxSplitSize specifies this parameter, the file is segmented with a fixed size (in M). The filtering condition is valid, but no rollback operation is performed. The default level is error level. Do not modify this level in a production environment, or there will be too much output-- include-gtids specifies the gtid that needs to be rolled back and supports both single and scope forms of gtid. -- exclude-gtids specifies the gtid that does not need to be rolled back, using the same as include-gtids (skip) 3.4 test cases

1) introduction to the test environment:

[root@gitlab binary] # cat / etc/redhat-release # system version 7.4CentOS Linux release 7.4.1708 (Core) # uname-a # system kernel 3.10 64-bit Linux gitlab.example.com 3.10.0-693.el7.x86_64 # 1 SMP Tue Aug 22 21:09:27 UTC 2017 x86 "64 GNU/Linux [root@gitlab binary] # mysql-V # mysql version mysql Ver 14.14 Distrib 5.6.39 For Linux (x86 / 64) using EditLine wrapper

2) simulated data

I take some data from the test library here and import it directly into sql as a case.

SELECT * FROM `sys_member_ msg` WHERE id show master status # View the latest binlog location +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_ Set | +-+ | mysql_bin.000007 | 16696881 | + -+ 1 row in set (0.00 sec)

4.3 generate rollback data file

[root@gitlab binary] # time. / flashback-- sqlTypes='DELETE'-- binlogFileNames=/var/lib/mysql/mysql_bin.000007real 0m0.053suser 0m0.015ssys 0m0.035s# parameter meaning see the above usage introduction # for example: start time-end time-DML statement type (add, delete and modify), post point, big data to split # generate a binlog_output_base.flashback file by default

4.4 restore to the database

[root@gitlab binary] # mysqlbinlog binlog_output_base.flashback | mysql-uroot-pEnter password: # enter the mysql password # No feedback. It should be OK 3.5 to check the data rollback SELECT * FROM `sys_member_ msg` WHERE id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.