mysql誤刪數據

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

對于誤刪行

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

對于誤刪庫/表

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

假如有人中午12點誤刪了一個庫，恢復數據的流程如下：

取最近一次全量備份，假設這個庫是一天一備，上次備份是當天0點；

用備份恢復出一個臨時庫；

從日志備份里面，取出凌晨0點之后的日志

把這些日志，除了誤刪除數據的語句外，全部應用到臨時庫。

注意：

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

在應用日志的時候，需要跳過12點誤操作的那個語句的binlog：

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

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

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

延遲復制備庫

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

對于rm刪除數據

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

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

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

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

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

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

• 把session B的運行狀態(tài)改為了THD::KILL_QUERY
• 給session B的執(zhí)行線程發(fā)了一個信號。

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

以上包含了三層意思：

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

一個kill不掉的例子

執(zhí)行set global innodb_thread_concurrency=2，將InnoDB的并發(fā)線程上限數設置為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。也就是說，客戶端雖然斷開了連接，但實際上服務端上這條語句還在執(zhí)行過程中。

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

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

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

• 把12號線程狀態(tài)設置為KILL_CONNECTION；
• 關掉12號線程的網絡連接。因為有這個操作，所以你會看到，這時候session C收到了斷開連接的提示。

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

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

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

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

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

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

關于客戶端連接慢的誤解

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

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

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

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

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持腳本之家。