MySQL複製主從例項表DDL不一致導致失敗案例
作者：土 豆娃娃
簡介：高階資料庫工程師，從事資料庫行業近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匹配。
至此，由主從不一致測試帶來的幾個疑問都解開了，記錄一下，方便以後回顧
參考資料
http://dev.mysql.com/doc/refman/8.0/en/replication-features-row-searches.html
http://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 站
http://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0
文 章 推 薦 ：
想 看 更 多 技 術 好 文 ， 點 個 “ 在 看 ” 吧 ！
「其他文章」
- 深入淺出MySQL 8.0錯誤日誌
- MySQL Update執行流程解讀
- MySQL複製主從例項表DDL不一致導致失敗案例
- Linux環境監控工具彙總
- MySQL 8.0 BACKUP LOCK淺析及其在xtrabackup的應用（深度好文）
- 建議收藏|MySQL DBA 防坑指南
- 利用sysbench執行測試
- GreatSQL 8.0.27 & 5.7.36背後的事
- MySQL8.0快速回收膨脹的UNDO表空間
- 通過DML語句淺談binlog和redo log
- MacOS下編譯percona及部分函式的運算差異
- MySQL為什麼"錯誤"選擇代價更大的索引
- 『葉問』#40，MySQL程序號、連線ID、查詢ID、InnoDB執行緒與系統執行緒如何對應
- 『葉問』#39，都有哪些情況可能導致MGR服務無法啟動
- MySQL SQL 優化引數 引發的悲劇
- MySQL備份問題排查和思考
- 技術分享 | Update更新慢、死鎖等問題的排查思路分享
- MySQL分割槽表的一個性能BUG
- 通過 Datafuse 理解全鏈路跟蹤
- 如何精確監控DB響應延時