目錄
- 一. 基礎使用場景
- 二. 統計binlog中各表的[增删改]次數
- 三. 解析定位binlog中大事務
- 四. 多元度解析binlog
- 1.檢視該binlog中 操作最多的表
- 2.檢視該binlog中 删除操作最多的表
- 3.檢視該binlog中 指定庫表的操作統計
- 4.檢視該binlog中 單個操作影響行數的top 3
- 5.檢視該binlog中 删除超過1000行的操作
- 6.檢視該binlog中 所有類型操作超過1000行的操作
- 五. MySQL Binlog Server
- 1.擷取遠端資料庫binlog日志
- 2.模拟複制線程實作Binlog Server
- 六. binlog備份恢複-基于複制特性
- 1. 使用mysqlbinlog恢複的劣勢
- 2. 使用SQL線程回放binlog的優勢
- 3. 恢複的場景示例
- 4. 環境準備及故障模拟
- 4.1 binlog Server備份模拟
- 4.2 生成模拟資料并模拟壓力
- 4.3 模拟每天實體備份
- 4.4 模拟備份後的資料變更
- 4.5 觸發誤操作模拟故障
- 5. 資料還原操作步驟
- 5.1 還原全量備份
- 5.2 binlog轉relay log
- 5.3 relay_log_recovery參數配置
- 6. 資料還原精細化控制
- 6.1 隻恢複單個庫的資料
- 6.2 隻恢複單個表的資料
- 6.3 恢複到指定的GTID或position點
- 6.4 提升恢複效率的參數優化
- 6.5 mysqlbinlog與SQL線程回放效率對比
一. 基礎使用場景
## 直接解析binlog檔案内容
shell> mysqlbinlog mysql-bin.000001 |less
## 顯示更詳細的binlog資訊
## binlog_rows_query_log_events為ON時，需要使用-vv才可看到具體SQL
shell> mysqlbinlog -vv mysql-bin.000001 |less
## 隻檢視指定GTID事務記錄
shell> mysqlbinlog --include-gtids='e165cded-5c97-11e8-9814-06d1a5b64aec:2' mysql-bin.000001 |less 關于--exclude-gtids的用法
- 排除單條:
'8528c8ee-1284-11e9-9e33-02000aba3c10:525'
- 排除連續:
'8528c8ee-1284-11e9-9e33-02000aba3c10:525-526'
- 排除不連續多條:
'8528c8ee-1284-11e9-9e33-02000aba3c10:525-526,8528c8ee-1284-11e9-9e33-02000aba3c10:528'
關于--stop-datetime指定時間點的建議： 假如在誤操作進行了資料庫删除,則指定--stop-datetime參數時可以直接指定到2019-01-01 21:00:00
，或者更加嚴謹來說，通過定位binlog中該誤操作以及2019-01-01 21:00:00
的時間點來決定恢複時的最後時間。如以下binlog資料,則指定stop-datetime時隻需保持時間在該誤操作上一個事務
内即可(2019-01-01 20:59:01,2019-01-01 21:00:00]
- 2019-01-01 20:40:01 INSERT INTO XXX
- 2019-01-01 20:48:01 UPDATE INTO XXX
- 2019-01-01 20:50:01 DELETE INTO XXX
- 2019-01-01 20:59:01 DELETE INTO XXX
- 2019-01-01 21:00:00 DROP DATABASE XXX
二. 統計binlog中各表的[增删改]次數
mysqlbinlog --no-defaults \
--base64-output=decode-rows \
-vv mysql-bin.000032 | awk '/###/ {if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count) print i,"\t",count[i]}' | column -t | sort -k2nr 輸出示例
三. 解析定位binlog中大事務
備注:該指令由同僚編寫
## 結果将輸出大事務的大小及起始點位置
mysqlbinlog mysql-bin.000001 | grep "last_committed=" -B 1| awk '/^# at/&&NR==1 {tmp=$NF} /^# at/&&NR>1 {print($NF-tmp,"--start-position="tmp,"--stop-position="$NF);tmp=$NF}' | sort -n -r | head -n 20
## 直接指定大事務的起始點位置檢視事務具體内容
mysqlbinlog -vv --start-position=46746293 --stop-position=938267951 mysql-bin.000001|less
## 也可在定位到具體的大事務GTID後單獨解析判斷這個大事務涉及的記錄數
mysqlbinlog -vv --include-gtids='56929ffe-5d09-11ea-bb4e-02000aba3da2:42312' mysql-bin.000001|egrep "### UPDATE|### DELETE|### INSERT"|wc -l 輸出示例
四. 多元度解析binlog
備注:指令摘取自網絡
基本邏輯: 先用腳本将binlog的結果解析彙總,再基于彙總的資料做過濾排序彙總
vim analyze_binlog.sh
#!/bin/bash
## 給定binlog目錄及檔案名稱
BINLOG_DIR=/data/mysql/data
BINLOG_FILE="mysql-bin.000001"
## 具體解析指令
${BINLOG_DIR}/${BINLOG_FILE} | awk \
mysqlbinlog --base64-output=decode-rows -vv ${BINLOG_DIR}/${BINLOG_FILE} | awk \
'BEGIN {s_type=""; s_count=0;count=0;insert_count=0;update_count=0;delete_count=0;flag=0;} \
{if(match($0, /#.*Table_map:.*mapped to number/)) {printf "Timestamp : " $1 " " $2 " Table : " $(NF-4); flag=1} \
else if (match($0, /(### INSERT INTO .*..*)/)) {count=count+1;insert_count=insert_count+1;s_type="INSERT"; s_count=s_count+1;} \
else if (match($0, /(### UPDATE .*..*)/)) {count=count+1;update_count=update_count+1;s_type="UPDATE"; s_count=s_count+1;} \
else if (match($0, /(### DELETE FROM .*..*)/)) {count=count+1;delete_count=delete_count+1;s_type="DELETE"; s_count=s_count+1;} \
else if (match($0, /^(# at) /) && flag==1 && s_count>0) {print " Query Type : "s_type " " s_count " row(s) affected" ;s_type=""; s_count=0; } \
else if (match($0, /^(COMMIT)/)) {print "[Transaction total : " count " Insert(s) : " insert_count " Update(s) : " update_count " Delete(s) : " \
delete_count "] \n+----------------------+----------------------+----------------------+----------------------+"; \
count=0;insert_count=0;update_count=0; delete_count=0;s_type=""; s_count=0; flag=0} } '
## 執行方式
sh analyze_binlog.sh >> analyze_binlog.log - 輸出樣式
1.檢視該binlog中 操作最多的表
cat analyze_binlog.log | grep Table |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr 2.檢視該binlog中 删除操作最多的表
cat analyze_binlog.log |grep -E 'DELETE' |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr 3.檢視該binlog中 指定庫表的操作統計
cat analyze_binlog.log |grep -i '`demo`.`t1`' | awk '{print $7 " " $11}' | sort -k1,2 | uniq -c 4.檢視該binlog中 單個操作影響行數的top 3
cat analyze_binlog.log | grep Table | sort -nr -k 12 | head -n 3 5.檢視該binlog中 删除超過1000行的操作
cat analyze_binlog.log |grep -E 'DELETE' | awk '{if($12>1000) print $0}' 6.檢視該binlog中 所有類型操作超過1000行的操作
cat analyze_binlog.log |grep -E 'Table' | awk '{if($12>1000) print $0}' 五. MySQL Binlog Server
1.擷取遠端資料庫binlog日志
-
--read-from-remote-server
-
--raw
-
--to-last-log
## 擷取單個檔案
mysql -h47.97.110.54 -uapp -papp -P3306 -e "show binary logs"
mysqlbinlog --raw --read-from-remote-server -h47.97.110.54 -uapp -papp -P3306 mysql-bin-zhenxing.000001>mysql-bin-zhenxing.000001
## 從指定檔案擷取到最後一個檔案
mysqlbinlog --read-from-remote-server -h47.97.110.54 -uapp -papp -P3306 --to-last-log mysql-bin-zhenxing.000001 --raw 2.模拟複制線程實作Binlog Server
-
--raw
-
--result-file
-
--stop-never
-
--stop-never-slave-server-id
mysqlbinlog --raw --read-from-remote-server -h47.97.110.54 -urepl -prepl -P3306 --result-file=remote- mysql-bin-zhenxing.000001 --stop-never --stop-never-slave-server-id=100 & -
局限性
- 資料庫重新開機後，需重新開啟mysqlbinlog 轉儲線程并
指定新的binlog日志轉儲起始點
- mysqlbinlog 轉儲線程異常斷開後，服務端dump線程不會消失而
一直處于僵死狀态
- 如果mysqlbinlog轉儲線程重新以相同配置啟動，則僵死程序會被激活再次使用
- 指定
-raw
MySQL5.7.22未複現該問題,可能已被修複
六. binlog備份恢複-基于複制特性
1. 使用mysqlbinlog恢複的劣勢
通常我們在資料庫誤操作,如drop database後需要基于實體備份及binlog實作資料的恢複,正常的做法如下但
- 恢複全量備份資料
- 恢複增量備份資料(如有)
- 基于備份記錄的日志位點或GTID恢複binlog資料到誤操作删除之前
使用mysqlbinlog解析還原binlog過程存在以下問題
- 存在大量binlog時,效率過低
- 一旦解析異常,不便于做斷點同步
- 跨binlog解析存在一些限制(如建立臨時表操作)
- 對于單庫或單表的還原,無法做到精細化控制
2. 使用SQL線程回放binlog的優勢
基于以上直接用mysqlbinlog解析日志會帶來的問題,結合MySQL複制同步特性,我們知道relay log本質上就是binlog,那我們可以嘗試将,用複制的SQL線程來回放binlog,進而達到高效恢複的目的,且由于binlog僞裝成relay log
用到SQL線程回放,也就能使用到回放的特性,如并行回放,庫表過濾
等等
優勢點基本就是MySQL複制的基本特點,有如下優勢
- 隻需配置好同步的位點,資料庫
自動使用SQL線程恢複資料
- 可以使用MySQL
并行複制
- 可以
精細化控制恢複的庫表
- 可以指定
恢複到具體的GTID
跳過誤操作的GTID
- 資料恢複
可以暫停
繼續同步
3. 恢複的場景示例
以下是使用SQL線程回放binlog的故障場景模拟資訊
- 做全量xtrabackup備份模拟日常備份
- 執行sysbench壓測4張表，20個線程，壓測10分鐘，模拟大量binlog
- 删除執行個體模拟資料庫被誤删除或硬體故障(binlog需要保留)
- 使用xtrabackup恢複全量備份
- 使用MySQL Replication SQL線程回放binlog
(注意:恢複前需要将relay_log_recocery參數設定為0
4. 環境準備及故障模拟
4.1 binlog Server備份模拟
使用mysqlbinlog将binlog檔案實時轉儲到備份目錄,模拟binlog備份(目前環境備份目錄配置在/data/mysql/backup),該步驟可以改為其他binlog備份方式如定期cp或rsync或不備份也可
[root@10-186-61-162 ~]# mkdir -p /data/mysql/backup/binlog
[root@10-186-61-162 ~]# mysqlbinlog --raw --read-from-remote-server -h10.186.61.162 -urepl -psyeWVv1jnWImRaQD -P3306 --result-file=/data/mysql/backup/binlog/ mysql-bin.000001 --stop-never --stop-never-slave-server-id=100 & 4.2 生成模拟資料并模拟壓力
生成sysbench測試資料,建立4張表，每張各100W資料庫并壓測一分鐘模拟資料變化
## 插入基礎資料
[root@10-186-61-162 ~]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-ps-mode=disable --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --tables=4 --table-size=1000000 --report-interval=1 --time=600 --threads=20 prepare
## 執行sysbench壓測模拟資料變化，壓測1分鐘
[root@10-186-61-162 ~]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-ps-mode=disable --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --tables=4 --table-size=1000000 --report-interval=1 --time=60 --threads=20 run 4.3 模拟每天實體備份
執行實體備份模拟每天的全量備份
[root@10-186-61-162 ~]# innobackupex --host=10.186.61.162 --port=3306 --user=backup --password=87hcvjYnJPT4k9tr /data/mysql/backup/ 4.4 模拟備份後的資料變更
[root@10-186-61-162 ~]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-ps-mode=disable --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --tables=4 --table-size=1000000 --report-interval=1 --time=600 --threads=20 run 4.5 觸發誤操作模拟故障
## 生成一個新的binlog包含誤操作測試環境為了友善查找binlog位點
flush logs
## 執行誤操作删庫
drop database sbtest; 5. 資料還原操作步驟
5.1 還原全量備份
[root@10-186-61-162 ~]# cd /data/mysql/backup
[root@10-186-61-162 backup]# innobackupex --apply-log 2020-03-02_17-39-07/
[root@10-186-61-162 backup]# innobackupex --defaults-file=/etc/my.cnf --copy-back 2020-03-02_17-39-07/ 5.2 binlog轉relay log
将binlog轉換為relay log并拷貝到資料目錄以便SQL線程回放資料
[root@10-186-61-162 backup]# cd /data/mysql/backup/binlog/
## 将binlog重命名為relay log
[root@10-186-61-162 binlog]# rename mysql-bin mysql-relay mysql-bin*
## 建立relay log index檔案
[root@10-186-61-162 binlog]# ls ./mysql-relay.0000* >mysql-relay.index
## 拷貝relay log到資料目錄
[root@10-186-61-162 binlog]# cp mysql-relay.* /data/mysql/data/
## 修改資料目錄權限
[root@10-186-61-162 binlog]# chown -R mysql:mysql /data/mysql/data/ 5.3 relay_log_recovery參數配置
修改relay_log_recovery參數，設定為0并啟動資料庫,relay_log_recovery為1時，relay log會在複制線程啟動時被清除重新拉
## 擷取備份檔案中的binlog位點資訊及GTID點對應的relay log檔案
[root@10-186-61-162 ~]# cd /data/mysql/backup/2020-03-02_17-39-07/
[root@10-186-61-162 2020-03-02_17-39-07]# cat xtrabackup_binlog_info
mysql-bin.000007 80456866 f41abe78-5c62-11ea-abf1-02000aba3da2:1-52013
## 啟動資料庫
[root@10-186-61-162 2020-03-02_17-39-07]# systemctl start mysql_3306
## change master指定一個空的主庫，建立SQL線程
root@localhost[(none)]> reset master;
## 指定備份檔案中對應的binlog位點
## 其中MASTER_HOST任意指定一個不存在的位址即可,RELAY_LOG_FILE和RELAY_LOG_POS填入全量備份中的值
root@localhost[(none)]> CHANGE MASTER TO MASTER_HOST='1.1.1.1',RELAY_LOG_FILE='mysql-relay.000007',RELAY_LOG_POS=80456866;
## 檢視指定的位點是否生效
root@localhost[(none)]> select * from mysql.slave_relay_log_info;
+-----------------+----------------------+---------------+-----------------+----------------+-----------+-------------------+----+--------------+
| Number_of_lines | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers | Id | Channel_name |
+-----------------+----------------------+---------------+-----------------+----------------+-----------+-------------------+----+--------------+
| 7 | ./mysql-relay.000007 | 80456866 | | 0 | 0 | 0 | 1 | |
+-----------------+----------------------+---------------+-----------------+----------------+-----------+-------------------+----+--------------+
## 隻需要開啟SQL線程對指定的relay log開始回放即可
root@localhost[(none)]> START SLAVE SQL_THREAD;
## 持續執行可看到binlog資料開始回放
root@localhost[(none)]> show slave status\G
## 待binlog恢複完成且資料校驗無問題後可将複制關系重置
root@localhost[(none)]> stop slave;
root@localhost[(none)]> reset slave;
root@localhost[(none)]> reset slave all; 6. 資料還原精細化控制
6.1 隻恢複單個庫的資料
由于備份是全量備份，無法單庫還原(如果已知表結構可使用可傳輸表空間方式單庫還原),可先将全量恢複後對隻對單庫做binlog還原
隻需在資料恢複步驟的基礎上,開啟SQL線程回放操作前增加一條以下指令即可實作對sbtest單庫的binlog還原
-
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('sbtest.%');
6.2 隻恢複單個表的資料
該步驟與單庫還原類似,隻需将複制過濾屬性指定為具體的表,如
-
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('sbtest.sbtest1','sbtest.sbtest2');
可在開啟binlog還原前後對sbtest1-4表做checksum，驗證是否隻有sbtest1和sbtest2做了binlog還原
checksum table sbtest1,sbtest2,sbtest3,sbtest4;
6.3 恢複到指定的GTID或position點
該步驟隻需在資料恢複步驟的基礎上,将START SLAVE SQL_THREAD改為 START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS
-
START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = 'f41abe78-5c62-11ea-abf1-02000aba3da2:500000;
6.4 提升恢複效率的參數優化
set global sync_binlog=0; ## 也可直接關閉binlog
set global innodb_flush_log_at_trx_commit=0; ## 8.0支援關閉redo log
set global slave_parallel_type=LOGICAL_CLOCK;
set global slave_parallel_workers=8; 6.5 mysqlbinlog與SQL線程回放效率對比
| 恢複方式 | binlog數量 | 恢複耗時 | 備注 |
| mysqlbinlog | 1.2G | 722s | sync_binlog=0和innodb_flush_log_at_trx_commit=0 |
| SQL_THREAD | 1.2G |