---

MySQL數據庫備份及日志

在生產環境中，數據的安全性是至關重要的，任何數據的丟失都可能產生嚴重的后果。造成數據丟失的原因

程序錯誤
人為操作錯誤
運算錯誤
磁盤故障
災難(如火災、地震) 和盜竊

所以數據庫需要備份。這里以mysql5.7為例。

一、數據庫備份分類：

從物理與邏輯的角度，備份可分為物理備份和邏輯備份。
（1）物理備份：對數據庫操作系統的物理文件（如數據文件、日志文件等）的備份。又可以分為冷備份和熱備份。

• 冷備份：關閉數據庫時進行的備份操作

• 熱備份：在數據庫運行狀態中進行備份操作，這種備份方法依賴于數據庫的日志文件。

• 溫備份
  數據庫鎖定表格(不可寫入但可讀)的狀態下進行備份操作 (mysgldump)

（2）邏輯備份：對數據庫邏輯組件（如表等數據庫對象）的備份。

從數據庫的備份策略角度，備份可分為完全備份、差異備份、增量備份。

（1）完全備份：每次對數據庫進行完整的備份。可以備份單個數據庫，多個數據庫，所有數據庫，也可以備份數據
庫中的單個表，多個表。

（2）差異備份：備份那些自從上次完全備份之后被修改過的文件，只備份數據庫部分內容，但是存儲和恢復速度快

（3）增量備份：只有那些在上次完全備份或者增量備份后被修改的文件才會被備份。

如何選擇邏輯備份策略 (頻率)

合理值區間
一周一次的全備，全備的時間需要在不提供業務的時間區間進行 晚上2-4點之間進行全備
增量:3天/2天/1天一次增量備份
差異:選擇特定的場景進行備份
一個處理(NFS) 提供額外空間給與mysql 服務器用

完全備份與恢復

備份

使用mysqldump工具可以靈活的控制備份的內容，比如某幾個表或庫都可以單獨備份。

對單個庫進行完全備份。命令格式如下：

mysqldump -u用戶名 -p[密碼] [選項] [數據庫名] > /備份路徑/備份文件名

對多個庫進行完全備份。命令格式如下：

mysqldump -u用戶名 -p[密碼] [選項] --databases 庫名1 庫名2 ... > /備份路徑/備份文件名

對所有庫進行完全備份。命令格式如下：

mysqldump -u用戶名 -p[密碼] --opt --all-databases > /備份路徑/備份文件名

對表結構進行完全備份。命令格式如下：

mysqldump -u用戶名 -p[密碼] -d 數據庫名 表名 > /備份路徑/備份文件名

對表進行完全備份。命令格式如下：

mysqldump -u用戶名 -p[密碼] 數據庫名 表名 > /備份路徑/備份文件名

恢復

登錄mysql，使用source命令恢復庫。命令格式如下：

source 庫備份腳本的路徑

在不登錄MySQL的情況下，使用mysql命令直接恢復整庫。命令格式如下：

mysql -u用戶名 -p[密碼] < 庫備份腳本的路徑

增量備份與恢復

MySQL沒有提供直接的增量辦法，但是可以通過對MySQL的二進制日志間接實現增量備份。二進制日志保存了所有更新或者可能更新數據庫的操作。

特點：

沒有重復數據，備份量不大，時間短。

需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且要進行逐個反推恢復，操作繁瑣。

實現增量備份

要進行MySQL增量備份，首先要開啟二進制日志功能。
（1）在mysql的配置文件的[mysqld]選項中加入log-bin=mysql-bin，然后重啟服務。

 vim /etc/my.cnf[mysqld]log-bin=mysql-bin

然后重啟mysql

（2）使用mysqldump完全備份school庫。

 mysqldump -u root -p123456 xcz1 > /bak/mysql_bak/$(date +%F).sql 

（3）使用mysqladmin的選項flush-logs生成新的二進制文件，這樣在插入新的數據后，新的二進制文件對應的就是數據庫的變化的內容。

mysqladmin -uroot -p123456 flush-logs 

（4）插入一條新的數據，以模擬數據的增加或變更。

此時的數據庫變化保存在編號2 的二進制文件中，使用mysqlbinlog命令可以查看二進制文件的內容，里面保存了插入數據的語句。

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

（5）再次執行flush- logs操作生成新的二進制文件，而新的二進制文件會保存之后的數據操作。

mysqladmin -uroot -p123456 flush-logs

基于時間點與位置恢復

基于位置恢復
使用基于時間點的恢復可能會出現在一個時間點里既同時存在正確的操作又存在錯誤的操作，基于位置是一種更為精確的恢復方式。

mysqlbinlog --no-defaults --start-position='1560' --stop-position='1825'
/opt/mysql-bin.000006 | mysql -uroot -pabc123

基于時間點恢復

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小時:分鐘:秒' --stopdatetime='
年-月-日小時:分鐘:秒' 二進制日志 | mysql -u 用戶名 -p 密碼
#例如：
mysqlbinlog --no-defaults --stop-datetime='2023-07-17 23:04:18' /opt/mysqlbin.
000006 | mysql -uroot -pabc123

二.MySQL日志管理

MySQL日志管理
MySQL的默認日志保存位置為/usr/local/mysql/data
yum安裝為 /var/lib/mysql
日志開啟方式有兩種：通過配置文件或者是通過命令
通過命令修改開啟的日志是臨時的，關閉或重啟服務后就會關閉

可在 /etc/my.cnf 配置文件中的 [mysqld] 中進行日志的路徑修改、開啟、關閉等操作
①、錯誤日志
用于記錄 mysql 啟動、停止或運行時產生的錯誤信息
可通過一下字段進行更新
log-error=/usr/local/mysql/data/mysql_error.log （指定日志的保存位置和文件名）
②、二進制日志
二進制日志，用來記錄所有更新的數據或者已經潛在更新了數據的語句，記錄了數據的更改，可用于數據恢復
開啟方式：

log-bin=mysql-bin #或者 
log_bin=mysql-bin

③、中繼日志
一般情況下，它在 mysql 主從同步（復制）、讀寫分離集群的從節點上才開啟。
主節點一般不需要這個日志。
④、慢查詢日志
慢查詢日志，用來記錄所有執行時間超過long_query_time秒的語句，可以找到哪些查詢語句執行時間長，以便于優化
開啟方式：

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log #（指定文件路徑和名稱）
long_query_time=5 #（設置執行超過5秒的語句會被記錄，缺省時默認為10秒）

數據庫中查詢日志狀態
①、查看二進制日志開啟狀態

show variables like '%log_bin%';

②、查看慢查詢日志功能是否開啟

show variables like '%slow%';

③、查看慢查詢時間設置

show variables like 'long_query_time';

查看通用查詢日志是否開啟

#重新mysql服務
systemctl restart mysqld.service
mysql -uroot -pabc123

show variables like 'general%';