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

• 使用delete語(yǔ)句誤刪數(shù)據(jù)行
• 使用drop table或者truncate table誤刪數(shù)據(jù)表
• 使用drop database語(yǔ)句誤刪數(shù)據(jù)庫(kù)
• 使用rm誤刪mysql整個(gè)實(shí)例
  

對(duì)于誤刪行

• 使用flashback工具閃回，把數(shù)據(jù)恢復(fù)回來(lái)。原理是修改binlog的內(nèi)容，拿回原庫(kù)重放，需要確保binlog_format=row和binlog_row_imsge=Full
  
• 具體恢復(fù)時(shí)
  
  • 如果是insert，將binlog event類(lèi)型是write_rows event改為delete_rows event。
  • 如果是delete則相反。
  • 如果是update，binlog里有數(shù)據(jù)修改前和修改后的值，對(duì)調(diào)這兩行即可。
• 多個(gè)事物也是按照以上原則倒敘執(zhí)行。
• 預(yù)防：把sql_safe_updates參數(shù)設(shè)置為on。這樣一來(lái)，如果我們忘記在delete或者update語(yǔ)句中寫(xiě)where條件，或者where條件里面沒(méi)有包含索引字段的話，這條語(yǔ)句的執(zhí)行就會(huì)報(bào)錯(cuò)。
  

對(duì)于誤刪庫(kù)/表

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

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

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

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

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

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

注意：

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

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

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

一個(gè)系統(tǒng)不可能備份無(wú)限的日志，你還需要根據(jù)成本和磁盤(pán)空間資源，設(shè)定一個(gè)日志保留
的天數(shù)。如果你的DBA團(tuán)隊(duì)告訴你，可以保證把某個(gè)實(shí)例恢復(fù)到半個(gè)月內(nèi)的任意時(shí)間點(diǎn)，這就表示備份系統(tǒng)保留的日志時(shí)間就至少是半個(gè)月。

雖然“發(fā)生這種事，大家都不想的”，但是萬(wàn)一出現(xiàn)了誤刪事件，能夠快速恢復(fù)數(shù)據(jù)，將損失
降到最小，也應(yīng)該不用跑路了。而如果臨時(shí)再手忙腳亂地手動(dòng)操作，最后又誤操作了，對(duì)業(yè)務(wù)造成了二次傷害，那就說(shuō)不過(guò)去了。

延遲復(fù)制備庫(kù)

• 如果一個(gè)庫(kù)的備份特別大，或者誤操作的時(shí)間距離上一個(gè)全量備份的時(shí)間較長(zhǎng)，比如一周一備的實(shí)例，在備份之后的第6天發(fā)生誤操作，那就需要恢復(fù)6天的日志，這個(gè)恢復(fù)時(shí)間可能是要按天來(lái)計(jì)算的。
• 延遲復(fù)制的備庫(kù)是一種特殊的備庫(kù)，通過(guò) CHANGE MASTER TO MASTER_DELAY = N命令，可以指定這個(gè)備庫(kù)持續(xù)保持跟主庫(kù)有N秒的延遲。
• 比如你把N設(shè)置為3600，這就代表了如果主庫(kù)上有數(shù)據(jù)被誤刪了，并且在1小時(shí)內(nèi)發(fā)現(xiàn)了這個(gè)誤操作命令，這個(gè)命令就還沒(méi)有在這個(gè)延遲復(fù)制的備庫(kù)執(zhí)行。這時(shí)候到這個(gè)備庫(kù)上執(zhí)行stopslave，再通過(guò)之前介紹的方法，跳過(guò)誤操作命令，就可以恢復(fù)出需要的數(shù)據(jù)。

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

只要不是惡意地把整個(gè)集群刪除，而只是刪掉了其中某一個(gè)節(jié)點(diǎn)的數(shù)據(jù)的話，HA系統(tǒng)就會(huì)開(kāi)始工作，選出一個(gè)新的主庫(kù)，從而保證整個(gè)集群的正常工作。這時(shí)，你要做的就是在這個(gè)節(jié)點(diǎn)上把數(shù)據(jù)恢復(fù)回來(lái)，再接入整個(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)都全軍覆沒(méi)。應(yīng)對(duì)這種情況，我的建議只能是說(shuō)盡量把你的備份跨機(jī)房，或者最好是跨城市保存。Kill sql語(yǔ)句

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

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

kill并不是馬上停止的意思，而是告訴執(zhí)行線程說(shuō)，這條語(yǔ)句已經(jīng)不需要繼續(xù)執(zhí)行了，可以開(kāi)始“執(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退出等待，來(lái)處理這個(gè)THD::KILL_QUERY狀態(tài)。

以上包含了三層意思：

• 一個(gè)語(yǔ)句執(zhí)行過(guò)程中有多處埋點(diǎn)，在這些“埋點(diǎn)”的地方判斷線程狀態(tài)，如果發(fā)現(xiàn)線程狀態(tài)
• 是THD::KILL_QUERY，才開(kāi)始進(jìn)入語(yǔ)句終止邏輯；
• 如果處于等待狀態(tài)，必須是一個(gè)可以被喚醒的等待，否則根本不會(huì)執(zhí)行到“埋點(diǎn)”處；
• 語(yǔ)句從開(kāi)始進(jìn)入終止邏輯，到終止邏輯完全完成，是有一個(gè)過(guò)程的。

一個(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命令卻沒(méi)什么效果，

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

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

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

在這個(gè)例子里，12號(hào)線程的等待邏輯是這樣的：每10毫秒判斷一下是否可以進(jìn)入InnoDB執(zhí)
行，如果不行，就調(diào)用nanosleep函數(shù)進(jìn)入sleep狀態(tài)。

也就是說(shuō)，雖然12號(hào)線程的狀態(tài)已經(jīng)被設(shè)置成了KILL_QUERY，但是在這個(gè)等待進(jìn)入InnoDB的循環(huán)過(guò)程中，并沒(méi)有去判斷線程的狀態(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收到了斷開(kāi)連接的提示。

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

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

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

kill無(wú)效的第一類(lèi)情況，即：線程沒(méi)有執(zhí)行到判斷線程狀態(tài)的邏輯?？赡芤矔?huì)由于IO壓力過(guò)大，讀寫(xiě)IO的函數(shù)一直無(wú)法返回，導(dǎo)致不能及時(shí)判斷線程的狀態(tài)。

• 第二類(lèi)情況，終止邏輯耗時(shí)較長(zhǎng)
• 超大事物執(zhí)行期間被kill，回滾操作耗時(shí)很長(zhǎng)。
• 大會(huì)滾操作，比如查詢(xún)過(guò)程中生成了很大的臨時(shí)文件，刪除臨時(shí)文件需要等待IO資源，導(dǎo)致耗時(shí)較長(zhǎng)。
• DDL執(zhí)行到最后階段，如果被kill，需要?jiǎng)h除中間過(guò)程的臨時(shí)文件，也需要IO資源。

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

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

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

• 執(zhí)行show databases
• 切到db1，執(zhí)行show tables
• 把這兩個(gè)命令的結(jié)果用于構(gòu)建一個(gè)本地hash表。

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

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

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