1.二進(jìn)制日志

1.1 概述

作用：二進(jìn)制日志(Binary Log)以二進(jìn)制格式存儲(chǔ)，記錄所有修改數(shù)據(jù)庫數(shù)據(jù)的SQL語句(如insert、update、delete)或事件(如表結(jié)構(gòu)變更)

核心功能：

• 主從復(fù)制：主庫通過二進(jìn)制日志將數(shù)據(jù)變更同步到從庫
• 數(shù)據(jù)恢復(fù)：配合MySQL 自帶的二進(jìn)制日志解析工具mysqlbinlog，可將二進(jìn)制日志轉(zhuǎn)換為 SQL 語句并執(zhí)行

配置：

系統(tǒng)級(jí)配置：使用Vim 編輯器編輯MySQL的配置文件(vim /etc/mysql/my.cnf)，永久生效

會(huì)話級(jí)配置：在命令行客戶端中設(shè)置變量session sql_log_bin，僅本次連接生效

-- 1 -> 開啟 
-- 0 -> 關(guān)閉
mysql> set session sql_log_bin = [1 | 0];
mysql> show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON   |
+---------------+-------+

1.2 磁盤文件

• 二進(jìn)制日志文件名由 基本名 + 數(shù)字?jǐn)U展名 組成，每生稱新的文件時(shí)數(shù)字?jǐn)U展名遞增，從而保證有序的文件序列。當(dāng)發(fā)生以下情況時(shí)會(huì)生成新的日志文件：
  • 服務(wù)器重啟
  • 在命令行客戶端刷新日志

日志文件的大小達(dá)到 max_binlog_size設(shè)置的上線(默認(rèn)值1GB)

可以使用reset master重置日志文件和索引文件為初始狀態(tài)

1.3 過期時(shí)間

在配置文件my.cnf中配置日志文件的過期時(shí)間，默認(rèn)為2592000秒(30天)，過期后會(huì)自動(dòng)刪除

#MySQL服務(wù)器配置
[mysqld]
#二進(jìn)制日志過期時(shí)間
binlog_expire_logs_seconds=2592000

查看系統(tǒng)變量

mysql> show variables like '%binlog_expire_logs_seconds%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+

1.4 日志格式

• statement：記錄的是 SQL 語句本身，即實(shí)際執(zhí)行的 SQL 語句
  • 優(yōu)點(diǎn)：僅記錄 SQL 語句，占用空間少
  • 缺點(diǎn)：某些情況下可能導(dǎo)致主從數(shù)據(jù)不一致，例如使用非確定性函數(shù)(如 now() 或 rand() )時(shí)，主從服務(wù)器執(zhí)行結(jié)果可能不同
• row：記錄的是每一行數(shù)據(jù)的變更情況，即具體哪些行被修改以及修改后的值
  • 優(yōu)點(diǎn)：數(shù)據(jù)一致性高，避免了非確定性函數(shù)的問題，適用于復(fù)雜的復(fù)制場(chǎng)景
  • 缺點(diǎn)：日志文件較大，尤其是批量操作時(shí)，會(huì)記錄大量行變更信息
• mixed：結(jié)合了statement和row格式的優(yōu)點(diǎn)。默認(rèn)情況下使用statement格式記錄，但在某些可能導(dǎo)致不一致的場(chǎng)景下自動(dòng)切換為row格式
  • 優(yōu)點(diǎn)：平衡了日志大小和數(shù)據(jù)一致性
  • 缺點(diǎn)：仍需注意某些特殊情況下可能存在的一致性問題

推薦使用row格式

1.5 刷盤策略

binlog日志文件的刷盤策略可以通過sync_binlog系統(tǒng)變量來設(shè)置

