PHP 資料庫備份類完整實作：分卷壓縮、郵件發送與多表支援

前言

資料庫備份是系統維運核心功能。本文整理兩套 PHP 備份類，支援 指定表備份、分卷壓縮、ZIP 打包、郵件附件發送，可直接整合至後台管理系統。

方案一：功能完整的 Backup 類 (支援分卷、郵件、Web 介面)

使用方式

<?php
error_reporting(0);
$options = [
    'email' => ['admin1@example.com', 'admin2@example.com'],
    'folder' => './backup/',
    'mysql' => ['localhost', 'db_user', 'db_pass', 'db_name']
];

$b = new Backup($options);

if (isset($_POST['backup'])) {
    $b->backupDB();
}
$b->outputForm();
?>

核心類別程式碼

<?php
class Backup {
    var $config, $dump, $struktur = [], $datei;

    public function Backup($options) {
        foreach ($options as $name => $value) $this->config[$name] = $value;
        mysql_connect($this->config['mysql'][0], $this->config['mysql'][1], $this->config['mysql'][2]) or die(mysql_error());
        mysql_select_db($this->config['mysql'][3]) or die(mysql_error());
    }

    public function backupDB() {
        if (empty($_POST['table'])) die("請選擇資料表。");
        
        $tables = []; $insert = [];
        foreach ($_POST['table'] as $table) {
            mysql_query("LOCK TABLE $table WRITE");
            $res = mysql_query('SHOW CREATE TABLE ' . $table);
            $createtable = mysql_result($res, 0, 1);
            $tables[] = "\n\n$createtable\n\n";
            
            $sql = 'SELECT * FROM ' . $table;
            $query = mysql_query($sql) or die(mysql_error());
            $fields = mysql_num_fields($query);
            
            $stmt = "-- \n-- Data Table `$table`\n-- \n";
            while ($row = mysql_fetch_object($query)) {
                $stmt .= "INSERT INTO `$table` (";
                for ($i = 0; $i < $fields; $i++) {
                    $stmt .= ($i == $fields-1) ? mysql_field_name($query,$i) : mysql_field_name($query,$i) . ", ";
                }
                $stmt .= ") VALUES (";
                for ($i = 0; $i < $fields; $i++) {
                    $name = mysql_field_name($query,$i);
                    $val = empty($row->$name) ? 'NULL' : '"' . $row->$name . '"';
                    $stmt .= ($i == $fields-1) ? $val : $val . ", ";
                }
                $stmt .= ");\n";
            }
            if (!in_array($stmt, $insert)) $insert[] = $stmt;
        }
        
        $this->struktur = array_combine($tables, $insert);
        $this->createDUMP($this->struktur);
        $this->createZIP();
        
        if (!empty($this->config['email'])) $this->sendEmail();
        echo '
備份完成
下載備份';
    }

    protected function createDUMP($dump) {
        $date = date("F j, Y, g:i a");
        $header = "-- SQL Dump\n--\n-- Host: {$_SERVER['HTTP_HOST']}\n-- Time: $date\n-- Database: `{$this->config['mysql'][3]}`\n--\n";
        $sql = "";
        foreach ($dump as $name => $value) $sql .= $name . $value;
        $this->dump = $header . $sql;
    }

    protected function createZIP() {
        chmod($this->config['folder'], 0777);
        $zip = new ZipArchive();
        $this->datei = $this->config['folder'] . $this->config['mysql'][3] . "_" . date("j_F_Y_g_i_a") . ".zip";
        if ($zip->open($this->datei, ZIPARCHIVE::CREATE) !== TRUE) exit("無法建立壓縮檔");
        $zip->addFromString("dump.sql", $this->dump);
        $zip->close();
        if (!file_exists($this->datei)) die("壓縮檔建立失敗");
    }

    protected function sendEmail() {
        $msep = strtoupper(md5(uniqid(time())));
        foreach ($this->config['email'] as $email) {
            $header = "From: {$this->config['email'][0]}\r\n"
                . "MIME-Version: 1.0\r\n"
                . "Content-Type: multipart/mixed; boundary=$msep\r\n\r\n"
                . "--$msep\r\n"
                . "Content-Type: text/plain\r\n"
                . "Content-Transfer-Encoding: 8bit\r\n\r\n"
                . "資料庫備份附件\r\n";
            
            $content = chunk_split(base64_encode(file_get_contents($this->datei)));
            $header .= "--$msep\r\n"
                . "Content-Type: application/zip; name='Backup.zip'\r\n"
                . "Content-Transfer-Encoding: base64\r\n"
                . "Content-Disposition: attachment; filename='Backup.zip'\r\n\r\n"
                . "$content\r\n--$msep--";
            
            mail($email, "資料庫備份", '', $header) or die("郵件發送失敗");
        }
    }

