域名預(yù)訂/競(jìng)價(jià)，好“米”不錯(cuò)過

這篇文章主要介紹了Mysql誤刪數(shù)據(jù)解決方案及kill語句原理,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下

mysql誤刪數(shù)據(jù)

使用delete語句誤刪數(shù)據(jù)行

使用drop table或者truncate table誤刪數(shù)據(jù)表

使用drop database語句誤刪數(shù)據(jù)庫

使用rm誤刪mysql整個(gè)實(shí)例

對(duì)于誤刪行

使用flashback工具閃回，把數(shù)據(jù)恢復(fù)回來。原理是修改binlog的內(nèi)容，拿回原庫重放，需要確保binlog_format=row和binlog_row_imsge=Full

具體恢復(fù)時(shí)

如果是insert，將binlog event類型是write_rows event改為delete_rows event。

如果是delete則相反。

如果是update，binlog里有數(shù)據(jù)修改前和修改后的值，對(duì)調(diào)這兩行即可。

多個(gè)事物也是按照以上原則倒敘執(zhí)行。

預(yù)防：把sql_safe_updates參數(shù)設(shè)置為on。這樣一來，如果我們忘記在delete或者update語句中寫where條件，或者where條件里面沒有包含索引字段的話，這條語句的執(zhí)行就會(huì)報(bào)錯(cuò)。

對(duì)于誤刪庫/表

需要使用全量備份，加增量日志的方式。要求線上有定期的全量備份嗎，并且實(shí)時(shí)備份binlog。

假如有人中午12點(diǎn)誤刪了一個(gè)庫，恢復(fù)數(shù)據(jù)的流程如下：

取最近一次全量備份，假設(shè)這個(gè)庫是一天一備，上次備份是當(dāng)天0點(diǎn)；

用備份恢復(fù)出一個(gè)臨時(shí)庫；

從日志備份里面，取出凌晨0點(diǎn)之后的日志

把這些日志，除了誤刪除數(shù)據(jù)的語句外，全部應(yīng)用到臨時(shí)庫。

注意：

為了加速數(shù)據(jù)恢復(fù)，如果這個(gè)臨時(shí)庫上有多個(gè)數(shù)據(jù)庫，你可以在使用mysqlbinlog命令時(shí)，加上一個(gè)–database參數(shù)，用來指定誤刪表所在的庫。這樣，就避免了在恢復(fù)數(shù)據(jù)時(shí)還要應(yīng)用其他庫日志的情況。

在應(yīng)用日志的時(shí)候，需要跳過12點(diǎn)誤操作的那個(gè)語句的binlog：

加速恢復(fù)的方法：備份恢復(fù)出臨時(shí)實(shí)例之后，將這個(gè)臨時(shí)實(shí)例設(shè)置成線上備庫的從庫，

一個(gè)系統(tǒng)不可能備份無限的日志，你還需要根據(jù)成本和磁盤空間資源，設(shè)定一個(gè)日志保留

的天數(shù)。如果你的DBA團(tuán)隊(duì)告訴你，可以保證把某個(gè)實(shí)例恢復(fù)到半個(gè)月內(nèi)的任意時(shí)間點(diǎn)，這就表示備份系統(tǒng)保留的日志時(shí)間就至少是半個(gè)月。

雖然“發(fā)生這種事，大家都不想的”，但是萬一出現(xiàn)了誤刪事件，能夠快速恢復(fù)數(shù)據(jù)，將損失

降到最小，也應(yīng)該不用跑路了。而如果臨時(shí)再手忙腳亂地手動(dòng)操作，最后又誤操作了，對(duì)業(yè)務(wù)造成了二次傷害，那就說不過去了。

延遲復(fù)制備庫

如果一個(gè)庫的備份特別大，或者誤操作的時(shí)間距離上一個(gè)全量備份的時(shí)間較長(zhǎng)，比如一周一備的實(shí)例，在備份之后的第6天發(fā)生誤操作，那就需要恢復(fù)6天的日志，這個(gè)恢復(fù)時(shí)間可能是要按天來計(jì)算的。

延遲復(fù)制的備庫是一種特殊的備庫，通過 CHANGE MASTER TO MASTER_DELAY = N命令，可以指定這個(gè)備庫持續(xù)保持跟主庫有N秒的延遲。

