关于mysql:主从结构不一致复制问题验证

42次阅读

共计 17350 个字符，预计需要花费 44 分钟才能阅读完成。

起源：原创投稿

作者：土豆娃娃
简介：高级数据库工程师，从事数据库行业近 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 信息

[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
# 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 COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@0I /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 COMPLETION_TYPE=@OLD_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.[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
#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 COMPLETION_TYPE=@OLD_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/ref…

https://dev.mysql.com/doc/ref…

Enjoy GreatSQL :)

文章举荐：

GreatSQL 季报（2021.12.26）

https://mp.weixin.qq.com/s/FZ…

技术分享 |sysbench 压测工具用法浅析

https://mp.weixin.qq.com/s/m1…

故障剖析 | linux 磁盘 io 利用率高，剖析的正确姿态

https://mp.weixin.qq.com/s/7c…

技术分享 | 闪回在 MySQL 中的实现和改良

https://mp.weixin.qq.com/s/6j…

万答 #20，索引下推如何进行数据过滤

https://mp.weixin.qq.com/s/pt…

对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

Gitee:

https://gitee.com/GreatSQL/Gr…

GitHub:

https://github.com/GreatSQL/G…

Bilibili：

https://space.bilibili.com/13…

微信 &QQ 群：

可搜寻增加 GreatSQL 社区助手微信好友，发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群：533341697

微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

正文完
mysql
发表至： mysql
2022-03-04
 0
关于mysql:datagrip使用mysqldump导出sql出现1109错误
关于mysql:mysql8容器之间的replication配置
关于mysql:MySQL更新数据update
关于数组:第24期索引设计多值索引的适用场景