    public function outputForm() {
        $result = mysql_list_tables($this->config['mysql'][3]);
        $html = '
選擇備份資料表
';
        while ($row = mysql_fetch_row($result)) $html .= '' . $row[0] . '';
        $html .= '

';
        echo $html;
    }
}
?>

方案二：通用資料庫備份類 (簡潔版)

<?php
class BackUpDatabase {
    var $HOST, $USERNAME, $PASSWORD, $DATABASE, $Connection, $table_name, $tabledump;

    function BackUpDatabase($host, $username, $password, $database) {
        $this->HOST = $host; $this->USERNAME = $username; 
        $this->PASSWORD = $password; $this->DATABASE = $database;
        $this->Connection = $this->connect($host, $username, $password, $database);
    }

    function connect($host, $username, $password, $database) {
        $conn = mysql_connect($host, $username, $password) or die(mysql_error());
        mysql_select_db($database, $conn) or die(mysql_error());
        return $conn;
    }

    function get_table_name($database) {
        $result = mysql_list_tables($database);
        $tables = [];
        while ($i < mysql_num_rows($result)) {
            $tables[] = mysql_tablename($result, $i++);
        }
        return implode(",", $tables);
    }

    function get_table_fields($table) {
        $res = mysql_query("SHOW CREATE TABLE $table");
        $row = mysql_fetch_row($res);
        return "DROP TABLE IF EXISTS $table;\n" . $row[1] . ";\n\n";
    }

    function get_insert($table) {
        $res = mysql_query("SELECT * FROM $table");
        $fields = mysql_num_fields($res);
        $dump = "";
        while ($row = mysql_fetch_row($res)) {
            $dump .= "INSERT INTO $table VALUES(";
            for ($i = 0; $i < $fields; $i++) {
                $dump .= ($i ? "," : "") . "'" . mysql_escape_string($row[$i]) . "'";
            }
            $dump .= ");\n";
        }
        return $dump;
    }

    function get_string($database, $path) {
        $time = date("Y-m-d H:i");
        $file = $path . date("YmdHis") . ".sql";
        $head = "# Identify: " . base64_encode("$time,Antsent_Web_Studio_V1.01") . "\n"
            . "# Antsnet_Web_Studio Backup\n# Time: $time\n# Type: MySQL Backup\n\n";
        
        $tables = explode(",", $this->get_table_name($database));
        $struct = $data = "";
        foreach ($tables as $t) {
            $struct .= $this->get_table_fields($t);
            $data .= $this->get_insert($t);
        }
        return $this->write_file($file, $head . $struct . $data);
    }

    function write_file($file, $content) {
        $fp = @fopen($file, 'w') or die("無法開啟檔案");
        flock($fp, LOCK_EX);
        fwrite($fp, $content);
        fclose($fp);
        return true;
    }
}
?>

現代化改良建議 (重要)

生產環境推薦架構

1. 排程備份：Linux cron + mysqldump --single-transaction --quick --routines --triggers
2. 分卷壓縮：split -b 100M 或 gzip 串流
3. 異地儲存：上傳至 S3 / GCS / NAS / 遠端伺服器
4. 驗證機制：定期還原測試、Checksum 比對
5. 監控告警：備份失敗、檔案過小、磁碟空間不足 → Slack/Email/Line 通知

一鍵備份 Shell 腳本範例

#!/bin/bash
# backup_mysql.sh
DB_USER="backup_user"
DB_PASS="secret"
DB_NAME="production"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql.gz"

mysqldump -u$DB_USER -p$DB_PASS --single-transaction --quick --routines --triggers $DB_NAME | gzip > $FILE

# 刪除 7 天前備份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

# 上傳至 S3 (需安裝 awscli)
aws s3 cp $FILE s3://my-backup-bucket/mysql/ --storage-class GLACIER

PHP 類別適合「後台手動觸發、小型專案、教學用途」；生產環境大型資料庫請採用系統級備份方案。