前言
公司開發(fā)人員在更新數(shù)據(jù)時使用了 replace into 語句,由于使用不當(dāng)導(dǎo)致了數(shù)據(jù)的大量丟失,到底是如何導(dǎo)致的數(shù)據(jù)丟失本文對此進行分析。
一、問題說明
公司開發(fā)人員在更新數(shù)據(jù)時使用了 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)存在,則會刪除原有記錄并插入新行;如果在表中不存在,則直接插入
地址:https://dev.mysql.com/doc/refman/5.6/en/replace.html
b. 問題現(xiàn)象
丟失數(shù)據(jù)的表結(jié)構(gòu)如下:
CREATE TABLE `active_items` (
執(zhí)行的replace語句如下(多條):
REPLACE INTO active_items(ad_id,score) VALUES('XXXXXXX', 1800);
通過查詢binlog找到執(zhí)行記錄,部分如下:
### UPDATE `items`.`active_items`
### @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. 對比測試
接下來我進行了如下測試:
- MySQL進行Replace操作時造成數(shù)據(jù)丟失
- 左側(cè)使用 REPLACE 語句,右側(cè)使用 DELETE + INSERT 語句,最后結(jié)果完全相同
- 原主鍵id為1的行被刪除,新插入行主鍵id更新為4,沒有指定內(nèi)容的字段c則插入了默認(rèn)值
- 使用 REPLACE 更新了一行數(shù)據(jù),MySQL提示受影響行數(shù)為2行
綜上所述,說明確實是刪除一行,插入一行
三、數(shù)據(jù)恢復(fù)
數(shù)據(jù)丟失或數(shù)據(jù)錯誤后,可以有如下幾種方式恢復(fù):
- 業(yè)務(wù)方自己寫腳本恢復(fù)
- 通過MySQL的binlog查出誤操作sql,生成反向sql進行數(shù)據(jù)恢復(fù)(適合sql數(shù)據(jù)量較小的情況)
- 通過歷史備份文件+增量binlog將數(shù)據(jù)狀態(tài)恢復(fù)到誤操作的前一刻
四、問題擴展
通過上述分析可以發(fā)現(xiàn),REPLACE 會刪除舊行并插入新行,但是binlog中是以update形式記錄,這樣就帶來另一個問題:
從庫自增長值小于主庫
1. 測試
a. 主從一致:
主庫:
mysql> show create table tG
從庫:
mysql> show create table tG
b. 主庫REPLACE:
主庫:
mysql> replace into t (a,b)values(1,7);
從庫:
mysql> show create table tG
注意此時主從兩個表的AUTO_INCREMENT值已經(jīng)不同了
c. 模擬從升主,在從庫進行INSERT:
mysql> insert into t (a,b,c)values(4,4,4);
從庫插入時會報錯,主鍵重復(fù),報錯后AUTO_INCREMENT會 +1,因此再次執(zhí)行就可以成功插入
2. 結(jié)論
這個問題在平時不會有絲毫影響,但是:
如果主庫平時大量使用 REPLACE 語句,造成從庫 AUTO_INCREMENT 值落后主庫太大,當(dāng)主從發(fā)生切換后,再次插入數(shù)據(jù)時新的主庫就會出現(xiàn)大量主鍵重復(fù)報錯,導(dǎo)致數(shù)據(jù)無法插入。
總結(jié)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,謝謝大家對腳本之家的支持。
您可能感興趣的文章:- 解決docker重啟redis,mysql數(shù)據(jù)丟失的問題
- 防止服務(wù)器宕機時MySQL數(shù)據(jù)丟失的幾種方案
- MySQL 丟失數(shù)據(jù)的原因及解決
- MySQL 數(shù)據(jù)丟失排查案例