Shulou(Shulou.com)06/01 Report--

This article mainly tells you briefly about the solutions to errors reported in mysql5.6 master-slave replication. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, let's go straight to the topic, and hope that there are any solutions to errors reported in mysql5.6 master-slave replication. This article can bring you some practical help.

Mysql5.6 master-slave replication error resolution 1.Mysql database slave synchronous error message 1366:

1. The reason is that the character set of the database table is inconsistent with the character set of the fields in the table.

The following four analysis processes and solutions:

17030310 Lost connection to MySQLserver during query (server_errno=2013) 17030310 Lost connection to MySQLserver during query (server_errno=2013) 17030310 Note Slave I thread exiting O thread killed while reading event17030310:07:33 [Note] Slave I thread exiting, read up to log'mysql- bin.000009',position 39822933517030310 Note 38 [Note] Slave SQL thread initialized, starting replication in log'mysql-bin.000002' at position 54629541 Relay log'. / mht-relay-bin.000004'position: 5462968717030310 ERROR 38 [ERROR] Slave SQL: Error 'Incorrect string value:'\ xC2\ xB7\ xE5\ x93\ x88\ xE5...' For column 'author' at row 1' on query. Defaultdatabase: 'mahtu'. Query: 'update `dr_mh_ book` set `author` =' Asaf Hanuka', `intro` ='

Realist cartoon, realist is mainly an autobiographical story. Describe the difficulties of raising and taking care of your family and children as a father and husband in a war-torn country. The author's narrative skills are so excellent that he is able to tell the whole story of others in a single picture. At the same time, the painting skill is also very good, the picture hides a lot of humorous small details, but will not give people a sense of confusion, often can depict a short story in unexpected ways. The work uses a humorous means of expression to make readers feel the cruelty of war. The author likes to use beautiful and silent illustrations to show the dangers faced in a country ravaged by war. This work is like a bright window, through which we can understand the reality of Israel, a country we are not familiar with. 17030310 Warning 07Ze38 [Warning] Slave: Incorrect string value:'\ xC2\ xB7\ xE5\ x93\ x88\ xE5...'for column 'author' at row 1 Error_code: 136617030310 slave SQL thread aborted. Fix theproblem, and restart the slave SQL thread with "SLAVE START". Westopped at log 'mysql-bin.000002' position 5462954117030310 Note 38 [Note] Slave I Dot O thread: connected to master'rep1@221.195.1.254:3306',replication started in log' mysql-bin.000009' atposition 398229335

Reference document: http://www.cnblogs.com/zhoujinyi/p/4568663.html

Inconsistency between table field character set and table character set causes mysql master-slave synchronization error: 1366

Mysql > show create table dr_mh_book\ Gateway 1. Row * * Table: dr_mh_bookCreateTable: CREATE TABLE `dr_mh_ book` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (100) DEFAULT NULL, `url` varchar (100) DEFAULT NULL, `fengmian` varchar (100) DEFAULT NULL `updatetime` varchar (20) DEFAULTNULL, `tag` varchar (20) DEFAULTNULL, `diqu` varchar (20) DEFAULTNULL, `intro` text, `pingyin` varchar (200) DEFAULTNULL, `fpingyin` varchar (5) DEFAULTNULL, `updatestatus` varchar (10) DEFAULTNULL, `status` int (11) DEFAULTNULL, `author`varchar (50) CHARACTER SET gb2312DEFAULT NULL, `createtime` int (11) DEFAULTNULL, `fabutime` int (11) DEFAULTNULL, `chapternum` int (11) DEFAULTNULL, `isdown` int (2) unsigned zerofill DEFAULTNULL, PRIMARY KEY (`id`) KEY `index_ name` (`name`) ENGINE=InnoDB AUTO_INCREMENT=34182 DEFAULT CHARSET=utf8mb41 row inset (0.00 sec)

Modify the table field character set:

Reference:

Http://www.2cto.com/database/201308/235153.html

Operate in the main library:

