Mysql双主架构及其异常

2022-05-18T10:54:29.211484+08:00 124482968 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction '284aaa9b-ac5d-11ea-8559-005056814878:1025875055' at master log mysql-bin.001266, end_log_pos 254572627; Column 10 of table 'bi.zmmr0051' cannot be converted from type 'varchar(210(bytes))' to type 'varchar(300(bytes) utf8)', Error_code: 1677

　　

1.mysql架构为mysql双主，master-1和master-2分别写入不同的表

2.mysql双主配置文件

[mysqld]
#***********base***************
basedir=/usr/local/mysql
datadir=/flex/mysql/data/mysql3306/var
socket=/flex/mysql/data/mysql3306/tmp/mysql.sock
port=3306
server-id=141  //master-1和master-2的server-id应不一样
skip-name-resolve
skip-slave-start
lower_case_table_names=1
transaction-isolation=READ-COMMITTED
default-time_zone = '+8:00'
log_timestamps=SYSTEM
##**********connection************
max_connections=500
max_connect_errors=100000
max_user_connections=300
####********binlog & relaylog********
log-error=/flex/mysql/data/mysql3306/log/mysql.err
log_bin=/flex/mysql/data/mysql3306/var/mysql-bin
binlog_cache_size=128k
binlog_stmt_cache_size=128k
max_binlog_cache_size=4G
max_binlog_stmt_cache_size=4G
binlog_format=row
expire_logs_days=7
max_binlog_size=500M
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
relay_log_recovery=ON
gtid-mode=on
enforce-gtid-consistency=on
slow_query_log=1
slow_query_log_file=/flex/mysql/data/mysql3306/var/slow.log
long_query_time=2
log_queries_not_using_indexes=ON
log-bin-trust-function-creators=1
###*******character set**************
character_set_server=utf8
###*******ignore&&define repl database********
#replicate-do-db=bi
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
##*************** buffer & timeout ***************
innodb_buffer_pool_size=8G
#
##*************** muti thread slave ***************
slave_parallel_type =LOGICAL_CLOCK
slave_parallel_workers=4
master_info_repository=TABLE
relay_log_info_repository=TABLE
slave_preserve_commit_order     =1
##*************** group commit ***************
binlog_group_commit_sync_delay              =1
binlog_group_commit_sync_no_delay_count     =1000
sync_binlog=1
innodb_flush_log_at_trx_commit=2
#
##*********** innodb ***************
innodb_undo_directory =/flex/mysql/data/mysql3306/var/undo
innodb_undo_log_truncate=on
innodb_max_undo_log_size=1024M
innodb_undo_tablespaces=3
autocommit=1
default_storage_engine              =InnoDB
default_tmp_storage_engine          =InnoDB
internal_tmp_disk_storage_engine    =InnoDB
###***********slave******************************
log-slave-updates=1       //该值决定从库的relaylog是否同时计入binlog，为1是计入，为0是不记录，默认为0，如果从库作为其他数据库的主库应当开启，此参数和-log_bin结合使用，双主架构需设为1
log-bin-trust-function-creators=1
#
[mysql]
socket=/flex/mysql/data/mysql3306/tmp/mysql.sock

 

 

3.异常现象

开发通过navicate修改某张表的字段属性，由varchar(4) -->varchar(8)

导致异常：看字面意思就是对端节点无法执行type转化，但是实际上对端数据库修改成功

解决：

经过了种种方法解决，均为成功，无奈和开发沟通，整库重建主主架构，才解决

 

2022-05-18T10:54:29.211484+08:00 124482968 [ERROR] Slave SQL for channel '': 
Worker 1 failed executing transaction '284aaa9b-ac5d-11ea-8559-005056814878:1025875055' 
at master log mysql-bin.001266, end_log_pos 254572627; Column 10 of table 'bi.zmmr0051' 
cannot be converted from type 'varchar(210(bytes))' to type 'varchar(300(bytes) utf8)', Error_code: 1677

 

 

4.GTID

此问题引发了我对GTID的又一轮理解，包括一些错误理解

mysql主主架构的同步原理：

1）.Master-1 所有数据库变更写进 Binary Log, master-1库线程 Binlog Dump 把 Binary Log 内容发送到master-2库 上(master-2库被动接受数据，不是主动去获取)。
2）.master-2 Io 线程读取 Master-1上 Binary Log 日志信息，把接受到的 Binary Log 日志写到本地中继日志 Relay Log
3）.Slave Sql 线程读取 Ralay Log 日志内容写入本地数据库实例，并写入本地的binlog
4）.binglog采用gtid具有全局唯一性，所以master-1不会二次执行

 

5.GTID的原理：

 

5.1 GTID 的概述：
1、全局事物标识：global transaction identifieds。

2、GTID 事物是全局唯一性的，且一个事务对应一个 GTID。

3、一个 GTID 在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

4、GTID 用来代替classic的复制方法，不在使用 binlog+pos 开启复制。而是使用 master_auto_postion=1 的方式自动匹配 GTID 断点进行复制。

