MySQL binlog日志恢复数据

1、 找到要恢复数据的binlog文件

show variables like '%log_bin%';

hsto.png

如下：可能存在好多个 binlog日志文件，例如：binlog.000087
有些教程里，文件名也可能是这样的：mysql-bin.000001

[zyj4x@ecm-c7f4 ~]$ cd /data1/mysql/log/binlog
[zyj4x@ecm-c7f4 binlog]$ ll
total 9312840
-rw-r----- 1 mysql mysql 1073741897 Oct 23 09:38 mysql-bin.000080
-rw-r----- 1 mysql mysql 1095890341 Oct 24 21:57 mysql-bin.000081
-rw-r----- 1 mysql mysql 1073742919 Oct 29 09:03 mysql-bin.000082
-rw-r----- 1 mysql mysql 1073742101 Nov  2 04:37 mysql-bin.000083
-rw-r----- 1 mysql mysql 1074056819 Nov  6 09:58 mysql-bin.000084
-rw-r----- 1 mysql mysql 1073742183 Nov 11 15:05 mysql-bin.000085
-rw-r----- 1 mysql mysql 1077408307 Nov 15 09:08 mysql-bin.000086
-rw-r----- 1 mysql mysql 1424433027 Nov 19 13:26 mysql-bin.000087
-rw-r----- 1 mysql mysql  374962944 Nov 20 15:22 mysql-bin.000088
-rw-r----- 1 mysql mysql        369 Nov 19 13:26 mysql-bin.index

2、把日志文件导出为.sql文件

最好不要执行命令查看内容：show binlog EVENTS in 'binlog.000019';
因为文件太大的话，可能有上亿条数据...

导出命令有两种，一种根据事务区间导出，一种根据日期区间导出

事务区间：

--start-position=573 --stop-position=718

日期区间：

--start-datetime="2022-08-10 9:16:59" --stop-datetime="2022-08-13 5:06:48"

事务区间导出命令：

mysqlbinlog.exe --start-position=312 --stop-position=647 -d 数据库  mysql-bin.000087 > result.sql

日期区间导出命令：

mysqlbinlog --start-datetime="2024-11-19 03:00:00" --stop-datetime="2024-11-19 12:00:00" -d 数据库  mysql-bin.000087 >result.sql

输出注释信息

mysqlbinlog.exe --start-datetime="2022-09-09 13:00:00" --stop-datetime="2022-09-09 14:00:00"  --base64-output=decode-rows -vv -d 数据库 mysql-bin.000087 > result.sql

3、定位详细位置
使用 --base64-output=decode-rows -vv参数导出调试信息
使用文本编辑器打开文件，查看需要恢复数据的详细时间段或事务ID

SET TIMESTAMP=1662692401/*!*/;
BEGIN
/*!*/;
# at 233366329
#220909 11:00:01 server id 1729449020  end_log_pos 233366421 CRC32 0xe1123aa3 	Table_map: `ccmbase`.`mkt_black_card` mapped to number 208397920
# at 233366421
#220909 11:00:01 server id 1729449020  end_log_pos 233366518 CRC32 0x17d0eafc 	Delete_rows: table id 208397920 flags: STMT_END_F
### DELETE FROM `数据库`.`1kt_black_card`
### WHERE
###   @1=1655 /* INT meta=0 nullable=0 is_null=0 */
###   @2='1995' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
###   @3=NULL /* VARSTRING(150) meta=150 nullable=1 is_null=1 */
###   @4=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
###   @5=NULL /* VARSTRING(150) meta=150 nullable=1 is_null=1 */
###   @6=1 /* INT meta=0 nullable=1 is_null=0 */
###   @7=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
###   @8=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
###   @9='2021-10-21 00:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @10=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @11=1 /* INT meta=0 nullable=1 is_null=0 */
###   @12=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @13='1100109' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
###   @14='1公司' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
# at 233366518
#220909 11:00:01 server id 1729449020  end_log_pos 233366610 CRC32 0x9ccd839a 	Table_map: `ccmbase`.`mkt_black_card` mapped to number 208397920
# at 233366610
#220909 11:00:01 server id 1729449020  end_log_pos 233366707 CRC32 0xe69a9180 	Delete_rows: table id 208397920 flags: STMT_END_F
### DELETE FROM `ccmbase`.`mkt_black_card`
### WHERE
###   @1=2167 /* INT meta=0 nullable=0 is_null=0 */
###   @2='01162683' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
###   @3=NULL /* VARSTRING(150) meta=150 nullable=1 is_null=1 */
###   @4=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
###   @5=NULL /* VARSTRING(150) meta=150 nullable=1 is_null=1 */
###   @6=1 /* INT meta=0 nullable=1 is_null=0 */
###   @7=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
###   @8=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
###   @9='2021-10-21 00:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @10=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @11=1 /* INT meta=0 nullable=1 is_null=0 */
###   @12=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @13='1001209' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
###   @14='12司' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
# at 233373977
#220909 11:00:01 server id 1729449020  end_log_pos 233374008 CRC32 0xd89a841d 	Xid = 9515296014
COMMIT/*!*/;
# at 233374008
#220909 11:00:01 server id 1729449020  end_log_pos 233374073 CRC32 0x1dc58658 	GTID	last_committed=193495	sequence_number=193497	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '60da55b8-ebb5-11ec-809c-b4055d1bdac9:140704071'/*!*/;
# at 233374073
#220909 11:00:01 server id 1729449020  end_log_pos 233374148 CRC32 0x7830e3c4 	Query	thread_id=14140044	exec_time=0	error_code=0
SET TIMESTAMP=1662692401/*!*/;

Xid：事务ID

4、使用详细时间段或事务ID重新导出SQL文件

mysqlbinlog --start-datetime="2024-11-19 06:19:00" --stop-datetime="2024-11-19 10:54:00"  mysql-bin.000087 > result.sql

此次导出文件不需要添加调试参数 --base64-output=decode-rows -vv，不然会恢复失败

5、将SQL文件恢复到测试环境

mysql u - root -p
use 数据库
source  result.sql

6、将恢复完成的数据导入至生产环