数据库测试案例20240322-binlog_format为row binlog日志分析，主备数据不一致会导致复制出问题

1 测试概述

master-1，master-2表数据test如下：

9:26:  [mytest]> select *From test;
+----+------+
| id | name |
+----+------+
| 10 | 123  |
+----+------+
1 row in set (0.00 sec)

2 在主库将数据删除导致数据不一致

09:26:  [mytest]> set sql_log_bin=0;

09:26:  [mytest]> delete from test;

09:27:  [mytest]> select *From test;
Empty set (0.00 sec)
09:27:  [mytest]> set sql_log_bin=1;

3 主库插入测试数据

09:27:  [mytest]> insert into test (name) select '12asda';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

09:27:  [mytest]> insert into test (name) select '12asddadaa';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

09:27:  [mytest]> insert into test (name) select '12asdaa';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

09:27:  [mytest]> insert into test (name) select '121asdaa';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

09:27:  [mytest]> select * from test;
+----+------------+
| id | name       |
+----+------------+
| 14 | 12asda     |
| 15 | 12asddadaa |
| 16 | 12asdaa    |
| 17 | 121asdaa   |
+----+------------+
4 rows in set (0.00 sec)

此时备库test表数据如下：
9:28:  [mytest]> select *From test;
+----+------------+
| id | name       |
+----+------------+
| 10 | 123        |
| 14 | 12asda     |
| 15 | 12asddadaa |
| 16 | 12asdaa    |
| 17 | 121asdaa   |
+----+------------+
5 rows in set (0.00 sec)

4 主库执行delete from表操作

09:35:  [mytest]> delete from test;
Query OK, 4 rows affected (0.01 sec)

09:35:  [mytest]> select *From test;
Empty set (0.00 sec)


备份库查询test
09:28:  [mytest]> select *From test;
+----+------+
| id | name |
+----+------+
| 10 | 123  |
+----+------+
1 row in set (0.01 sec)

09:36:  [mytest]> 

5 查看二进制文件内容

主库

09:39:  [mytest]> system mysqlbinlog --no-defaults --start_position=1765 -v -v --base64-output=DECODE-ROWS /data/master-1-bin.000004
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 233
#240321  9:18:10 server id 125  end_log_pos 126         Start: binlog v 4, server v 8.0.36 created 240321  9:18:10 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 1765
#240321  9:35:38 server id 125  end_log_pos 1819        Table_map: `mytest`.`test` mapped to number 91
# at 1819
#240321  9:35:38 server id 125  end_log_pos 1921        Delete_rows: table id 91 flags: STMT_END_F
### DELETE FROM `mytest`.`test`
### WHERE
###   @1=14 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='12asda' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### DELETE FROM `mytest`.`test`
### WHERE
###   @1=15 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='12asddadaa' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### DELETE FROM `mytest`.`test`
### WHERE
###   @1=16 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='12asdaa' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### DELETE FROM `mytest`.`test`
### WHERE
###   @1=17 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='121asdaa' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
# at 1921
#240321  9:35:38 server id 125  end_log_pos 1948        Xid = 36
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*/;
09:39:  [mytest]> 

slave数据库已经commit，查看传过来的日志也是上述日志。执行的sql语句为上述一条一条的delete语句。

6 主库插入主键为备库有数据导致冲突

09:42:  [mytest]> insert into test values (10,'dadadada');
Query OK, 1 row affected (0.05 sec)

7 slave数据库sql线程报错冲突

09:42:  [mytest]> insert into test values (10,'dadadada');
Query OK, 1 row affected (0.05 sec)

备份数据库

09:43:  [mytest]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.1.25
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-1-bin.000004
          Read_Master_Log_Pos: 2226
               Relay_Log_File: relay-1.000009
                Relay_Log_Pos: 2122
        Relay_Master_Log_File: master-1-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '552561c8-df9f-11ee-be05-000c29947a85:24' at source log master-1-bin.000004, end_log_pos 2199. See error log and/or performance_schema.

8 问题处理

在slave数据库进行如下处理，保障和主库数据保持一致。

09:44:  [mytest]> set sql_log_bin=0;  --因为我这边是双主，所以要不生成日志
Query OK, 0 rows affected (0.00 sec)

09:44:  [mytest]> delete from test;
Query OK, 1 row affected (0.00 sec)

09:44:  [mytest]> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

09:44:  [mytest]> start slave ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

09:45:  [mytest]> select *From test;
+----+----------+
| id | name     |
+----+----------+
| 10 | dadadada |
+----+----------+
1 row in set (0.00 sec)

09:45:  [mytest]> 

9 mysql8 auto increment新特性

09:45:  [mytest]> insert into test (name) select '123';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

09:46:  [mytest]> insert into test (name) select '321';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

09:46:  [mytest]> select *From test;
+----+----------+
| id | name     |
+----+----------+
| 10 | dadadada |
| 18 | 123      |
| 19 | 321      |
+----+----------+
3 rows in set (0.00 sec)

09:46:  [mytest]> 

自增变量持久化 max(primary_key）+1 已经从18 19 开始了