MySQL數據庫表誤刪除恢復(一) 場景一：MySQL 從庫單張表誤刪除恢復 適用于： 測試環境： 測試數據： 主庫： 模擬誤刪除操作： 從庫： 主庫繼續插入數據： 此時從庫同步報錯： 詳細信息如下： 檢查t2對應的存儲過程還在 解決方案： 一.從庫上忽略該表的同步： 此時并不影響其他表數據的同步。 查看主從同步狀態： Replicate_Wild_Ignore_Table: cjc.t2 詳細信息如下： 此時SQL線程恢復正常 主庫繼續插入數據： 二：恢復從庫t2表數據 0).關閉從庫同步 1).主庫添加讀鎖 讀鎖和寫鎖說明： 讀鎖 當前會話可以讀取t2表數據，無法寫入數據，立即返回錯誤ERROR 1099。 其他會話可以讀取t2表數據，無法寫入數據，寫入時會卡住，Waiting for table metadata lock。 寫鎖 鎖定后，當前會話仍然可以讀、寫t2表，其他會話對t2表不可讀、不可寫。 2).主庫備份 注意： mysqldump中如果添加了--master-data=2或--flush-logs參數會導致備份時執行： 由于主庫已經將t2表改只讀狀態，導致mysqldump時FLUSH TABLES操作卡住，無法正常備份表。 所以備份時需要去掉--master-data=2和--flush-logs參數。 備份 傳到備庫 3).導入到從庫 從庫 恢復 4).忽略過濾 從庫檢查表數據 忽略過濾 注意：無單引號 檢查同步： 5).從庫：啟動同步 6).主、從：驗證數據量是否一致 7).主庫：解鎖表 適用于： 測試環境： 創建測試數據 先看一下mysqldump備份選項： 執行數據庫全備 全庫備份 指定數據庫備份 指定表備份 只備份表結構 只備份表數據 其中： --single-transaction 參數會添加下面額外執行： --master-data=2和--flush-logs都會添加下面額外操作，注意鎖表： 繼續插入數據 查看數據 場景二：MySQL恢復指定表結構 通常定時備份是備份所有數據庫--all-databases，如何通過備份文件恢復所需的數據？ 例如：cjc庫t2表 全備備份文件 恢復表結構 方式1：全備數據量很小時，直接通過vi進行查找 方式2：通過sed方式查找中備份文件中備份t2表結構部分 示例如下： 場景三：MySQL恢復指定表數據 例如：cjc庫t1表 方式1：全備數據量很小時，直接通過vi進行查找 方式2：通過grep方式查找中備份文件中備份t1表數據 示例如下： 場景四：MySQL恢復指定庫 通過--all-databases方式備份文件恢復指定數據庫數據。 例如:cjc庫數據 方式1：使用參數--one-database 還原： 通過general_log可以看到，只還原了cjc庫。 方式2：sed 查看文件 示例如下： 場景五：MySQL恢復所有庫數據 還原--all-databases備份的所有數據。 由于恢復了mysql庫，還原完數據庫后需要執行flush privileges;操作，或在備份是指定--flush-privileges。 參數說明如下： 場景六：MySQL恢復指定表到指定時間點 生成測試數據 備份表 繼續插入數據庫 更新數據 查看當前數據 刪除數據，模擬誤刪除 如何將數據恢復到刪除前時刻，恢復誤刪除的數據？ 主庫查看信息 查看binlog信息 查看event信息 解析binlog數據 信息如下： 或者 備份數據還原 查詢數據 已將t1表還原到備份時間點數據。 需要通過binlog將數據推進到誤刪除數據前一時刻。 查看備份文件位置信息 查詢delete位置信息 前面查到的 或者 誤刪除開始位置 1312 這個位置應該在delete的上一個事務COMMIT下面的位置，也就是delete前面SET @@SESSION.GTID_NEXT對應的位置信息。 生成還原語句 先生成可讀的文件，通過vi等校驗是否有問題 在去掉-vvv --base64-output=decode-rows參數，生成最終恢復文件 注意： 這里必須選擇上一條語句commit之后的position，不能是刪除語句開始的position 1439，否則會有這個警告。 警告：打印的事件范圍以未設置STMT_END_F標志的行事件或表映射事件結束。這可能是因為最后一條語句沒有完全寫入日志，或者是因為您使用了--stop-position或--stop-datetime來引用語句中間的事件。分部語句中的事件尚未寫入輸出。 使用 --skip-gtids=true 參數 如果要恢復數據到源數據庫或者和源數據庫有相同 GTID 信息的實例，那么就要使用該參數，否則無法恢復成功的。 因為相同的GTID事務已經在源數據庫執行過了，根據 GTID 特性，一個 GTID 信息在一個數據庫只能執行一次，所以默認不會恢復成功。 查看日志內容 刷新binlog 還原誤刪除的數據： 通常會先將數據還原到測試庫里，確保沒問題以后，在導出導入到生產庫。 查看數據 已恢復到誤刪除前的數據 ###chenjuchao 20230512###