MySQL複製主從例項表DDL不一致導致失敗案例

語言: CN / TW / HK

作者:土 豆娃娃

簡介:高階資料庫工程師,從事資料庫行業近10年,從Oralce轉戰MySQL,擅長MySQL資料庫效能優化、備份恢復、國產資料庫遷移,對開源資料庫相關技術有濃厚興趣。

* GreatSQL社群原創內容未經授權不得隨意使用,轉載請聯絡小編並註明來源。

背景說明:

在一次斷網測試過程中,在主庫發起了DDL操作,備庫丟失該DDL,導致主從表結構不一致,接下來的測試竟然都正常,表結構不一致,不影響複製程序,感覺比較奇怪,在這之前都是認為主從表結構不一致會導致複製異常,為了弄明白這個問題,進行了問題復現驗證。

測試環境

MySQL社群版 8.0.25

binlog_format=row

復現過程:

1、初始化8.0.25版本的兩個例項,並且建立了主從複製關係,過程略

主機IP 角色
10.0.0.70 3309 master
10.0.0.58 3309 slave

2、在58:3309中檢查複製關係,確認正常

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.70
                  Master_User: repl
                  Master_Port: 3309
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1094
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 442
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
  ...
1 row in set, 1 warning (0.01 sec)

3、在70:3309中建立test庫,並且建立測試表t_diff

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table t_diff(id int primary key auto_increment, a varchar(10), b varchar(10), c varchar(10), d varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql>

4、在70:3309中,往t_diff中插入4條測試資料

mysql> insert into t_diff values(1, 'a1', 'b1', 'c1', 'd1'),(2, 'a2', 'b2', 'c2', 'd2'),(3, 'a3', 'b3', 'c3', 'd3'),(4, 'a4', 'b4', 'c4', 'd4');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>

5、模擬主從表結構不一致,在58:3309中,在t_diff中刪除d列

mysql> alter table t_diff drop column d;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

6、在70:3309中,往t_diff中更新一條記錄,並且查看錶中資料

mysql> update t_diff set a='a14', d='d14' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_diff;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | a1   | b1   | c1   | d1   |
|  2 | a2   | b2   | c2   | d2   |
|  3 | a3   | b3   | c3   | d3   |
|  4 | a14  | b4   | c4   | d14  |
+----+------+------+------+------+
4 rows in set (0.00 sec)

mysql> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 10.0.0.70 |
+---------------+
1 row in set (0.00 sec)

mysql>

7、在58:3309中,檢視複製狀態正常

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.230.183.70
                  Master_User: repl
                  Master_Port: 3309
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 3658
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 3006
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
 ...
mysql> 

8、在58:3309中,查看錶資料條數正確

mysql> select * from test.t_diff;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 | a1   | b1   | c1   |
|  2 | a2   | b2   | c2   |
|  3 | a3   | b3   | c3   |
|  4 | a14  | b4   | c4   |
+----+------+------+------+
4 rows in set (0.00 sec)

mysql> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 10.0.0.58 |
+---------------+
1 row in set (0.00 sec)

mysql> 

9、為了查明主從執行的具體SQL,解析70:3309中最後更新的binlog資訊

[[email protected] /data/mysql/log]# /data/software/mysql-8.0.25-linux-glibc2.12-x86_64/bin/mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000003 | tail -n 23
# at 1097
#220302  9:52:15 server id 6  end_log_pos 1165  Update_rows: table id 129 flags: STMT_END_F
### UPDATE `test`.`t_diff`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @5='d4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @5='d14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
# at 1165
#220302  9:52:15 server id 6  end_log_pos 1192  Xid = 160
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET [email protected]_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[[email protected] /data/mysql/log]#

10、解析58:3309中最後插入的binlog資訊

[root:/data/mysql/log]# /data/software/mysql-8.0.25-linux-glibc2.12-x86_64/bin/mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000003 | tail -n 21
# at 1098
#220302  9:52:15 server id 6  end_log_pos 1159  Update_rows: table id 126 flags: STMT_END_F
### UPDATE `test`.`t_diff`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
# at 1159
#220302  9:52:15 server id 6  end_log_pos 1186  Xid = 51
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET [email protected]_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root:/data/mysql/log]#

11、解析58:3309中最後的relaylog資訊

