背景

開了并行復(fù)制的半同步從庫SQL 線程報(bào)1032錯(cuò)誤，異步復(fù)制從庫沒有報(bào)錯(cuò)，偶爾會(huì)出現(xiàn)這種

版本

mysql 5.7.16

redhat 6.8

mysql> show variables like '%slave_para%';

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

| Variable_name | Value |

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

| slave_parallel_type | LOGICAL_CLOCK |

| slave_parallel_workers | 16 |

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

分析

1、疑是對(duì)從庫執(zhí)行了更新操作，導(dǎo)致更新的記錄不存在

2、查看error log發(fā)現(xiàn)

2018-04-03T10:11:47.720156+08:00 16 [ERROR] Slave SQL for channel '': **Worker 13** failed executing transaction **'a272bbcf-874f-11e7-a288-00505695b721:687871861**' at master log mysql-bin.004119, end_log_pos 376471678; **Could not execute Update_rows event** on table anytxn.seq_xxxx; Can't find record in 'seq_xxxx', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.004119, end_log_pos 376471678, Error_code: 1032

2018-04-03T10:11:47.720230+08:00 2 [Warning] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756

2018-04-03T10:11:47.720959+08:00 2 [Note] Error reading relay log event for channel '': slave **SQL thread was killed**

3、從 SQL線程停止的position分析binlog發(fā)現(xiàn)

SET @@SESSION.GTID_NEXT= 'a272bbcf-874f-11e7-a288-00505695b721:687871861'/*!*/;

# at 376471694

#180403 10:11:47 server id 104073 end_log_pos 376471555 CRC32 0x1be91176 Querythread_id=2086049exec_time=0error_code=0

SET TIMESTAMP=1522721507/*!*/;

BEGIN

/*!*/;

# at 376471768

#180403 10:11:47 server id 104073 end_log_pos 376471616 CRC32 0x10644d77 Table_map: `anytxn`.`seq_xxxx` mapped to number 301

# at 376471829

#180403 10:11:47 server id 104073 end_log_pos 376471678 CRC32 0x871a9787 Update_rows: table id 301 flags: STMT_END_F

### UPDATE `anytxn`.`seq_xxxx`

### WHERE

### @1=7116088 /* LONGINT meta=0 nullable=0 is_null=0 */

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

### SET

### @1=7116089 /* LONGINT meta=0 nullable=0 is_null=0 */

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

# at 376471891

#180403 10:11:47 server id 104073 end_log_pos 376471709 CRC32 0x9eb59238 Xid = 22247621418

COMMIT/*!*/;

# at 376471922

#180403 10:11:47 server id 104073 end_log_pos 376471774 CRC32 0xf7b6ad5d GTIDlast_committed=641254sequence_number=641259

SET @@SESSION.GTID_NEXT= 'a272bbcf-874f-11e7-a288-00505695b721:687871862'/*!*/;

# at 376471987

#180403 10:11:47 server id 104073 end_log_pos 376471856 CRC32 0x6256de00 Querythread_id=2085350exec_time=0error_code=0

SET TIMESTAMP=1522721507/*!*/;

BEGIN

/*!*/;

# at 376472069

#180403 10:11:47 server id 104073 end_log_pos 376471979 CRC32 0x6c329578 Table_map: `anytxn`.`bm_cc_customer_address_info` mapped to number 1569

# at 376472192

#180403 10:11:47 server id 104073 end_log_pos 376472162 CRC32 0x834cc8b9 Write_rows: table id 1569 flags: STMT_END_F

### INSERT INTO `anytxn`.`bm_xxxxxxxxxxxxxx`

### SET

### @1=14480779 /* LONGINT meta=0 nullable=0 is_null=0 */

### @2='0000001002380654' /* STRING(96) meta=65120 nullable=0 is_null=0 */

### @3='B001' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */

### @4=NULL /* STRING(12) meta=65036 nullable=1 is_null=1 */

### @5='10000010001202000000001' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */

### @6='B00' /* STRING(9) meta=65033 nullable=1 is_null=0 */

### @7='xxxxxxxxxxx' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */

### @8=NULL /* STRING(18) meta=65042 nullable=1 is_null=1 */

### @9=NULL /* STRING(18) meta=65042 nullable=1 is_null=1 */

### @10=NULL /* STRING(18) meta=65042 nullable=1 is_null=1 *

mysql@xxxxxx:/home/mysql> mysqlbinlog -v -v --start-datetime='2018-04-03 10:11:45' --stop-datetime='2018-04-03 10:11:48' /data/mysql/xxxxx-relay-bin.005477 | grep last_comm | grep 10:11:47 | grep 641254

#180403 10:11:47 server id 104073 end_log_pos 376469618 CRC32 0xb6dc6cef GTIDlast_committed=641227sequence_number=641254

#180403 10:11:47 server id 104073 end_log_pos 376471774 CRC32 0xf7b6ad5d GTIDlast_committed=641254sequence_number=641259

#180403 10:11:47 server id 104073 end_log_pos 376472258 CRC32 0x27cf3013 GTIDlast_committed=641254sequence_number=641260

從上面信息可以看出，發(fā)生更新記錄不存在是在更新anytxn.xxxx_id表的標(biāo)識(shí)為7116088 的記錄

有兩個(gè)并發(fā)提交的事務(wù)last_committed=641254 ，與發(fā)現(xiàn)更新的記錄不存在的 GTID *.687871861 事務(wù)還有另一個(gè)并發(fā)提交的事務(wù) sequence_number=641260(即insert另一張表的操作),難道是master有并發(fā)提交的事務(wù)，slave多個(gè)work線程去apply的時(shí)候出現(xiàn)了問題？

4、查看更新的記錄不存在的表和相關(guān)記錄

show create table seq_xxxx;

| seq_xxxx | CREATE TABLE seq_xxxx (

currentValue bigint(20) NOT NULL,

increment int(11) NOT NULL DEFAULT '1'

mysql> select * from seq_xxxx;

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

| currentValue | increment |

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

| 7116088 | 1 |

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

可以發(fā)現(xiàn)實(shí)際數(shù)據(jù)庫中是存在該記錄的

測試

mysql> select @@version;

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

| @@version |

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

| 5.7.19-log |

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

1 row in set (0.00 sec)

mysql> show variables like '%para%';

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

| Variable_name | Value |

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

| slave_parallel_type | LOGICAL_CLOCK |

| slave_parallel_workers | 4 |

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

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=10.186.30.73 --mysql-socket=/opt/mysql/data/3307/mysqld.sock --mysql-port=3307 --db-driver=mysql --mysql-db=test --mysql-user=admin --mysql-password=admin --table_size=100000 --tables=5 --threads=100 --time=120 --report-interval=5 run

有并發(fā)提交的事務(wù)，但沒有模擬重現(xiàn)出更新的記錄不存在，但在庫中卻存在的情況

更新

slave_preserve_commit_order=1(default 0，MTS中有效)

確保事務(wù)以與在從服務(wù)器中繼日志中顯示的順序相同的順序在從服務(wù)器上apply，防止出現(xiàn)gaps。如果該值設(shè)置為0，slave并行apply事務(wù)可能會(huì)無序，檢查最近執(zhí)行的事務(wù)并不能保證主服務(wù)器上的所有先前事務(wù)都已在從服務(wù)器上執(zhí)行

開啟多線程復(fù)制后，這個(gè)參數(shù)推薦設(shè)置為1

總結(jié)

以上是生活随笔為你收集整理的mysql 并行 更新_MySQL 并行复制(MTS) 从库更新的记录不存在实际却存在的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。