Shulou(Shulou.com)05/31 Report--

Editor to share with you how to deal with the master-slave error caused by the Mysql character set, I believe most people do not know much about it, so share this article for your reference. I hope you will learn a lot after reading this article. Let's learn about it together.

After receiving the alarm, the master and slave were cut off.

1. View the character set settings of the master and slave

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`)

) key links of ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' security check are set up.

Why is the character set of the table different?

3. Checked the character set of the following library

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 * / | |

+-+

The character sets of the master and slave libraries are different, and the reasons can be traced back to historical reasons. The sources of the two libraries are different, one comes from xtracebackup and the other comes from mysqldump, and the character set of the source library is different.

A 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: creating tables

CREATE TABLE `test` (

`id`int (11) DEFAULT NULL

`tname` varchar (20) DEFAULT NULL

) ENGINE=InnoDB

View the information of the master-slave table:

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)

You can see that if you do not specify a character set when creating a table, the default is the character set of the current database.

Master: inserting data

Mysql > insert into test (id, tname) values

View master-slave status:

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

Solution: modify the default character set of the library

Mysql > alter database xxxxxx DEFAULT CHARACTER SET utf8mb4

The above is all the contents of the article "how to deal with Master-Slave errors caused by Mysql character set". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.