收到报警，主从断了

1.查看主从的字符集设置

mysql> show global variables like '%character%';
+--------------------------+-----------------------------------------+
| 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-5.6.34/share/charsets/ |
+--------------------------+-----------------------------------------+

2. 查看报错表的字符集

主：
CREATE TABLE `svc_special_important_step_setting` (
PRIMARY KEY (`svc_special_important_step_setting_id`),
KEY `idx_CityId` (`city_id`),
KEY `idx_CompanyId` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='安检关键环节设置';
从：
1. CREATE TABLE `svc_special_important_step_setting` (
  PRIMARY KEY (`svc_special_important_step_setting_id`),
  KEY `idx_CityId` (`city_id`),
  KEY `idx_CompanyId` (`company_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='安检关键环节设置';
为什么表的字符集会不一样呢？

3.查看了下库的字符集

mysql> show create database xxxxxx;
+-------------+-------------------------------------------------------------------------+
| Database | Create Database |
+-------------+-------------------------------------------------------------------------+
| ecejservice | CREATE DATABASE `xxxxx` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-------------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database xxxxxx;
+-------------+-------------------------------------------------------------------------+
| Database | Create Database |
+-------------+-------------------------------------------------------------------------+
| ecejservice | CREATE DATABASE `xxxxxx` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+-------------+-------------------------------------------------------------------------+

主从两个库的字符集都不一样，原因要追溯到历史原因了，两个库的来源不一样，一个来之与xtracebackup 一个来源于mysqldump，并且来源库的字符集就是不一样的

一个case

master：
mysql> show create database test1234\G
*************************** 1. row ***************************
Database: test1234
Create Database: CREATE DATABASE `test1234` /*!40100 DEFAULT CHARACTER SET utf8mb4 */
1 row in set (0.00 sec)
slave：
mysql> show create database test1234\G
*************************** 1. row ***************************
Database: test1234
Create Database: CREATE DATABASE `test1234` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

master：建表
CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`tname` varchar(20) DEFAULT NULL
) ENGINE=InnoDB

查看主从表的信息：
master：
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`tname` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

slave：
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`tname` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

可以看到：如果建表时不指定字符集会默认以当前database的字符集为准

master：插入数据
mysql> insert into test(id ,tname) values (123,'12314');

查看主从状态：
Replicate_Wild_Ignore_Table:
Last_Errno: 1677
Last_Error: Column 1 of table 'test1234.test' cannot be converted from type 'varchar(80)' to type 'varchar(20)'
Skip_Counter: 0
Exec_Master_Log_Pos: 158988157
Relay_Log_Space: 2240633

解决：修改库的默认字符集吧

mysql> alter database xxxxxx DEFAULT CHARACTER SET utf8mb4;

关于字符集的内核月报：
http://mysql.taobao.org/monthly/2018/01/07/

ITPub博客

【Mysql】字符集引发的主从报错

1.查看主从的字符集设置

2. 查看报错表的字符集

3.查看了下库的字符集

解决：修改库的默认字符集吧

最新文章