• sync_binlog=0：由操作系統(tǒng)決定刷盤時(shí)機(jī)。性能最高，但宕機(jī)時(shí)可能丟失較多binlog數(shù)據(jù)
• sync_binlog=1(推薦)：每次事務(wù)提交時(shí)都會(huì)刷盤，確保binlog不丟失。安全性最高，但性能較低
• sync_binlog=N(N>1)：每N次事務(wù)提交后刷盤一次。平衡性能與安全性

1.6 mysqlbinlog工具

簡(jiǎn)介：mysqlbinlog是MySQL自帶的二進(jìn)制日志解析工具，用于查看和管理MySQL的二進(jìn)制日志文件(binlog)
主要功能：

• 查看二進(jìn)制日志內(nèi)容：將二進(jìn)制日志轉(zhuǎn)換為可讀的文本格式
• 過濾日志事件：按時(shí)間、位置或數(shù)據(jù)庫名篩選特定日志記錄
• 生成SQL腳本：將日志內(nèi)容還原為SQL語句，用于數(shù)據(jù)恢復(fù)
• 遠(yuǎn)程解析：支持解析遠(yuǎn)程MySQL服務(wù)器的二進(jìn)制日志

• postion：每條日志都以 # at 開始，后?的數(shù)字表示該條日志記錄的事件在文件中的偏移量
• timestamp：事件發(fā)生的時(shí)間戳
• server id：服務(wù)器標(biāo)識(shí)
• end_log_pos：下一個(gè)事件在文件中的偏移量(等于當(dāng)前事件的結(jié)束偏移量+1)
• type：事件類型(Query)
• thread_id：執(zhí)行事件的線程id
• exec_time：執(zhí)行事件花費(fèi)的時(shí)間
• error_code：錯(cuò)誤碼(0表示沒有錯(cuò)誤)

1.6.1 準(zhǔn)備數(shù)據(jù)

-- 建庫
drop database if exists testdb;
create database testdb character set utf8mb4 collate utf8mb4_0900_ai_ci;
use testdb
-- 建表
create table t1 (
id bigint not null,
name varchar(20) not null
);
-- 寫?
insert into t1 (id, name) values (101, 'user101');
insert into t1 (id, name) values (102, 'user102');
insert into t1 (id, name) values (103, 'user103');
insert into t1 (id, name) values (104, 'user104');
insert into t1 (id, name) values (105, 'user105');
insert into t1 (id, name) values (106, 'user106');
-- 更新
update t1 set name = 'person101' where id = 101;
update t1 set name = 'person102' where id = 102;
update t1 set name = 'person103' where id = 103;
-- 刪除
delete from t1 where id = 104;
delete from t1 where id = 105;
delete from t1 where id = 106;

1.6.2 查看binlog

mysqlbinlog --no-defaults --database=testdb --base64-output=decode-rows -vv --start-position=421 --stop-position=4626 binlog.000001

• no-defaults：禁止讀取默認(rèn)配置文件(如my.cnf)，確保命令執(zhí)行不受配置文件參數(shù)干擾
• database：限定只顯示與指定數(shù)據(jù)庫相關(guān)的日志記錄，過濾其他庫的操作
• base64-output=decode-rows：對(duì)二進(jìn)制日志中的行事件(row格式日志)進(jìn)行Base64解碼，否則這類事件會(huì)以Base64編碼形式顯示
• vv：雙倍詳細(xì)模式(verbose level 2)，輸出最完整的日志信息
• start-position：從二進(jìn)制日志文件的指定位置開始解析
• stop-position：在二進(jìn)制日志文件的指定位置停止解析

1.6.3 數(shù)據(jù)恢復(fù)

• 刪除數(shù)據(jù)庫

  drop database testdb;

• 通過日志文件恢復(fù)數(shù)據(jù)

  mysqlbinlog --no-defaults --skip-gtids=true --start-position=234 --stop-position=4470 binlog.000001 | mysql -uroot -p -h127.0.0.1 -P3306
  

2.數(shù)據(jù)備份與恢復(fù)

2.1 分類

2.1.1 按數(shù)據(jù)存儲(chǔ)形式劃分