比如你把N設(shè)置為3600，這就代表了如果主庫上有數(shù)據(jù)被誤刪了，并且在1小時(shí)內(nèi)發(fā)現(xiàn)了這個(gè)誤操作命令，這個(gè)命令就還沒有在這個(gè)延遲復(fù)制的備庫執(zhí)行。這時(shí)候到這個(gè)備庫上執(zhí)行stopslave，再通過之前介紹的方法，跳過誤操作命令，就可以恢復(fù)出需要的數(shù)據(jù)。

對(duì)于rm刪除數(shù)據(jù)

只要不是惡意地把整個(gè)集群刪除，而只是刪掉了其中某一個(gè)節(jié)點(diǎn)的數(shù)據(jù)的話，HA系統(tǒng)就會(huì)開始工作，選出一個(gè)新的主庫，從而保證整個(gè)集群的正常工作。這時(shí)，你要做的就是在這個(gè)節(jié)點(diǎn)上把數(shù)據(jù)恢復(fù)回來，再接入整個(gè)集群。

當(dāng)然了，現(xiàn)在不止是DBA有自動(dòng)化系統(tǒng)，SA（系統(tǒng)管理員）也有自動(dòng)化系統(tǒng)，所以也許一個(gè)批量下線機(jī)?的操作，會(huì)讓你整個(gè)MySQL集群的所有節(jié)點(diǎn)都全軍覆沒。應(yīng)對(duì)這種情況，我的建議只能是說盡量把你的備份跨機(jī)房，或者最好是跨城市保存。Kill sql語句

session B是直接終止掉線程，什么都不管就直接退出嗎？顯然，這是不行的。

當(dāng)對(duì)一個(gè)表做增刪改查操作時(shí)，會(huì)在表上加MDL讀鎖。所以，session B雖然處于blocked狀態(tài)，但還是拿著一個(gè)MDL讀鎖的。如果線程被kill的時(shí)候，就直接終止，那之后這個(gè)MDL讀鎖就沒機(jī)會(huì)被釋放了。

kill并不是馬上停止的意思，而是告訴執(zhí)行線程說，這條語句已經(jīng)不需要繼續(xù)執(zhí)行了，可以開始“執(zhí)行停止的邏輯了”。

實(shí)際上，當(dāng)執(zhí)行kill query thread_id_b,mysql里處理kill命令的線程做了以下事情：

把session B的運(yùn)行狀態(tài)改為了THD::KILL_QUERY

給session B的執(zhí)行線程發(fā)了一個(gè)信號(hào)。

因?yàn)橄駡D1的我們例子里面，session B處于鎖等待狀態(tài)，如果只是把session B的線程狀態(tài)設(shè)置

THD::KILL_QUERY，線程B并不知道這個(gè)狀態(tài)變化，還是會(huì)繼續(xù)等待。發(fā)一個(gè)信號(hào)的目的，就

是讓session B退出等待，來處理這個(gè)THD::KILL_QUERY狀態(tài)。

以上包含了三層意思：

一個(gè)語句執(zhí)行過程中有多處埋點(diǎn)，在這些“埋點(diǎn)”的地方判斷線程狀態(tài)，如果發(fā)現(xiàn)線程狀態(tài)

是THD::KILL_QUERY，才開始進(jìn)入語句終止邏輯；

如果處于等待狀態(tài)，必須是一個(gè)可以被喚醒的等待，否則根本不會(huì)執(zhí)行到“埋點(diǎn)”處；

語句從開始進(jìn)入終止邏輯，到終止邏輯完全完成，是有一個(gè)過程的。

一個(gè)kill不掉的例子

執(zhí)行set global innodb_thread_concurrency=2，將InnoDB的并發(fā)線程上限數(shù)設(shè)置為2；然后，執(zhí)行下面的序列：

可以看到：

sesssion C執(zhí)行的時(shí)候被堵住了；

但是session D執(zhí)行的kill query C命令卻沒什么效果，

直到session E執(zhí)行了kill connection命令，才斷開了session C的連接，提示“Lost connection to MySQL server during query”，