[root:/data/mysql/log]# /data/software/mysql-8.0.25-linux-glibc2.[[email protected] /data/mysql/log]# /data/software/mysql-8.0.25-linux-glibc2.12-x86_64/bin/mysqlbinlog -vvv --base64-output=decode-rows mysql-relay-bin.000006 | tail -n 22
#220302  9:52:15 server id 6  end_log_pos 1165  Update_rows: table id 129 flags: STMT_END_F
### UPDATE `test`.`t_diff`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @5='d4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @5='d14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
# at 1286
#220302  9:52:15 server id 6  end_log_pos 1192  Xid = 160
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET [email protected]_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root:/data/mysql/log]#

12、從上面三個日誌檔案解析可以得知,主庫的binlog記錄完整資料,從庫的relay log記錄完整資料,而到了從庫的binlog,就只有前4個欄位了,此處獲得如下幾個疑問?

  • 1.主庫、從庫欄位不一致,為什麼可以正常同步資料

  • 2.從庫應用relaylog的時候,是否跳過了欄位名稱檢查

現象解答

經過多方資料查詢與諮詢,最終在官方資料中找到答案,一定條件下複製結構的主、從庫中表結構允許不一致,即主庫相比從庫多了欄位、少了欄位,都不影響同步,甚至在部分場景下,資料型別不一致都是可以正常同步的

主從表字段數量不一致的條件及驗證

主從相同的欄位,其定義順序必須一致

比如本次測試中剛開始的建表語句,主從都是具有相同的欄位,並且順序一致

create table t_diff(id int primary key auto_increment, a varchar(10), b varchar(10), c varchar(10), d varchar(10));

如果我們此時使用下面的命令,在從庫58:3309中修改表結構,即可以使表結構順序不一致

mysql> alter table t_diff change d d varchar(10) after a;
Query OK, 0 rows affected (0.02 sec)
mysql>  select * from t_diff;
+----+------+------+------+------+
| id | a    | d    | b    | c    |
+----+------+------+------+------+
|  1 | a1   | d1   | b1   | c1   |
|  2 | a2   | d2   | b2   | c2   |
|  3 | a3   | d3   | b3   | c3   |
|  4 | a4   | d4   | b4   | c4   |
+----+------+------+------+------+
4 rows in set (0.00 sec)

在主庫70:3309做一次update動作

mysql> update t_diff set a='a14', d='d14' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_diff;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | a1   | b1   | c1   | d1   |
|  2 | a2   | b2   | c2   | d2   |
|  3 | a3   | b3   | c3   | d3   |
|  4 | a14  | b4   | c4   | d14  |
+----+------+------+------+------+
4 rows in set (0.00 sec)

mysql> 

此時再檢視從庫58:3309中的資料

mysql>  select * from t_diff;
+----+------+------+------+------+
| id | a    | d    | b    | c    |
+----+------+------+------+------+
|  1 | a1   | d1   | b1   | c1   |
|  2 | a2   | d2   | b2   | c2   |
|  3 | a3   | d3   | b3   | c3   |
|  4 | a14  | b4   | c4   | d14  |
+----+------+------+------+------+
4 rows in set (0.00 sec)

mysql>

可以看到一個比較神奇的地方,雖然資料複製過來了,但是資料是錯亂的。

  • 1.主庫ID為4的資料修改內容為 a=>'a14', d=>'d14'
  • 2.從庫ID為4的資料修改內容為 a=>'a14', d=>'b4', c=>'d14'

解析主binlog、從庫relaylog,發現內容均一致

#220302 11:09:54 server id 6  end_log_pos 2286  Update_rows: table id 148 flags: STMT_END_F
### UPDATE `test`.`t_diff`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @5='d4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @5='d14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
# at 2286

然而在從庫的binlog中,就變成了

### UPDATE `test`.`t_diff`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='d4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @5='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @5='d14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
# at 2495

從這個現象,我們可以大膽的猜測,官方解釋的欄位順序一致,其實只是針對欄位型別來說,並不要求欄位名稱一致,為驗證心中所想,再做進一步測試,將從庫58:3309的欄位d,重新命名為e

alter table t_diff change d e varchar(10);

此時主庫70:3309表結構為

mysql> show create table t_diff \G
*************************** 1. row ***************************
       Table: t_diff
Create Table: CREATE TABLE `t_diff` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `c` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `d` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql>

從庫58:3309表結構為

mysql> show create table t_diff \G
*************************** 1. row ***************************
       Table: t_diff
Create Table: CREATE TABLE `t_diff` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `e` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `c` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql>

在主庫70:3309中發起新的update命令

