01-數據庫服務日誌管理
二進制日誌管理
1.二進制日誌格式設置
查看數據庫二進制日誌事務信息，對於DML語句相關事務操作內容無法查看？
若出現以上查看日誌信息問題，需要對數據庫二進制日誌格式做修改
binlog_format -- 影響數據庫二進制日誌記錄DML語句信息形式
statement（SBR） -- 採用語句格式進行記錄DML語句日誌信息，對於DML語句信息可以直接查看
row（RBR） -- 採用行格式進行記錄DML語句日誌信息，對於DML語句會以編碼方式記錄，並且解碼後會發現以具體行數據記錄信息
mixed（MBR） -- 採用混合格式進行DML語句日誌信息，自動識別DML語句會採用明文或編碼方式記錄
mysqlbinlog --base64-output=decode-rows -vvv binlog.000002
-- 執行以上查看二進制日誌操作，可以對二進制日誌中編碼信息做解碼操作
INSERT INTO `xiaoX`.`t1` SET @1=1 @2='xiaoA' @3='abc'
INSERT INTO `xiaoX`.`t1` SET @1=2 @2='xiaoB' @3='def'
-- 數據插入操作 set @1 表示第一列信息 @2 表示第二列信息 @3 表示第三列信息
xiaoX.t1
1 xiaoA abc
2 xiaoB def
UPDATE `xiaoX`.`t1` WHERE @1=2 @2='xiaoB' @3='def' SET @1=2 @2='xiaoB' @3='xxx'
1 xiaoA abc
2 xiaoB xxx
set global binlog_format='statement';
在進行二進制格式配置時，應該選擇哪種格式配置更合理？
SBR
優勢特點：可以方便直接獲取事務中DML操作的語句信息，日誌量信息會相比ROW格式記錄少一些
應用缺陷：可能會在進行主從同步或數據修復時，會導致數據不一致
RBR
優勢特點：在進行主從同步或數據修復時，可以有效保證數據一致性
應用缺陷：不能直接獲取事務中DML操作的語句信息，需要解碼查看，日誌量信息會更多些
業務場景操作DML SQL語句是不會加載函數執行，可以選擇SBR格式，會更合適
業務場景操作DML SQL語句將使用函數執行語句，可以選擇RBR格式，會更合適（保證數據一致性）
2.二進制日誌記錄功能設置
sql_log_bin -- 表示可以控制事務操作語句信息是否記錄到binlog日誌中
sql_log_bin=1 (默認設置) -- 表示所有事務操作語句都會記錄到binlog日誌
sql_log_bin=0 -- 表示所有事務操作語句不會記錄到binlog日誌
-- 在數據庫中，利用二進制日誌進行數據修復時，可以不用將事務信息記錄到日誌中；
-- 在數據庫中，某些主庫中的事務操作，無需同步到從庫中，可以將事務信息不用記錄到日誌中；
3.二進制日誌信息切割處理
方法一：利用語句或命令實現日誌切割
flush logs;
mysqladmin -uroot -p123456 flush-logs
方法二：可以將數據庫服務重啓實現切割
restart
方法三：根據日誌數據量做切割處理
max_binlog_size -- 做配置項設置，可以根據日誌數據量進行切割處理（默認日誌切割數據量 1G）
4.二進制日誌清理刪除操作
方法一：利用語句實現刪除清理
purge binary logs to 'mysql-bin.010'
-- 根據指定binlig日誌，將對應日誌編號前的歷史日誌都做清理
PURGE BINARY LOGS BEFORE '2026-03-03 06:49:00';
-- 根據日誌時間信息，將對應歷史的日誌信息做刪除
方法二：實現日誌信息自動清理
binlog_expire_logs_seconds -- 可以識別切割後的日誌存在系統中多少秒就做清理
expire_logs_days -- 可以識別切割後的日誌存在系統中多少天就做清理
5.二進制日誌備份操作處理
# 保存重要二進制日誌/方便實現高可用架構數據的一致性
步驟一：創建日誌備份服務器/安裝好數據庫服務程序
.....省略.....
步驟二：在日誌備份服務器中創建日誌備份目錄 並實現日誌備份功能
mkdir -p /data/binlog-51-3306/ && chown -R mysql.mysql /data/binlog-51-3306/
cd /data/binlog-51-3306/
mysqlbinlog -R --host=10.0.0.51 --user=root --password=123456 --raw --stop-never binlog.000008 &
-R -- 實現遠程binlog日誌備份
--raw -- 可以實現實時同步binlog日誌數據信息
--stop-never -- 以守護進程方式一直做數據庫日誌備份
6.二進制日誌信息截取方法
企業應用：單個數據庫實例中有單一業務數據庫
創建單一業務庫
create database game;
use game;
create table ga_user (id int,name char(10),level tinyint);
begin;
insert into ga_user values (1,'xiaoA',0);
insert into ga_user values (2,'xiaoB',0);
insert into ga_user values (3,'xiaoC',0);
commit;
begin;
update ga_user set level=5 where id=1;
update ga_user set level=6 where id=2;
commit;
delete from ga_user where id>=2; -- 錯誤操作
mysqlbinlog命令截取二進制日誌信息有以下幾種方法
01 指定起始和結束位置
可以使用 --start-position 和 --stop-position 參數來指定你想要查看的二進制日誌的起始和結束位置事件信息
mysqlbinlog --start-position=842 --stop-position=1554 binlog.000019
-- 確認截取的數據信息是否正確
mysqlbinlog --start-position=842 --stop-position=1554 binlog.000019 >/data/backup01.sql
set session sql_log_bin=0
mysql -S /tmp/mysql87.sock </data/backup01.sql
set session sql_log_bin=1
-- 利用binlog日誌中截取語句信息 還原修復數據
02 指定起始和結束時間
可以使用--start-datetime 和 --stop-datetime 參數來指定你想要查看的二進制日誌的起始和結束時間事件信息
mysqlbinlog --start-datetime="2026-03-03 7:36:34" --stop-datetime="2023-01-02 00:00:00" binlog.000001
03 過濾特定的對象數據 [瞭解]
可以使用 --to-last-log 參數來查看從指定文件開始到最後一個日誌文件的全部內容，或結合使用 --database 來過濾特定數據庫的事件。
# 從binlog日誌中，過濾有關特定數據庫的所有事務事件信息
mysqlbinlog -R --base64-output=decode-rows -vvv --database=xiaoB --to-last-log binlog.000016
--to-last-log -- 可以過濾多個binlog日誌信息
--base64-output=decode-rows -vvv -- 解碼DML事務操作語句信息，方便做查看或過濾處理
--database=xiaoB -- 只將指定數據庫有關的數據信息過濾
7.二進制日誌信息數據閃回功能
企業應用：單個數據庫實例中有多個業務數據庫
create database game;
use game;
create table ga_user (id int,name char(10),level tinyint);
begin;
insert into ga_user values (1,'xiaoA',0);
insert into ga_user values (2,'xiaoB',0); 300
insert into ga_user values (3,'xiaoC',0);
commit;
use www;
create table www_user (id int,name char(10),level tinyint);
begin;
begin;
update ga_user set level=5 where id=1;
update ga_user set level=6 where id=2;
commit; 500
begin;
insert into www.www_user values (1,'xiaoA',0);
insert into www.www_user values (2,'xiaoB',0);
insert into www.www_user values (3,'xiaoC',0); 100000
commit;
use www;
delete from ga_user where id>=2; -- 錯誤操作
-- 對DML操作實現快速撤銷功能 -- 數據閃回功能
在數據損壞異常時，只是做了誤刪除 誤修改 誤插入導致的數據損壞，可以採用閃回機制恢復誤操作數據
閃回工具+binlog
數據閃回工具安裝部署
步驟一：下載閃回工具包（www.github.com -- binlog2sql）
ll /opt/binlog2sql-master.zip
-rw-r--r-- 1 root root 15030 10月 10 2022 /opt/binlog2sql-master.zip
步驟二：安裝閃回工具包
[root@oldboy-db01 opt]# cd binlog2sql-master/
[root@oldboy-db01 binlog2sql-master]# ll
總用量 44
-rwxr-xr-x 1 root root 9155 10月 29 2019 binlog2sql.py
-- 加壓軟件程序壓縮包，會生成程序目錄，在程序目錄中可以有python腳本信息
步驟三：部署python腳本運行環境
yum install -y python3 python3-pip
pip3 install -r requirements.txt -- 安裝python程序依賴
擴展説明：保證binlog2sql數據閃回工具可以正常使用，需要確認以下信息
1）確認數據庫中binlog格式配置為 ROW 行格式 binlog_format=ROW
如果以上信息不做調整，在執行閃回操作命令後，不會有任何信息輸出
2）確認閃回工具所使用遠程連接用户，密碼加密方式必須是mysql_native_password
如果以上信息不做調整，在執行閃回操作命令後，會有連接報錯提示
數據閃回工具應用方法（python腳本執行方法）
python3 binlog2sql.py -h10.0.0.51 -P3307 -uroot -p123456 -d game -t ga_user --sql-type=delete --start-file='binlog.000020'
DELETE FROM `game`.`ga_user` WHERE `id`=2 AND `name`='xiaoB' AND `level`=6 LIMIT 1;
#start 1514 end 1789 time 2026-03-03 09:05:11 gtid
DELETE FROM `game`.`ga_user` WHERE `id`=3 AND `name`='xiaoC' AND `level`=0 LIMIT 1;
#start 1514 end 1789 time 2026-03-03 09:05:11 gtid
python3 binlog2sql.py -h10.0.0.51 -P3307 -uroot -p123456 -d game -t ga_user --sql-type=delete --start-file='binlog.000020' -B
INSERT INTO `game`.`ga_user`(`id`, `name`, `level`) VALUES (3, 'xiaoC', 0);
#start 1514 end 1789 time 2026-03-03 09:05:11 gtid
INSERT INTO `game`.`ga_user`(`id`, `name`, `level`) VALUES (2, 'xiaoB', 6);
#start 1514 end 1789 time 2026-03-03 09:05:11 gtid
-B 參數表示將錯誤操作語句進行逆向操作
02.數據庫服務數據備份恢復操作
1.如何實現數據庫數據備份和恢復
方法一：採用邏輯方式進行數據庫備份與恢復
利用mysqldump命令就可以實現數據庫索引備份
原理説明： 是將數據庫中所有數據進行掃描 掃描後將所有數據信息轉換為SQL語句進行保存備份
特點説明： 可以更靈活的對數據庫中數據做備份保存，利用邏輯備份或恢復數據效率較低
應用場景： 在需要備份的數據量小的情況下，可以優先選擇邏輯方式備份 （100G~500G）
方法二：採用物理方式進行數據庫備份與恢復
將數據庫數據目錄做備份拷貝 （物理冷備）
利用xbk工具可以實現數據備份（物理熱備）
原理説明： 是將數據庫數據目錄中所有數據文件信息做拷貝
特點説明： 可以快速實現數據備份與恢復，不能靈活備份與恢復某個庫或某個表的數據信息
應用場景： 在需要備份的數據量大的情況下，可以優先選擇物理方式備份 （100G~500G）
2.實現數據庫邏輯備份和恢復數據操作過程
mysqldump：
mysqldump -u用户 -p密碼 -S 套接字文件 備份選項參數 >/backup/backup.sql
-- 實現本地數據庫中數據備份
mysqldump -u用户 -p密碼 -h數據庫服務地址 -P服務端口 備份選項參數 >/backup/backup.sql
-- 實現遠程數據庫中數據備份
實現數據庫基礎備份與恢復操作
mysqldump -uroot -S /tmp/mysql87.sock -A >/backup/all.sql
-- 數據庫服務全備
mysql -uroot -p12345 套接字/tcp-ip </backup/all.sql
mysql> source /backup/all.sql
-- 數據庫服務全備恢復
mysqldump -uroot -S /tmp/mysql87.sock -B game xiaoA >/backup/database.sql
-- 實現數據服務分庫備份
mysql -uroot -p12345 套接字/tcp-ip </backup/database.sql
mysql> source /backup/database.sql
-- 數據庫服務分庫恢復
mysqldump -uroot -S /tmp/mysql87.sock oldboy student t100w >/backup/table.sql
-- 實現數據庫服務分表備份
mysql -uroot -p12345 套接字/tcp-ip 庫名 </backup/table.sql
mysql> use oldboy
mysql> source /backup/table.sql
-- 數據庫服務分表恢復
3.實現數據庫基礎進階備份操作
1）實現數據表結構信息備份
mysqldump -uroot -S /tmp/mysql87.sock 全備/分庫/分表 --no-data >/backup/no-data.sql
mysqldump -uroot -S /tmp/mysql87.sock -B oldboy --no-data >/backup/no-data.sql
-- 只是將表結構進行保存，可以藉助保存的備份文件恢復庫表信息，通過獨立表空間遷移操作，也能實現數據修復
2）實現備份開發相關數據信息
存儲過程(-R)：在數據庫中創建SQL語句腳本信息
事件信息(-E)：在數據庫中創建定時任務
觸發器信息(--triggers)：當數據庫中數據滿足什麼條件時，會自動觸發的操作信息
mysqldump -uroot -S /tmp/mysql87.sock -A -R -E --triggers >/backup/all.sql
3) 實現數據庫數據熱備操作
--single-transaction
-- 在進行應用後，完成數據備份不會影響正常數據庫服務業務，並且利用參數進行數據備份可以保證備份數據一致性
--source-data
-- 在進行數據備份結束後，會記錄備份數據事務信息的位置點
mysqldump -uroot -S /tmp/mysql87.sock -A -R -E --triggers --single-transaction --source-data >/backup/all.sql
總結
01 數據庫日誌管理操作
二進制日誌管理操作：
1）日誌格式配置方法：binlog_format
（語句格式-DML語句可直接讀取 行格式-DML語句需要編碼處理，但是可以保證數據恢復或同步一致性）
2）日誌記錄功能設置：sql_log_bin
3) 日誌切割操作應用：利用命令語句切割/重啓數據庫切割/根據日誌存儲量自動切割
4）日誌文件清理操作：利用語句清理/根據切割後日志存在時間情況做清理
5）日誌文件遠程備份：mysqlbinlog -R 將數據二進制日誌統一管理備份
6）日誌信息截取操作：根據位置點/根據時間/可以截取多個日誌或選擇指定數據庫信息截取
7）日誌信息閃回操作：binlog2sql工具可以實現DML誤操作行為快速逆向操作，從而將誤操作數據信息修復
02 數據庫服務備份恢復
1）數據庫備份恢復方法：邏輯備份恢復/物理備份恢復 （原理/特點/應用場景）
2）數據庫邏輯備份恢復方法：mysqldump命令可以應用選項參數
