MySQL通过binlog数据恢复

最新推荐文章于 2023-07-30 18:18:57 发布
最新推荐文章于 2023-07-30 18:18:57 发布
阅读量1.6k 收藏 2
点赞数
分类专栏： MySQL 数据库 文章标签： 数据库 数据恢复
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/w5wangdeqing/article/details/109807287
版权

MySQL数据恢复

binlog的作用

数据恢复通过BinLog进行恢复，binlog的作用一个是进行主从同步，一个进行数据恢复，数据恢复的前提条件是binlog不能丢失，如果丢失了则不能进行恢复的。

  1. 在MySQL中开启binlog
[root@localhost ~]#  vim /etc/my.cnf
[mysqld]
log_bin=mysql-bin #(位置一般和mysql库文件所在位置一样)
server-id=128 #机器ID，在主从复制时使用
expire_logs_days = 0 #(表示此日志保存时间为单位是天，默认为0即不清理)

Binlog日志记录数据库所有的DDL和DML(除了查询语句select)语句事件

  1. 查看所有binlog日志列表:
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1611 |
| mysql-bin.000002 |       614 |
| mysql-bin.000003 |      3095 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       692 |
| mysql-bin.000007 |       502 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |      7745 |
+------------------+-----------+
10 rows in set (0.00 sec)
mysql>
  1. 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 |     7745 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
  1. 刷新日志，此刻开始产生一个新编号的binlog文件，例如：
mysql> Flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>

每当mysqld服务重启时，会自动执行刷新binlog日志命令，mysqldump备份数据时加-F选项也会刷新binlog日志,清空所有binlog日志命令:

  1. 查看binlog文件内容，使用查看工具mysqlbinlog来查看(cat/vi/more都是无法打开的) ,可以使用在SQL编辑器上查看，以下命令更为方便查看命令
#可以这么查看
mysql> show binlog events in 'mysql-bin.000011';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000011 |   4 | Format_desc    |       128 |         123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| mysql-bin.000011 | 123 | Previous_gtids |       128 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第4条','王德卿','2020-11-19');
mysql> show binlog events in 'mysql-bin.000011';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000011 |   4 | Format_desc    |       128 |         123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| mysql-bin.000011 | 123 | Previous_gtids |       128 |         154 |                                       |
| mysql-bin.000011 | 154 | Anonymous_Gtid |       128 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000011 | 219 | Query          |       128 |         294 | BEGIN                                 |
| mysql-bin.000011 | 294 | Table_map      |       128 |         356 | table_id: 111 (test001.testTable)     |
| mysql-bin.000011 | 356 | Write_rows     |       128 |         424 | table_id: 111 flags: STMT_END_F       |
| mysql-bin.000011 | 424 | Xid            |       128 |         455 | COMMIT /* xid=612 */                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set (0.00 sec)
mysql>
  1. 指定查询,从pos点356 开始查询，如下：
mysql> show binlog events in 'mysql-bin.000011' from 356;
+------------------+-----+------------+-----------+-------------+---------------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                            |
+------------------+-----+------------+-----------+-------------+---------------------------------+
| mysql-bin.000011 | 356 | Write_rows |       128 |         424 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000011 | 424 | Xid        |       128 |         455 | COMMIT /* xid=612 */            |
+------------------+-----+------------+-----------+-------------+---------------------------------+
2 rows in set (0.00 sec)
mysql>
  1. 下面进行数据删除恢复操作，数据插入
mysql> use test001;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第100条','新增','2020-11-19');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第101条','要删除的','2020-11-19');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第102条','要更新的','2020-11-19');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第103条','要删除的','2020-11-19');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第104条','要更新的','2020-11-19');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第105条','新增','2020-11-19');
Query OK, 1 row affected (0.01 sec)
mysql> 
mysql> SELECT * FROM testTable WHERE test_id >=28;
+---------+-----------------+--------------+------------+
| test_id | test_title      | test_author  | test_date  |
+---------+-----------------+--------------+------------+
|      28 | 这是第4条       | 王德卿       | 2020-11-19 |
|      29 | 这是第100条     | 新增         | 2020-11-19 |
|      30 | 这是第101条     | 要删除的     | 2020-11-19 |
|      31 | 这是第102条     | 要更新的     | 2020-11-19 |
|      32 | 这是第103条     | 要删除的     | 2020-11-19 |
|      33 | 这是第104条     | 要更新的     | 2020-11-19 |
|      34 | 这是第105条     | 新增         | 2020-11-19 |
+---------+-----------------+--------------+------------+
7 rows in set (0.00 sec)
mysql> DELETE FROM testTable WHERE  test_id IN (30,32);
Query OK, 2 rows affected (0.01 sec)
mysql> UPDATE  testTable SET test_author ='已更新'  WHERE  test_id IN (31,33);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> SELECT * FROM testTable WHERE test_id >=28;
+---------+-----------------+-------------+------------+
| test_id | test_title      | test_author | test_date  |
+---------+-----------------+-------------+------------+
|      28 | 这是第4条       | 王德卿      | 2020-11-19 |
|      29 | 这是第100条     | 新增        | 2020-11-19 |
|      31 | 这是第102条     | 已更新      | 2020-11-19 |
|      33 | 这是第104条     | 已更新      | 2020-11-19 |
|      34 | 这是第105条     | 新增        | 2020-11-19 |
+---------+-----------------+-------------+------------+
5 rows in set (0.01 sec)
mysql>
  1. 查看binlog
[root@localhost ~]# mysqlbinlog -vv --start-datetime='2020-11-19 12:28:00' /var/lib/mysql/mysql-bin.000011 | more
BEGIN
/*!*/;
# at 595
#201119 12:29:37 server id 128  end_log_pos 657 CRC32 0x7f44f6d6        Table_map: `test001`.`testTable` mapped to number 111
# at 657
#201119 12:29:37 server id 128  end_log_pos 724 CRC32 0xb1212fd9        Write_rows: table id 111 flags: STMT_END_F