邏輯備份：備份數(shù)據(jù)庫的邏輯結(jié)構(gòu)和數(shù)據(jù)內(nèi)容(如SQL語句、導(dǎo)出文件)，與物理存儲(chǔ)無關(guān)。例如MySQL的mysqldump

• 備份文件中包含SQL語句，稍加修改就可以在不同數(shù)據(jù)庫系統(tǒng)上執(zhí)行
• 可以備份整個(gè)數(shù)據(jù)庫或特定的數(shù)據(jù)庫對(duì)象(使用database關(guān)鍵字創(chuàng)建的對(duì)象)
• 備份和恢復(fù)的速度比物理備份慢
• 物理備份：直接復(fù)制數(shù)據(jù)庫的物理文件(如數(shù)據(jù)文件、日志文件)。這種備份方法不涉及數(shù)據(jù)庫的邏輯結(jié)構(gòu)，而是直接在文件系統(tǒng)層面上復(fù)制數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)(相當(dāng)于Windows系統(tǒng)的Ctrl + C)

• 通常不能跨數(shù)據(jù)庫廠商進(jìn)行備份與恢復(fù)
• 可以非?？焖俚貍浞莺突謴?fù)大型數(shù)據(jù)庫，因?yàn)椴恍枰馕鯯QL語句，直接復(fù)制文件即可

2.1.2 按數(shù)據(jù)庫運(yùn)行狀態(tài)劃分

• 冷備份(離線備份)：需停止數(shù)據(jù)庫服務(wù)后備份，備份過程中數(shù)據(jù)庫服務(wù)不可用
  • 影響業(yè)務(wù)連續(xù)性
  • 技術(shù)實(shí)現(xiàn)較簡(jiǎn)單，不需要考慮數(shù)據(jù)一致性和并發(fā)控制
  • 不會(huì)對(duì)數(shù)據(jù)庫性能產(chǎn)生影響
• 熱備份(在線備份)：在數(shù)據(jù)庫運(yùn)行時(shí)進(jìn)行備份，備份過程中數(shù)據(jù)庫服務(wù)依然可用
  • 備份數(shù)據(jù)是系統(tǒng)當(dāng)前最新的
  • 不影響業(yè)務(wù)連續(xù)性
  • 在高負(fù)載情況下，可能對(duì)數(shù)據(jù)庫性能產(chǎn)生一定影響
  • 技術(shù)實(shí)現(xiàn)復(fù)雜，需要考慮數(shù)據(jù)一致性和并發(fā)控制
• 溫備份：介于冷備份和熱備份之間的一種備份方式，數(shù)據(jù)庫在備份過程中部分可用或者處于只讀模式
  • 業(yè)務(wù)中斷時(shí)間較少

2.1.3 按備份數(shù)據(jù)范圍劃分

全量備份：備份整個(gè)數(shù)據(jù)庫或文件系統(tǒng)的所有數(shù)據(jù)，包括所有的文件、數(shù)據(jù)庫表和配置文件等。不依賴于其他備份，可用獨(dú)立恢復(fù)數(shù)據(jù)

增量備份：僅備份自上次備份后變化的數(shù)據(jù)塊

差異備份：僅備份自上次全量備份以來所有變化的數(shù)據(jù)

2.2 mysqldump

2.2.1 工具介紹

作用：它是MySQL數(shù)據(jù)庫系統(tǒng)提供的命令行工具，用于邏輯備份和數(shù)據(jù)導(dǎo)出。它生成包含SQL語句的文本文件，可用于重建數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)
核心功能：

• 備份數(shù)據(jù)庫：導(dǎo)出表結(jié)構(gòu)、數(shù)據(jù)、存儲(chǔ)過程、觸發(fā)器等
• 恢復(fù)數(shù)據(jù)：通過導(dǎo)入SQL文件恢復(fù)數(shù)據(jù)庫狀態(tài)
• 跨版本兼容：導(dǎo)出的SQL文件可在不同MySQL版本間遷移

