binlog-rollback.pl Online Recovery update and delete without conditional error operation SQL

Source: Internet
Author: User
Tags db2

I. Introduction of binlog-rollback.pl Tools

is a Perl-developed scripting tool that primarily generates a reverse DML SQL statement:

#基于row模式的binlog,生成DML(insert/update/delete)的rollback语句#通过mysqlbinlog -v 解析binlog生成可读的sql文件#提取需要处理的有效sql#"### "开头的行.如果输入的start-position位于某个event group中间,则会导致"无法识别event"错误#将INSERT/UPDATE/DELETE 的sql反转,并且1个完整sql只能占1行#INSERT: INSERT INTO => DELETE FROM, SET => WHERE#UPDATE: WHERE => SET, SET => WHERE#DELETE: DELETE FROM => INSERT INTO, WHERE => SET#用列名替换位置@{1,2,3}#通过desc table获得列顺序及对应的列名#特殊列类型value做特别处理#逆序

#注意:

#表结构与现在的表结构必须相同[谨记]#由于row模式是幂等的,并且恢复是一次性,所以只提取sql,不提取BEGIN/COMMIT#只能对INSERT/UPDATE/DELETE进行处理#线上误操作后,一定要立刻对操作的表进行加锁,避免其他数据继续写入
Second, MySQL server must set the following parameters:
cat /etc/my.cnf[client]port = 3306socket = /tmp/mysql.sockdefault-character-set=utf8[mysqld]server_id = 1log_bin = /var/log/mysql/mysql-bin.logmax_binlog_size = 1Gbinlog_format = rowbinlog_row_image = fullcharacter-set-server = utf8
Third, this tool uses the need to create a MySQL administrative user to operate

It is not recommended to use the root account of MySQL directly, it is recommended to authorize a least privileged user to operate
Log in to MySQL to create the smallest authorized user admin
Minimum set of permissions required by User:

select, super/replication client, replication slaveGRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

Permission Description

select:需要读取server端information_schema.COLUMNS表,获取表结构的元信息,拼接成可视化的sql语句super/replication client:两个权限都可以,需要执行‘SHOW MASTER STATUS‘, 获取server端的binlog列表replication slave:通过BINLOG_DUMP协议获取binlog内容的权限
MySQL [(none)]> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO [email protected]‘127.0.0.1‘ identified by ‘admin‘;
Four, binlog-rollback.pl tool parameter Introduction
[[email protected] opt]# perl binlog-rollback.pl-help<error>:p Lease input Binlog file==================== ======================================================================command Line Options:--help # out : Print Help Info-f,--srcfile # in:binlog file. [Required]-O,--outfile # out:output SQL file. [Required]-H,--host # In:host. Default ' 127.0.0.1 '-u,--user # In:user. [Required]-P,--password # In:password. [Required]-P,--port # In:port. Default ' 3306 '--start-datetime # in:start datetime--stop-datetime # in:stop datetime--sta   Rt-position # In:start Position--stop-position # in:stop position-d,--database # in : Database, Split comma-t,--table # in:table, split comma. [Required] set-d-I,--ignore # in:ignore binlog check contain DDL (create| alter|drop| RENAME)--debug # in:print Debug informationsample:shell> perl binlog-rollback.pl-f ' Mysql-bin. 000001 '-o '/tmp/t.sql '-u ' user '-P ' pwd ' shell> perl binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-u ' User '-P ' pwd '-i shell> perl binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-u ' user '-P ' pwd '--debug s Hell> perl binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-h ' 192.168.1.2 '-u ' user '-P ' pwd '-p 3307 Shell > Perl binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-u ' user '-P ' pwd '--start-position=107 shell> per   L binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-u ' user '-P ' pwd '--start-position=107--stop-position=10000 Shell> perl binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-u ' user '-P ' pwd '-d ' db1,db2 ' shell> perl Binlog-rollback.pl-f ' mysql-bin.0000* '-o '/tmp/t.sql '-u ' user '-P ' pwd '-d ' db1,db2 '-t ' tb1,tb2 '

The operation error is as follows:

[[email protected] opt]#  perl binlog-rollback.pl -f ‘/data/mysql/data/mysql-bin.000002‘ -o ‘/tmp/t.sql‘ -u ‘admin‘ -p ‘admin‘ mysqlbinlog: unknown variable ‘default-character-set=utf8‘mysqlbinlog: unknown variable ‘default-character-set=utf8‘mysqlbinlog: unknown variable ‘default-character-set=utf8‘

Workaround:

[[email protected] opt]# grep mysqlbinlog binlog-rollback.pl #通过mysqlbinlog -v 解析binlog生成可读的sql文件    $MYSQLBINLOG = qq{mysqlbinlog --no-defaults -v}; ###添加上参数--no-defaults  解决问题
Five, test delete, and update error operation after the SQL extraction and data recovery 5.1delete Delete forgot to add conditional recovery test
MySQL [zixun3]> SELECT * from zx_scores;+----+--------+------+----------+-----------+| ID | Titles | icon | Integral |  IsDefault |+----+--------+------+----------+-----------+| 2 |    Pvt |        1 |         0 |  1 | | 3 |    Monitor |     2 |         1000 |  1 | | 4 |    Ensign |     3 |         2000 |  1 | | 5 |    Lieutenant |     4 |         3000 |  1 | | 6 |    Captain |     5 |         4000 |  1 | | 7 |    Major |     6 |         5000 |  1 | | 8 |    Lieutenant Colonel |     7 |         6000 |  1 | | 9 |    Colonel |     8 |         9000 | 1 | | 10 |    Major General |    9 |         14000 | 1 | | 11 |   Lieutenant |    10 |         19000 | 1 | | 12 |   Admiral |    11 |         24000 | 1 | | 15 |   Generals |    12 |         29000 | 1 |+----+--------+------+----------+-----------+12 rows in Set (0.00 sec) MySQL [zixun3]> Show Master status\g******** 1. Row *************************** file:mysql-bin.000003 position:592 binlog_do_db:binlog_ignore_ Db:executed_gtid_set:1 row in Set (0.00 sEC) MySQL [zixun3]> flush logs; Query OK, 0 rows affected (0.10 sec) MySQL [zixun3]> Show Master status\g*************************** 1. Row *************************** file:mysql-bin.000004 position:120 binlog_do_db:binlog_ignore_ Db:executed_gtid_set:1 row in Set (0.00 sec)

Delete without any conditions, resulting in accidental deletion of

MySQL [zixun3]> Delete from zx_scores; Query OK, Rows Affected (0.00 sec) MySQL [zixun3]> [[email protected] opt]# perl binlog-rollback.pl-f '/data/m ysql/data/mysql-bin.000004 '-o '/tmp/insert.sql '-u ' admin '-P ' admin '-H 127.0.0.1-iwarning:using a password on the COM Mand line interface can is insecure. [[email protected] opt]# cat/tmp/insert.sql insert INTO ' zixun3 '. ' Zx_scores ' SET ' id ' =15, ' titles ' = ' General ', ' icon ' =12 , ' Integral ' =29000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =12, ' titles ' = ' Admiral ', ' icon ' =11, ' integral ' = 24000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =11, ' titles ' = ' Will ', ' icon ' =10, ' integral ' = 19000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =10, ' titles ' = ' Major General ', ' icon ' =9, ' integral ' =14000, ' IsDefault ' =1;i Nsert into ' zixun3 '. ' Zx_scores ' SET ' id ' =9, ' titles ' = ' Colonel ', ' icon ' =8, ' integral ' =9000, ' IsDefault ' =1;insert into ' zixun3 ' . ' Zx_scores ' SET ' id ' =8, ' titles ' = ' Lieutenant Colonel ', ' icon ' =7, ' integral ' =6000, ' IsDefault ' =1;insert into ' Zixun3 '. ' Zx_scores ' SET ' id ' =7, ' titles ' = ' major ', ' icon ' =6, ' integral ' =5000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_ Scores ' Set ' ID ' =6, ' titles ' = ' Captain ', ' icon ' =5, ' integral ' =4000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' set ' id ' = 5, ' Titles ' = ' Lieutenant ', ' icon ' =4, ' integral ' =3000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =4, ' titles ' = ' Ensign ', ' Icon ' =3, ' integral ' =2000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =3, ' titles ' = ' monitor ', ' icon ' =2, ' integral ' =1000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =2, ' titles ' = ' private ', ' icon ' =1, ' integral ' =0, ' IsDefault ' =1 ;

MySQL [zixun3]> Source/tmp/insert.sql

MySQL [zixun3]> select *  from zx_scores;+----+--------+------+----------+-----------+| id | titles | icon | integral | isdefault |+----+--------+------+----------+-----------+|  2 | 列兵   |    1 |        0 |         1 ||  3 | 班长   |    2 |     1000 |         1 ||  4 | 少尉   |    3 |     2000 |         1 ||  5 | 中尉   |    4 |     3000 |         1 ||  6 | 上尉   |    5 |     4000 |         1 ||  7 | 少校   |    6 |     5000 |         1 ||  8 | 中校   |    7 |     6000 |         1 ||  9 | 上校   |    8 |     9000 |         1 || 10 | 少将   |    9 |    14000 |         1 || 11 | 中将   |   10 |    19000 |         1 || 12 | 上将   |   11 |    24000 |         1 || 15 | 大将   |   12 |    29000 |         1 |+----+--------+------+----------+-----------+12 rows in set (0.00 sec)

Delete operation Recovery complete

5.2update update forgot to add conditional data recovery
  MySQL [zixun3]> update zx_scores set titles= ' monitor '; Query OK, one rows affected (0.00 sec) rows matched:12 changed:11 warnings:0mysql [zixun3]> select * from Zx_scores ;+----+--------+------+----------+-----------+| ID | Titles | icon | Integral |  IsDefault |+----+--------+------+----------+-----------+| 2 |    Monitor |        1 |         0 |  1 | | 3 |    Monitor |     2 |         1000 |  1 | | 4 |    Monitor |     3 |         2000 |  1 | | 5 |    Monitor |     4 |         3000 |  1 | | 6 |    Monitor |     5 |         4000 |  1 | | 7 |    Monitor |     6 |         5000 |  1 | | 8 |    Monitor |     7 |         6000 |  1 | | 9 |    Monitor |     8 |         9000 | 1 | | 10 |    Monitor |    9 |         14000 | 1 | | 11 |   Monitor |    10 |         19000 | 1 | | 12 |   Monitor |    11 |         24000 | 1 | | 15 |   Monitor |    12 |         29000 | 1 |+----+--------+------+----------+-----------+12 rows in Set (0.00 sec)  