BINLOG '
sfS1XxOAAAAAPgAAAJECAAAAAG8AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
CNb2RH8=
sfS1Xx6AAAAAQwAAANQCAAAAAG8AAAAAAAEAAgAE//AdAAAADwDov5nmmK/nrKwxMDDmnaEG5paw
5aKec8kP2S8hsQ==
'/*!*/;
### INSERT INTO `test001`.`testTable`
### SET
###   @1=29 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第100条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 724
#201119 12:29:37 server id 128  end_log_pos 755 CRC32 0x3b9e5228        Xid = 632
COMMIT/*!*/;
# at 755
#201119 12:29:37 server id 128  end_log_pos 820 CRC32 0x9df3941f        Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 820
#201119 12:29:37 server id 128  end_log_pos 895 CRC32 0x4282198f        Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1605760177/*!*/;
BEGIN
/*!*/;
# at 895
#201119 12:29:37 server id 128  end_log_pos 957 CRC32 0xd9d8727c        Table_map: `test001`.`testTable` mapped to number 111
# at 957
#201119 12:29:37 server id 128  end_log_pos 1030 CRC32 0x7d36660f       Write_rows: table id 111 flags: STMT_END_F

BINLOG '
sfS1XxOAAAAAPgAAAL0DAAAAAG8AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
CHxy2Nk=
sfS1Xx6AAAAASQAAAAYEAAAAAG8AAAAAAAEAAgAE//AeAAAADwDov5nmmK/nrKwxMDHmnaEM6KaB
5Yig6Zmk55qEc8kPD2Y2fQ==
'/*!*/;
### INSERT INTO `test001`.`testTable`
### SET
###   @1=30 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第101条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要删除的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 1030
#201119 12:29:37 server id 128  end_log_pos 1061 CRC32 0x0b6be2a2       Xid = 633
COMMIT/*!*/;
# at 1061
#201119 12:29:37 server id 128  end_log_pos 1126 CRC32 0x865d215e       Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1126
#201119 12:29:37 server id 128  end_log_pos 1201 CRC32 0x5de1aa68       Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1605760177/*!*/;
BEGIN
/*!*/;
# at 1201
#201119 12:29:37 server id 128  end_log_pos 1263 CRC32 0x4da3a3fd       Table_map: `test001`.`testTable` mapped to number 111
# at 1263
#201119 12:29:37 server id 128  end_log_pos 1336 CRC32 0x2c1d00db       Write_rows: table id 111 flags: STMT_END_F

BINLOG '
sfS1XxOAAAAAPgAAAO8EAAAAAG8AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
CP2jo00=
sfS1Xx6AAAAASQAAADgFAAAAAG8AAAAAAAEAAgAE//AfAAAADwDov5nmmK/nrKwxMDLmnaEM6KaB
5pu05paw55qEc8kP2wAdLA==
'/*!*/;
### INSERT INTO `test001`.`testTable`
### SET
###   @1=31 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第102条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要更新的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 1336
#201119 12:29:37 server id 128  end_log_pos 1367 CRC32 0x53734b2c       Xid = 634
COMMIT/*!*/;
# at 1367
#201119 12:29:37 server id 128  end_log_pos 1432 CRC32 0x66ae9ea4       Anonymous_GTID  last_committed=4        sequence_number=5       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1432
#201119 12:29:37 server id 128  end_log_pos 1507 CRC32 0xa1b93f05       Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1605760177/*!*/;
BEGIN
/*!*/;
# at 1507
#201119 12:29:37 server id 128  end_log_pos 1569 CRC32 0x88a5eebd       Table_map: `test001`.`testTable` mapped to number 111
# at 1569
#201119 12:29:37 server id 128  end_log_pos 1642 CRC32 0xabe30b98       Write_rows: table id 111 flags: STMT_END_F

BINLOG '
sfS1XxOAAAAAPgAAACEGAAAAAG8AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
CL3upYg=
sfS1Xx6AAAAASQAAAGoGAAAAAG8AAAAAAAEAAgAE//AgAAAADwDov5nmmK/nrKwxMDPmnaEM6KaB
5Yig6Zmk55qEc8kPmAvjqw==
'/*!*/;
### INSERT INTO `test001`.`testTable`
### SET
###   @1=32 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第103条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要删除的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 1642
#201119 12:29:37 server id 128  end_log_pos 1673 CRC32 0x4df05683       Xid = 635
COMMIT/*!*/;
# at 1673
#201119 12:29:37 server id 128  end_log_pos 1738 CRC32 0x87df0eca       Anonymous_GTID  last_committed=5        sequence_number=6       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1738
#201119 12:29:37 server id 128  end_log_pos 1813 CRC32 0x668b5497       Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1605760177/*!*/;
BEGIN
/*!*/;
# at 1813
#201119 12:29:37 server id 128  end_log_pos 1875 CRC32 0x93b7e8ec       Table_map: `test001`.`testTable` mapped to number 111
# at 1875
#201119 12:29:37 server id 128  end_log_pos 1948 CRC32 0x410c2de1       Write_rows: table id 111 flags: STMT_END_F

BINLOG '
sfS1XxOAAAAAPgAAAFMHAAAAAG8AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
COzot5M=
sfS1Xx6AAAAASQAAAJwHAAAAAG8AAAAAAAEAAgAE//AhAAAADwDov5nmmK/nrKwxMDTmnaEM6KaB
5pu05paw55qEc8kP4S0MQQ==
'/*!*/;
### INSERT INTO `test001`.`testTable`
### SET
###   @1=33 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第104条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要更新的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 1948
#201119 12:29:37 server id 128  end_log_pos 1979 CRC32 0xd01b0606       Xid = 636
COMMIT/*!*/;
# at 1979
#201119 12:29:37 server id 128  end_log_pos 2044 CRC32 0x306598cd       Anonymous_GTID  last_committed=6        sequence_number=7       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2044
#201119 12:29:37 server id 128  end_log_pos 2119 CRC32 0x0c7bbf82       Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1605760177/*!*/;
BEGIN
/*!*/;
# at 2119
#201119 12:29:37 server id 128  end_log_pos 2181 CRC32 0x7b234bfe       Table_map: `test001`.`testTable` mapped to number 111
# at 2181
#201119 12:29:37 server id 128  end_log_pos 2248 CRC32 0x4e0b3c57       Write_rows: table id 111 flags: STMT_END_F

