透過延時從庫+binlog複製，恢復誤運算元據

GreatSQL發表於2024-12-04

透過延時從庫+binlog複製，恢復誤運算元據

一、介紹環境

資料庫版本 例項角色 ip地址 埠
GreatSQL 8.0.32-26 master 192.168.134.199 5725
GreatSQL 8.0.32-26 slave 192.168.134.199 5726

二、主庫配置

shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5725.sock -p
greatsql> create user 'repl'@'%' identified by '123';
greatsql> grant replication slave on . to 'repl'@'%';

三、配置延時從庫

greatsql> CHANGE MASTER TO
    master_host='192.168.134.199',
    master_port=5725,
    master_user='repl',
    master_password='123',
    master_auto_position=1,
    master_delay = 7200;
greatsql> START SLAVE;
greatsql> SHOW SLAVE STATUS\G

file

四、模擬主庫誤刪除資料表

shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5725.sock -p sysbench
greatsql> DROP TABLE sbtest2;

五、延時從庫恢復資料到主庫故障前

1、為了防止恢復失敗，先備份一下從庫。

可以使用Xtrabackup/mysqldump，進行備份從庫，這裡演示使用 Xtrabackup 備份從庫

$ xtrabackup --defaults-file=/data1/greatsql/greatsql5726/my5726.cnf -S /tmp/greatsql5726.sock --backup --slave-info \
--stream=xbstream --target-dir=/backup/full.xb

2、我們找到主庫誤操作在哪個binlog裡面，並需要確認誤操作的binlog位置資訊。

