作者：楊奇龍

網名“北在南方”，資深 DBA，主要負責資料庫架構設計和運維平臺開發工作，擅長資料庫效能調優、故障診斷。

本文來源：原創投稿

*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

一介紹

本章內容透過一個例子說明 replace into 帶來的潛在的資料質量風險，當涉及 replace into 操作的表含有自增主鍵時，主備切換後會造成資料覆蓋等不一致的情況發生。

二案例分析

在主庫上操作

root@test 12:36:51>show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@test 12:37:41>insert into t1(name) values('a')

此時檢查主備庫上 t1 的表結構都是一樣的，AUTO_INCREMENT 都是2.

root@test 12:37:51>show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

在主庫上進行 replace into 操作

root@test 12:37:58>replace into t1(name) values('a');
root@test 12:38:40>replace into t1(name) values('a');
root@test 12:38:49>select * from t1;
+----+------+
| id | name |
+----+------+
| 3 | a |
+----+------+
1 row in set (0.00 sec)

此時檢查主庫中 t1 表結構，請注意 AUTO_INCREMENT=4

root@test 12:38:51>show create table t1 \\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

從庫上 t1 的表結構，AUTO_INCREMENT=2

oot@test 12:39:35>show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@test 12:39:43>select * from t1;
+----+------+
| id | name |
+----+------+
| 3 | a |
+----+------+
1 row in set (0.00 sec)

分析

備庫表 t1 的表結構 AUTO_INCREMENT=2 而主庫上的t1表結構的 AUTO_INCREMENT=4.原本 replace 操作是在自增主鍵的情況下，遇到唯一鍵衝突時執行的是 delete+insert ，但是在記錄 binlog 時，卻記錄成了 update 操作，update操作不會涉及到 auto_increment 的修改。備庫應用了 binlog 之後，備庫的表的 auto_increment 屬性不變。

三風險點

如果主備庫發生主從切換，備庫變為原來的主庫，按照原來的業務邏輯再往下會發生什麼?

root@test 12:40:46>replace into t1(name) values('a');  
Query OK, 2 rows affected (0.00 sec)
root@test 12:40:48>select * from t1;
+----+------+
| id | name |
+----+------+
|  2 | a    |  ---id由原來的3變成了2.
+----+------+
1 row in set (0.00 sec)

如果 t1 表本來就存在多條記錄，主從切換之後，應用寫新的主庫則會發生主鍵衝突，這個留給各位讀者自己測試一下。^_^

四總結

由於 replace into 操作在遇到主鍵衝突的時候會修改主鍵的值，所以如果業務邏輯強依賴自增 ID ，絕對不要用 replace ，普通環境也不建議這樣用，因為 replace into 操作可能會導致主鍵的重新組織。

本文關鍵字：#故障分析# #MySQL#

型別	地址
版本庫
文件
釋出資訊	/releases
資料稽核外掛開發文件	3.modules/3.7_auditplugin/auditplugin_development.html

故障分析 | replace into 導致主備不一致

一介紹

二案例分析

分析

三風險點

四總結

相關文章

故障分析 | replace into 導致主備不一致

一 介紹

二 案例分析

分析

三 風險點

四 總結

相關文章

一介紹

二案例分析

三風險點

四總結