BINLOG '
sfS1XxOAAAAAPgAAAIUIAAAAAG8AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
CP5LI3s=
sfS1Xx6AAAAAQwAAAMgIAAAAAG8AAAAAAAEAAgAE//AiAAAADwDov5nmmK/nrKwxMDXmnaEG5paw
5aKec8kPVzwLTg==
'/*!*/;
### INSERT INTO `test001`.`testTable`
### SET
###   @1=34 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第105条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 2248
#201119 12:29:37 server id 128  end_log_pos 2279 CRC32 0x363f891d       Xid = 637
COMMIT/*!*/;
# at 2279
#201119 12:33:51 server id 128  end_log_pos 2344 CRC32 0xcc488f6e       Anonymous_GTID  last_committed=7        sequence_number=8       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2344
#201119 12:33:51 server id 128  end_log_pos 2419 CRC32 0x3caaace6       Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1605760431/*!*/;
BEGIN
/*!*/;
# at 2419
#201119 12:33:51 server id 128  end_log_pos 2481 CRC32 0xc921521f       Table_map: `test001`.`testTable` mapped to number 111
# at 2481
#201119 12:33:51 server id 128  end_log_pos 2592 CRC32 0x700f2f73       Delete_rows: table id 111 flags: STMT_END_F

BINLOG '
r/W1XxOAAAAAPgAAALEJAAAAAG8AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
CB9SIck=
r/W1XyCAAAAAbwAAACAKAAAAAG8AAAAAAAEAAgAE//AeAAAADwDov5nmmK/nrKwxMDHmnaEM6KaB
5Yig6Zmk55qEc8kP8CAAAAAPAOi/meaYr+esrDEwM+adoQzopoHliKDpmaTnmoRzyQ9zLw9w
'/*!*/;
### DELETE FROM `test001`.`testTable`
### WHERE
###   @1=30 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第101条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要删除的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### DELETE FROM `test001`.`testTable`
### WHERE
###   @1=32 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第103条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要删除的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 2592
#201119 12:33:51 server id 128  end_log_pos 2623 CRC32 0x912dea44       Xid = 679
COMMIT/*!*/;
# at 2623
#201119 12:34:33 server id 128  end_log_pos 2688 CRC32 0x77cec15f       Anonymous_GTID  last_committed=8        sequence_number=9       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2688
#201119 12:34:33 server id 128  end_log_pos 2763 CRC32 0x0f0306bf       Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1605760473/*!*/;
BEGIN
/*!*/;
# at 2763
#201119 12:34:33 server id 128  end_log_pos 2825 CRC32 0x24e42cf7       Table_map: `test001`.`testTable` mapped to number 111
# at 2825
#201119 12:34:33 server id 128  end_log_pos 3007 CRC32 0xda9978ae       Update_rows: table id 111 flags: STMT_END_F

BINLOG '
2fW1XxOAAAAAPgAAAAkLAAAAAG8AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
CPcs5CQ=
2fW1Xx+AAAAAtgAAAL8LAAAAAG8AAAAAAAEAAgAE///wHwAAAA8A6L+Z5piv56ysMTAy5p2hDOim
geabtOaWsOeahHPJD/AfAAAADwDov5nmmK/nrKwxMDLmnaEJ5bey5pu05pawc8kP8CEAAAAPAOi/
meaYr+esrDEwNOadoQzopoHmm7TmlrDnmoRzyQ/wIQAAAA8A6L+Z5piv56ysMTA05p2hCeW3suab
tOaWsHPJD654mdo=
'/*!*/;
### UPDATE `test001`.`testTable`
### WHERE
###   @1=31 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第102条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要更新的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### SET
###   @1=31 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第102条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='已更新' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### UPDATE `test001`.`testTable`
### WHERE
###   @1=33 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第104条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要更新的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### SET
###   @1=33 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第104条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='已更新' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 3007
#201119 12:34:33 server id 128  end_log_pos 3038 CRC32 0x75d2ac1e       Xid = 683
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost ~]#

修改类型恢复

从上面的日志看出，更新动作，开始2763 结束是在3007（包括新增和删除）
下面只针对删除和修改进行恢复

[root@localhost tmp]# mysqlbinlog -vv --start-position=2763 --stop-position=3007 /var/lib/mysql/mysql-bin.000011|grep ^"###" >/tmp/updateData1
[root@localhost tmp]# more /tmp/updateData1
### UPDATE `test001`.`testTable`
### WHERE
###   @1=31 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第102条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要更新的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### SET
###   @1=31 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第102条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='已更新' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### UPDATE `test001`.`testTable`
### WHERE
###   @1=33 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第104条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要更新的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### SET
###   @1=33 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第104条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='已更新' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
[root@localhost tmp]# cat /tmp/updateData1 |sed -n '/###/p'|sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}'|sed -r '/WHERE/{:a;N;/@4/!ba;s/###   @2.*//g}'|sed 's/### //g;s/\/\*.*/,/g;'|sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g}'|egrep ^"UPDATE|SET|WHERE|@1|@3" | sed -r 's/(@3=.*),/\1/g' >/tmp/updateData2
[root@localhost tmp]# more /tmp/updateData2
UPDATE `test001`.`testTable`
SET
  @1=31 ,
  @3='要更新的' 
WHERE
  @1=31 ;