2.2.2 示例

• 創(chuàng)建目錄

  mkdir /backup/mysql

• 導(dǎo)出

  mysqldump -uroot -p -h127.0.0.1 -P3306 -B testdb > /backup/mysql/dump.sql

查看導(dǎo)出的SQL文件

cat /backup/mysql/dump.sql

• 刪除數(shù)據(jù)庫

  drop database testdb;
  

• 導(dǎo)入數(shù)據(jù)
  • 在命令行通過MySQL客戶端工具直接恢復(fù)

    mysql -uroot -p < /backup/mysql/dump.sql

  • 登錄MySQL客戶端導(dǎo)入SQL文件

    source /backup/mysql/dump.sql

• 查看數(shù)據(jù)

  mysql> use testdb;
  Database changed
  mysql> select * from t1;
  +-----+-----------+
  | id  | name      |
  +-----+-----------+
  | 101 | person101 |
  | 102 | person102 |
  | 103 | person103 |
  +-----+-----------+

2.2.3 數(shù)據(jù)一致性問題

• mysqldump備份數(shù)據(jù)時(shí)的執(zhí)行流程如下
  • 連接數(shù)據(jù)庫
  • 收集需要備份的數(shù)據(jù)
  • 對(duì)所有待備份表加讀鎖
  • 生成數(shù)據(jù)插入語句
  • 釋放鎖
• 備份過程中可能會(huì)產(chǎn)生數(shù)據(jù)一致性問題
  • mysqldump在備份時(shí)默認(rèn)情況下不會(huì)回滾未提交的事務(wù)，因此備份可能包含未提交的事務(wù)中的數(shù)據(jù)更改
  • 如果在加讀鎖后發(fā)生事務(wù)回滾，備份結(jié)果備份可能包含部分事務(wù)的中間狀態(tài)

解決辦法：使用--single-transaction，在事務(wù)中備份，使用MVCC獲取一致性視圖

2.3 mysqlimport

2.3.1 工具介紹

作用：它是MySQL數(shù)據(jù)庫系統(tǒng)提供的命令行工具，用于高效地將文本文件數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫表中。它是load data infile語句的封裝，適用于批量數(shù)據(jù)加載場(chǎng)景
核心功能：

• 快速導(dǎo)入：直接讀取文件并加載到表，跳過SQL解析環(huán)節(jié)，性能優(yōu)于逐行insert

2.3.2 示例

• 查看MySQL允許導(dǎo)出的授權(quán)目錄

  mysql> show variables like 'secure_file_priv';
  +------------------+-----------------------+
  | Variable_name    | Value                 |
  +------------------+-----------------------+
  | secure_file_priv | /var/lib/mysql-files/ |
  +------------------+-----------------------+
  

• 導(dǎo)出

  select * from t1 into outfile '/var/lib/mysql-files/t1.txt';
  

查看導(dǎo)出的文本文件

• 刪除數(shù)據(jù)表

  delete from t1;
  

• 導(dǎo)入

  mysqlimport -uroot -p testdb /var/lib/mysql-files/t1.txt
  

  mysql> select * from t1;
  +-----+-----------+
  | id  | name      |
  +-----+-----------+
  | 101 | person101 |
  | 102 | person102 |
  | 103 | person103 |
  +-----+-----------+
  

  • 查看數(shù)據(jù)

2.4 Xtrabackup

2.4.1 工具介紹

作用：Xtrabackup 是由 Percona 開發(fā)的一款開源MySQL數(shù)據(jù)庫備份工具。它通過熱備份實(shí)現(xiàn)高性能的數(shù)據(jù)庫備份與恢復(fù)，適用于大規(guī)模生產(chǎn)環(huán)境
核心功能：

