devbox
Monodyee
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL复制主从实例表DDL不一致导致失败案例

作者：Monodyee | 2024-05-05 13:09:45

赞

踩

mysql 13146

作者：土豆娃娃

简介：高级数据库工程师，从事数据库行业近10年，从Oralce转战MySQL，擅长MySQL数据库性能优化、备份恢复、国产数据库迁移，对开源数据库相关技术有浓厚兴趣。

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

背景说明：

在一次断网测试过程中，在主库发起了DDL操作，备库丢失该DDL，导致主从表结构不一致，接下来的测试竟然都正常，表结构不一致，不影响复制进程，感觉比较奇怪，在这之前都是认为主从表结构不一致会导致复制异常，为了弄明白这个问题，进行了问题复现验证。

测试环境

MySQL社区版 8.0.25
binlog_format=row

复现过程：

1、初始化8.0.25版本的两个实例，并且建立了主从复制关系，过程略

主机IP端口角色
10.0.0.703309master
10.0.0.583309slave

2、在58:3309中检查复制关系,确认正常

  1. mysql> show slave status \G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 10.0.0.70
  5.                   Master_User: repl
  6.                   Master_Port: 3309
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000002
  9.           Read_Master_Log_Pos: 1094
  10.                Relay_Log_File: mysql-relay-bin.000003
  11.                 Relay_Log_Pos: 442
  12.         Relay_Master_Log_File: mysql-bin.000002
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB: 
  16.           Replicate_Ignore_DB: 
  17.   ...
  18. 1 row in set, 1 warning (0.01 sec)

3、在70:3309中创建test库，并且创建测试表t_diff

  1. mysql> create database test;
  2. Query OK, 1 row affected (0.00 sec)
  3.  
  4. mysql> use test
  5. Database changed
  6. mysql> create table t_diff(id int primary key auto_increment, a varchar(10), b varchar(10), c varchar(10), d varchar(10));
  7. Query OK, 0 rows affected (0.01 sec)
  8.  
  9. mysql>

4、在70:3309中，往t_diff中插入4条测试数据

  1. 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');
  2. Query OK, 4 rows affected (0.01 sec)
  3. Records: 4  Duplicates: 0  Warnings: 0
  4.  
  5. mysql>