Mysql > SELECT * FROM dr_mh_book WHERE `author` = 'Asaf Hanuca'; ERROR1267 (HY000): Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation'= 'mysql > alter table dr_mh_book change `author`author` varchar (50) character setutf8mb4 DEFAULT NULL;Query OK,6238 rows affected (0.20 sec) Records:6238 Duplicates: 0 Warnings: 0 mysql > flush privileges;Query OK,0 rows affected (0.00 sec) mysql > SELECT * FROM dr_mh_book WHERE `author` =' Asaf Hanuka' Empty set (0.01sec) operates from the library: mysql > SELECT * FROM dr_mh_book WHERE `author` = 'Asaf Hanuka'; ERROR1267 (HY000): Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation'= 'mysql > alter table dr_mh_book change `author` varchar (50) character setutf8mb4 DEFAULT NULL;Query OK,6238 rows affected (0.20 sec) Records:6238 Duplicates: 0 Warnings: 0 mysql > flush privileges Query OK,0 rows affected (0.00 sec) mysql > SELECT * FROM dr_mh_book WHERE `author` = 'Asaf Hanuka'; Empty set (0.01 sec): database synchronization is normal.

Http://blog.csdn.net/dqchouyang/article/details/50012203

2.mysql database slave synchronization error 1061

The reason is that the index of a table on the main database already exists on the slave corresponding table, and if the main database continues to synchronize to the slave, it will prompt that a table index on the slave already exists, resulting in a synchronous error.

Here is the solution:

Then it appears that an index has been created for a table on the master database, but the same index has been created for the slave database, resulting in a replication error of 1061

View the index in the main library: mysql > show index from mahtu.dr_mh_book +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -+- -- + | dr_mh_book | 0 | PRIMARY | 1 | id | A | 5888 | NULL | NULL | BTREE | | dr_mh_book | 1 | index_name | 1 | name | A | 5888 | NULL | NULL | YES | BTREE | | | + -+

Report an error from the Treasury:

Last_Errno: 1061 Last_Error: Error 'Duplicatekey name' index_name'' on query. Default database: 'mahtu'. Query: 'ALTER TABLE`dr _ mh_ book`add' index _ name` (`name`) 'Last_Errno: 1061 Last_Error: Error' Duplicatekey name' index_book_name'' on query. Default database: 'mahtu'. Query: 'ALTERTABLE `dr_mh_ chapter`ADD index _ book_ name` (`bookid`, `name`)' deleting table index from library operation: mysql > drop index index_name on mahtu.dr_mh_book;Query OK,0 rows affected (0.00 sec) Records:0 Duplicates: 0 Warnings: 0 mysql > flush privileges;mysql > show index from mahtu.dr_mh_book +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -+- -+ | dr_mh_book | 0 | PRIMARY | 1 | id | A | 4170 | NULL | NULL | BTREE | | dr_mh_book | 1 | index_name | 1 | name | A | 245 | NULL | NULL | YES | BTREE | +- -+

Synchronization is normal at this point, followed by an error of 1366:

Resolve:

Mysql main library:

Mysql > show create table dr_mh_chapter\ Gateway chapter 1. Row * * Table: dr_mh_chapterCreateTable: CREATE TABLE `dr_mh_ chapter` (`id` int (11) NOT NULL AUTO_INCREMENT, `bookid` int (11) DEFAULTNULL, `name` varchar (100) CHARACTER SET gbk DEFAULTNULL, `url` varchar (100) DEFAULTNULL, `imgstr` longtext `pingyin` DEFAULT NULL, `status` int (11) DEFAULT NULL, `createtime` int (11) DEFAULT NULL, `uptime` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_book_ name` (`bookid`, `name`) ENGINE=InnoDB AUTO_INCREMENT=574818 DEFAULT CHARSET=utf8mb41 row inset (0.00 sec) mysql > alter table dr_mh_chapter change `name`name` varchar Query OK,163079 rows affected (9.44 sec) Records:163079 Duplicates: 0 Warnings: 0 mysql > flush privileges Query OK, 0 rows affected (0.00 sec) mysql > show create table dr_mh_chapter\ Graph * 1. Row** Table:dr_mh_chapterCreate Table: CREATE TABLE `dr_mh_ chapter` (`id` int (11) NOTNULL AUTO_INCREMENT, `bookid` int (11) DEFAULT NULL, `name` varchar (100) DEFAULT NULL `url` varchar (100) DEFAULT NULL, `imgstr` longtext, `pingyin`varchar (200) DEFAULT NULL, `status` int (11) DEFAULT NULL, `createtime` int (11) DEFAULT NULL, `updatetime` int (11) DEFAULT NULL, PRIMARY KEY (`id`), key `index _ book_ name` (`bookid`) `name`) ENGINE=InnoDB AUTO_INCREMENT=574820 DEFAULTCHARSET=utf8mb41 row in set (0.00 sec) operates from the library: mysql > show create table dr_mh_chapter\ Graph * 1. Row** Table: dr_mh_chapterCreate Table: CREATE TABLE `dr_mh_ chapter` (`id` int (11) NOTNULL AUTO_INCREMENT, `bookid` int (11) DEFAULT NULL `name` varchar CHARACTER SET gbk DEFAULT NULL, `url` varchar DEFAULT NULL, `imgstr` longtext, `pingyin`varchar (200) DEFAULT NULL, `status` int (11) DEFAULT NULL, `createtime` int (11) DEFAULT NULL, `updatetime` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY`index _ book_ name` (`bookid`, `name`) ENGINE=InnoDB AUTO_INCREMENT=494977 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec) mysql > alter tabledr_mh_chapter change `name`name`varchar Query OK, 126243 rows affected (12.34 sec) Records: 126243 Duplicates: 0 Warnings: 0 mysql > show create table dr_mh_chapter\ Graph * 1. Row** Table:dr_mh_chapterCreate Table: CREATE TABLE `dr_mh_ chapter` (`id`int (11) NOTNULL AUTO_INCREMENT, `bookid`int (11) DEFAULT NULL `name` varchar DEFAULT NULL, `url` varchar DEFAULT NULL, `imgstr` longtext, `pingyin`varchar (200) DEFAULT NULL, `status` int (11) DEFAULT NULL, `createtime` int (11) DEFAULT NULL, `updatetime` int (11) DEFAULT NULL, PRIMARY KEY (`id`), key `index _ book_ name` (`bookid`, `name`) ENGINE=InnoDB AUTO_INCREMENT=494977 DEFAULTCHARSET=utf8mb41 row in set (0.00 sec) mysql > flush privileges Query OK, 0 rows affected (0.00 sec) mysql > start slave;Query OK, 0 rows affected (0.00 sec) mysql > show slave status\ G

Reference documentation:

Http://blog.itpub.net/15456724/viewspace-682681/

3.mysql master-slave replication error: 1060

The reason is that the upper table field of slave is duplicated.

Mysql reported an error from the library: [ERROR] Slave SQL: Error 'Duplicate column name' isdown'' on query. Default database:'mahtu'. Query: 'ALTER TABLE `dr_mh_ book`ADD ColumN`isdown` int (2) NULL After`chapternum`, Error_code: 106017030312 int 2709 [Warning] Slave: Duplicate column name' isdown' Error_code: 1060 View this field in the main database: desc dr_mh_book this table field exists but this field also exists when viewing the thesaurus from the database, so the error is reported because the slave database also has this field, so delete the field altertable mh_ecms_list drop column isdown;flushprivileges of this table Master-slave synchronization normal 4.mysql synchronization error reporting 1396mysql database master-slave synchronization error reporting Last_Errno: 1396Last_Error:Error 'Operation DROP USER failed for' mhtuser'@'124.207.48.234'' on query.Default database:'. Query: 'drop user' mhtuser'@'124.207.48.234'' is caused by the master database deleting database users who do not exist on the slave database: mysql > stop slave;mysql > set global sql_slave_skip_counter=1mysql > start slave;6.mysql master-slave replication error 1007

Error reported by slave:

Last_SQL_Errno: 1007 Last_SQL_Error: Error 'Can'tcreate database' wjw02'; database exists' on query. Default database: 'wjw02'.Query:' create database wjw02' Replicate_Ignore_Server_Ids:

The reason is:

A library with the same name on master already exists on slave, so if you create a library on master again, you will be prompted on slave that the library already exists, resulting in an error.

Solution:

Mysql > stop slave;Query OK,0 rows affected (0.01 sec) mysql > set global sql_slave_skip_counter=1;Query OK,0 rows affected (0.00 sec) mysql > start slave;Query OK,0 rows affected (0.01 sec) 7.mysql master-slave replication error 1008

The hint on slave:

Last_SQL_Errno:1008 Last_SQL_Error: Error 'Can'tdrop database' wjw02'; database doesn't exist' on query. Default database:'wjw02'. Query: 'drop database wjw02'

The reason is that the database wjw02 is deleted from the library in advance, and then the wjw02 library is deleted again on the master. The slave indicates that the wjw02 library does not exist, resulting in a replication error:

Solution:

Execute on slave:

Mysql > stop slave;Query OK,0 rows affected (0.02sec) mysql > set global sql_slave_skip_counter=1; Query OK,0 rows affected (0.05sec) mysql > start slave;Query OK,0 rows affected (0.02sec) 8. Write the slave_skip_errors= parameter to the configuration file

. Write the slave_skip_errors= parameter to the configuration file my.cnf on slave

[root@localhost ~] # grep slave_skip_errors / etc/my.cnf

Slave_skip_errors=1007,1008

[root@localhost ~] # / etc/init.d/mysqld restart

In this way, the slave synchronization error message can be ignored directly.

# Note: considering the consistency of master-slave database data, the production environment does not allow parameters such as set global sql_slave_skip_counter=1; slave_skip_errors operation regardless of circumstances to ignore errors. If master-slave synchronization fails, let him get stuck there and manually intervene to recover the failure manually.

The 9.slave_exec_mode parameter can automatically handle synchronous replication errors dynamically.

Note: the setting of this parameter can only ignore errors 1032 and 1062.

Mysql > show variables like 'slave_exec_mode';+-+-+ | Variable_name | Value | +-+-+ | slave_exec_mode | STRICT | +-+-+

This parameter defaults to STRICT strict mode

If you set this parameter to IDEMPOTENT mode, if there are 1032 errors (record not found) and 1062 errors (duplicate primary keys) in slave synchronization, you will automatically skip the second error and record it in the error log. In fact, this parameter has the same effect as slave_skip_errors.

It's just that the slave_skip_errors parameter must be written to the configuration file my.cnf to restart mysql, while IDEMPOTENT does not need to restart slave's mysql service by default.

Mysql > set global slave_exec_mode='IDEMPOTENT';Query OK,0 rows affected (0.05sec) mysql > show variables like 'slave_exec_mode' +-+-+ | Variable_name | Value | +-+-+ | slave_exec_mode | IDEMPOTENT | +-+-+ 1 row inset (0.00 sec) mysql > stop slave Query OK,0 rows affected (0.00 sec) mysql > start slave

Demo:

Operations on slave:

Mysql > select * from dr_user_info +-+-- + | id | dev_id | tel | updatetime | pwd | + -+ | 20 | CFD0CCDA603705F35C8EE74427C9CC45 | 13713340729 | 1488174916 | 111111 | 21 | 11713EA0EDAFC1113E9AB016471BBDCF | 18341732393 | 1487632768 | 111111 | 22 | C9DEBE883FF6C05A19D5C0487E9C9D63 | 18600271207 | 1488521317 | 111111 | 23 | D73C9DADAB3611D82B7D5CA11B62DBA1 | 18974748447 | 1491177725 | 111111 | 24 | qwertyuiop | 18234123308 | 1491177726 | 666666 | +- +-+ 5 rows inset (0.00 sec) mysql > delete from dr_user_info where id=24

Operate on master:

Mysql > select * from dr_user_info +-+-- + | id | dev_id | tel | updatetime | pwd | + -- + | 20 | CFD0CCDA603705F35C8EE74427C9CC45 | 13713340729 | 1488174916 | 111111 | 21 | 11713EA0EDAFC1113E9AB016471BBDCF | 18341732393 | 1487632768 | 111111 | 22 | C9DEBE883FF6C05A19D5C0487E9C9D63 | 18600271207 | 1488521317 | 111111 | | 23 | D73C9DADAB3611D82B7D5CA11B62DBA1 | 18974748447 | 1491177725 | 111111 | 24 | qwertyuiop | | 18234123308 | 1491177726 | 33333333 | +-+ 5 rows inset (0.00 sec) mysql > select * from dr_user_info | +-+-- + | id | dev_id | tel | updatetime | pwd | + -- + | 20 | CFD0CCDA603705F35C8EE74427C9CC45 | 13713340729 | 1488174916 | 111111 | | 21 | 11713EA0EDAFC1113E9AB016471BBDCF | 18341732393 | 1487632768 | 111111 | 22 | C9DEBE883FF6C05A19D5C0487E9C9D63 | 18600271207 | 1488521317 | 111111 | 23 | D73C9DADAB3611D82B7D5CA11B62DBA1 | 18974748447 | 1491177725 | 111111 | +

Mysql > show slave status\ G

However, when you view the error log of mysql when synchronizing at this time on slave:

[root@localhost logs] # tail-1/data/mysql/logs/mysql-error.log

2017-05-1405 dr_user_info', Error_code:1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master logmysql-bin.000010 22 4707 [Warning] Slave SQL: Could not execute Delete_rows event on tabledr_brower_db.dr_user_info; Can't find record in 'dr_user_info', Error_code:1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master logmysql-bin.000010, end_log_pos 5298692, Error_code: 103210.mysql synchronous error 1062 (primary key repeat)

Duplicate table records on slave, causing slave replication error 1062 primary key to repeat

View the table-building statement of a table

Mysql > show create table dr_user_info\ Gateway NOT NULL AUTO_INCREMENT 1. Row * * Table: dr_user_infoCreateTable: CREATE TABLE `dr_user_ info` (`id` int (11) NOT NULL AUTO_INCREMENT, `dev_ id` varchar (100) DEFAULT NULL, `tel` varchar (20) DEFAULT NULL, `updatetime` int (11) DEFAULT NULL `pwd` varchar (20) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mysql > select * from dr_user_info +-+-- + | id | dev_id | tel | updatetime | pwd | + -+ | 20 | CFD0CCDA603705F35C8EE74427C9CC45 | 13713340729 | 1488174916 | 111111 | 21 | 11713EA0EDAFC1113E9AB016471BBDCF | 18341732393 | 1487632768 | 111111 | 22 | C9DEBE883FF6C05A19D5C0487E9C9D63 | 18600271207 | 1488521317 | 111111 | | 23 | D73C9DADAB3611D82B7D5CA11B62DBA1 | 18974748447 | 1491177725 | 111111 |

Insert a record into the table on slave

Mysql > insert into dr_user_info (id,dev_id,tel,updatetime,pwd) values; mysql > select * from dr_user_info +-+-- + | id | dev_id | tel | updatetime | pwd | + -+ | 20 | CFD0CCDA603705F35C8EE74427C9CC45 | 13713340729 | 1488174916 | 111111 | 21 | 11713EA0EDAFC1113E9AB016471BBDCF | 18341732393 | 1487632768 | 111111 | 22 | C9DEBE883FF6C05A19D5C0487E9C9D63 | 18600271207 | 1488521317 | 111111 | | 23 | D73C9DADAB3611D82B7D5CA11B62DBA1 | 18974748447 | 1491177725 | 111111 | 24 | qwertyuiop | 182341383 | 1491177726 | 111111 |

Insert the same table record on master:

Insert into dr_user_info (id,dev_id,tel,updatetime,pwd) values (24meme qwertyuiophenol 18234131383 pr. 1491177726pr 111111')

Check the error report on slave:

Last_SQL_Errno:1062 Last_SQL_Error: Could notexecute Write_rows event on table dr_brower_db.dr_user_info; Duplicate entry '24' for key' PRIMARY', Error_code:1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master logmysql-bin.000010, end_log_pos 5295916

It is caused by repeated primary keys.

The solution is to delete the record with a duplicate id of 24 in the table dr_user_info on slave.

Mysql > delete from dr_user_info where id=24;mysql > stop slave;Query OK,0 rows affected (0.01sec) mysql > start slave;mysql > show slave status\ G solves the problem of duplicate primary keys, and slave synchronization is normal. Update a table record on master but cannot find it on slave with error 1032

Demo:

View table records on slave

Mysql > select * from dr_user_info +-+-- + | id | dev_id | tel | updatetime | pwd | + -- + | 20 | CFD0CCDA603705F35C8EE74427C9CC45 | 13713340729 | 1488174916 | 111111 | 21 | 11713EA0EDAFC1113E9AB016471BBDCF | 18341732393 | 1487632768 | 111111 | 22 | C9DEBE883FF6C05A19D5C0487E9C9D63 | 18600271207 | 1488521317 | 111111 | | 23 | D73C9DADAB3611D82B7D5CA11B62DBA1 | 18974748447 | 1491177725 | 111111 | 24 | qwertyuiop | 182341383 | 1491177726 | 111111 | 111111 | +- -+-- + 5 rows in set (0.00 sec)

Delete record 24 on slave

Mysql > delete from dr_user_info where id=24

Then delete dr_user_info record 24 on master

At this point, a synchronous error is reported on slave: 1032, indicating that the table record cannot be found on slave.

Last_SQL_Errno:1032 Last_SQL_Error: Could notexecute Delete_rows event on table dr_brower_db.dr_user_info; Can't find recordin 'dr_user_info', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; theevent's master log mysql-bin.000010, end_log_pos 5296175

Solution:

Check the sql statement on the mysql-bin.000010 log on master and analyze what the error 5296175 SQL statement is doing.

[root@localhost binlog] # mysqlbinlog-- no-defaults-v-v--base64-output=DECODE-ROWS mysql-bin.000010 | grep-A '20' 5295947

# 17051318 end_log_pos 5296027CRC32 0x4acf5867 Query thread_id=23 exec_time=0 37 server id 41 server id 1230445 end_log_pos5295947 CRC32 0x3abbe149 Xid = 2363 COMMIT raceme tricks at5295947#17051318:56:31 server id 1230445 and errorstamp codewords 0SETTIMESTAMPPS 1494672991 races # at5296027#17051318:56:31 server id 1230445 end_log_pos5296100 CRC32 0xd1d14129 Table_map: `dr_brower_ db`.`dr _ user_ info` mapped to number 154th at5296100#17051318:56:31 server id 1230445 end_log_pos5296175 CRC32 0xf6405bec Delete_rows: table id 158flags: STMT_END_F### DELETE FROM `dr_brower_ db`.`dr _ user_ info` # WHERE### @ 1room24 / * INT meta=0 nullable=0is_null=0 * / # @ 2='qwertyuiop '/ * VARSTRING (300) meta=300 nullable=1 is_null=0 * / # @ 3 at5296175#17051318:56:31 server id 18234131383' / * VARSTRING (60) meta=60 nullable=1 is_null=0 * / # @ 4 # 1491177726 / * INTmeta=0 nullable=1 is_null=0 * / # # @ 5 "1111' / * VARSTRING (60) meta=60 nullable=1 is_null=0 * / # at5296175#17051318:56:31 server id 1230445 end_log_pos5296206 CRC32 0x8307d44e Xid = 2390com DELIMITER;# End oflog file

The red font prompts you to perform delete on master. Delete table record: 24

So the error reported here can be ignored and skipped on slave.

Mysql > stop slave;Query OK,0 rows affected (0.01 sec) mysql > set global sql_slave_skip_counter=1;Query OK,0 rows affected (0.00 sec) mysql > start slave;Query OK,0 rows affected (0.01 sec)

Solve this problem

# Note: if the update statement is executed and the slave synchronization error is 1032, set global sql_slave_skip_counter=1; is not allowed to ignore this error. It is recommended to check the binlog log to see where it is stuck, and then put this non-existent record insert on the slave library, and then enable synchronization in start slave.

12.slave Relay Log relay-log corruption caused mysql synchronization error

The reason is that when slave goes down unexpectedly, the relay log relay-log may be damaged. When synchronous replication is started again, the error message is as follows:

Solution: find the synchronized binlog log and pos point. And then resynchronize.

Tip: in mysql5.5 version and above, it has been considered that the problem of relay-log damage caused by server downtime caused mysql synchronization failure.

That is, add the parameter relay_log_recovery=1 to the my.cnf configuration file of slave, and that is fine.

13. Server-id on master equals server-id on slave

Human error when configuring mysql master-slave replication, the value is equal to the value of server-id on slave.

The solution is to make sure that the value of server-id is different.

14.mysql master-slave replication error: 2003Last_IO_Errno:2003 Last_IO_Error: error connectingto master 'rep@10.0.0.201:3306'-retry-time: 60 retries: 2

There are many reasons, it may be that the firewall on slave limits port 3306, or it may be caused by the incorrect account and password of the specified connection to the replication database when change master to is executed on slave. It may also be due to the restriction of port 3306 when the master is enabled by the firewall, or it may be caused by the network problem between the CVMs.

Also, the my.cnf configuration file on master uses the parameter: the my.cn configuration file on binlog_ignore_db=mydb1,slave uses the parameter: replicate-ignore-db = mydb1, which can also cause mysql synchronization error 2003.

On master: [root@localhost~] # grep wjw01 / etc/my.cnf binlog_ignore_db= wjw01 [root@localhost~] # / etc/init.d/mysqld restart

On slave:

[root@localhost~] # grep wjw01 / etc/my.cnfreplicate-ignore-db= wjw01 [root@localhost~] # / etc/init.d/mysqld restartShuttingdown MySQL.... SUCCESS!StartingMySQL... SUCCESS!

Mysql error log on slave:

[root@localhost~] # tail-3 / data/mysql/logs/mysql-error.log

2017-05-1415 Storing MySQL user name or password information in themaster info repository is not secure and is therefore not recommended 59 5335 [Warning]. Pleaseconsider using the USER and PASSWORD connection options for START SLAVE Seethe 'START SLAVE Syntax' in the MySQL Manual for more information.2017-05-1415 Slave 59 Slave 54 [ERROR] synchronous replication failure caused by Slave: connected to master'rep@10.0.0.201:3306',replication resumed inlog' mysql-bin.000014' at position62815.binlog_ignore_db synchronous replication failure caused by O: error reconnecting to master'rep@10.0.0.201:3306'-retry-time: 60 retries: 1, Error_code: 20032017-05-1416

A netizen's case:

After using the binlog_ignore_db command on mysql's master to ignore a library, all statements executed with mysql-e will not be written to binlog, because during master-slave replication, there is a library that does not copy. Check his configuration. The binlog format is row mode, and ask him for the sql statement at that time:

Mysql-e'create table db.tb like db.tb1'

Check mysql's manual to know that there are two parameters for ignoring replication of a database, one is binlog_ignore_db and the other is replicate-ignore-db. There is a difference between them:

The binlog_ignore_db parameter is set on master, for example: binlog_ignore_db=test, then all operations (additions, deletions and corrections) under the test library will not be recorded, so the number of files will be reduced when the binlog on the main library is accepted on the slave. The advantage of this is that it can reduce the network io, reduce the amount of Ibinlog O of the IThank O thread on the slave side, and maximize replication performance. But there is also a hidden danger, which will be mentioned below.

Replicate-ignore-db is a replicate-ignore-db=test1 set on slave, so all operations (additions, deletions, deletions and modifications) under the test1 library will not be performed by the sql thread

Conclusion: if you want to ignore the replication of a library on slave, it is best not to use the parameter binlog_ignore_db, but to use replicate-ignore-db= db instead

A bug synchronized by 16.mysql5.5.19/mysql5.5.20

When we replicate synchronously from a lower version of mysql5.1.43 (slave) to a higher version of mysql5.5.19 (master), we encounter that the mysql on the host master has been rebooting. After troubleshooting, we learned that the lower version of mysql replicates synchronously to the higher version of mysql. As long as the replication point of synchronization is wrong, the mysql service of the host master will always restart, but there is no such phenomenon when the version is consistent. For example, the binlog and pos points on master are mysql-bin.000010.

So you do the following on slave:

Change master TO master_LOG_FILE='mysql-bin.000010'.master_LOG_POS=106, this will promote the development of this kind of bug.,. The problem has been fixed since the current mysql5.5.25a version.

Note: according to the mysql manual, mysql supports synchronization from a higher version to a lower version (that is, the lower version is master and the higher version is slave, so slave to master is compatible with synchronous replication, no problem)

But in turn, there will be problems. There is the character set setting section.

If there is any solution to the error report of mysql5.6 master-slave replication, let's stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.