收到報警，主從斷了

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;

關於字符集的核心月報：

【Mysql】字符集引發的主從報錯

1.檢視主從的字符集設定

2. 檢視報錯表的字符集

3.檢視了下庫的字符集

解決：修改庫的預設字符集吧

相關文章