通過延時從庫+binlog復制，恢復誤操作數據，你學會了嗎？

作者：Aurora 2024-12-04 08:58:46

為了讓 GreatSQL 數據庫運行更安全，建議遵循以下幾點規范：在應用端，所有用戶請求及輸入數據都要做預處理，不能直接提交到數據庫，避免被SQL注入。定期掃描應用端用戶請求日志，掃描異常請求并及時處理。

一、介紹環境

數據庫版本	實例角色	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

圖片

四、模擬主庫誤刪除數據表

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

圖片

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 已經停止

圖片

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

六、總結防范誤操作

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

1.常見危險誤操作

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

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

想要刪除當前目錄下的文件，卻不小心執行了 rm -fr /，把整個系統中的所有文件都給強行刪了。
誤以為是測試環境，想要刪除某個數據對象，卻把線上生產環境的數據庫、表等數據對象給刪除了。
誤以為是測試環境，想要關閉或重啟數據庫實例，甚至是關閉或重啟主機操作系統。
服務器更換硬盤等熱插拔操作，現場工程師搞錯信息，把正常的服務器給插拔了。
只想更新或刪除部分數據，但由于還沒來得及寫好 WHERE 條件，不小心按下了回車鍵，導致全表被更新或刪除。

可以防范的方法有幾個：

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

2.數據安全維護建議

為了讓 GreatSQL 數據庫運行更安全，建議遵循以下幾點規范：

在應用端，所有用戶請求及輸入數據都要做預處理，不能直接提交到數據庫，避免被SQL注入。
定期掃描應用端用戶請求日志，掃描異常請求并及時處理。
應用服務器端部署防火墻，阻斷用戶非法請求。
應用程序上線前，都需要進行必要安全掃描，避免常見SQL注入等風險。
數據庫端定期掃描請求特征，判斷是否有符合安全隱患的請求，及時阻斷處理。
數據庫端啟用審計（AUDIT）、SQL防火墻等組件，及時發現并阻斷非法請求。
數據庫中存儲的敏感數據，務必先進行單向加密，避免被破解、信息泄漏。
生產環境中的數據，導入開發測試環境前，要先進行轉碼脫敏操作，避免信息泄漏。
做好連接請求檢測和監控，發現有異常頻繁請求時，及時阻斷處理。

責任編輯：武曉燕來源： GreatSQL社區

binlog 復制數據