UPDATE `test001`.`testTable`
SET
  @1=33 ,
  @3='要更新的' 
WHERE
  @1=33 ;
[root@localhost tmp]# cat /tmp/updateData2|sed 's/@1/test_id/g;s/@3/test_author/g'  >/tmp/updateData2.sql
[root@localhost tmp]# 
[root@localhost tmp]# more /tmp/updateData2.sql
UPDATE `test001`.`testTable`
SET
  test_id=31 ,
  test_author='要更新的' 
WHERE
  test_id=31 ;
UPDATE `test001`.`testTable`
SET
  test_id=33 ,
  test_author='要更新的' 
WHERE
  test_id=33 ;
  
#将生成的回滚SQL导回数据库：
[root@localhost tmp]# mysql -h127.0.0.1 -uroot -pPwd@123456 </tmp/updateData2.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost tmp]# 

命令释义

cat /tmp/updateData1
#读取文件
|sed -n ‘/###/p’
#过滤出以“###”开头的行，固定形式
|sed ‘/WHERE/{:a;N;/SET/!ba;s/([^\n])\n(.)\n(.)/\3\n\2\n\1/}’
#转换SET和WHERE子句位置，固定形式
|sed -r '/WHERE/{:a;N;/@4/!ba;s/### @2.//g}’
#该步命令的作用是删除每个where子句中的非主键字段，本示例中主键字段是@1，其余均为非主键字段；黄色部分需根据实际情况更改；
|sed ‘s/### //g;s//*./,/g;’
#该步命令作用是删除“###”和“/…/”，并在每行末尾加上逗号，固定形式；
|sed ‘/WHERE/{:a;N;/@1/!ba;s/,/;/g}’
#该步命令作用是将每个where子句后面的逗号转为分号，分号代表一个update命令结束，@1是主键字段；黄色部分根据实际更改；
|egrep ^“UPDATE|SET|WHERE|@1|@3”
#该步命令作用是去除非主键和非修改字段，@1是主键列，@3是被修改的字段；黄色部分根据实际更改；
| sed -r 's/(@3=.),/\1/g’
#该步命令的作用是去除每个set子句后面的逗号，黄色部分根据实际更改。
cat /tmp/updateData2|sed ‘s/@1/test_id/g;s/@2/test_author/g’
#该命令是将@n替换成相应的字段名，黄色部分根据实际更改。

删除类型恢复

[root@localhost tmp]# mysqlbinlog -vv --start-datetime='2020-11-19 12:28:00' /var/lib/mysql/mysql-bin.000011 | more
# at 2279
#201119 12:33:51 server id 128  end_log_pos 2344 CRC32 0xcc488f6e       Anonymous_GTID  last_committed=7        sequence_number=8       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2344
#201119 12:33:51 server id 128  end_log_pos 2419 CRC32 0x3caaace6       Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1605760431/*!*/;
BEGIN
/*!*/;
# at 2419
#201119 12:33:51 server id 128  end_log_pos 2481 CRC32 0xc921521f       Table_map: `test001`.`testTable` mapped to number 111
# at 2481
#201119 12:33:51 server id 128  end_log_pos 2592 CRC32 0x700f2f73       Delete_rows: table id 111 flags: STMT_END_F

BINLOG '
r/W1XxOAAAAAPgAAALEJAAAAAG8AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
CB9SIck=
r/W1XyCAAAAAbwAAACAKAAAAAG8AAAAAAAEAAgAE//AeAAAADwDov5nmmK/nrKwxMDHmnaEM6KaB
5Yig6Zmk55qEc8kP8CAAAAAPAOi/meaYr+esrDEwM+adoQzopoHliKDpmaTnmoRzyQ9zLw9w
'/*!*/;
### DELETE FROM `test001`.`testTable`
### WHERE
###   @1=30 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第101条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要删除的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### DELETE FROM `test001`.`testTable`
### WHERE
###   @1=32 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第103条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要删除的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 2592
#201119 12:33:51 server id 128  end_log_pos 2623 CRC32 0x912dea44       Xid = 679
COMMIT/*!*/;
[root@localhost tmp]# mysqlbinlog -vv --start-position=2344 --stop-position=2763 /var/lib/mysql/mysql-bin.000011|grep ^"###" >/tmp/deleteData1
[root@localhost tmp]# more /tmp/deleteData1
### DELETE FROM `test001`.`testTable`
### WHERE
###   @1=30 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第101条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要删除的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### DELETE FROM `test001`.`testTable`
### WHERE
###   @1=32 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第103条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='要删除的' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
[root@localhost tmp]# cat /tmp/bin_data | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g' >/tmp/deleteData1.sql
cat: /tmp/bin_data: No such file or directory
[root@localhost tmp]# cat /tmp/deleteData1 | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g' >/tmp/deleteData1.sql
[root@localhost tmp]# more /tmp/deleteData1.sql
INSERT INTO `test001`.`testTable`
SELECT
  30 ,
  '这是第101条' ,
  '要删除的' ,
  '2020:11:19' ;
INSERT INTO `test001`.`testTable`
SELECT
  32 ,
  '这是第103条' ,
  '要删除的' ,
  '2020:11:19' ;
[root@localhost tmp]# mysql -h127.0.0.1 -uroot -pPwd@123456 </tmp/deleteData1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost tmp]# 

mysql> select * from testTable where test_id >=28;
+---------+-----------------+--------------+------------+
| test_id | test_title      | test_author  | test_date  |
+---------+-----------------+--------------+------------+
|      28 | 这是第4条       | 王德卿       | 2020-11-19 |
|      29 | 这是第100条     | 新增         | 2020-11-19 |
|      30 | 这是第101条     | 要删除的     | 2020-11-19 |
|      31 | 这是第102条     | 要更新的     | 2020-11-19 |
|      32 | 这是第103条     | 要删除的     | 2020-11-19 |
|      33 | 这是第104条     | 要更新的     | 2020-11-19 |
|      34 | 这是第105条     | 新增         | 2020-11-19 |
+---------+-----------------+--------------+------------+
7 rows in set (0.00 sec)
mysql>