• 熱備份：在不中斷數(shù)據(jù)庫服務(wù)的情況下進(jìn)行備份
• 壓縮與加密：支持備份文件的壓縮和加密，提升安全性和存儲(chǔ)效率
• 快速可靠：備份速度快且可靠，同時(shí)會(huì)對(duì)備份的數(shù)據(jù)進(jìn)行自動(dòng)校驗(yàn)，確保備份數(shù)據(jù)的完整性
• 性能影響小：在備份過程中，Xtrabackup對(duì)數(shù)據(jù)庫的性能影響較小，不會(huì)增加太多的性能壓力

官網(wǎng)：Xtrabackup下載網(wǎng)址

版本選擇：

查看Linux系統(tǒng)版本

查看MySQL版本

• XtraBackup 2.4：支持 MySQL 5.1、5.5、5.6 和 5.7，但不支持 MySQL 8.0
• XtraBackup 8.0：專為 MySQL 8.0 設(shè)計(jì)，但早期版本(如 8.0.12)不支持 MySQL 8.0.20 及以上版本
• 若使用 MySQL 8.0.20 及以上版本，建議選擇 XtraBackup 8.0.27-19 或更高版本

查看CPU架構(gòu)

安裝軟件源

• 把在Windows系統(tǒng)上下載完畢的percona-xtrabackup-80_8.0.35-34-1.noble_amd64.deb文件上傳至Linux服務(wù)器
• 安裝軟件源

# 安裝軟件源
dpkg -i percona-xtrabackup-80_8.0.35-34-1.noble_amd64.deb
# 更新源
apt update
# 安裝xtrabackup
apt install percona-xtrabackup-80
# 如果提示缺少依賴運(yùn)行以下命令安裝
apt-get install -f
# 更新源
apt update
# 安裝xtrabackup
apt install percona-xtrabackup-80

驗(yàn)證是否安裝成功

2.4.2 示例

創(chuàng)建備份用戶

mysql> create user 'backup_user'@'localhost' identified with mysql_native_password by '123456Aa@@';
Query OK, 0 rows affected (0.00 sec)
mysql> grant backup_admin,process,select,reload,lock tables,replication client,event on *.* to 'backup_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

• 默認(rèn)情況下密碼策略要求密碼包含大小寫字母、數(shù)字和特殊字符
• 查看需要備份的目錄

備份數(shù)據(jù)文件

xtrabackup --defaults-file=/etc/mysql/my.cnf --host=localhost --port=3306 --user=backup_user --password=123456Aa@@ --use-memory=1G --parallel=2 --backup --target-dir=/backup/mysql/full

移動(dòng)或刪除原來的的數(shù)據(jù)目錄

創(chuàng)建數(shù)據(jù)目錄同名的空目錄

• xtrabackup --prepare 命令用于將備份數(shù)據(jù)轉(zhuǎn)換為可恢復(fù)的數(shù)據(jù)庫狀態(tài)。該步驟對(duì)物理備份文件執(zhí)行事務(wù)日志回放和回滾未提交事務(wù)，確保數(shù)據(jù)文件的一致性
• 數(shù)據(jù)恢復(fù)

# 準(zhǔn)備
xtrabackup --prepare --target-dir=/backup/mysql/full
# 數(shù)據(jù)恢復(fù)
xtrabackup --defaults-file=/etc/mysql/my.cnf --copy-back --parallel=2 --target-dir=/backup/mysql/full

為恢復(fù)后的數(shù)據(jù)目錄授權(quán)

chown -R mysql:mysql /var/lib/mysql

重啟MySQL服務(wù)

systemctl restart mysql

登錄數(shù)據(jù)庫驗(yàn)證數(shù)據(jù)

mysql> use testdb;
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+-----+-----------+
| id  | name      |
+-----+-----------+
| 101 | person101 |
| 102 | person102 |
| 103 | person103 |
+-----+-----------+

到此這篇關(guān)于MySQL核心日志與備份恢復(fù)示例詳解的文章就介紹到這了,更多相關(guān)mysql日志與備份恢復(fù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論