mysql5.6主從複制報錯以及解決方法mysql5.6主從複制報錯解決方法

1.原因是資料庫表的字元集和表中的字段的字元集不一緻導緻的

下面四分析過程和解決辦法：

17030310:07:33 [ERROR] Error reading packet from server: Lost connection to MySQLserver during query ( server_errno=2013)

17030310:07:33 [Note] Slave I/O thread killed while reading event

17030310:07:33 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000009',position 398229335

17030310:07:38 [Note] Slave SQL thread initialized, starting replication in log'mysql-bin.000002' at position 54629541, relay log './mht-relay-bin.000004'position: 54629687

17030310:07: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` = '阿薩夫·哈努卡' ,`intro` = '<p> 現實主義者漫畫，現實主義者主要是一部自傳形式的故事。描述身為一名父親和丈夫在一個飽受戰争蹂躏的國家中撫養和照顧自己的家庭與孩子的難處。作者的叙事技巧十分出色，能夠以一幅畫完整說出别人整本書想表達的故事。同時畫技也十分了得，畫面中隐藏着很多幽默的小細節，但又不會給給人以淩亂感，往往能以意想不到的手法描繪出一個小故事。作品以一種幽默的的表達手法令讀者感受到戰争的殘酷。作者很喜歡以美麗而無聲的插圖來展示身處一個受到戰火摧殘的國家之中所要面對的危險。這部作品恰似一扇明窗，讓我們可以透過它來了解以色列這個我們并不熟悉的國家的現實狀況，以

17030310:07:38 [Warning] Slave: Incorrect string value: '\xC2\xB7\xE5\x93\x88\xE5...'for column 'author' at row 1 Error_code: 1366

17030310:07:38 [ERROR] Error running query, slave SQL thread aborted. Fix theproblem, and restart the slave SQL thread with "SLAVE START". Westopped at log 'mysql-bin.000002' position 54629541

17030310:07:38 [Note] Slave I/O thread: connected to master'[email protected]:3306',replication started in log 'mysql-bin.000009' atposition 398229335

參考文檔：http://www.cnblogs.com/zhoujinyi/p/4568663.html

表字段字元集和表字元集不一緻導緻mysql主從同步報錯：1366

mysql>show create table  dr_mh_book\G

***************************1. row ***************************

       Table: dr_mh_book

CreateTable: 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) DEFAULT NULL,

  `tag` varchar(20) DEFAULT NULL,

  `diqu` varchar(20) DEFAULT NULL,

  `intro` text, 

  `pingyin` varchar(200) DEFAULT NULL,

  `fpingyin` varchar(5) DEFAULT NULL,

  `updatestatus` varchar(10) DEFAULT NULL,

  `status` int(11) DEFAULT NULL,

  `author` varchar(50) CHARACTER SET gb2312DEFAULT NULL,

  `createtime` int(11) DEFAULT NULL,

  `fabutime` int(11) DEFAULT NULL,

  `chapternum` int(11) DEFAULT NULL,

  `isdown` int(2) unsigned zerofill DEFAULTNULL,

  PRIMARY KEY (`id`),

  KEY `index_name` (`name`)

)ENGINE=InnoDB AUTO_INCREMENT=34182 DEFAULT CHARSET=utf8mb4

1 row inset (0.00 sec)

修改表字段字元集：

參考資料：

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

在主庫操作：

mysql>SELECT *  FROM dr_mh_book WHERE `author`= '阿薩夫·哈努卡';

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)

Empty set(0.01 sec)

在從庫操作：

ERROR1267 (HY000): Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and(utf8_general_ci,COERCIBLE) for operation '='

到此出，資料庫同步正常

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

原因是主庫上某表的索引已經在slave對應表上存在，主庫繼續同步到slave上，會提示slave上某表索引已經存在而導緻同步報錯。

下面是解決辦法：

接下來又出現，主庫上某表已經建立索引，但是從庫已經建立了同樣的索引，導緻複制報錯1061

   在主庫檢視索引：

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

從庫報錯：

 Last_Errno: 1061

                   Last_Error: Error 'Duplicatekey name 'index_name'' on query. Default database: 'mahtu'. Query: 'ALTER TABLE`dr_mh_book`

ADD INDEX`index_name` (`name`)'

                   Last_Error: Error 'Duplicatekey name 'index_book_name'' on query. Default database: 'mahtu'. Query: 'ALTERTABLE `dr_mh_chapter`

ADD INDEX`index_book_name` (`bookid`, `name`)'

在從庫操作删除表索引：

mysql>  drop index index_name on  mahtu.dr_mh_book;

Records:0  Duplicates: 0  Warnings: 0

mysql>show index  from  mahtu.dr_mh_book;

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

到此同步正常，接下來又出現1366報錯：

解決：

mysql 主庫：

mysql>show create table dr_mh_chapter\G

       Table: dr_mh_chapter

CreateTable: CREATE TABLE `dr_mh_chapter` (

  `bookid` int(11) DEFAULT NULL,

  `name` varchar(100) CHARACTER SET gbk DEFAULTNULL,

  `imgstr` longtext,        

  `updatetime` int(11) DEFAULT NULL,

  KEY `index_book_name` (`bookid`,`name`)

)ENGINE=InnoDB AUTO_INCREMENT=574818 DEFAULT CHARSET=utf8mb4

mysql>  alter table dr_mh_chapter  change `name` `name` varchar(100) characterset utf8mb4 DEFAULT NULL;

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\G

*************************** 1. row***************************

       Table:dr_mh_chapter

Create 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,

  KEY`index_book_name` (`bookid`,`name`)

) ENGINE=InnoDB AUTO_INCREMENT=574820 DEFAULTCHARSET=utf8mb4

1 row in set (0.00 sec)

從庫上操作：

 mysql> show create table dr_mh_chapter\G

  `name` varchar(100)CHARACTER SET gbk DEFAULT NULL,

) ENGINE=InnoDB AUTO_INCREMENT=494977 DEFAULT CHARSET=utf8mb4

mysql>  alter tabledr_mh_chapter  change `name` `name`varchar(100) character set utf8mb4 DEFAULT NULL;

Query OK, 126243 rows affected (12.34 sec)

Records: 126243 Duplicates: 0  Warnings: 0

) ENGINE=InnoDB AUTO_INCREMENT=494977 DEFAULTCHARSET=utf8mb4

 Query OK, 0 rows affected (0.00 sec)

 mysql> start slave;

 mysql> show slave status\G

參考文檔：

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

原因是slave上表字段重複

mysql從庫報錯：

[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: 1060

17030312:27:09 [Warning] Slave: Duplicate column name 'isdown' Error_code: 1060

在主庫檢視此字段：desc dr_mh_book 此表字段存在

但是在從庫檢視詞表此段desc dr_mh_book，此字段也存在

是以，報錯原因是從庫也有此字段，是以删除此表的字段

altertable mh_ecms_list drop column isdown;

flushprivileges;       

主從同步正常

mysql 資料庫主從同步報錯

 Last_Errno: 1396

Last_Error:Error 'Operation DROP USER failed for 'mhtuser'@'124.207.48.234'' on query.Default database: ''. Query: 'drop user 'mhtuser'@'124.207.48.234''

原因是主庫删除從庫上不存在的資料庫使用者導緻的

解決辦法：

mysql>stop slave;

mysql>  set global sql_slave_skip_counter=1

mysql>start slave;

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:

原因是：

slave上早已經存在master上同名的的庫，是以master上再次建立庫，slave上會提示庫已經存在，導緻報錯；

Query OK,0 rows affected (0.01 sec)

mysql>set global sql_slave_skip_counter=1;

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'

 原因是從庫提前删除資料庫wjw02,然後在master上再次删除wjw02庫，slave上提示為wjw02庫不存在，導緻複制報錯：

解決方法：

在slave上執行：

Query OK,0 rows affected (0.02 sec)

mysql>set global sql_slave_skip_counter=1; 

Query OK,0 rows affected (0.05 sec)

.将slave_skip_errors=參數寫入slave上的配置檔案my.cnf

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

slave_skip_errors=1007,1008

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

這樣可以直接忽略slave同步報錯資訊

注意：此參數的設定隻能忽略錯誤1032和1062的錯誤。

mysql>show variables like 'slave_exec_mode';

+-----------------+--------+

|Variable_name   | Value  |

|slave_exec_mode | STRICT |

此參數預設是STRICT嚴格模式；

将該參數設定為IDEMPOTENT模式，slave同步出現1032錯誤（記錄沒找到）和1062錯誤（主鍵重複），就會自動跳過次錯誤，并且記錄到錯誤日志裡面，其實此參數和slave_skip_errors作用是一樣的。

隻不過slave_skip_errors參數必須寫入配置檔案my.cnf，重新開機mysql，然而IDEMPOTENT預設不需要重新啟動slave的mysql服務。

mysql>set global slave_exec_mode='IDEMPOTENT';

+-----------------+------------+

|Variable_name   | Value      |

|slave_exec_mode | IDEMPOTENT |

示範：

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;

在master上操作：

+----+----------------------------------+-------------+------------+----------+

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

| id | dev_id                           | tel         | updatetime | pwd    |

在slave上檢視同步複制的狀态為yes

mysql> show slave status\G

但是在slave上檢視此時同步時，mysql的錯誤日志：

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

2017-05-1405:22:55 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: 1032

slave上表記錄重複,導緻slave複制報錯1062主鍵重複 

檢視某表的建表語句

mysql>show create table dr_user_info\G

       Table: dr_user_info

CreateTable: CREATE TABLE `dr_user_info` (

  `dev_id` varchar(100) DEFAULT NULL,

  `tel` varchar(20) DEFAULT NULL,

  `pwd` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

)ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8

slave上表中插入一條記錄

mysql> insert into dr_user_info(id,dev_id,tel,updatetime,pwd) values(24,'qwertyuiop','18234131383','1491177726','111111');

| 24 | qwertyuiop                       | 18234131383 |1491177726 | 111111 |

在master上插入這個表同樣的一條表記錄：

insert into dr_user_info (id,dev_id,tel,updatetime,pwd)values (24,'qwertyuiop','18234131383','1491177726','111111');

 在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

是主鍵重複導緻的

解決辦法是删除slave上表dr_user_info重複的id為24的記錄

mysql>show slave status\G

解決主鍵重複，slave同步正常

slave上檢視表記錄

mysql> select * from dr_user_info;

| 20 | CFD0CCDA603705F35C8EE74427C9CC45 | 13713340729 |1488174916 | 111111 |

| 21 | 11713EA0EDAFC1113E9AB016471BBDCF | 18341732393 |1487632768 | 111111 |

| 22 | C9DEBE883FF6C05A19D5C0487E9C9D63 | 18600271207 |1488521317 | 111111 |

| 23 | D73C9DADAB3611D82B7D5CA11B62DBA1 | 18974748447 |1491177725 | 111111 |

5 rows in set (0.00 sec)

slave上删除記錄24

mysql> delete from dr_user_info where id=24;

然後在master上删除dr_user_info表記錄24

此時slave上同步報錯：1032，提示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

在master上檢視mysql-bin.000010日志上的sql語句，分析出錯處5296175，sql語句在幹嘛

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

#17051318:37:41 server id 1230445  end_log_pos5295947 CRC32 0x3abbe149        Xid =2363

COMMIT/*!*/;

# at5295947

#17051318:56:31 server id 1230445  end_log_pos 5296027CRC32 0x4acf5867        Query   thread_id=23    exec_time=0     error_code=0

SETTIMESTAMP=1494672991/*!*/;

BEGIN

/*!*/;

# at5296027

#17051318:56:31 server id 1230445  end_log_pos5296100 CRC32 0xd1d14129       Table_map: `dr_brower_db`.`dr_user_info` mapped to number 158

# at5296100

#17051318:56:31 server id 1230445  end_log_pos5296175 CRC32 0xf6405bec       Delete_rows: table id 158 flags: STMT_END_F

### DELETE FROM `dr_brower_db`.`dr_user_info`

### WHERE

###   @1=24 /* INT meta=0 nullable=0is_null=0 */

###   @2='qwertyuiop' /*VARSTRING(300) meta=300 nullable=1 is_null=0 */

###   @3='18234131383' /*VARSTRING(60) meta=60 nullable=1 is_null=0 */

###   @4=1491177726 /* INTmeta=0 nullable=1 is_null=0 */

###   @5='111111' /* VARSTRING(60)meta=60 nullable=1 is_null=0 */

# at5296175

#17051318:56:31 server id 1230445  end_log_pos5296206 CRC32 0x8307d44e        Xid =2390

DELIMITER;

# End oflog file

紅色字型提示在master上執行delete了删除表記錄：24

是以此處的報錯可以在slave上忽略跳過這個錯誤

 解決此問題 

原因是當slave意外當機時，有可能會損壞中繼日志relay-log,再次開啟同步複制時，報錯資訊如下：

解決辦法：找到同步的binlog日志和pos點。然後重新同步，

提示：在mysql5.5版本及以上版本，已經考慮到slave當機導緻relay-log損壞的問題造成mysql同步失敗。

即在slave的my.cnf配置檔案中加入參數relay_log_recovery=1,就可以了

人為的失誤在配置mysql主從複制時，的數值等于slave上的server-id的數值。

解決辦法是：保證server-id的數值不一樣

Last_IO_Errno:2003

                Last_IO_Error: error connectingto master '[email protected]:3306' - retry-time: 60  retries: 2

原因有多種，可能是slave上防火牆開啟限制了3306端口，也可能是在slave上執行change master to 時，指定的連接配接複制資料庫的賬戶和密碼不對導緻的。也可能是對端的master開啟防火牆做了3306端口的的限制等，也有可能是伺服器之間網絡的問題導緻的

還有就是master上的my.cnf配置檔案使用參數：binlog_ignore_db=mydb1,slave上的my.cn配置檔案使用參數：replicate-ignore-db =mydb1,也可以導緻mysql同步報錯2003

master上：[root@localhost~]# grep wjw01 /etc/my.cnf   

binlog_ignore_db= wjw01

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

slave上：

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

replicate-ignore-db= wjw01

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

Shuttingdown MySQL.... SUCCESS!

StartingMySQL... SUCCESS!

slave上的mysql錯誤日志：

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

2017-05-1415:59:54 5335 [Warning] Storing MySQL user name or password information in themaster info repository is not secure and is therefore not recommended. 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:59:54 5335 [ERROR] Slave I/O: error reconnecting to master'[email protected]:3306' - retry-time: 60 retries: 1, Error_code: 2003

2017-05-1416:00:54 5335 [Note] Slave: connected to master'[email protected]:3306',replication resumed in log 'mysql-bin.000014' at position628

一個網友的案例：

在mysql的master上采用binlog_ignore_db指令忽略了一個庫以後，使用mysql –e 執行的所有的語句就不寫入binlog了，原因是在進行主從複制時，有一個庫不複制，檢視了一下他的配置，binlog格式為row模式，跟他要了當時的sql語句：

mysql  -e  ‘create table db.tb like db.tb1’;

檢視mysql的手冊知道忽略某個資料庫的複制有兩個參數，一個是binlog_ignore_db,另一個是replicate-ignore-db,他們是有差別的：

binlog_ignore_db參數是設定在master上的，例如：binlog_ignore_db=test,那麼針對test庫下的所有的操作（增删改）都不會記錄下來，這樣slave上接受主庫上的binlog時檔案量就會減少，這樣做好處是可以減少網絡io,減少slave端I/O線程的I/O量，進而最大程度的優化複制性能，。但是也存在一個隐患，在下面會提到。

replicate-ignore-db是設定在slave上的replicate-ignore-db=test1，那麼針對test1庫下的所有的操作（增删改）都不會被sql線程執行，

結論：如果想在slave上忽略一個庫的複制，最好不要采用binlog_ignore_db這個參數，使用replicate-ignore-db= db來代替

當我們使用低版本的mysql5.1.43（slave）向高版本的mysql5.5.19（master）同步複制時，會遇到主機master上的mysql一直在重新開機。後面經過排查，得知低版本的mysql向高版本的mysql同步複制，隻要同步的複制點指錯，主機master的mysql服務就會一直重新開機，但是版本一緻的就沒有這樣的現象。比如：master上的binlog和pos點是mysql-bin.000010 ,107

那麼你在slave上執行如下操作：

change master TO master_LOG_FILE=’mysql-bin.000010’.master_LOG_POS=106,這樣就會促發這個樣的bug.,目前mysql5.5.25a版本以後這樣的問題已經修複了

注釋：mysql手冊上介紹，mysql支援從高版本向低版本同步（即，低版本是master，高版本是slave，那麼slave向master同步複制時是相容的，沒問題的）

但是反過來，就會出現問題。有其是字元集設定這一塊。

 本文轉自 wjw555 51CTO部落格，原文連結:http://blog.51cto.com/wujianwei/1934135