来源:原创投稿
作者:土豆娃娃
简介:高级数据库工程师,从事数据库行业近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个字段了,此处获得如下几个疑问?
-
- 主库、从库字段不一致,为什么可以正常同步数据
-
- 从库应用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匹配。
至此,由主从不一致测试带来的几个疑问都解开了,记录一下,方便以后回顾
共同学习,写下你的评论
评论加载中...
作者其他优质文章