mysql> update t_diff set a='a13', d='d13' where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_diff;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | a1   | b1   | c1   | d1   |
|  2 | a2   | b2   | c2   | d2   |
|  3 | a13  | b3   | c3   | d13  |
|  4 | a14  | b4   | c4   | d14  |
+----+------+------+------+------+
4 rows in set (0.00 sec)

mysql>

觀察從庫58:3309中的最新資料

mysql> select * from t_diff;
+----+------+------+------+------+
| id | a    | e    | b    | c    |
+----+------+------+------+------+
|  1 | a1   | d1   | b1   | c1   |
|  2 | a2   | d2   | b2   | c2   |
|  3 | a13  | b3   | c3   | d13  |
|  4 | a14  | b4   | c4   | d14  |
+----+------+------+------+------+
4 rows in set (0.00 sec)

mysql>

可以看到資料仍然同步了,並且按照主庫的值順序重新賦值了整行到從庫,也驗證了我們上面的猜測。

主從相同的欄位(其實是欄位資料型別),必須建立在差異欄位之前

使用下面的命令,在從庫58:3309中新增欄位f int,此時主從的前5個欄位型別都是Int\varchar(10)\varchar(10)\varchar(10)\varchar(10),資料可以同步,上面的實驗也驗證了此說明

alter table t_diff add column f int;

我這時在從庫58:3309的表結構中,再新增一個欄位g int,但是位置放在欄位id之後,看資料同步情況

alter table t_diff add g int after id;

在主庫70:3309做update更新

mysql> update t_diff set a='a12', d='d12' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_diff;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | a1   | b1   | c1   | d1   |
|  2 | a12  | b2   | c2   | d12  |
|  3 | a13  | b3   | c3   | d13  |
|  4 | a14  | b4   | c4   | d14  |
+----+------+------+------+------+
4 rows in set (0.00 sec)

mysql>

看從庫58:3309的表資料,發現並未更新

mysql> select * from t_diff;
+----+------+------+------+------+------+------+
| id | g    | a    | e    | b    | c    | f    |
+----+------+------+------+------+------+------+
|  1 | NULL | a1   | d1   | b1   | c1   | NULL |
|  2 | NULL | a2   | d2   | b2   | c2   | NULL |
|  3 | NULL | a13  | b3   | c3   | d13  | NULL |
|  4 | NULL | a14  | b4   | c4   | d14  | NULL |
+----+------+------+------+------+------+------+
4 rows in set (0.00 sec)

觀察58:3309的複製狀態

查詢表performance_schema.replication_applier_status_by_worker中資料資訊

mysql> select * from performance_schema.replication_applier_status_by_worker limit 1 \G
*************************** 1. row ***************************
                                           CHANNEL_NAME: 
                                              WORKER_ID: 1
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 13146
                                     LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '2b8e36fa-9939-11ec-b5a7-8446fe2f3210:23' at master log mysql-bin.000003, end_log_pos 2912; Colu
mn 1 of table 'test.t_diff' cannot be converted from type 'varchar(40(bytes))' to type 'int'                                   LAST_ERROR_TIMESTAMP: 2022-03-02 15:06:53.429471
                               LAST_APPLIED_TRANSACTION: 2b8e36fa-9939-11ec-b5a7-8446fe2f3210:22
     LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2022-03-02 11:22:55.339506
    LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2022-03-02 11:22:55.339506
         LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2022-03-02 11:22:54.182084
           LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2022-03-02 11:22:54.183170
                                   APPLYING_TRANSACTION: 2b8e36fa-9939-11ec-b5a7-8446fe2f3210:23
         APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2022-03-02 15:06:54.591737
        APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2022-03-02 15:06:54.591737
             APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2022-03-02 15:06:53.429206
                 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                     APPLYING_TRANSACTION_RETRIES_COUNT: 0
       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
      APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
    APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)

mysql>

報錯資訊為 Column 1 of table 'test.t_diff' cannot be converted from type 'varchar(40(bytes))' to type 'int' ,也就是我們上面在從庫上做了g欄位的新增,導致資料型別無法轉換,同步才異常中斷。

主從差異欄位,必須有預設值

我們上面測試的int、varchar(10)資料型別都是有預設值的,此處直接給出所有具有預設值的資料型別

主從表字段型別不一致也能同步的情況

這種情況比較好理解,核心思路就是欄位精度或者儲存範圍擴大。

為繼續試驗,先把從庫58:3309上多的兩個欄位f、g刪除

mysql> alter table t_diff drop column f, drop column g;

在主庫70:3309新增欄位col_int型別為int