5、模拟主从表结构不一致，在58:3309中，在t_diff中删除d列

  1. mysql> alter table t_diff drop column d;
  2. Query OK, 0 rows affected (0.02 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4.  
  5. mysql>

6、在70:3309中，往t_diff中更新一条记录，并且查看表中数据

  1. mysql> update t_diff set a='a14', d='d14' where id=4;
  2. Query OK, 1 row affected (0.00 sec)
  3. Rows matched: 1  Changed: 1  Warnings: 0
  4.  
  5. mysql> select * from t_diff;
  6. +----+------+------+------+------+
  7. | id | a    | b    | c    | d    |
  8. +----+------+------+------+------+
  9. |  1 | a1   | b1   | c1   | d1   |
  10. |  2 | a2   | b2   | c2   | d2   |
  11. |  3 | a3   | b3   | c3   | d3   |
  12. |  4 | a14  | b4   | c4   | d14  |
  13. +----+------+------+------+------+
  14. 4 rows in set (0.00 sec)
  15.  
  16. mysql> select @@report_host;
  17. +---------------+
  18. | @@report_host |
  19. +---------------+
  20. | 10.0.0.70 |
  21. +---------------+
  22. 1 row in set (0.00 sec)
  23.  
  24. mysql>

7、在58:3309中，查看复制状态正常

  1. mysql> show slave status \G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 10.230.183.70
  5.                   Master_User: repl
  6.                   Master_Port: 3309
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000002
  9.           Read_Master_Log_Pos: 3658
  10.                Relay_Log_File: mysql-relay-bin.000003
  11.                 Relay_Log_Pos: 3006
  12.         Relay_Master_Log_File: mysql-bin.000002
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB: 
  16.           Replicate_Ignore_DB: 
  17.  ...
  18. mysql>

8、在58:3309中，查看表数据条数正确

  1. mysql> select * from test.t_diff;
  2. +----+------+------+------+
  3. | id | a    | b    | c    |
  4. +----+------+------+------+
  5. |  1 | a1   | b1   | c1   |
  6. |  2 | a2   | b2   | c2   |
  7. |  3 | a3   | b3   | c3   |
  8. |  4 | a14  | b4   | c4   |
  9. +----+------+------+------+
  10. 4 rows in set (0.00 sec)
  11.  
  12. mysql> select @@report_host;
  13. +---------------+
  14. | @@report_host |
  15. +---------------+
  16. | 10.0.0.58 |
  17. +---------------+
  18. 1 row in set (0.00 sec)
  19.  
  20. mysql>

9、为了查明主从执行的具体SQL，解析70:3309中最后更新的binlog信息

  1. [root@0I /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
  2. # at 1097
  3. #220302  9:52:15 server id 6  end_log_pos 1165  Update_rows: table id 129 flags: STMT_END_F
  4. ### UPDATE `test`.`t_diff`
  5. ### WHERE
  6. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
  7. ###   @2='a4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  8. ###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  9. ###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  10. ###   @5='d4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  11. ### SET
  12. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
  13. ###   @2='a14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  14. ###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  15. ###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  16. ###   @5='d14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  17. # at 1165
  18. #220302  9:52:15 server id 6  end_log_pos 1192  Xid = 160
  19. COMMIT/*!*/;
  20. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  21. DELIMITER ;
  22. # End of log file
  23. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  24. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  25. [root@0I /data/mysql/log]#

10、解析58:3309中最后插入的binlog信息

  1. [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
  2. # at 1098
  3. #220302  9:52:15 server id 6  end_log_pos 1159  Update_rows: table id 126 flags: STMT_END_F
  4. ### UPDATE `test`.`t_diff`
  5. ### WHERE
  6. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
  7. ###   @2='a4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  8. ###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  9. ###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  10. ### SET
  11. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
  12. ###   @2='a14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  13. ###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  14. ###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  15. # at 1159
  16. #220302  9:52:15 server id 6  end_log_pos 1186  Xid = 51
  17. COMMIT/*!*/;
  18. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  19. DELIMITER ;
  20. # End of log file
  21. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  22. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  23. [root:/data/mysql/log]#

11、解析58:3309中最后的relaylog信息

  1. [root:/data/mysql/log]# /data/software/mysql-8.0.25-linux-glibc2.[root@pod5-hb-c3-test-31 /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
  2. #220302  9:52:15 server id 6  end_log_pos 1165  Update_rows: table id 129 flags: STMT_END_F
  3. ### UPDATE `test`.`t_diff`
  4. ### WHERE
  5. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
  6. ###   @2='a4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  7. ###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  8. ###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  9. ###   @5='d4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  10. ### SET
  11. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
  12. ###   @2='a14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  13. ###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  14. ###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  15. ###   @5='d14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  16. # at 1286
  17. #220302  9:52:15 server id 6  end_log_pos 1192  Xid = 160
  18. COMMIT/*!*/;
  19. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  20. DELIMITER ;
  21. # End of log file
  22. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  23. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  24. [root:/data/mysql/log]#

12、从上面三个日志文件解析可以得知，主库的binlog记录完整数据，从库的relay log记录完整数据，而到了从库的binlog，就只有前4个字段了，此处获得如下几个疑问?

  • 1.主库、从库字段不一致，为什么可以正常同步数据

  • 2.从库应用relaylog的时候，是否跳过了字段名称检查

现象解答

经过多方资料查找与咨询，最终在官方资料中找到答案，一定条件下复制结构的主、从库中表结构允许不一致，即主库相比从库多了字段、少了字段，都不影响同步，甚至在部分场景下，数据类型不一致都是可以正常同步的

b0d2d2dcdd004bd3fef5c587ff12fe63.png

主从表字段数量不一致的条件及验证

主从相同的字段，其定义顺序必须一致

比如本次测试中刚开始的建表语句，主从都是具有相同的字段，并且顺序一致

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

如果我们此时使用下面的命令，在从库58:3309中修改表结构，即可以使表结构顺序不一致

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

在主库70:3309做一次update动作

  1. mysql> update t_diff set a='a14', d='d14' where id=4;
  2. Query OK, 1 row affected (0.00 sec)
  3. Rows matched: 1  Changed: 1  Warnings: 0
  4.  
  5. mysql> select * from t_diff;
  6. +----+------+------+------+------+
  7. | id | a    | b    | c    | d    |
  8. +----+------+------+------+------+
  9. |  1 | a1   | b1   | c1   | d1   |
  10. |  2 | a2   | b2   | c2   | d2   |
  11. |  3 | a3   | b3   | c3   | d3   |
  12. |  4 | a14  | b4   | c4   | d14  |
  13. +----+------+------+------+------+
  14. 4 rows in set (0.00 sec)
  15.  
  16. mysql>

此时再查看从库58:3309中的数据

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

可以看到一个比较神奇的地方，虽然数据复制过来了，但是数据是错乱的。

  • 1.主库ID为4的数据修改内容为a=>'a14', d=>'d14'

  • 2.从库ID为4的数据修改内容为a=>'a14', d=>'b4', c=>'d14'

解析主binlog、从库relaylog，发现内容均一致

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

然而在从库的binlog中，就变成了

  1. ### UPDATE `test`.`t_diff`
  2. ### WHERE
  3. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
  4. ###   @2='a4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  5. ###   @3='d4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  6. ###   @4='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  7. ###   @5='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  8. ### SET
  9. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
  10. ###   @2='a14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  11. ###   @3='b4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  12. ###   @4='c4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  13. ###   @5='d14' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
  14. # at 2495

从这个现象，我们可以大胆的猜测，官方解释的字段顺序一致，其实只是针对字段类型来说，并不要求字段名称一致，为验证心中所想，再做进一步测试，将从库58:3309的字段d，重命名为e

alter table t_diff change d e varchar(10);

此时主库70:3309表结构为

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

从库58:3309表结构为

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

在主库70:3309中发起新的update命令

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

观察从库58:3309中的最新数据

  1. mysql> select * from t_diff;
  2. +----+------+------+------+------+
  3. | id | a    | e    | b    | c    |
  4. +----+------+------+------+------+
  5. |  1 | a1   | d1   | b1   | c1   |
  6. |  2 | a2   | d2   | b2   | c2   |
  7. |  3 | a13  | b3   | c3   | d13  |
  8. |  4 | a14  | b4   | c4   | d14  |
  9. +----+------+------+------+------+
  10. 4 rows in set (0.00 sec)
  11.  
  12. 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更新

  1. mysql> update t_diff set a='a12', d='d12' where id=2;
  2. Query OK, 1 row affected (0.00 sec)
  3. Rows matched: 1  Changed: 1  Warnings: 0
  4.  
  5. mysql> select * from t_diff;
  6. +----+------+------+------+------+
  7. | id | a    | b    | c    | d    |
  8. +----+------+------+------+------+
  9. |  1 | a1   | b1   | c1   | d1   |
  10. |  2 | a12  | b2   | c2   | d12  |
  11. |  3 | a13  | b3   | c3   | d13  |
  12. |  4 | a14  | b4   | c4   | d14  |
  13. +----+------+------+------+------+
  14. 4 rows in set (0.00 sec)
  15.  
  16. mysql>

看从库58:3309的表数据，发现并未更新

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

观察58:3309的复制状态

d1b75a032484b9555281d19560ed4dc0.png

查询表performance_schema.replication_applier_status_by_worker中数据信息

  1. mysql> select * from performance_schema.replication_applier_status_by_worker limit 1 \G
  2. *************************** 1. row ***************************
  3.                                            CHANNEL_NAME: 
  4.                                               WORKER_ID: 1
  5.                                               THREAD_ID: NULL
  6.                                           SERVICE_STATE: OFF
  7.                                       LAST_ERROR_NUMBER: 13146
  8.                                      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
  9. 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
  10.                                LAST_APPLIED_TRANSACTION: 2b8e36fa-9939-11ec-b5a7-8446fe2f3210:22
  11.      LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2022-03-02 11:22:55.339506
  12.     LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2022-03-02 11:22:55.339506
  13.          LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2022-03-02 11:22:54.182084
  14.            LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2022-03-02 11:22:54.183170
  15.                                    APPLYING_TRANSACTION: 2b8e36fa-9939-11ec-b5a7-8446fe2f3210:23
  16.          APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2022-03-02 15:06:54.591737
  17.         APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2022-03-02 15:06:54.591737
  18.              APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2022-03-02 15:06:53.429206
  19.                  LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
  20.    LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  21.   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
  22. LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
  23.                      APPLYING_TRANSACTION_RETRIES_COUNT: 0
  24.        APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  25.       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
  26.     APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
  27. 1 row in set (0.00 sec)
  28.  
  29. mysql>

报错信息为Column 1 of table 'test.t_diff' cannot be converted from type 'varchar(40(bytes))' to type 'int'，也就是我们上面在从库上做了g字段的添加，导致数据类型无法转换，同步才异常中断。

主从差异字段，必须有默认值

我们上面测试的int、varchar(10)数据类型都是有默认值的，此处直接给出所有具有默认值的数据类型

a84fa6199b19ce8686e824dea00b87e4.png

主从表字段类型不一致也能同步的情况

这种情况比较好理解，核心思路就是字段精度或者存储范围扩大。

为继续试验，先把从库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就直接报错中断了，错误信息为

  1. mysql> select * from performance_schema.replication_applier_status_by_worker limit 1 \G
  2. *************************** 1. row ***************************
  3.                                            CHANNEL_NAME: 
  4.                                               WORKER_ID: 1
  5.                                               THREAD_ID: NULL
  6.                                           SERVICE_STATE: OFF
  7.                                       LAST_ERROR_NUMBER: 13146
  8.                                      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
  9. 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
  10.                                LAST_APPLIED_TRANSACTION: 2b8e36fa-9939-11ec-b5a7-8446fe2f3210:25
  11.      LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2022-03-02 16:08:02.092786
  12.     LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2022-03-02 16:08:02.092786
  13.          LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2022-03-02 16:08:58.042357
  14.            LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2022-03-02 16:08:58.043196
  15.                                    APPLYING_TRANSACTION: 2b8e36fa-9939-11ec-b5a7-8446fe2f3210:26
  16.          APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2022-03-02 16:14:39.577788
  17.         APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2022-03-02 16:14:39.577788
  18.              APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2022-03-02 16:14:38.413522
  19.                  LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
  20.    LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  21.   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
  22. LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
  23.                      APPLYING_TRANSACTION_RETRIES_COUNT: 0
  24.        APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  25.       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
  26.     APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
  27. 1 row in set (0.00 sec)

而如果是主库字段类型为tinyint，从库字段类型为int，那么复制就能正常运行，也就是上面所述的存储范围扩大。

下面是整理的常用数据类型精度(存储范围)递增扩大顺序，注意在浮点型的精度也必须主库小于等于从库，字符串类型的长度也是主库小于等于从库

  1. TINYINT->SMALLINT->MEDIUMINT->INT->BIGINT
  2. DECIMAL->FLOAT->DOUBLE->NUMERIC
  3. CHAR\VARCHAR->TEXT

从库应用relaylog的搜索算法

上面我们还提到一个疑问，从库解析出来的relaylog中，包含完整的更新前的字段在where条件中

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

实际上由于我的主从做了表字段名字不一致的处理，转换为正常字段后where条件是无法找到数据的，而实际上数据却同步写到从库了，数据变动如下

  1. 1) 主库ID为4的数据修改内容为`a=>'a14', d=>'d14'`
  2. 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

Enjoy GreatSQL :)

《深入浅出MGR》视频课程

戳此小程序即可直达B站

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


文章推荐：

想看更多技术好文，点个“在看”吧！

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Monodyee/article/detail/539011
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号