$ /usr/local/greatsql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -vvv ./* | grep -rli 'drop'
$ /usr/local/greatsql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -vvv mysql-bin.000002 |less

file

3、停止sql_thread執行緒，設定不延時複製，設定複製停止在誤操作binlog位置點。

shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5726.sock -p
greatsql> STOP SLAVE;
greatsql> CHANGE MASTER TO master_delay = 0;
greatsql> START SLAVE io_thread;
greatsql> START SLAVE sql_thread until SQL_BEFORE_GTIDS='2fc5a82c-2ac3-11ee-9f7f-00163e402951:187';
greatsql> SHOW SLAVE STATUS\G

4、等待複製到需要的停止的位置點，sql_thread已經停止

file

5、檢視從庫誤操作的表，備份出來恢復到主庫

greatsql> SHOW TABLES FROM sysbench;
greatsql> SELECT COUNT(*) FROM sysbench.sbtest2;
shell> /usr/local/greatsql/bin/mysqldump -S /tmp/mysql5726.sock --set-gtid-purged=OFF --single-transaction --master-data=2 --max-allowed-packet=32M -q sysbench sbtest2 > sbtest2.sql

6、將sbtest2表備份資料恢復到主庫裡

shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5725.sock -p -A sysbench
greatsql> SET sql_log_bin = off;
greatsql> source sbtest2.sql;
greatsql> EXIT;

7、從庫跳過誤操作的gtid，重新設定延時從庫，從庫繼續複製主庫

shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5726.sock -p

greatsql> STOP SLAVE;
greatsql> SET gtid_next='2fc5a82c-2ac3-11ee-9f7f-00163e402951:187';
greatsql> BEGIN;COMMIT;
greatsql> SET gtid_next='automatic';
greatsql> CHANGE MASTER TO master_delay = 7200;
greatsql> START SLAVE;
greatsql> SHOW SLAVE STATUS\G

六、總結防範誤操作

如何避免誤刪庫、刪表等誤操作，以及如何提高資料庫的安全性。

推薦閱讀GreatSQL運維管理手冊：資料庫防範誤操作 https://greatsql.cn/docs/8.0.32-26/6-oper-guide/7-avoid-mistakes.html

1.常見危險誤操作

線上上生產環境中的任何操作都要十分謹慎，可能因為微小疏忽造成無法挽回的巨大損失。

比較常見的線上誤操作有幾種：

  • 想要刪除當前目錄下的檔案，卻不小心執行了 rm -fr /，把整個系統中的所有檔案都給強行刪了。
  • 誤以為是測試環境，想要刪除某個資料物件，卻把線上生產環境的資料庫、表等資料物件給刪除了。
  • 誤以為是測試環境，想要關閉或重啟資料庫例項，甚至是關閉或重啟主機作業系統。
  • 伺服器更換硬碟等熱插拔操作，現場工程師搞錯資訊，把正常的伺服器給插拔了。
  • 只想更新或刪除部分資料，但由於還沒來得及寫好WHERE條件，不小心按下了Enter鍵，導致全表被更新或刪除。

可以防範的方法有幾個：

  1. 總是確認每個資料庫是否有可靠的備份策略，以及備份檔案的有效性。
  2. 配置好一個延遲複製例項，避免在主節點上誤操作刪除資料後，還可以在從節點上實現快速恢復。
  3. 避免層層跳轉的伺服器連線方式，每跳轉一次，就多了一分誤操作的可能性。
  4. 完成操作後立即退出生產業務伺服器，減少犯錯誤的機會。
  5. 經常性確認伺服器、資料庫和路徑標示，並且在每次操作前都要反覆確認伺服器資訊。
  6. 每個伺服器主機系統上都要設定唯一的主機名，提高辨識度。
  7. 生產環境和測試環境要物理隔絕開，使之不能相互連線。
  8. 連線生產環境使用專門的操作機或必須先撥VPN等，多加一道防護門檻。
  9. 避免同時開啟多個終端或操作視窗，這非常容易導致犯錯。
  10. 所有重要操作執行前，都先在文件中寫清楚，並逐一檢查確認無誤。
  11. 每個資料庫的賬號只授予必要的許可權，避免許可權過高而有了更多破壞的機會。
  12. 不要在生產環境執行刪除操作，而是改成RENAME操作，先改名，確認無誤後再刪除，而不是直接刪除。
  13. 在資料庫中設定 sql_safe_updates=1，儘量避免被全表更新、刪除的風險。

萬一發生誤刪資料或者誤操作大面積更新資料，可以參考下面幾種方法進行閃回或挽救：

  1. [MySQL資料誤刪除的總結(opens new window)]：https://mp.weixin.qq.com/s/zMtgC24j7iIJ9xwbNo6AYQ
  2. [MySQL閃回工具binlog2sql(opens new window)]：https://mp.weixin.qq.com/s/hEE12-LeCUsC1zKH48hcag
  3. [my2sql工具之快速入門(opens new window)]：https://mp.weixin.qq.com/s/APgBs7MvJuxJvLwg5i7N_w
  4. [Slave被誤寫入資料如何恢復到主庫(opens new window)]：https://mp.weixin.qq.com/s/yoUNEehE6eOBQ7uFqSR48A
  5. [延遲從庫加上MASTER_DELAY，主庫當機後如何快速恢復服務(opens new window)]：https://mp.weixin.qq.com/s/qlAhbJq_ZPB5OXDd_zTQNw
  6. [一個延遲庫恢復的案例(opens new window)]：https://mp.weixin.qq.com/s/i8cvftodUhkcejswuLSQ3w
  7. [淺談MySQL閃回的實現(opens new window)]：https://mp.weixin.qq.com/s/ZuXS2UcgGS2p2x3p9mTwQg

2.資料安全維護建議

為了讓 GreatSQL 資料庫執行更安全，建議遵循以下幾點規範：

  • 在應用端，所有使用者請求及輸入資料都要做預處理，不能直接提交到資料庫，避免被SQL隱碼攻擊。
  • 定期掃描應用端使用者請求日誌，掃描異常請求並及時處理。
  • 應用伺服器端部署防火牆，阻斷使用者非法請求。
  • 應用程式上線前，都需要進行必要安全掃描，避免常見SQL隱碼攻擊等風險。
  • 資料庫端定期掃描請求特徵，判斷是否有符合安全隱患的請求，及時阻斷處理。
  • 資料庫端啟用審計（AUDIT）、SQL防火牆等元件，及時發現並阻斷非法請求。
  • 資料庫中儲存的敏感資料，務必先進行單向加密，避免被破解、資訊洩漏。
  • 生產環境中的資料，匯入開發測試環境前，要先進行轉碼脫敏操作，避免資訊洩漏。
  • 做好連線請求檢測和監控，發現有異常頻繁請求時，及時阻斷處理。

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章