MySQL 資料庫備份完整方案：PHP 類庫與 Linux Crontab 自動化

方案對比

一、PHP 備份類庫 (適合中小項目集成後台)

<?php
/**
 * MySQL 備份類 (PDO 版)
 * 用法: $bak = new DbBackup($pdo); $bak->backup(['users','orders'], '/backups');
 */
class DbBackup {
    private PDO $pdo;
    private string $backupDir;
    
    public function __construct(PDO $pdo, string $backupDir = __DIR__ . '/backups') {
        $this->pdo = $pdo;
        $this->backupDir = rtrim($backupDir, '/');
        if (!is_dir($this->backupDir)) mkdir($this->backupDir, 0755, true);
    }
    
    /** 備份指定表 (['*'] 為全庫) */
    public function backup(array $tables = ['*'], int $batchRows = 1000): string {
        $dbName = $this->pdo->query("SELECT DATABASE()")->fetchColumn();
        $tables = $this->resolveTables($tables);
        
        $filename = "{$dbName}_" . date('Ymd_His') . ".sql";
        $filepath = "{$this->backupDir}/{$filename}";
        $gzpath = $filepath . '.gz';
        
        $fp = gzopen($gzpath, 'wb9'); // 直接寫入 gzip
        
        // 文件頭
        $this->write($fp, "-- Backup: {$dbName} at " . date('c') . "\n");
        $this->write($fp, "SET FOREIGN_KEY_CHECKS=0;\n\n");
        
        foreach ($tables as $table) {
            $this->backupTable($fp, $table, $batchRows);
        }
        
        $this->write($fp, "\nSET FOREIGN_KEY_CHECKS=1;\n");
        gzclose($fp);
        
        return $gzpath;
    }
    
    private function resolveTables(array $tables): array {
        if (in_array('*', $tables)) {
            return $this->pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
        }
        return $tables;
    }
    
    private function backupTable($fp, string $table, int $batchRows) {
        // 1. 表結構
        $create = $this->pdo->query("SHOW CREATE TABLE `{$table}`")->fetchColumn(1);
        $this->write($fp, "DROP TABLE IF EXISTS `{$table}`;\n");
        $this->write($fp, $create . ";\n\n");
        
        // 2. 數據分批導出
        $cols = $this->pdo->query("SHOW COLUMNS FROM `{$table}`")->fetchAll(PDO::FETCH_COLUMN);
        $colList = '`' . implode('`,`', $cols) . '`';
        
        $total = $this->pdo->query("SELECT COUNT(*) FROM `{$table}`")->fetchColumn();
        for ($offset = 0; $offset < $total; $offset += $batchRows) {
            $rows = $this->pdo->query(
                "SELECT {$colList} FROM `{$table}` LIMIT {$batchRows} OFFSET {$offset}"
            )->fetchAll(PDO::FETCH_NUM);
            
            if (empty($rows)) break;
            
            $this->write($fp, "INSERT INTO `{$table}` ({$colList}) VALUES\n");
            $lines = [];
            foreach ($rows as $row) {
                $vals = array_map(fn($v) => $this->escape($v), $row);
                $lines[] = '(' . implode(',', $vals) . ')';
            }
            $this->write($fp, implode(",\n", $lines) . ";\n\n");
        }
    }
    
    private function escape($value): string {
        if ($value === null) return 'NULL';
        if (is_bool($value)) return $value ? '1' : '0';
        if (is_int($value) || is_float($value)) return (string)$value;
        return "'" . str_replace(["\\", "'"], ["\\\\", "\\'"], $value) . "'";
    }
    
    private function write($fp, string $data) { gzwrite($fp, $data); }
}

// 使用示例
$pdo = new PDO('mysql:host=127.0.0.1;dbname=myapp', 'user', 'pass');
$backup = new DbBackup($pdo, '/var/backups/mysql');
$file = $backup->backup(['*']); // 全庫
echo "備份完成: $file";

二、Linux 專業自動化備份腳本 (mysqldump + crontab)

/usr/local/bin/mysql_backup.sh

#!/bin/bash
# MySQL 專業備份腳本 - 支援全量/增量、壓縮、加密、上傳雲存儲
# 用法: mysql_backup.sh [full|incr]

set -euo pipefail

# ===== 配置區 =====
DB_HOST="127.0.0.1"
DB_PORT="3306"
DB_USER="backup_user"
DB_PASS="strong_password"
DB_NAME="myapp"                 # 為空則全實例
BACKUP_ROOT="/backup/mysql"
RETENTION_DAYS=30               # 本地保留天數
COMPRESS="gzip"                 # gzip | zstd | xz
ENCRYPT_KEY=""                  # 可選: openssl aes-256-cbc 密鑰
S3_UPLOAD=false                 # 是否上傳 S3 兼容存儲
# ===================

