大家好，我是anyux。本文介紹MySQL基於二進制日誌備份恢復。
文末總結全備與二進制日誌聯合使用，並有導圖
首先說明一下，mysqldump的備份參數
特殊備份參數
-R 備份存儲過程和函數。相當於Linux的shell腳本，一般是對數據庫做大型操作，通常會持續幾個小時。在數據庫服務過程中，原數據庫的一些操作會使用到一些手動構建的函數，如果只備份數據，而不包含這些函數，在數據恢復後會存在問題。-R參數的意義在於，如果數據庫中存在存儲過程和函數，就連接同數據一起備份，否則忽略
-E 備份事件調度器。相當於Linux中的定時任務，也是手動構建的小的腳本，只不過運行在數據庫中。備份也需要一併備份
--triggers 備份觸發器。同上一樣，是MySQL中高級的內容，也一併備份
-F 在備份開始前，刷新一個新的 binlog日誌
--master-data=2 以註釋形式，保存備份開始時間的binlog的狀態信息
<code> mysql -uroot -proot -A -R --triggers --master-data=2 >/tmp/world.sql;/<code>
查看binlog的修改信息
<code> grep "CHANGE" /tmp/world.sql/<code>
記錄備份時刻的binlog信息
--master-data=2
自動鎖表
不加--single-transaction，溫備份，鎖表
加--single-transaction，對於InnoDB表不鎖表備份(快照備份)，其他引擎的表備份時會鎖表
--single-transaction
對於InnoDB表，進行一致性快照備份，不鎖表
MySQL全備與二進制日誌聯合使用
背景環境
正在運行的網站系統，mysql5.7.20數據庫，數據庫量500G,日業務增是1-5M
備份策略：每天凌晨3點，計劃任務調用 mysqldump執行全備腳本
故障時間點：模擬上午10點誤刪除數據庫
思路（重點）
停業務，掛維護頁面，避免數據的二次傷害
找一個臨時庫，恢復凌晨3點全備
截取凌晨3點到10點間誤刪除的binlog，恢復到臨時庫
測試可用性和完整性
可以直接使用臨時庫頂替原生產庫，前端應用對接到新庫
也可以將誤刪除的表導出，導入到原生產庫
開啟業務
處理結果：經過20分鐘的處理，最終業務恢復正常
故障模擬演練
準備數據
注意：此模擬不是恢復全部數據，只恢復tmp表，tmp的角色為用戶表，tmp2表為日誌表。就本次演練，日誌表可有可無。
<code> create database backup charset utf8mb4;
use backup;
create table tmp(id int , name char(20));
insert into tmp values(1,'zs'),(2,'ls'),(3,'w5');
commit;/<code>
備份數據(本過程應當是自動備份)
<code> mysqldump -uroot -proot -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction | gzip > /tmp/full_$(date +%F).sql.gz/<code>
模擬週二23點到週三10點之間數據變化
<code> use backup;
insert into tmp values(4,'aa'),(5,'bb'),(66,'cc');
commit;
create table tmp2 (id int);
insert into tmp2 values(11),(22),(33);
commit;/<code>
模擬故障，刪除表(只是從邏輯上刪除)
<code> drop database backup;/<code>
恢復過程
準備臨時數據庫(多實例)
<code> systemctl restart mysqld3307/<code>
準備恢復全備
<code> cd /tmp
gunzip full_2020-03-14.sql.gz/<code>
截取二進制日誌（是重點，難點）
<code> 過濾 Position to start replication,找到二進制日誌和POS號
grep -A 5 "Position to start replication" full_2020-03-14.sql
根據以前的方法，查找刪除庫前的position號下面兩語句都可以，推薦第一條，速度快show binlog in 'log-bin.000017';mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/log-bin.000017 | grep -n -C 10 "drop"
使用mysqlbinlog 將二進制日誌轉換為sql文件
mysqlbinlog --skip-gtids --start-position=838 --stop-position=1556 /data/mysql/log-bin.000017 > /tmp/log-bin.sql
導出後，使用vim工具查看導入的sql語句是否完整，position號是否一致
/<code>
恢復備份到臨時庫
<code> mysql -uroot -proot -S /data/3307/mysql.sock
僅當前會話關閉二進制日誌
set sql_log_bin=0;
source /tmp/full_2020-03-14.sql;
source /tmp/log-bin.sql;
查詢驗證
use backup;
select * from tmp;
select * from tmp2;/<code>
將故障表導出並恢復到生產
<code> mysqldump -uroot -proot -S /data/3307/mysql.sock backup tmp > /tmp/tmp.sql
mysql -uroot -proot
create database backup charset utf8mb4;
set sql_log_bin=0;
use backup;
source /tmp/tmp.sql;/<code>
xmind圖如下
歡迎在評論區一起討論，質疑。文章都是手打原創，每天最淺顯的介紹運維、數據庫相關的技術，喜歡我的文章就關注一波吧，可以看到最新更新和之前的文章。
閱讀更多 anyux1 的文章
