嗟乎！

一、概述

mysql數據庫該怎么備份呢？

數據庫備份有幾個概念：全量備份、增量備份、差異備份。當然啦，數據庫備份又有冷備份和熱備份，即物理備份和邏輯備份之分。冷備份就是將mysql停了，然后直接拷貝它的數據文件，簡單粗暴。問題是，數據庫怎可輕易停止或重啟？所以我們通常說的備份都是指熱備份，即邏輯備份。邏輯備份包括全量備份、增量備份、差異備份。

全量備份就是整個庫備份。增量備份是將上一次全量備份或增量備份后的變化備份。差異備份是將上次全量備份后的變化備份。所以增量備份和差異備份有重疊。由于mysql中，并不直接支持所謂增量備份或差異備份，所謂的加過濾條件簡直就是瞎掰，根本不屬于自動備份的概念，毫無意義。mysql只能通過拷貝二進制日志文件（BinLog）來實現增量備份的效果，聊勝于無。BinLog里面，記錄了對mysql的增刪改，而沒有記錄查，所以重新執行這個BinLog就可以達到增量備份的效果。有關BinLog，可以參考拙作（mysql的日志文件）

二、備份思路

將備份命令寫到批處理文件，然后定期運行它們。windows的可以用操作系統的任務計劃來定期執行批處理；linux可以設置定時器crontab。

另外，備份文件一般比較大，為長久計，備份之后，應將備份文件壓縮。

三、windows

1、全量備份

@echo off
rem 設置 MySQL 用戶名和密碼
set MYSQL_USER=數據庫賬號
set MYSQL_PASSWORD=密碼
set DATABASE=待備份的數據庫名稱
rem mysql數據文件路徑。一般在mysql安裝路徑,可在mysql.ini里設置。
set MYSQL_BIN=e:\mysql\data\
rem 設置備份文件路徑
set BACKUP_PATH=C:\temp\backuprem 獲取當前時間戳作為備份文件名
for /f "delims=" %%a in ('powershell -command "Get-Date -Format 'yyyyMMdd_HHmmss'"') do set TIMESTAMP=%%aset FULL_BACKUP_FILE=%BACKUP_PATH%\full_backup_%TIMESTAMP%.sql
set COMPRESSED_FULL_BACKUP_FILE=%FULL_BACKUP_FILE%.ziprem 檢查是否已存在該時刻的備份文件，如果存在則不備份
if not exist %COMPRESSED_FULL_BACKUP_FILE% (rem 執行全量備份mysqldump -u%MYSQL_USER% -p%MYSQL_PASSWORD% %DATABASE% --single-transaction --routines --triggers > %FULL_BACKUP_FILE%echo Full Backup completed: %FULL_BACKUP_FILE%rem 壓縮備份文件。這里使用360zip360zip -ar %FULL_BACKUP_FILE% %COMPRESSED_FULL_BACKUP_FILE% rem 刪除備份文件del %FULL_BACKUP_FILE%
) else (echo Full Backup file already exists: %COMPRESSED_FULL_BACKUP_FILE%
)

2、增量備份

rem 設置 MySQL 用戶名和密碼
set MYSQL_USER=數據庫賬號
set MYSQL_PASSWORD=密碼
set DATABASE=待備份的數據庫名稱
rem mysql數據文件路徑。一般在mysql安裝路徑,可在mysql.ini里設置。
set MYSQL_BIN=e:\mysql\data\
rem 設置備份文件路徑
set BACKUP_PATH=C:\temp\backuprem 獲取當前時間戳作為備份文件名
for /f "delims=" %%a in ('powershell -command "Get-Date -Format 'yyyyMMdd_HHmmss'"') do set TIMESTAMP=%%arem 生成的備份文件名稱
set BINLOG_FILE=%BACKUP_PATH%\mysql-bin.%TIMESTAMP%
rem 壓縮后的備份文件名稱
set COMPRESSED_BINLOG_FILE=%BINLOG_FILE%.zip
rem 增量備份對應的position
set BINLOG_POSITION_FILE=%BACKUP_PATH%\binlog_position.txtrem 檢查是否已存在該時刻的二進制日志文件，如果存在則不備份
if not exist %COMPRESSED_BINLOG_FILE% (rem 獲取當前二進制日志文件名和位置for /f "tokens=1,2" %%i in ('mysql -u%MYSQL_USER% -p%MYSQL_PASSWORD% -e "SHOW MASTER STATUS" ^| find " " /v') do (set CURRENT_BINLOG_FILE=%%iset CURRENT_BINLOG_POSITION=%%j)rem 拷貝二進制日志文件到備份目錄copy /Y %MYSQL_BIN%%CURRENT_BINLOG_FILE% %BINLOG_FILE%rem 壓縮360zip -ar %BINLOG_FILE% %COMPRESSED_BINLOG_FILE%rem 刪除備份文件del %BINLOG_FILE%rem 將二進制日志信息保存到文件，增量備份恢復時使用echo %COMPRESSED_BINLOG_FILE% %BINLOG_POSITION% > %BINLOG_POSITION_FILE%       
) else (echo Compressed Binary log file already exists: %COMPRESSED_BINLOG_FILE%
)