DATE=$(date '+%Y%m%d_%H%M%S')
TYPE="${1:-full}"
DAY_DIR="${BACKUP_ROOT}/${TYPE}/$(date '+%Y-%m-%d')"
mkdir -p "$DAY_DIR"

BASE_FILE="${DB_NAME:-all}_${TYPE}_${DATE}.sql"
OUT_FILE="${DAY_DIR}/${BASE_FILE}"

# 通用參數
DUMP_OPTS=(
    --host="$DB_HOST" --port="$DB_PORT"
    --user="$DB_USER" --password="$DB_PASS"
    --single-transaction --quick --routines --triggers --events
    --hex-blob --column-statistics=0
)

if [[ "$TYPE" == "incr" ]]; then
    # 增量備份：需啟用 binlog，這裡演示自 昨天 以來的 binlog
    # 實際生產建議用 mysqlbinlog 或 Percona XtraBackup
    DUMP_OPTS+=(--master-data=2 --flush-logs)
fi

if [[ -n "$DB_NAME" ]]; then
    DUMP_OPTS+=(--databases "$DB_NAME")
else
    DUMP_OPTS+=(--all-databases)
fi

echo "[$(date)] 開始 $TYPE 備份 -> $OUT_FILE"

# 執行備份並壓縮
if mysqldump "${DUMP_OPTS[@]}" | $COMPRESS -c > "${OUT_FILE}.gz"; then
    SIZE=$(du -h "${OUT_FILE}.gz" | cut -f1)
    echo "[$(date)] 備份成功: ${OUT_FILE}.gz ($SIZE)"
    
    # 可選加密
    if [[ -n "$ENCRYPT_KEY" ]]; then
        openssl enc -aes-256-cbc -salt -pbkdf2 -pass pass:"$ENCRYPT_KEY" \
            -in "${OUT_FILE}.gz" -out "${OUT_FILE}.gz.enc"
        rm "${OUT_FILE}.gz"
        echo "[$(date)] 已加密"
    fi
    
    # 可選上傳
    if [[ "$S3_UPLOAD" == true ]]; then
        # aws s3 cp "${OUT_FILE}.gz" s3://bucket/mysql-backups/ ...
        echo "[$(date)] 已上傳 S3"
    fi
else
    echo "[$(date)] 備份失敗!" >&2
    exit 1
fi

# 清理舊備份
find "$BACKUP_ROOT" -type f -name '*.gz*' -mtime +$RETENTION_DAYS -delete
echo "[$(date)] 清理完成"

授權與定時

chmod +x /usr/local/bin/mysql_backup.sh

# crontab -e (root)
# 每天 02:30 全量備份
30 2 * * * /usr/local/bin/mysql_backup.sh full >> /var/log/mysql_backup.log 2>&1
# 每小時增量備份 (需 binlog)
0 * * * * /usr/local/bin/mysql_backup.sh incr >> /var/log/mysql_backup_incr.log 2>&1

三、恢複驗證 (關鍵步驟)

# 解壓並導入
gunzip -c backup.sql.gz | mysql -u root -p myapp

# 或加密文件
openssl enc -d -aes-256-cbc -pass pass:KEY -in backup.sql.gz.enc | gunzip | mysql -u root -p myapp

建議每季度演練一次恢複，驗證備份完整性與 RTO/RPO。

四、監控告警

• 腳本返回碼非 0 觸發告警 (Prometheus node_exporter + Alertmanager / Zabbix / 企業微信/鉚釘機器人)
• 監控備份文件大小突變 (可能數據異常或備份不全)
• 定期校驗 CHECKSUM TABLE 或 pt-table-checksum

結合 PHP 類庫滿足業務側「立即備份/下載」需求，以 myshldump + crontab 作為核心自動化備份，構建分層備份體系。

1. 上一篇文章： PHP 導入 Excel/CSV 到 MySQL 完整方案：PhpSpreadsheet 實戰
2. 下一篇文章： PHP MySQL 資料庫連線類別：從基礎到實務應用

PHP 相關知識

Laravel 框架實現手機簡訊驗證碼功能完整程式碼

Laravel 框架實現手機簡訊驗證碼功能完整程式碼 為保護

jQuery + PHP + MySQL 實現自動完成功能完整教學

前言 關於 jQuery、PHP 與 MySQL 結合實現自

PHP + MySQL + AJAX 輕量級聊天室完整實作：從資料庫設計到長輪詢優化

系統架構 後端：PHP + MySQL（儲存訊息）

PHP 微信公眾平台開發（二）：官方示例代碼深度解析與架構重構

官方示例代碼結構 valid(); class wecha