但是這時(shí)候，如果在session E中執(zhí)行show processlist，你就能看到下面這個(gè)圖：

id=12這個(gè)線程的Commnad列顯示的是Killed。也就是說，客戶端雖然斷開了連接，但實(shí)際上服務(wù)端上這條語句還在執(zhí)行過程中。

在這個(gè)例子里，12號(hào)線程的等待邏輯是這樣的：每10毫秒判斷一下是否可以進(jìn)入InnoDB執(zhí)

行，如果不行，就調(diào)用nanosleep函數(shù)進(jìn)入sleep狀態(tài)。

也就是說，雖然12號(hào)線程的狀態(tài)已經(jīng)被設(shè)置成了KILL_QUERY，但是在這個(gè)等待進(jìn)入InnoDB的循環(huán)過程中，并沒有去判斷線程的狀態(tài)，因此根本不會(huì)進(jìn)入終止邏輯階段。

而當(dāng)session E執(zhí)行kill connection 命令時(shí)，是這么做的，

把12號(hào)線程狀態(tài)設(shè)置為KILL_CONNECTION；

關(guān)掉12號(hào)線程的網(wǎng)絡(luò)連接。因?yàn)橛羞@個(gè)操作，所以你會(huì)看到，這時(shí)候session C收到了斷開連接的提示。

那為什么執(zhí)行show processlist的時(shí)候，會(huì)看到Command列顯示為killed呢？其實(shí)，這就是因?yàn)樵趫?zhí)行show processlist的時(shí)候，有一個(gè)特別的邏輯：

如果一個(gè)線程的狀態(tài)是KILL_CONNECTION，就把Command列顯示成Killed。

所以其實(shí)，即使是客戶端退出了，這個(gè)線程的狀態(tài)仍然是在等待中。只有等到滿足進(jìn)入InnoDB的條件后，session C的查詢語句繼續(xù)執(zhí)行，然后才有可能判斷到線程狀態(tài)已經(jīng)變成了KILL_QUERY或者KILL_CONNECTION，再進(jìn)入終止邏輯階段。

kill無效的第一類情況，即：線程沒有執(zhí)行到判斷線程狀態(tài)的邏輯。可能也會(huì)由于IO壓力過大，讀寫IO的函數(shù)一直無法返回，導(dǎo)致不能及時(shí)判斷線程的狀態(tài)。

第二類情況，終止邏輯耗時(shí)較長(zhǎng)

超大事物執(zhí)行期間被kill，回滾操作耗時(shí)很長(zhǎng)。

大會(huì)滾操作，比如查詢過程中生成了很大的臨時(shí)文件，刪除臨時(shí)文件需要等待IO資源，導(dǎo)致耗時(shí)較長(zhǎng)。

DDL執(zhí)行到最后階段，如果被kill，需要?jiǎng)h除中間過程的臨時(shí)文件，也需要IO資源。

ctrl+C，mysql實(shí)際上也是啟動(dòng)了一個(gè)連接進(jìn)程發(fā)送了kill query命令。

關(guān)于客戶端連接慢的誤解

如果庫里面的表很多，連接就會(huì)很慢。比如有一個(gè)庫有上萬個(gè)表，使用默認(rèn)參數(shù)連接的時(shí)候，mysql會(huì)提供一個(gè)本地庫名和表名補(bǔ)全的功能：

執(zhí)行show databases

切到db1，執(zhí)行show tables

把這兩個(gè)命令的結(jié)果用于構(gòu)建一個(gè)本地hash表。

第三步是耗時(shí)比較長(zhǎng)的操作，也就是我們感知到慢不是連接滿，也不是服務(wù)端慢，而是客戶端慢。如果在這個(gè)連接中加上 -A，就可以取消自動(dòng)補(bǔ)全功能，很快返回。

自動(dòng)補(bǔ)全的效果就是，在輸入庫名或者表名的時(shí)候，將輸入前綴，可以使用tab自動(dòng)補(bǔ)全或者顯示提示。實(shí)際如果自動(dòng)補(bǔ)全用的不多，可以每次使用都加-A。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

來源：腳本之家

鏈接：https://www.jb51.net/article/195345.htm

申請(qǐng)創(chuàng)業(yè)報(bào)道，分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！