建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
MySQL進(jìn)行Replace操作時(shí)造成數(shù)據(jù)丟失——那些坑你踩了嗎？
一、問題說明
公司開發(fā)人員在更新數(shù)據(jù)時(shí)使用了 replace into 語句，由于使用不當(dāng)導(dǎo)致了數(shù)據(jù)的大量丟失，到底是如何導(dǎo)致的數(shù)據(jù)丟失?現(xiàn)分析如下。
二、問題分析
a. REPLACE 原理
REPLACE INTO 原理的官方解釋為：
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
如果新插入行的主鍵或唯一鍵在表中已經(jīng)存在，則會(huì)刪除原有記錄并插入新行;如果在表中不存在，則直接插入
地址：https://dev.mysql.com/doc/refman/5.6/en/replace.html
b. 問題現(xiàn)象
丟失數(shù)據(jù)的表結(jié)構(gòu)如下：
- CREATE TABLE `active_items` (
- `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
- `ad_id` char(32) NOT NULL DEFAULT '' COMMENT 'XXX',
- `ap_id` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'XXX',
- `price` bigint(20) NOT NULL DEFAULT '0' COMMENT 'xxx',
- `rate` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `cc_price` bigint(20) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `sale_count` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `prom_count` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `c1` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `order_num` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `score` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `audit_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `prom_modify_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `sales_modify_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '創(chuàng)建時(shí)間',
- `modify_time` int(11) NOT NULL DEFAULT '0' COMMENT '最后修改時(shí)間',
- `score_modify_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `brand_id` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `freight_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'XXX',
- `flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'XXX',
- PRIMARY KEY (`id`),
- UNIQUE KEY `idx_ad_id` (`ad_id`) USING BTREE,
- ...
- ) ENGINE=InnoDB AUTO_INCREMENT=2699 DEFAULT CHARSET=utf8 COMMENT='XXXXX'
執(zhí)行的replace語句如下(多條)：
- REPLACE INTO active_items(ad_id,score) VALUES('XXXXXXX', 1800);
通過查詢binlog找到執(zhí)行記錄，部分如下：
- ### UPDATE `items`.`active_items`
- ### WHERE
- ### @1=21926 /* LONGINT meta=0 nullable=0 is_null=0 */
- ### @2='XXXXXXX' /* STRING(96) meta=65120 nullable=0 is_null=0 */
- ### @3=1 /* TINYINT meta=0 nullable=0 is_null=0 */
- ### @4=3900 /* LONGINT meta=0 nullable=0 is_null=0 */
- ### @5=315 /* INT meta=0 nullable=0 is_null=0 */
- ### @6=1228 /* LONGINT meta=0 nullable=0 is_null=0 */
- ### @7=19 /* INT meta=0 nullable=0 is_null=0 */
- ### @8=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @9=-6 (4294967290) /* INT meta=0 nullable=0 is_null=0 */
- ### @10=0 /* TINYINT meta=0 nullable=0 is_null=0 */
- ### @11=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @12=8 /* INT meta=0 nullable=0 is_null=0 */
- ### @13=1489997272 /* INT meta=0 nullable=0 is_null=0 */
- ### @14=1495765800 /* INT meta=0 nullable=0 is_null=0 */
- ### @15=1495728000 /* INT meta=0 nullable=0 is_null=0 */
- ### @16=1489996426 /* INT meta=0 nullable=0 is_null=0 */
- ### @17=1489997272 /* INT meta=0 nullable=0 is_null=0 */
- ### @18=1495728725 /* INT meta=0 nullable=0 is_null=0 */
- ### @19=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @20=1 /* TINYINT meta=0 nullable=0 is_null=0 */
- ### @21=0 /* TINYINT meta=0 nullable=0 is_null=0 */
- ### SET
- ### @1=35313 /* LONGINT meta=0 nullable=0 is_null=0 */
- ### @2='XXXXXXX' /* STRING(96) meta=65120 nullable=0 is_null=0 */
- ### @3=1 /* TINYINT meta=0 nullable=0 is_null=0 */
- ### @4=0 /* LONGINT meta=0 nullable=0 is_null=0 */
- ### @5=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
- ### @7=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @8=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @9=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @10=0 /* TINYINT meta=0 nullable=0 is_null=0 */
- ### @11=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @12=3150 /* INT meta=0 nullable=0 is_null=0 */
- ### @13=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @14=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @15=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @16=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @17=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @18=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @19=0 /* INT meta=0 nullable=0 is_null=0 */
- ### @20=0 /* TINYINT meta=0 nullable=0 is_null=0 */
- ### @21=0 /* TINYINT meta=0 nullable=0 is_null=0 */
- 操作的ad_id已經(jīng)存在，因此先刪除后插入，可以看到除了指定的 ad_id,score，其他字段都變?yōu)槟J(rèn)值，導(dǎo)致原有數(shù)據(jù)丟失(雖然在日志中轉(zhuǎn)為了update)
c. 對(duì)比測(cè)試
接下來我進(jìn)行了如下測(cè)試：
- 左側(cè)使用 REPLACE 語句，右側(cè)使用 DELETE + INSERT 語句，最后結(jié)果完全相同
- 原主鍵id為1的行被刪除，新插入行主鍵id更新為4，沒有指定內(nèi)容的字段c則插入了默認(rèn)值
- 使用 REPLACE 更新了一行數(shù)據(jù)，MySQL提示受影響行數(shù)為2行
- 綜上所述，說明確實(shí)是刪除一行，插入一行
三、數(shù)據(jù)恢復(fù)
數(shù)據(jù)丟失或數(shù)據(jù)錯(cuò)誤后，可以有如下幾種方式恢復(fù)：
- 業(yè)務(wù)方自己寫腳本恢復(fù)
- 通過MySQL的binlog查出誤操作sql，生成反向sql進(jìn)行數(shù)據(jù)恢復(fù)(適合sql數(shù)據(jù)量較小的情況)
- 通過歷史備份文件+增量binlog將數(shù)據(jù)狀態(tài)恢復(fù)到誤操作的前一刻
四、問題擴(kuò)展
通過上述分析可以發(fā)現(xiàn)，REPLACE 會(huì)刪除舊行并插入新行，但是binlog中是以u(píng)pdate形式記錄，這樣就帶來另一個(gè)問題：
從庫自增長值小于主庫
1. 測(cè)試
a. 主從一致：
主庫：
- mysql> show create table t\G
- *************************** 1. row ***************************
- Table: t
- Create Table: CREATE TABLE `t` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `a` int(11) DEFAULT '0',
- `b` int(11) DEFAULT '0',
- `c` int(11) DEFAULT '0',
- PRIMARY KEY (`id`),
- UNIQUE KEY `a` (`a`)
- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
從庫：
- mysql> show create table t\G
- *************************** 1. row ***************************
- Table: t
- Create Table: CREATE TABLE `t` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `a` int(11) DEFAULT '0',
- `b` int(11) DEFAULT '0',
- `c` int(11) DEFAULT '0',
- PRIMARY KEY (`id`),
- UNIQUE KEY `a` (`a`)
- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
b. 主庫REPLACE：
主庫：
- mysql> replace into t (a,b)values(1,7);
- Query OK, 2 rows affected (0.01 sec)
- mysql> show create table t\G
- *************************** 1. row ***************************
- Table: t
- Create Table: CREATE TABLE `t` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `a` int(11) DEFAULT '0',
- `b` int(11) DEFAULT '0',
- `c` int(11) DEFAULT '0',
- PRIMARY KEY (`id`),
- UNIQUE KEY `a` (`a`)
- ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
從庫：
- mysql> show create table t\G
- *************************** 1. row ***************************
- Table: t
- Create Table: CREATE TABLE `t` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `a` int(11) DEFAULT '0',
- `b` int(11) DEFAULT '0',
- `c` int(11) DEFAULT '0',
- PRIMARY KEY (`id`),
- UNIQUE KEY `a` (`a`)
- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
- 注意此時(shí)主從兩個(gè)表的AUTO_INCREMENT值已經(jīng)不同了
c. 模擬從升主，在從庫進(jìn)行INSERT：
- mysql> insert into t (a,b,c)values(4,4,4);
- ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
- mysql> show create table t\G
- *************************** 1. row ***************************
- Table: t
- Create Table: CREATE TABLE `t` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `a` int(11) DEFAULT '0',
- `b` int(11) DEFAULT '0',
- `c` int(11) DEFAULT '0',
- PRIMARY KEY (`id`),
- UNIQUE KEY `a` (`a`)
- ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
- mysql> insert into t (a,b,c)values(4,4,4);
- Query OK, 1 row affected (0.00 sec)
- 從庫插入時(shí)會(huì)報(bào)錯(cuò)，主鍵重復(fù)，報(bào)錯(cuò)后AUTO_INCREMENT會(huì) +1，因此再次執(zhí)行就可以成功插入
2. 結(jié)論
這個(gè)問題在平時(shí)不會(huì)有絲毫影響，但是：
如果主庫平時(shí)大量使用 REPLACE 語句，造成從庫 AUTO_INCREMENT 值落后主庫太大，當(dāng)主從發(fā)生切換后，再次插入數(shù)據(jù)時(shí)新的主庫就會(huì)出現(xiàn)大量主鍵重復(fù)報(bào)錯(cuò)，導(dǎo)致數(shù)據(jù)無法插入。
3. 參考文章
http://www.cnblogs.com/monian/archive/2014/10/09/4013784.html
網(wǎng)站標(biāo)題：MySQL進(jìn)行Replace操作時(shí)造成數(shù)據(jù)丟失——那些坑你踩了嗎？
轉(zhuǎn)載源于：http://www.5511xx.com/article/cocdopg.html