5、MySQL-5.6.5 开始支持的，MySQL-5.6.10 后开始完善。

6、在传统的 slave 端，binlog 是不用开启的，但是在 GTID 中，slave 端的 binlog 是必须开启的，目的是记录执行过的 GTID(强制);但是从 5.7.5 版本开始无需在 GTID 模式下启用参数 log_slave_updates

 

 5.2 GTID 的组成部分：

GTID = source_id:transaction_id

source_id 正常即是 server_uuid，在第一次启动时生成(函数 generate_server_uuid)，并持久化到 DATADIR/auto.cnf 文件里。

transaction_id 是顺序化的序列号(sequence number)，在每台 MySQL 服务器上都是从 1 开始自增长的序列，是事务的唯一标识。例如：3E11FA47-71CA-11E1-9E33-C80AA9429562:23

GTID 的集合(GTIDs)可以用 source_id+transaction_id 范围表示，例如：3E11FA47-71CA-11E1-9E33-C80AA9429562:1-18

复杂一点的：如果这组 GTIDs 来自不同的 source_id，各组 source_id 之间用逗号分隔;如果事务序号有多个范围区间，各组范围之间用冒号分隔，例如：3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5:11-18,2C256447-3F0D-431B-9A12-575BB20C1507:1-27

5.3  GTID 如何产生
GTID 的生成受 gtid_next 控制。

在 Master 上，gtid_next 是默认的 AUTOMATIC,即 GTID 在每次事务提交时自动生成。它从当前已执行的 GTID 集合(即 gtid_executed)中，找一个大于 0 的未使用的最小值作为下个事务 GTID。同时将 GTID 写入到 binlog(set gtid_next 记录)，在实际的更新事务记录之前。

在 Slave 上，从 binlog 先读取到主库的 GTID(即 set gtid_next 记录)，而后执行的事务采用该 GTID。

5.4 GTID 相关的变量

GTID_EXECUTED

#表示已经在该实例上执行过的事务; 执行 RESET MASTER 会将该变量置空; 我们还可以通过设置 GTID_NEXT 在执行一个空事务，来影响 GTID_EXECUTED

GTID_PURGED

#已经被删除了 binlog 的事务，它是 GTID_EXECUTED 的子集，只有在 GTID_EXECUTED 为空时才能设置该变量，修改 GTID_PURGED 会同时更新 GTID_EXECUTED 和 GTID_PURGED 的值。

GTID_OWNED

#表示正在执行的事务的 gtid 以及对应的线程 ID。

GTID_NEXT

#SESSION 级别变量，表示下一个将被使用的 GTID。

5.5 GTID 的工作原理

master 更新数据时，会在事务前产生 GTID，`一同记录到 binlog 日志中`。
slave 端的 i/o 线程将变更的 binlog，写入到本地的 relay log 中,读取值是根据`gitd_next变量`，告诉我们slave下一个执行哪个GTID。
sql 线程从 relay log 中获取 GTID，然后对比 slave 端的 binlog 是否有记录。
如果有记录，说明该 GTID 的事务已经执行，slave 会忽略。
如果没有记录，slave 就会从 relay log 中执行该 GTID 的事务，并记录到 binlog。
在解析过程中会判断是否有主键，如果没有二级索引就用全部扫描。

5.6 pos 与 GTID 有什么区别

两者都是日志文件里事件的一个标志,如果将整个 mysql 集群看作一个整体,pos就是局部的,GTID 就是全局的.
mysql 节点的集群,一主两从,在 master,slave1,slave2 日志文件里的 pos,都各不相同,就是一个 event,在 master 的日志里,pos 可能是 700,而在 slave1,slave2 里,pos 可能就是 300,400 了,因为众多 slave 也可能不是同时加入集群的,不是从同一个位置进行同步.

而 GTID,在 master,slave1,slave2 各自的日志文件里,同一个 event 的 GTID 值都是一样的.

5.7 查看binlog

查看binlog列表

root@localhost : b:34: >show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |  15275772 |
+------------------+-----------+　

 

查看binlog的信息

 

mysql-bin.000001 15275772 Gtid 141 15275837 SET @@SESSION.GTID_NEXT= '627a78ad-d67f-11ec-8d41-005056a6b4be:31'   //GTID
mysql-bin.000001 15275837 Query 141 15275900 BEGIN
mysql-bin.000001 15275900 Table_map 141 15275946 table_id: 111 (bi.t3)
mysql-bin.000001 15275946 Write_rows 141 15275999 table_id: 111 flags: STMT_END_F
mysql-bin.000001 15275999 Xid 141 15276030 COMMIT /* xid=120 */　

 

 

5.8 event是什么

二进制日志的最小记录单位

对于DDL,DCL,一个语句就是一个event

对于DML语句来讲:例如以下列子,就被分为了4个event

begin;

DML1

DML2

commit;

 

event的组成

开始位置(#at 157)+事件内容+结束位置(下一个#at 890,或者是end_log_pos 890)