快捷導(dǎo)航

MySQL數(shù)據(jù)庫實現(xiàn)本地和異地備份的全面指南

更新時間：2025年09月02日 10:06:16 作者：XMYX-0

本文將分享一份改進版的數(shù)據(jù)庫備份腳本,并結(jié)合實際案例,闡述如何定期備份MySQL數(shù)據(jù)庫,并實現(xiàn)本地和異地備份,有需要的小伙伴可以參考一下

在Linux環(huán)境下，mysqldump工具是進行MySQL數(shù)據(jù)庫備份的常見方式。本文將分享一份改進版的數(shù)據(jù)庫備份腳本，并結(jié)合實際案例，闡述如何定期備份MySQL數(shù)據(jù)庫，并實現(xiàn)本地和異地備份。通過本文，您將掌握一個高效、安全的備份方案，確保數(shù)據(jù)的安全性和可恢復(fù)性。

一定要全部看完，再操作，因為數(shù)據(jù)庫密碼我沒有再腳本中體現(xiàn)，當(dāng)然也可以體現(xiàn)，看具體需求

備份腳本概述

我們創(chuàng)建一個改進版的備份腳本，命名為mysql_backup.sh。該腳本的目標(biāo)是：

自動備份MySQL數(shù)據(jù)庫，并排除系統(tǒng)數(shù)據(jù)庫（如mysql、information_schema等）。
將備份數(shù)據(jù)打包并進行本地壓縮。
同步備份到異地備份服務(wù)器（通過rsync）。
自動清理30天前的備份文件，節(jié)省存儲空間。
記錄詳細(xì)的備份日志，便于排查問題。

編寫備份腳本

腳本內(nèi)容

#!/bin/bash
set -eo pipefail
trap 'echo "錯誤發(fā)生在行 $LINENO"; exit 1' ERR

# 配置區(qū)
USER="root"                           # MySQL用戶名
BACKUP_DIR="/data/backup"             # 備份存儲目錄
EXCLUDE_DBS="mysql|information_schema|performance_schema|sys"  # 排除的系統(tǒng)數(shù)據(jù)庫
RSYNC_TARGET="rsync_backup@192.168.1.2::mysqlbackup/192.168.1.3/"     # 異地備份目標(biāo)
LOGFILE="$BACKUP_DIR/data_backup.log" # 日志文件路徑
DATE=$(date +%Y%m%d_%H%M)             # 當(dāng)前日期時間

# 創(chuàng)建備份目錄
mkdir -p "$BACKUP_DIR" || exit 1
cd "$BACKUP_DIR" || exit 1

# 記錄開始時間
echo "==== 備份開始 $(date +"%Y-%m-%d %H:%M:%S") ====" >> "$LOGFILE"

# 獲取數(shù)據(jù)庫列表并備份
DATABASES=$(mysql -e "SHOW DATABASES;" | grep -Ev "$EXCLUDE_DBS" | tr '\n' ' ')
echo "備份數(shù)據(jù)庫: $DATABASES" >> "$LOGFILE"
mysqldump --routines --single-transaction --master-data=2 --databases $DATABASES > "wzqmysql_bak${DATE}.sql"

# 壓縮備份
if [ -s "wzqmysql_bak${DATE}.sql" ]; then
  tar -czf "wzqmysql_bak${DATE}.tar.gz" "wzqmysql_bak${DATE}.sql"
  rm -f "wzqmysql_bak${DATE}.sql"
  echo "壓縮完成，文件大小: $(du -sh wzqmysql_bak${DATE}.tar.gz)" >> "$LOGFILE"
else
  echo "備份文件為空，終止流程！" >> "$LOGFILE"
  exit 1
fi

# 同步到異地
rsync -avz --password-file=/etc/rsync.password "wzqmysql_bak${DATE}.tar.gz" "$RSYNC_TARGET"

# 清理30天前備份
find "$BACKUP_DIR" -type f -mtime +30 -name "*.gz" -delete

echo "==== 備份完成 $(date +"%Y-%m-%d %H:%M:%S") ====" >> "$LOGFILE"

主要參數(shù)解釋

--single-transaction：對于InnoDB表，確保備份的一致性。
--master-data=2：將主服務(wù)器的二進制日志位置寫入備份文件，適用于主從復(fù)制環(huán)境。
--routines：備份存儲過程和函數(shù)。
rsync：用于將備份文件同步到異地服務(wù)器。
find -mtime +30：清理30天前的備份文件。

設(shè)置腳本權(quán)限

在Linux系統(tǒng)中，為腳本添加執(zhí)行權(quán)限：

chmod +x /data/mysql_backup.sh

定期執(zhí)行備份

為了實現(xiàn)自動備份，可以將該腳本添加到定時任務(wù)中（例如每天凌晨1點執(zhí)行）。

設(shè)置crontab任務(wù)

編輯crontab配置文件：

crontab -e

添加如下行，設(shè)定每天凌晨1點執(zhí)行備份腳本：

0 1 * * * /data/mysql_backup.sh

保存退出后，crontab會自動更新。

安全性建議

避免明文密碼

將MySQL密碼存儲在~/.my.cnf配置文件中，并設(shè)置嚴(yán)格權(quán)限：

[client]
user = root
password = 123456

設(shè)置配置文件權(quán)限：

chmod 600 ~/.my.cnf

rsync密碼文件安全

確保/etc/rsync.password權(quán)限為600：

chmod 600 /etc/rsync.password

進階功能和優(yōu)化

增量備份

mysqldump不支持增量備份，建議使用二進制日志或Percona XtraBackup。以下是簡要步驟：

查看二進制日志位置：

head -n 30 wzqmysql_bak${DATE}.sql | grep "CHANGE MASTER TO"

使用mysqlbinlog導(dǎo)出增量日志：

mysqlbinlog --start-datetime="2025-02-21 00:00:00" /var/lib/mysql/binlog.000001 > incremental.sql

備份驗證

定期驗證備份文件是否可以成功恢復(fù)：

# 解壓備份
tar -wzqf wzqmysql_bak20250221_0100.tar.gz
# 恢復(fù)數(shù)據(jù)庫
mysql -e "CREATE DATABASE test_restore;"
mysql test_restore < wzqmysql_bak20250221_0100.sql

日志輪轉(zhuǎn)

使用logrotate管理日志文件：

/data/backup/data_backup.log {
  daily
  rotate 30
  compress
  missingok
}

詳細(xì)解釋

daily: 這表示日志文件將每天進行輪換。如果日志文件每天更新或者你希望每次備份后都生成一個新的日志文件，可以使用這個選項。
rotate 30: 這表示系統(tǒng)將保留最近30個輪換過的日志文件，其余的將被刪除。比如，你的日志文件每天生成一個新備份，當(dāng)系統(tǒng)積累到30個日志文件時，最舊的日志文件將會被刪除，確保只保留最新的30個日志備份。
compress: 這個選項告訴logrotate在輪換日志時，對舊的日志文件進行壓縮（通常是.gz格式），從而節(jié)省磁盤空間。壓縮文件的名字一般會附加.gz擴展名，例如data_backup.log.1.gz、data_backup.log.2.gz等。
missingok: 如果指定的日志文件不存在，不會拋出錯誤，而是跳過處理。例如，如果/data/backup/data_backup.log文件在某一天沒有生成，logrotate會忽略這個情況，不會報錯或中止執(zhí)行。