mysql主从隔一天同步一次_一次mysql主从同步报错及解决过程

一、背景

mysql版本为5.7.2，存储引擎使用InnoDB, 默认字符集使用utf8mb4。主从复制模式为多主一从，在多个主库写入数据并汇总到从库供web后台读取，并没有使用中间件和pt工具集对主从状态进行监控。

二、 报错场景复原

通过navicat远程连接在某一主库新建了一张表,建表语句如下：

DROP TABLE IF EXISTS tb_tmp_test;

CREATE TABLE tb_tmp_test

(

id int auto_increment primary key,

columnA int,

columnB int,

mark varchar(50)

);

查看主库和从库均建表成功。于是开始向主库表中插入数据。写入了近1万条数据后查看从库表数据，发现表为空。查看从库slave状态，发现报错：

Last_SQL_Errno : 1677

Last_SQL_Error : Column 3 of table 'db_test.tb_tmp_test' cannot be converted from type 'varchar(150(bytes))' to type 'varchar(200(bytes) utf8mb4)'

三、 错误原因排查

根据报错提示，是由于字符编码格式转化导致的报错，分别查看主库和从库的表结构：

主库：show create table tb_tmp_test;

CREATE TABLE `tb_tmp_test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`columnA` int(11) DEFAULT NULL,

`columnB` int(11) DEFAULT NULL,

`mark` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

从库： show create table tb_tmp_test;

CREATE TABLE `tb_tmp_test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`columnA` int(11) DEFAULT NULL,

`columnB` int(11) DEFAULT NULL,

`mark` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

发现差异：主库的字符编码是utf8，从库则是utf8mb4。问题应该就在这里了。但是，前边背景里提到了，数据库设置了默认字符编码是utf8mb4,为什么主库的表定义却是utf8呢？

检查mysql配置文件字符集设置：

[mysqld]

character-set-server = utf8mb4

collation-server = utf8mb4_general_ci

init_connect='SET NAMES utf8mb4'

character-set-client-handshake = FALSE

通过本地登陆连接mysql，查看字符集相关设置

mysql> show variables like '%chara%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.15 sec)

以上配置及字符集设置主从库均一致。都是默认utf8mb4。然而现实是，主库在未指定默认字符集的情况下建表，却使用了utf8类型。

尝试在主库中新建表复现错误时，却发现新建的表又默认使用utf8mb4了。

经过一番百度后，终于发现了问题。参考这篇文章 修改MySQL的字符集为utf8mb4

配置.png

先前提到，第一次出错的建表操作，是在navicat连接中完成的。而后来的检查配置文件以及连接数据库查看字符集设置，却是在服务器上完成的。

再参考文章中对配置文件的注释，原来的配置文件只对本地mysql服务进行了配置，于是登陆数据库查看字符集设置以及新建表想要复现错误都没出现问题。猜测是由于没有对[mysql]远程连接客户端进行配置，所以导致在navicat中建表使用了utf8类型。

为了验证猜测，在navicat中启用命令行，查看数据库字符集设置：

mysql> show variables like '%chara%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.04 sec)

发现数据库字符设置 character_set_database 变为了utf8。

同时初始建表语句又没有指定默认字符类型，导致沿用了数据库的字符类型设置。而从库复制主库操作建表确实数据库自己完成的。可以理解为时是在数据库本地连接中完成的，于是根据mysql配置文件使用了utf8mb4类型。最终导致主从复制报错。

四、错误修复

根据提示，将主库表修改为utf8mb4字符类型。同时在主库及从库配置文件中追加配置如下：

[mysql]

default-character-set = utf8mb4

重启主库，使主库字符类型更改为utf8mb4生效。

并在从库中使用跳过事务的方式使从库跳过该错误

stop slave;

set global sql_slave_skip_counter = 1;

start slave for channel 'slave1';

show slave status;

发现依旧报错，究其原因，应该是表插入操作已经写入到了relay log日志中，表明了要进行字符类型转化，故多条数据插入会出现多次报错。

为了一次性跳过所有错误，在从库配置文件中追加一项配置：

[mysqld]

slave-skip-errors=1677

跳过错误编号为1677的所有错误。

重启从库，再次开启主从复制，发现恢复正常。但从库表中无数据。

在主库中将表清理掉，重新建表、插入数据，从库可以正常同步。问题解决。

最后再在从库配置文件中将跳过此类错误的配置注释掉，再次重启即可。