Linux下mysql误删或更新错数据后，从binlog中进行恢复数据(六)

一、整体思路：

1、查看binlog状态，确认binlog文件存放位置：

show variables like'%log_bin%';

 2、查看当前操作的binlog的状态：

 show master status;

3、flush logs刷新状态，重新生成新的binlog文件，确保原文件不被修改：

flush logs;

4、 查找删除语句，确定起始位置等信息。

show binlog events in 'mysql-bin.000035';

5、通过mysqlbinlog恢复数据：

/mysqlsoft/mysql/bin/mysqlbinlog  -vv   --start-position=234 --stop-position=426 /mysqlsoft/mysql/data/mysql-bin.000034 >c.txt

二、演示整个流程：

1、刷新binlog，生成新的文件(可省略)：

flush logs;
show master status;

2、创建临时表插入数据：

create table tm_2 
(id int not null auto_increment,
aa varchar(10),
bb varchar(10),
cc varchar(10),
primary key (id));
insert into tm_2
(aa,bb,cc)values
('A','a','aa'),
('B','b','bb'),
('C','c','cc'),
('D','d','dd');

3、修改和删除数据：

更新数据：
update tm_2 set aa='大C' where id=3;
删除数据：
delete from tm_2 where aa='D';
查询数据：
select * from tm_2;

4、 查询binlog和刷新binlog：

 show master status;
 flush logs;
 show binlog events in 'mysql-bin.000036';

+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                       |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000036 |    4 | Format_desc    |         1 |         124 | Server ver: 8.0.16, Binlog ver: 4                       |
| mysql-bin.000036 |  124 | Previous_gtids |         1 |         155 |                       |
| mysql-bin.000036 |  155 | Anonymous_Gtid |         1 |         234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
| mysql-bin.000036 |  234 | Query          |         1 |         434 | use `test`; create table tm_2 (id int not null auto_increment,aa varchar(10),bb varchar(10),cc varchar(10),primary key (id)) /* xid=2153 */ |
| mysql-bin.000036 |  434 | Anonymous_Gtid |         1 |         513 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
| mysql-bin.000036 |  513 | Query          |         1 |         588 | BEGIN                       |
| mysql-bin.000036 |  588 | Table_map      |         1 |         652 | table_id: 97 (test.tm_2)                       |
| mysql-bin.000036 |  652 | Write_rows     |         1 |         735 | table_id: 97 flags: STMT_END_F                       |
| mysql-bin.000036 |  735 | Xid            |         1 |         766 | COMMIT /* xid=2154 */                       |
| mysql-bin.000036 |  766 | Anonymous_Gtid |         1 |         845 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
| mysql-bin.000036 |  845 | Query          |         1 |         929 | BEGIN                       |
| mysql-bin.000036 |  929 | Table_map      |         1 |         993 | table_id: 97 (test.tm_2)                       |
| mysql-bin.000036 |  993 | Update_rows    |         1 |        1056 | table_id: 97 flags: STMT_END_F                       |
| mysql-bin.000036 | 1056 | Xid            |         1 |        1087 | COMMIT /* xid=2156 */                       |
| mysql-bin.000036 | 1087 | Anonymous_Gtid |         1 |        1166 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
| mysql-bin.000036 | 1166 | Query          |         1 |        1241 | BEGIN                       |
| mysql-bin.000036 | 1241 | Table_map      |         1 |        1305 | table_id: 97 (test.tm_2)                       |
| mysql-bin.000036 | 1305 | Delete_rows    |         1 |        1352 | table_id: 97 flags: STMT_END_F                       |
| mysql-bin.000036 | 1352 | Xid            |         1 |        1383 | COMMIT /* xid=2157 */                       |
| mysql-bin.000036 | 1383 | Rotate         |         1 |        1430 | mysql-bin.000037;pos=4                       |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+

5、通过mysqlbinlog将相关的binlog写入临时文件：

进入mysqlbinlog目录：
cd /mysqlsoft/mysql/bin/
第一步更新的binlog：
mysqlbinlog -vv --start-position=845 --stop-position=1056 /mysqlsoft/mysql/data/mysql-bin.000036 >a.txt
第二步删除的binlog：
mysqlbinlog -vv --start-position=1166 --stop-position=1352 /mysqlsoft/mysql/data/mysql-bin.000036 >b.txt

6、查看第一步更新的binlog的内容：

cat /mysqlsoft/mysql/bin/a.txt
[root@localhost bin]# cat a.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 155
#231012 20:34:01 server id 1  end_log_pos 124 CRC32 0xa7c30931  Start: binlog v 4, server v 8.0.16 created 231012 20:34:01
BINLOG '
uecnZQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgExCcOn
'/*!*/;
# at 845
#231012 20:45:44 server id 1  end_log_pos 929 CRC32 0x5e3c5b6c  Query   thread_id=28    exec_time=0     error_code=0
SET TIMESTAMP=1697114744/*!*/;
SET @@session.pseudo_thread_id=28/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=32/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 929
#231012 20:45:44 server id 1  end_log_pos 993 CRC32 0x90e646b8  Table_map: `test`.`tm_2` mapped to number 97
# at 993
#231012 20:45:44 server id 1  end_log_pos 1056 CRC32 0x8ffbbfcb         Update_rows: table id 97 flags: STMT_END_F

BINLOG '
eOonZRMBAAAAQAAAAOEDAAAAAGEAAAAAAAEABHRlc3QABHRtXzIABAMPDw8GKAAoACgADgEBAAID
/P8AuEbmkA==
eOonZR8BAAAAPwAAACAEAAAAAGEAAAAAAAEAAgAE//8AAwAAAAFDAWMCY2MAAwAAAATlpKdDAWMC
Y2PLv/uP
'/*!*/;
### UPDATE `test`.`tm_2`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='C' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='c' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='cc' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='大C' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='c' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='cc' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;

 7、根据binlog将更新的数据进行恢复：

update tm_2 set aa='C',bb='c',cc='cc' where id=3;

8、 查看第二步删除的binlog的内容：

cat /mysqlsoft/mysql/bin/b.txt
[root@localhost bin]# cat b.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 155
#231012 20:34:01 server id 1  end_log_pos 124 CRC32 0xa7c30931  Start: binlog v 4, server v 8.0.16 created 231012 20:34:01
BINLOG '
uecnZQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgExCcOn
'/*!*/;
# at 1166
#231012 20:46:00 server id 1  end_log_pos 1241 CRC32 0xd70158fe         Query   thread_id=28    exec_time=0     error_code=0
SET TIMESTAMP=1697114760/*!*/;
SET @@session.pseudo_thread_id=28/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=32/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 1241
#231012 20:46:00 server id 1  end_log_pos 1305 CRC32 0x1b59eec2         Table_map: `test`.`tm_2` mapped to number 97
# at 1305
#231012 20:46:00 server id 1  end_log_pos 1352 CRC32 0x1538251d         Delete_rows: table id 97 flags: STMT_END_F

BINLOG '
iOonZRMBAAAAQAAAABkFAAAAAGEAAAAAAAEABHRlc3QABHRtXzIABAMPDw8GKAAoACgADgEBAAID
/P8Awu5ZGw==
iOonZSABAAAALwAAAEgFAAAAAGEAAAAAAAEAAgAE/wAEAAAAAUQBZAJkZB0lOBU=
'/*!*/;
### DELETE FROM `test`.`tm_2`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='D' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='d' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='dd' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;

9、根据binlog将删除的数据进行恢复：

insert into tm_2 values(4,'D','d','dd');