四、linux

1、全量備份

#!/bin/bash# 設置 MySQL 用戶名和密碼
MYSQL_USER=數據庫賬號
MYSQL_PASSWORD=密碼
DATABASE=待備份的數據庫名稱
MYSQL_PORT=mysql端口
MYSQL_HOST=mysqlIP地址# 設置備份文件路徑
BACKUP_PATH="/home/admin/db-backup/files"# 獲取當前時間戳作為備份文件名
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")# 備份文件名
FULL_BACKUP_FILE="$BACKUP_PATH/full_backup_$TIMESTAMP.sql"# 檢查是否已存在備份文件，如果存在則不再創建
if [ ! -f "$FULL_BACKUP_FILE" ]; then# 執行全量備份mysqldump -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$DATABASE" --single-transaction --routines --triggers > "$FULL_BACKUP_FILE"# 壓縮全量備份文件gzip "$FULL_BACKUP_FILE"echo "Full Backup completed: $FULL_BACKUP_FILE"
elseecho "Full Backup file already exists: $FULL_BACKUP_FILE"
fi

2、增量備份

獲取當前二進制日志文件名和位置的時候，要使用mysql命令。由于該命令在bash文件中執行，可能系統會找不到mysql，提示未知命令，所以要指定mysql路徑。如果不清楚mysql安裝在什么地方，可以敲入：

which mysql

這樣可以得到mysql所在路徑，如：/usr/local/mysql/mysql-8.0/bin/mysql，
然后據此修改bash文件。

#!/bin/bash# 設置 MySQL 用戶名和密碼
MYSQL_USER=數據庫賬號
MYSQL_PASSWORD=密碼
DATABASE=待備份的數據庫名稱
MYSQL_PORT=mysql端口
MYSQL_HOST=mysqlIP地址# MySQL 數據文件路徑
MYSQL_BIN="/home/admin/mysql/data/"
# 設置備份文件路徑
BACKUP_PATH="/home/admin/db-backup/files"
# 獲取當前時間戳作為備份文件名
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")BINLOG_FILE="$BACKUP_PATH/mysql-bin.$TIMESTAMP"
COMPRESSED_BINLOG_FILE="$BINLOG_FILE.gz"
BINLOG_POSITION_FILE="$BACKUP_PATH/binlog_position.txt"# 檢查是否已存在二進制日志文件，如果存在則不再創建
if [ ! -f "$COMPRESSED_BINLOG_FILE" ]; then# 獲取當前二進制日志文件名和位置read -r CURRENT_BINLOG_FILE CURRENT_BINLOG_POSITION <<<$(/usr/local/mysql/mysql-8.0/bin/mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW MASTER STATUS" | awk 'NR==2{print $1, $2}')# 拷貝并壓縮二進制日志文件到備份目錄cp "$MYSQL_BIN$CURRENT_BINLOG_FILE" "$BINLOG_FILE"gzip "$BINLOG_FILE"# 將二進制日志信息保存到文件，以備增量備份時使用echo "$COMPRESSED_BINLOG_FILE $BINLOG_POSITION" > "$BINLOG_POSITION_FILE"
elseecho "Compressed Binary log file already exists: $COMPRESSED_BINLOG_FILE"
fi

3、定時執行

1）首先將shell文件賦權為可執行文件。這一步好像不是必要的。

chmod 777 backup.sh

2）切換為超級管理員

su

3）設置定時器
輸入命令

crontab -e

在打開的界面中輸入

0 3 * * 6 sh /home/admin/db-backup/backup.sh

意思是每周六凌晨3點執行。

五、小結

mysql并不天然支持增量備份，這是我沒想到的。之前sql server不論是全量、差異備份，都有直接的命令，可以交替執行。mysql吹得天上有，地上無，感覺有些基本的配套功能都沒有。另外，mysql應該是國內最流行的數據庫了，按說相關教程、方案都非常完備了，稍微搜一下，資料就會一大把，但事實上好像并非如此。尤其這個差異備份、增量備份，我還真沒看到多少。這里面，原因可能是多方面的，一是mysql多用于互聯網，互聯網的特點就是迭代快，趕工厲害，很少有時間停下來總結一下。二是國內互聯網日益封閉，質量越來越差，搜索引擎都沒什么好搜的，也搜不到什么有價值的東西。如果不是chatGPT的指導，還真不容易寫出上面的批處理。

相關文章
mysql備份及還原
linux下設置定期執行需要root權限的sh文件