mysql> alter table t_diff add col_int int;

在從庫58:3309將欄位col_int型別從int修改為tinyint

mysql> alter table t_diff change  col_int col_int tinyint;

此時在主庫70:3309上對欄位col_int執行update

mysql> update t_diff set col_int=1000000000 where id =4;

此時在從庫58:3309的sql_thread就直接報錯中斷了,錯誤資訊為

mysql> select * from performance_schema.replication_applier_status_by_worker limit 1 \G
*************************** 1. row ***************************
                                           CHANNEL_NAME: 
                                              WORKER_ID: 1
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 13146
                                     LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '2b8e36fa-9939-11ec-b5a7-8446fe2f3210:26' at master log mysql-bin.000003, end_log_pos 3747; Colu
mn 5 of table 'test.t_diff' cannot be converted from type 'int' to type 'tinyint'                                   LAST_ERROR_TIMESTAMP: 2022-03-02 16:14:38.413747
                               LAST_APPLIED_TRANSACTION: 2b8e36fa-9939-11ec-b5a7-8446fe2f3210:25
     LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2022-03-02 16:08:02.092786
    LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2022-03-02 16:08:02.092786
         LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2022-03-02 16:08:58.042357
           LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2022-03-02 16:08:58.043196
                                   APPLYING_TRANSACTION: 2b8e36fa-9939-11ec-b5a7-8446fe2f3210:26
         APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2022-03-02 16:14:39.577788
        APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2022-03-02 16:14:39.577788
             APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2022-03-02 16:14:38.413522
                 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                     APPLYING_TRANSACTION_RETRIES_COUNT: 0
       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
      APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
    APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)

而如果是主庫欄位型別為tinyint,從庫欄位型別為int,那麼複製就能正常執行,也就是上面所述的儲存範圍擴大。

下面是整理的常用資料型別精度(儲存範圍)遞增擴大順序,注意在浮點型的精度也必須主庫小於等於從庫,字串型別的長度也是主庫小於等於從庫

TINYINT->SMALLINT->MEDIUMINT->INT->BIGINT
DECIMAL->FLOAT->DOUBLE->NUMERIC
CHAR\VARCHAR->TEXT

從庫應用relaylog的搜尋演算法

上面我們還提到一個疑問,從庫解析出來的relaylog中,包含完整的更新前的欄位在where條件中

#220302 11:09:54 server id 6  end_log_pos 2286  Update_rows: table id 148 flags: STMT_END_F
### UPDATE `test`.`t_diff`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @5='d4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @5='d14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
# at 2286

實際上由於我的主從做了表字段名字不一致的處理,轉換為正常欄位後where條件是無法找到資料的,而實際上資料卻同步寫到從庫了,資料變動如下

1) 主庫ID為4的資料修改內容為`a=>'a14', d=>'d14'`
2) 從庫ID為4的資料修改內容為`a=>'a14', d=>'b4', c=>'d14'`

可以得出如下結論,relay log中未記錄欄位名稱,只有欄位順序,先通過順序取出值後,再放到對應順序的欄位上去,也就解釋了為什麼從庫的update欄位和主庫update的欄位不一致。

另外一個問題就是從庫通過何種方法定位到update的這一行資料,畢竟上面的where條件不成立,後經過查證,從庫執行update、delete定位一條記錄時,預設查詢演算法通過引數 slave_rows_search_algorithms 控制,目前預設值為 INDEX_SCAN,HASH_SCAN ,按如下優先順序依次進行查詢

  • 1.主鍵

  • 2.具有非空約束的唯一索引,如果有多個索引滿足此條件,則使用最左邊的索引

  • 3.其他二級索引,如果有多個索引滿足此條件,則使用最左邊的索引

需要注意的是,資料庫不會使用下面的索引型別進行資料查詢

  • 1.Fulltext indexes.

  • 2.Hidden indexes.

  • 3.Generated indexes.

  • 4.Multi-valued indexes.

  • 5.Any index where the before-image of the row event does not contain all the columns of the index.

當沒有索引可用時,系統會針對整個表,做一個hash表,進行整行的hash匹配。

至此,由主從不一致測試帶來的幾個疑問都解開了,記錄一下,方便以後回顧

參考資料

https://dev.mysql.com/doc/refman/8.0/en/replication-features-row-searches.html

https://dev.mysql.com/doc/refman/8.0/en/replication-features-differing-tables.html

E n j o y   G r e a t S Q L   : )

M G R

B

https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0