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

mysql誤刪數據

使用delete語(yǔ)句誤刪數據行

使用drop table或??者truncate table誤刪數據表

使(′?_?`)用drop database語(yǔ)句誤刪數據庫

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

對于誤刪行

使用flashback工具閃回，把數據恢復回來(lái)。案及原理是語(yǔ)句原理修改binlog的內容，拿回(′?_?`)原庫重放，刪數需要確保binlog_format=row和binlog_row_imsge=Full

具體恢復時(shí)

如果是語(yǔ)句原理delete則相(′▽?zhuān)?)反。

如果是刪數update，binlog里有數據修改前和修改(gai)后的據??解決方值，對調這兩行即可。案及

多個(gè)事物也是語(yǔ)句原理按照以上原則倒敘執行。

預防：把sql_safe_updates參數設置為on。刪數這樣一來(lái)，據解決方如果我們忘記在(???)delete或者update語(yǔ)句中寫(xiě)where條件，案及(ji)或者where條件里面沒(méi)有包含索引字段的話(huà)，這條語(yǔ)句的執行就會(huì )報錯。

對于誤刪庫/表

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

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

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

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

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

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

注意：

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

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

一個(gè)系統不可能備份無(wú)限的日志，你還需要根據成本和磁盤(pán)空間資源，設定一個(gè)日??志保留

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

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

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

延遲復制備庫

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

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

對于rm刪除數據

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

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

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

kil??l并不是馬上停止的意思，而是告訴執行線(xiàn)程說(shuō)，這條語(yǔ)句已經(jīng)不需要繼續執行了，可以開(kāi)始“執行停止的邏輯了&rヽ(′?｀)ノdq(′?ω?`)uo;。(′?｀*)

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

因為像圖1的我們例子里面，session B處于鎖等待狀態(tài)，如果只是把session B的線(xiàn)程狀態(tài)設置

THD::KILL_QUERY，線(xiàn)程B并不知道這個(gè)狀態(tài)變ヽ(′▽?zhuān)?ノ化，還是??會(huì )繼續等待。發(fā)一個(gè)信號的目的，就

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

以上包含了三層意思(si)：

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

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

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

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

執行set globa┐(′д｀)┌l(fā) innodb_thread_concurrency=2，將InnoDB的并發(fā)線(xiàn)程上限數設置為2；然后，執??行下面的序列：

可以看到：

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

但是session D執行的kill query?? C命令卻沒(méi)什么??效果，

直到session E執行了ヽ(′ー｀)ノkill?? connection命令，才斷開(kāi)了session C的連接，提示“Lost connection to MySQL server during query&rdq??uo;，

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

id=12這個(gè)線(xiàn)程的Commnad列顯示的是Killed。也??ヾ(＾-＾)ノ就(???)是說(shuō)，客戶(hù)端雖然斷開(kāi)了連接，但實(shí)際上服務(wù)端上這條語(yǔ)句還在執行過(guò)程中。

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

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

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

而??當session E執行kill connection 命令時(shí)，是這么做ヽ(′ー｀)ノ的，

把12號線(xiàn)程狀態(tài)設置為KILL_CONNECTION；

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

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

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

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

超大事物執行期間被kilヽ(′▽?zhuān)?ノl，回??滾操作??耗時(shí)很長(cháng)。

大會(huì )滾操作，比如查詢(xún)過(guò)程中生成了很大的臨時(shí)文件，刪除臨時(shí)文???件需要等待IO資源，導致耗時(shí)??較長(cháng)。

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

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

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

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

執(′_｀)行show databaseヾ(′▽?zhuān)??s

切到db1，執行show tables

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

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

以上就是本文的全部?jì)热?，希望對大家的學(xué)習有所幫助，也希望大家多多支??持腳本之家。