收到报警,主从断了
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/ |
+--------------------------+-----------------------------------------+
是一样的
关于字符集的内核月报:
http://mysql.taobao.org/monthly/2018/01/07/
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/ |
+--------------------------+-----------------------------------------+
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='安检关键环节设置'; -
-
- 从:
-
- 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='安检关键环节设置';
为什么表的字符集会不一样呢? - CREATE TABLE `svc_special_important_step_setting` (
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
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博客 ” ,链接:http://blog.itpub.net/29096438/viewspace-2146363/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29096438/viewspace-2146363/