insert类型恢复

插入最简单的就是直接delete就行，但是为了学习技术那有必要折腾一下

mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第106条','新增错了','2020-11-19');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第107条','新增错了','2020-11-19');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第108条','新增错了','2020-11-19');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第109条','新增错了','2020-11-19');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第110条','新增错了','2020-11-19');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `testTable` ( `test_title`, `test_author`, `test_date`) VALUES('这是第111条','新增错了','2020-11-19');
Query OK, 1 row affected (0.00 sec)
mysql> select * from testTable where test_id >=35;
+---------+-----------------+--------------+------------+
| test_id | test_title      | test_author  | test_date  |
+---------+-----------------+--------------+------------+
|      35 | 这是第106条     | 新增错了     | 2020-11-19 |
|      36 | 这是第107条     | 新增错了     | 2020-11-19 |
|      37 | 这是第108条     | 新增错了     | 2020-11-19 |
|      38 | 这是第109条     | 新增错了     | 2020-11-19 |
|      39 | 这是第110条     | 新增错了     | 2020-11-19 |
|      40 | 这是第111条     | 新增错了     | 2020-11-19 |
+---------+-----------------+--------------+------------+
6 rows in set (0.00 sec)

#老套路找对应的起止值
[root@localhost tmp]# mysqlbinlog -vv --start-datetime='2020-11-19 17:24:00'  /var/lib/mysql/mysql-bin.000011 | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201119 12:17:56 server id 128  end_log_pos 123 CRC32 0xe644b4e4        Start: binlog v 4, server v 5.7.31-log created 201119 12:17:56
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
9PG1Xw+AAAAAdwAAAHsAAAABAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeS0ROY=
'/*!*/;
# at 4306
#201119 17:25:48 server id 128  end_log_pos 4371 CRC32 0x15b11976       Anonymous_GTID  last_committed=13       sequence_number=14      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 4371
#201119 17:25:48 server id 128  end_log_pos 4446 CRC32 0x33890e72       Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1605777948/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 4446
#201119 17:25:48 server id 128  end_log_pos 4508 CRC32 0x3e0ad79f       Table_map: `test001`.`testTable` mapped to number 111
# at 4508
#201119 17:25:48 server id 128  end_log_pos 4581 CRC32 0xb77eca32       Write_rows: table id 111 flags: STMT_END_F

BINLOG '
HDq2XxOAAAAAPgAAAJwRAAAAAG8AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
CJ/XCj4=
HDq2Xx6AAAAASQAAAOURAAAAAG8AAAAAAAEAAgAE//AjAAAADwDov5nmmK/nrKwxMDbmnaEM5paw
5aKe6ZSZ5LqGc8kPMsp+tw==
'/*!*/;
### INSERT INTO `test001`.`testTable`
### SET
###   @1=35 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第106条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增错了' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 4581
#201119 17:25:48 server id 128  end_log_pos 4612 CRC32 0x2e418151       Xid = 707
COMMIT/*!*/;

省略……

'/*!*/;
### INSERT INTO `test001`.`testTable`
### SET
###   @1=40 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第111条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增错了' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 6111
#201119 17:25:49 server id 128  end_log_pos 6142 CRC32 0xbba3b369       Xid = 712
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost tmp]#            cat /tmp/insertData | sed -n '/###/p'|sed -r '/SET/{:a;N;/@4/!ba;s/###   @2.*//g}'|sed 's/### //g;s/\/\*.*/,/g;s/INSERT INTO/DELETE FROM/g;s/SET/WHERE/g;'|sed -r 's/(@1.*),/\1;/g' >/tmp/insertData2
cat: /tmp/insertData: No such file or directory
[root@localhost tmp]# mysqlbinlog -vv --start-position=4446 --stop-position=6111 /var/lib/mysql/mysql-bin.000011|grep ^"###" >/tmp/insertData
[root@localhost tmp]# 
[root@localhost tmp]# more /tmp/insertData
### INSERT INTO `test001`.`testTable`
### SET
###   @1=35 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第106条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增错了' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### INSERT INTO `test001`.`testTable`
### SET
###   @1=36 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第107条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增错了' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### INSERT INTO `test001`.`testTable`
### SET
###   @1=37 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第108条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增错了' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### INSERT INTO `test001`.`testTable`
### SET
###   @1=38 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第109条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增错了' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### INSERT INTO `test001`.`testTable`
### SET
###   @1=39 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第110条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增错了' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
### INSERT INTO `test001`.`testTable`
### SET
###   @1=40 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第111条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增错了' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
[root@localhost tmp]# cat /tmp/insertData | sed -n '/###/p'|sed -r '/SET/{:a;N;/@4/!ba;s/###   @2.*//g}'|sed 's/### //g;s/\/\*.*/,/g;s/INSERT INTO/DELETE FROM/g;s/SET/WHERE/g;'|sed -r 's/(@1.*),/\1;/g' >/tmp/insertData2
[root@localhost tmp]# more /tmp/insertData2
DELETE FROM `test001`.`testTable`
WHERE
  @1=35 ;

DELETE FROM `test001`.`testTable`
WHERE
  @1=36 ;

DELETE FROM `test001`.`testTable`
WHERE
  @1=37 ;

DELETE FROM `test001`.`testTable`
WHERE
  @1=38 ;

DELETE FROM `test001`.`testTable`
WHERE
  @1=39 ;

DELETE FROM `test001`.`testTable`
WHERE
  @1=40 ;

[root@localhost tmp]# cat /tmp/insertData2 |sed 's/@1/test_id/g' >/tmp/insertData2.sql
[root@localhost tmp]# more /tmp/insertData2.sql
DELETE FROM `test001`.`testTable`
WHERE
  test_id=35 ;

