定期備份資料庫是運維基礎。本文提供一個功能完整、支援結構+數據導出、可配置壓縮與遠程傳輸的 PHP 備份腳本,並解析核心邏輯與安全加固要點。
<?php
/**
* MySQL 資料庫備份工具
* 依賴:mysqli 擴展
*/
class DbBackup {
private $mysqli;
private $dbName;
private $outputDir;
private $tables = '*'; // '*' 為全部,或陣列 ['table1', 'table2']
private $compress = false;
private $ignoreTables = [];
private $chunkSize = 1000; // 分批讀取行數
public function __construct(array $config) {
$this->mysqli = new mysqli(
$config['host'],
$config['user'],
$config['pass'],
$config['name'],
$config['port'] ?? 3306,
$config['socket'] ?? null
);
if ($this->mysqli->connect_errno) {
throw new Exception("連接失敗: " . $this->mysqli->connect_error);
}
$this->mysqli->set_charset('utf8mb4');
$this->dbName = $config['name'];
$this->outputDir = $config['output_dir'] ?? sys_get_temp_dir();
$this->tables = $config['tables'] ?? '*';
$this->compress = $config['compress'] ?? false;
$this->ignoreTables = $config['ignore_tables'] ?? [];
$this->chunkSize = $config['chunk_size'] ?? 1000;
}
public function backup(): string {
$timestamp = date('Ymd_His');
$filename = "{$this->dbName}_{$timestamp}.sql";
$filepath = $this->outputDir . DIRECTORY_SEPARATOR . $filename;
if ($this->compress) $filepath .= '.gz';
$handle = $this->compress ? gzopen($filepath, 'wb9') : fopen($filepath, 'wb');
if (!$handle) throw new Exception("無法創建備份文件: $filepath");
// 寫入文件頭
$this->write($handle, "-- MySQL Backup for `{$this->dbName}`\n");
$this->write($handle, "-- Generated: " . date('Y-m-d H:i:s') . "\n\n");
$this->write($handle, "SET FOREIGN_KEY_CHECKS=0;\n");
$this->write($handle, "SET NAMES utf8mb4;\n\n");
// 獲取表列表
$tables = $this->getTables();
foreach ($tables as $table) {
$this->backupTable($handle, $table);
}
$this->write($handle, "\nSET FOREIGN_KEY_CHECKS=1;\n");
$this->close($handle);
return $filepath;
}
private function getTables(): array {
if ($this->tables === '*') {
$res = $this->mysqli->query("SHOW TABLES");
$tables = [];
while ($row = $res->fetch_row()) {
if (!in_array($row[0], $this->ignoreTables)) {
$tables[] = $row[0];
}
}
return $tables;
}
return array_diff((array)$this->tables, $this->ignoreTables);
}
private function backupTable($handle, string $table) {
// 1. 表結構
$createRes = $this->mysqli->query("SHOW CREATE TABLE `{$table}`");
$createRow = $createRes->fetch_assoc();
$this->write($handle, "DROP TABLE IF EXISTS `{$table}`;\n");
$this->write($handle, $createRow['Create Table'] . ";\n\n");
// 2. 數據分批導出
$countRes = $this->mysqli->query("SELECT COUNT(*) AS c FROM `{$table}`");
$total = $countRes->fetch_row()[0];
if ($total === 0) return;
for ($offset = 0; $offset < $total; $offset += $this->chunkSize) {
$res = $this->mysqli->query(
"SELECT * FROM `{$table}` LIMIT {$this->chunkSize} OFFSET {$offset}"
);
if (!$res) continue;
$fields = [];
while ($field = $res->fetch_field()) {
$fields[] = "`{$field->name}`";
}
$fieldList = implode(', ', $fields);
while ($row = $res->fetch_assoc()) {
$values = [];
foreach ($row as $val) {
if ($val === null) {
$values[] = 'NULL';
} else {
$values[] = "'" . $this->mysqli->real_escape_string($val) . "'";
}
}
$this->write($handle, "INSERT INTO `{$table}` ({$fieldList}) VALUES (" . implode(', ', $values) . ");\n");
}
$res->free();
}
$this->write($handle, "\n");
}
private function write($handle, string $data) {
if ($this->compress) {
gzwrite($handle, $data);
} else {
fwrite($handle, $data);
}
}
private function close($handle) {
$this->compress ? gzclose($handle) : fclose($handle);
}
}
?>
<?php
require_once 'DbBackup.php';
$config = [
'host' => '127.0.0.1',
'user' => 'backup_user',
'pass' => 'strong_password',
'name' => 'my_database',
'port' => 3306,
'output_dir' => '/data/backups/mysql',
'tables' => '*', // 備份所有表
'ignore_tables' => ['logs', 'sessions', 'cache'], // 忽略大表/臨時表
'compress' => true, // 啟用 gzip 壓縮
'chunk_size' => 500, // 大表分批讀取
];
try {
$backup = new DbBackup($config);
$file = $backup->backup();
echo "備份成功: $file\n";
echo "文件大小: " . number_format(filesize($file) / 1024 / 1024, 2) . " MB\n";
// 可選:上傳到雲存儲、刪除舊備份
// uploadToS3($file);
// cleanOldBackups('/data/backups/mysql', 7); // 保留 7 天
} catch (Exception $e) {
error_log("備份失敗: " . $e->getMessage());
exit(1);
}
?>
LIMIT ... OFFSET 避免大表一次性載入導致記憶體溢出(PHP memory_limit)。$mysqli->real_escape_string() 防止特殊字符破壞 SQL 語法。FOREIGN_KEY_CHECKS,結束後恢復,避免導入時順序問題。SET NAMES utf8mb4,支援 Emoji 及完整 Unicode。gzopen/gzwrite/gzclose 串流壓縮,無需生成臨時文件再壓縮,節省磁盤 I/O。LOCK_EX 防止多實例並發寫入同一文件損壞數據。# 每天凌晨 2:30 備份
30 2 * * * /usr/bin/php /opt/scripts/db_backup.php >> /var/log/db_backup.log 2>&1
# 保留最近 7 天每日備份、每週一備份保留 4 週、每月 1 號保留 6 個月
find /data/backups/mysql -name '*.sql.gz' -mtime +7 -delete
# 更精細策略建議使用 logrotate 或專用備份工具
**務必定期演練恢復**:
# 解壓並導入測試庫
gunzip -c backup_file.sql.gz | mysql -u root -p test_restore_db
# 檢查表數量、行數、關鍵數據完整性
600,僅擁有者可讀寫。SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER 權限,嚴禁 DROP、DELETE。| 維度 | PHP 腳本 | mysqldump |
|---|---|---|
| 部署依賴 | 僅需 PHP + mysqli | 需安裝 MySQL 客戶端工具 |
| 跨平台 | 優(Windows/Linux 通用) | 需對應平台二進制 |
| 效能 | 中等(PHP 解析開銷) | 極高(C 實現,流式) |
| 功能豐富度 | 可自定義邏輯 | 參數極其豐富(--single-transaction, --master-data 等) |
| 適用場景 | 共享主機、無 Shell 權限、需嵌入業務邏輯 | 生產環境標準備份首選 |
建議:有 Shell 權限的生產環境優先使用 mysqldump --single-transaction --quick --routines --triggers --events;無 Shell 或需嵌入 PHP 系統時使用本腳本。