MySQL数据恢复
binlog的作用
数据恢复通过BinLog进行恢复,binlog的作用一个是进行主从同步,一个进行数据恢复,数据恢复的前提条件是binlog不能丢失,如果丢失了则不能进行恢复的。
- 在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)语句事件
- 查看所有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>
- 查看最后一个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>
- 刷新日志,此刻开始产生一个新编号的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日志命令:
- 查看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>
- 指定查询,从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>
- 下面进行数据删除恢复操作,数据插入
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>
- 查看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