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

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

對于誤刪行

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

對于誤刪庫/表

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

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

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

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

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

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

注意：

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

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

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

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

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

延遲復(fù)制備庫

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

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

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

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

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

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

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ā)了一個信號。

因?yàn)橄駡D1的我們例子里面，session B處于鎖等待狀態(tài)，如果只是把session B的線程狀態(tài)設(shè)置
THD::KILL_QUERY，線程B并不知道這個狀態(tài)變化，還是會繼續(xù)等待。發(fā)一個信號的目的，就
是讓session B退出等待，來處理這個THD::KILL_QUERY狀態(tài)。

以上包含了三層意思：

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

一個kill不掉的例子

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

可以看到：

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

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

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

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

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

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

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

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

• 把12號線程狀態(tài)設(shè)置為KILL_CONNECTION；
• 關(guān)掉12號線程的網(wǎng)絡(luò)連接。因?yàn)橛羞@個操作，所以你會看到，這時候session C收到了斷開連接的提示。

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

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

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

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

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

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

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

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

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

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

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

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