通過延時從庫+binlog復制，恢復誤操作數據，你學會了嗎？
作者：Aurora
為了讓 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.xb2、我們找到主庫誤操作在哪個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\G4、等待復制到需要的停止的位置點，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.sql6、將 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社區