DELETE FROM `test001`.`testTable`
WHERE
  test_id=36 ;

DELETE FROM `test001`.`testTable`
WHERE
  test_id=37 ;

DELETE FROM `test001`.`testTable`
WHERE
  test_id=38 ;

DELETE FROM `test001`.`testTable`
WHERE
  test_id=39 ;

DELETE FROM `test001`.`testTable`
WHERE
  test_id=40 ;

[root@localhost tmp]# mysql -h127.0.0.1 -uroot -pPwd@123456 </tmp/insertData2.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost tmp]#

结果确认

mysql> select * from testTable where test_id >=35;
Empty set (0.00 sec)
mysql>

cat /tmp/insertData
#读取文件
| sed -n ‘/###/p’
#过滤出以“###”开头的行，固定形式；
|sed -r ‘/SET/{:a;N;/@4/!ba;s/### @2.//g}’
#该步命令的作用是去除非主键字段，黄色部分根据实际更改；
|sed 's/### //g;s//*./,/g;s/INSERT INTO/DELETE FROM/g;s/SET/WHERE/g;’
#去除“###”和“* \”，将insert into和set转为delete from和where语句，固定形式；
|sed -r 's/(@1.),/\1;/g’
#在每个语句的最后面加上分号，黄色部分根据实际修改。
cat /tmp/insertData2 |sed ‘s/@1/id/g’
#将@n转为对应的字段名

删库跑路-彻底删除

既然打算删库就删的彻底一点，不删的彻底那就不爽，能恢复的删除就不是个好程序员。

清空binlog命令,清空了binlog这样就没法通过binlog进行数据恢复了

mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |       201 |
| mysql-bin.000011 |       201 |
| mysql-bin.000012 |       201 |
| mysql-bin.000013 |       201 |
| mysql-bin.000014 |       154 |
+------------------+-----------+
14 rows in set (0.01 sec)
mysql> reset master ;
Query OK, 0 rows affected (0.01 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

清除指定文件前的binlog文件

mysql> PURGE BINARY LOGS TO 'mysql-bin.000002';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |       201 |
| mysql-bin.000011 |       201 |
| mysql-bin.000012 |       201 |
| mysql-bin.000013 |       201 |
| mysql-bin.000014 |       154 |
+------------------+-----------+
13 rows in set (0.00 sec)

按照时间点进行删除

mysql> PURGE BINARY LOGS BEFORE '2020-11-20 09:30:51';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000014 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql>

出自设置expire_logs_days自动过期清理binlog

删库一时爽，事后火葬场

在删库之前想明白了，删库后需要承担的责任，做这个操作的人都没有什么好下场，希望大家别为了一时爽，而进监狱。现在信息时代，只要你在线上操作，不管手段多高明最后绝对会追踪到个人。法律的雷池万万不可越。
案例：

2020年微盟删库事件

事件回顾
在这里插入图片描述在这里插入图片描述

2018年杭州

2018 年，杭州科技公司的技术总监邱某因不满企业裁员，遂心生报复，远程登录服务器删除了数据库上的一些关键索引和部分表格，造成该企业直接经济损失 225 万元。
后邱某被判赔偿公司 8 万元，判刑 2 年 6 个月，缓刑三年。

2017年北京

2017 年，北京一软件工程师徐某离职后因公司未能如期结清工资而心生怨恨，便利用其在所设计的网站中安插的后门文件将网站源代码全部删除。
徐某的行为直接导致公司经济损失 26.5 万元，后因破坏计算机信息系统罪，被判处有期徒刑 5 年。
在这里插入图片描述

删库跑路这事还是过过嘴瘾就罢了，千万不要犯错，也不要将代码外漏。平时当你不是人，这时候就是个人了。

文章出处：还记得那个「最牛删库跑路事件」的程序员吗？他被判了…

恢复数据也有工具——binlog2sql

binlog2sql简介

从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。

  • 数据快速回滚(闪回)
  • 主从切换后新master丢数据的修复
  • 从binlog生成标准SQL，带来的衍生功能

下载binlog2sql

在git上有步骤，在此也列举一下,如果有yum会很方便，yum可以安装git,更新python，等软件，国内建议使用阿里云的yum源就很好

克隆binlog2sql

[root@localhost ~]# git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
Cloning into 'binlog2sql'...
remote: Enumerating objects: 323, done.
remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323
Receiving objects: 100% (323/323), 152.32 KiB | 43.00 KiB/s, done.
Resolving deltas: 100% (171/171), done.

如果没有安装pip则需安装

[root@localhost binlog2sql]# pip install -r requirements.txt
bash: pip: command not found...

安装步骤

[root@localhost binlog2sql]#  wget https://bootstrap.pypa.io/ez_setup.py -O - | python
#安装pip
[root@localhost binlog2sql]# wget https://pypi.python.org/packages/source/p/pip/pip-1.5.tar.gz
[root@localhost binlog2sql]# 
[root@localhost binlog2sql]# tar xzf pip-1.5.tar.gz
[root@localhost pip-1.5]# cd pip-1.5
[root@localhost pip-1.5]# python setup.py install

测试相关包

[root@localhost pip-1.5]# pip install gevent

最后回到binlog2sql目录下进行相关安装

[root@localhost binlog2sql]# pip install -r requirements.txt

安装结束后使用工具

需满足以下条件，user需要的最小权限集合：
select, super/replication client, replication slave
#建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
权限说明
  • select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
  • super/replication client：两个权限都可以，需要执行’SHOW MASTER STATUS’, 获取server端的binlog列表
  • replication slave：通过BINLOG_DUMP协议获取binlog内容的权限

基本用法

解析标准SQL

[root@localhost binlog2sql]#  python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'Pwd@123456' -dtest001 -t testTable --start-file='mysql-bin.000004'
INSERT INTO `test001`.`testTable`(`test_id`, `test_title`, `test_date`, `test_author`) VALUES (41, '这是第111条', '2020-11-19', '新增错了'); #start 4 end 429 time 2020-11-20 08:39:21
[root@localhost binlog2sql]#

解析回滚SQL

[root@localhost binlog2sql]# mysqlbinlog -vv --start-datetime='2020-11-19 12:28:00' /var/lib/mysql/mysql-bin.000004 | more
BEGIN
/*!*/;
# at 294
#201120  8:39:21 server id 129  end_log_pos 356 CRC32 0xa86c53dc        Table_map: `test001`.`testTable` mapped to number 109
# at 356
#201120  8:39:21 server id 129  end_log_pos 429 CRC32 0xbe646d28        Write_rows: table id 109 flags: STMT_END_F

BINLOG '
ORC3XxOBAAAAPgAAAGQBAAAAAG0AAAAAAAEAB3Rlc3QwMDEACXRlc3RUYWJsZQAEAw8PCgQsAXgA
CNxTbKg=
ORC3Xx6BAAAASQAAAK0BAAAAAG0AAAAAAAEAAgAE//ApAAAADwDov5nmmK/nrKwxMTHmnaEM5paw
5aKe6ZSZ5LqGc8kPKG1kvg==
'/*!*/;
### INSERT INTO `test001`.`testTable`
### SET
###   @1=41 /* INT meta=0 nullable=0 is_null=0 */
###   @2='这是第111条' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @3='新增错了' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @4='2020:11:19' /* DATE meta=0 nullable=1 is_null=0 */
# at 429
#201120  8:39:21 server id 129  end_log_pos 460 CRC32 0x20ef72d5        Xid = 219
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

恢复指定位点

[root@localhost binlog2sql]# python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -p'Pwd@123456' -dtest001 -ttestTable --start-file='mysql-bin.000004' --start-position=294 --stop-position=429
DELETE FROM `test001`.`testTable` WHERE `test_id`=41 AND `test_title`='这是第111条' AND `test_date`='2020-11-19' AND `test_author`='新增错了' LIMIT 1; #start 294 end 429 time 2020-11-20 08:39:21
[root@localhost binlog2sql]#

修改演示

mysql> use test001;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM testTable WHERE test_id =  42;
+---------+-----------------+--------------+------------+
| test_id | test_title      | test_author  | test_date  |
+---------+-----------------+--------------+------------+
|      42 | 这是第111条     | 新增错了     | 2020-11-19 |
+---------+-----------------+--------------+------------+
1 row in set (0.01 sec)

mysql> 
mysql> UPDATE  testTable SET test_author ='已更新'  WHERE  test_id IN (42);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM testTable WHERE test_id =  42;
+---------+-----------------+-------------+------------+
| test_id | test_title      | test_author | test_date  |
+---------+-----------------+-------------+------------+
|      42 | 这是第111条     | 已更新      | 2020-11-19 |
+---------+-----------------+-------------+------------+
1 row in set (0.00 sec)

mysql>

恢复SQL语句

[root@localhost binlog2sql]# python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -p'Pwd@123456' -dtest001 -ttestTable --start-file='mysql-bin.000004' --start-position=294 --stop-position=11111
UPDATE `test001`.`testTable` SET `test_id`=42, `test_title`='这是第111条', `test_date`='2020-11-19', `test_author`='新增错了' WHERE `test_id`=42 AND `test_title`='这是第111条' AND `test_date`='2020-11-19' AND `test_author`='已更新' LIMIT 1; #start 1072 end 1383 time 2020-11-20 08:52:19
[root@localhost binlog2sql]# 

mysql> UPDATE `test001`.`testTable` SET `test_id`=42, `test_title`='这是第111条', `test_date`='2020-11-19', `test_author`='新增错了' WHERE `test_id`=42 AND `test_title`='这是第111条' AND `test_date`='2020-11-19' AND `test_author`='已更新' LIMIT 1; #start 1072 end 1383 time 2020-11-20 08:52:19
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM testTable WHERE test_id =  42;
+---------+-----------------+--------------+------------+
| test_id | test_title      | test_author  | test_date  |
+---------+-----------------+--------------+------------+
|      42 | 这是第111条     | 新增错了     | 2020-11-19 |
+---------+-----------------+--------------+------------+
1 row in set (0.00 sec)

mysql>

选项

mysql连接配置
-h host; -P port; -u user; -p password

解析模式

--stop-never 持续解析binlog。可选。默认False，同步至执行命令时最新的binlog位置。

-K, --no-primary-key 对INSERT语句去除主键。可选。默认False

-B, --flashback 生成回滚SQL，可解析大文件，不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。

--back-interval -B模式下，每打印一千行回滚SQL，加一句SLEEP多少秒，如不想加SLEEP，请设为0。可选。默认1.0。

解析范围控制

--start-file 起始解析文件，只需文件名，无需全路径 。必须。

--start-position/--start-pos 起始解析位置。可选。默认为start-file的起始位置。

--stop-file/--end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。

--stop-position/--end-pos 终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。

--start-datetime 起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。

--stop-datetime 终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。

对象过滤

-d, --databases 只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。

-t, --tables 只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。

--only-dml 只解析dml，忽略ddl。可选。默认False。

--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

参考：Mysql闪回工具之binlog2sql的原理及其使用
参考：pip安装
感谢:binlog2sql

Darren_wdq
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql binlog日志恢复【亲测有效，有例子实测】
03-22
二进制日志（binnary log）以事件形式记录了对MySQL数据库执行更改的所有操作。 binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT...数据恢复：通过mysqlbinlog工具来恢复数据。
MySQL——通过binlog恢复数据
热门推荐
醉酒的戈多的专栏
04-07 3万+
二进制日志(binnary log)以事件形式记录了对MySQL数据库执行更改的所有操作。binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE、DROP等）以及表数据修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。
mysql误删数据后，从binlog中进行恢复删除数据（拯救手残，不跑路）
mu_yu_xi的博客
03-28 9849
通过mysql的binlog日志，对误删数据进行恢复
MYSQL使用binlog日志恢复数据
好吃的油炸代码
08-23 4625
注意事项：本教程目的在于，你的MYSQL采用了binlog日志，且产生了binlog日志文件，使用日志文件恢复数据。
MySQL使用binlog日志恢复数据
Felix的博客
09-14 6380
MySQL使用binlog日志恢复数据
MySQL binlog日志恢复数据详细操作步骤
巴啦啦臭魔仙的博客
01-07 8060
因为后台管理人员刚误删就反馈给我，00038 是最新的，所以确定我需要恢复的数据在 mysql-bin.000038 文件内。PS：我是直接通过导出的 sql 文件进行恢复的，数据量少可以这样操作，数据量大还是需要根据 pos 点或者时间区间进行恢复。可以在终端工具中执行如下命令查看该文件内的内容，但是内容很多的情况下查看或者查询起来并不方便。如果为 ON 则为开启状态，如果开启了就可以继续操作了，如果没开启就GG。2、可以执行如下命令，查看文件最后一个操作事件Position结束点的值。
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
08-04
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
mysql.binlog数据恢复
12-14
这是一个eclipse安装插件，在oxygen下测试是可用的。...操作：1、获取binlog文件 2、选中要处理的部分 3、选择菜单“jhh->binlog->”下对应的操作 4、会在相同目录下生成sql文件（文件名相同，加了sql后缀）
mysql如何利用binlog进行数据恢复详解
12-16
最近线上误操作了一个数据，由于是直接修改的数据库，所有唯一的恢复方式就在mysql的binlog。binlog使用的是ROW模式，即受影响的每条记录都会生成一个sql。同时利用了binlog2sql项目。 MySQL Binary Log也就是常说的...
Mysql的Binlog数据恢复:不小心删除数据库详解
12-16
Mysql的Bin log数据恢复:不小心删除数据库 前言：因为不小心删除了测试机器上Mysql的一整个数据库Schema，因为是测试机所以没有做备份，现在通过MySQL的Bin log方式恢复到删除以前的数据库。 当然做Bin log的数据...
通过binlog恢复mysql数据
西原一点红的博客
06-19 2538
在临时库中，恢复定期执行的全量备份数据；然后基于全量备份的数据点，通过 BINLOG 来恢复误操作和正常的数据.binlog单个存储不要太大，会影响分析效率；在全量备份定时任务脚本中，最好加上 flush logs，按天存储binlog日志，方便应急恢复。
ES数据的导入和导出工具elasticsearch-dump
qinqinde123的博客
09-18 4458
1.安装es-dump https://blog.csdn.net/qq_31547771/article/details/104381869 2.es数据的导入导出 https://www.jianshu.com/p/8c5faa916c08
MySQL利用bin log恢复数据
CoderJia的学习之路
07-01 1438
本文尝试通过bin log恢复误删除的数据，删库不再跑了
windows下mysql中binlog日志分析和数据恢复
weixin_44137201的博客
05-27 4077
windows系统binlog恢复数据
Mysql 通过 binlog日志 恢复数据（数据搞丢看过来）
Jokers_lin的博客
08-21 8068
选中区间 只是重新执行了一次 --start-position=17639 到 --stop-position=18038 期间记录的操作（见案例7.6偏差） 文章仅供参考，问题也比较多，还有几个情况没考虑。 比如 日志不单单是这个表的日志，还夹杂着其他表，日志内按照执行时间顺序记录，如果区间中包含其他表操作，那么其他表也会被重新执行，会造成脏数据。
通过binlog恢复数据
zhangyupeng的博客
10-17 952
通过binlog恢复数据
MySQL使用binlog恢复数据
安静的技术宅
07-30 3739
MySQL的二进制日志（Binlog）是一种事务日志，用于记录对数据库的更改操作。复制: 从库通过拉取主库的binlog实现主从数据一致恢复: 通过重放binlog恢复数据丢失或误操作情况MySQL的Binlog是数据库的重要组成部分，它记录了数据库的更改操作，并为数据复制和数据恢复提供了有力支持。通过了解Binlog的原理和使用技巧，我们可以更好地利用这一特性，保障数据库的稳健运行，并在必要时实现高级的数据恢复。然而，进行数据恢复时，请务必谨慎操作，以免造成不可逆的损失。
Mysql使用binlog日志恢复数据
漠效的博客
08-31 1346
Mysql的binlog日志,以事件的形式记录了Mysql执行的除去 数据查询语句之外所有的DDL和DML语句，可以通过mysqlbinlog工具进行查看。模式特点优点缺点STATEMENT模式(默认)基于SQL语句的复制(statement-based replication, SBR),仅记录每一条会修改数据的sql语句binlog日志相对较小,减少了磁盘IO,提高了性能。
Mysql 通过binlog日志恢复数据
hzp666的博客
07-08 1万+
Binlog日志，即binary log，是二进制日志文件，有两个作用，一个是增量备份，另一个是主从复制，即主节点维护一个binlog日志文件，从节点从binlog中同步数据，也可以通过binlog日志来恢复数据1，登录mysql查看binlog日志的状态，输入show variables like ‘%log_bin%’;查看binlog为off关闭状态2，开启mysql binlog日志，进入mysql配置文件(vi /etc/my.cnf) 在mysqld区域内添加如下内容，①server-id =
mysql binlog 数据恢复工具
最新发布
08-07
MySQL提供了多种工具来进行binlog数据恢复。其中最常用的工具是mysqlbinlog。mysqlbinlog是一个命令行工具，用于解析binlog文件，并将其中的SQL语句打印出来。通过mysqlbinlog，可以将binlog中记录的SQL语句重新执行...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值