实时分析数据丢失

[ocpeng]

背景

源库一直insert数据，指定位点启动my2sql，停止源库写入，查看解析SQL情况；

执行命令

./my2sql -user xxx -password 'xxx' -host xxx -port 3306 -mode repl -work-type 2sql -do-not-add-prifixDb -start-pos "7077788" -start-file "mysql-bin.000020" -output-dir /tmp/my2sql

日志

会一直卡在这里，单数数据一直在解析中 [2023/04/25 17:30:35] [info] binlogsyncer.go:148 create BinlogSyncer with config {1113306 mysql 10.10.xxx 3306 xxx utf8 false false false Local false 0 0s 0s 0 false false 0 } [2023/04/25 17:30:35] [info] binlogsyncer.go:374 begin to sync binlog from position (mysql-bin.000020, 7077788) [2023/04/25 17:30:35] [info] events.go:221 start thread to write redo/rollback sql into file [2023/04/25 17:30:35] [info] stats_process.go:166 start thread to analyze statistics from binlog [2023/04/25 17:30:35] [info] events.go:61 start thread 1 to generate redo/rollback sql [2023/04/25 17:30:35] [info] events.go:61 start thread 2 to generate redo/rollback sql [2023/04/25 17:30:35] [info] repl.go:16 start to get binlog from mysql [2023/04/25 17:30:35] [info] binlogsyncer.go:791 rotate to (mysql-bin.000020, 7077788)

停止

1. 停止源库insert数据写入；
2. 因为my2sql 还在一直运行，只能kill -15 停止；

问题

查看源库与my2sql forward.20.sql 文件比较，数据行数不一致，如下： 源库：

mysql> select count(*) from demo_tbl1;
+----------+
| count(*) |
+----------+
|      790 |
+----------+
1 row in set (0.00 sec)

my2sql forward.20.sql 数据行数：

# wc -l forward.20.sql
775 forward.20.sql

但是my2sql binlog_status.txt 通过自行binlog解析位点比较，确认没问题：

binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql-bin.000020  2023-04-25_17:30:31 2023-04-25_17:30:34 7082310    7128882    157      0        0        testdb          demo_tbl1           
mysql-bin.000020  2023-04-25_17:30:35 2023-04-25_17:30:50 7129060    7317609    633      0        0        testdb          demo_tbl1  

可以看到最后的位点是 7129060，自行解析binlog，查看数据：

#230425 17:30:50 server id 52  end_log_pos 7317609 CRC32 0x4a6c2e53     Write_rows: table id 379 flags: STMT_END_F
### INSERT INTO `testdb`.`demo_tbl1`
### SET
###   @1=790 /* INT meta=0 nullable=0 is_null=0 */
###   @2=790 /* INT meta=0 nullable=1 is_null=0 */
###   @3='ljp790' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
###   @4='2023-04-25 17:30:50' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 7317609
#230425 17:30:50 server id 52  end_log_pos 7317640 CRC32 0xca3b9bc9     Xid = 31435749
COMMIT/*!*/;

问题怀疑

forward.20.sql 数据不准，binlog_status.txt 无问题，怀疑kill -15 数据还在内存中，my2sql进程并没有把数据最终写入forward.20.sql文件，有其他方式优雅停止my2sql？