[[email protected] opt]#  perl binlog-rollback.pl -f ‘/data/mysql/data/mysql-bin.000004‘ -o ‘/tmp/update.sql‘ -u ‘admin‘ -p ‘admin‘ -h 127.0.0.1 -iWarning: Using a password on the command line interface can be insecure.

Since all SQL domain names are recorded in mysql-bin.000004, and the binlog-rollback.pl tool is filtering all DML statements insert,update,delete in mysql-bin.000004, So we need to filter out the individual update statements.

[[email protected] opt]# grep-i update/tmp/update.sql >/tmp/3.sql[[email protected] opt]# cat/tmp/3.sql UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =15, ' titles ' = ' General ', ' icon ' =12, ' integral ' =29000, ' IsDefault ' =1 WHERE ' id ' =15 and ' Les ' = ' monitor ' and ' icon ' =12 and ' integral ' =29000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =12, ' titles ' = ' Admiral ', ' icon ' =11, ' integral ' =24000, ' IsDefault ' =1 WHERE ' id ' =12 and ' Les ' = ' monitor ' and ' icon ' =11 and ' integral ' =24000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =11, ' titles ' = ' Will ', ' icon ' =10, ' integral ' =19000, ' IsDefault ' =1 WHERE ' id ' =11 and ' Les ' = ' monitor ' and ' icon ' =10 and ' integral ' =19000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =10, ' titles ' = ' Major General ', ' icon ' =9, ' integral ' =14000, ' IsDefault ' =1 WHERE ' id ' =10 and ' titl Es ' = ' monitor ' and ' icon ' =9 and ' integral ' =14000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =9, ' titles ' = ' Colonel ', ' icon ' =8, ' integral ' =9000, ' IsDefault ' =1 WHERE ' id ' =9 and ' titles ' = ' monitor ' and ' icon ' =8 and ' INtegral ' =9000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =8, ' titles ' = ' Lieutenant Colonel ', ' icon ' =7, ' integral ' =6000, ' IsDefault ' =1 WHERE ' id ' =8 and ' titles ' = ' monitor ' and ' icon ' =7 and ' integral ' =6000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =7, ' titles ' = ' major ', ' icon ' =6, ' integral ' =5000, ' IsDefault ' =1 WHERE ' id ' =7 and ' titles ' = ' monitor ' and ' icon ' =6 and ' integral ' =5000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =6, ' titles ' = ' Captain ', ' icon ' =5, ' integral ' =4000, ' IsDefault ' =1 WHERE ' id ' =6 and ' titles ' = ' monitor ' and ' icon ' =5 and ' integral ' =4000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =5, ' titles ' = ' Lieutenant ', ' icon ' =4, ' integral ' =3000, ' IsDefault ' =1 WHERE ' id ' =5 and ' titles ' = ' monitor ' and ' icon ' =4 and ' integral ' =3000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =4, ' titles ' = ' Ensign ', ' Icon ' =3, ' integral ' =2000, ' IsDefault ' =1 WHERE ' id ' =4 and ' titles ' = ' monitor ' and ' icon ' =3 and ' integral ' =2000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =2, ' titles ' = ' private ', ' icon ' =1, ' integral ' = 0, ' IsDefault ' =1 WHERE ' id ' =2 and ' titles ' = ' monitor ' and ' icon ' =1 and ' integral ' =0 ' and ' isdefault ' = 1; MySQL [zixun3]> source//tmp/3.sql; MySQL [zixun3]> SELECT * from zx_scores;+----+--------+------+----------+-----------+| ID | Titles | icon | Integral |  IsDefault |+----+--------+------+----------+-----------+| 2 |    Pvt |        1 |         0 |  1 | | 3 |    Monitor |     2 |         1000 |  1 | | 4 |    Ensign |     3 |         2000 |  1 | | 5 |    Lieutenant |     4 |         3000 |  1 | | 6 |    Captain |     5 |         4000 |  1 | | 7 |    Major |     6 |         5000 |  1 | | 8 |    Lieutenant Colonel |     7 |         6000 |  1 | | 9 |    Colonel |     8 |         9000 | 1 | | 10 |    Major General |    9 |         14000 | 1 | | 11 |   Lieutenant |    10 |         19000 | 1 | | 12 |   Admiral |    11 |         24000 | 1 | | 15 |   Generals |    12 |         29000 | 1 |+----+--------+------+----------+-----------+12 rows in Set (0.00 sec)

Complete recovery

Simple, and this tool can also be combined with location points and point-in-time recovery, please follow the post.

binlog-rollback.pl Online Recovery update and delete without conditional error operation SQL

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.