雖然生產環境推薦使用 `mysqldump`、Percona XtraBackup、或雲廠商快照，但某些場景 (共享主機無 Shell、定時任務需純 PHP、跨平台部署) 需在 PHP 代碼中實現備份。本文提供兩種方案：純 SQL 拼接版 (兼容性最強) 與 mysqli 預處理版 (安全、效能更好)。
<?php
/**
* 完整備份資料庫到 .sql 文件
*
* @param array $config ['host','user','pass','db','charset'=>'utf8mb4']
* @param string $backupDir 備份目錄
* @return array ['success'=>bool, 'file'=>string, 'msg'=>string]
*/
function backup_mysql_sql(array $config, string $backupDir): array {
$mysqli = new mysqli($config['host'], $config['user'], $config['pass'], $config['db']);
if ($mysqli->connect_errno) {
return ['success' => false, 'msg' => "連接失敗: {$mysqli->connect_error}"];
}
$mysqli->set_charset($config['charset'] ?? 'utf8mb4');
if (!is_dir($backupDir)) mkdir($backupDir, 0755, true);
$filename = $backupDir . '/' . $config['db'] . '_' . date('Ymd_His') . '.sql';
$fp = fopen($filename, 'wb');
if (!$fp) {
return ['success' => false, 'msg' => "無法創建備份文件: $filename"];
}
// 1. 禁用外鍵檢查、設置時區、字符集
fwrite($fp, "SET FOREIGN_KEY_CHECKS=0;\n");
fwrite($fp, "SET TIME_ZONE='+00:00';\n");
fwrite($fp, "SET NAMES {$config['charset']};\n\n");
// 2. 獲取所有表
$tables = [];
$res = $mysqli->query("SHOW TABLES");
while ($row = $res->fetch_row()) $tables[] = $row[0];
$res->free();
foreach ($tables as $table) {
// 2.1 表結構
$createRes = $mysqli->query("SHOW CREATE TABLE `{$table}`");
$createRow = $createRes->fetch_row();
fwrite($fp, "DROP TABLE IF EXISTS `{$table}`;\n");
fwrite($fp, $createRow[1] . ";\n\n");
$createRes->free();
// 2.2 表數據
$dataRes = $mysqli->query("SELECT * FROM `{$table}`");
$fields = [];
$finfo = $dataRes->fetch_fields();
foreach ($finfo as $f) $fields[] = "`{$f->name}`";
while ($row = $dataRes->fetch_assoc()) {
$values = [];
foreach ($row as $val) {
if ($val === null) {
$values[] = 'NULL';
} elseif (is_bool($val)) {
$values[] = $val ? 'TRUE' : 'FALSE';
} elseif (is_numeric($val)) {
$values[] = $val;
} else {
$values[] = "'" . $mysqli->real_escape_string($val) . "'";
}
}
fwrite($fp, "INSERT INTO `{$table}` (" . implode(',', $fields) . ") VALUES (" . implode(',', $values) . ");\n");
}
fwrite($fp, "\n");
$dataRes->free();
}
fwrite($fp, "SET FOREIGN_KEY_CHECKS=1;\n");
fclose($fp);
$mysqli->close();
return ['success' => true, 'file' => $filename, 'msg' => '備份完成'];
}
// 使用
$config = ['host'=>'localhost','user'=>'root','pass'=>'','db'=>'myapp','charset'=>'utf8mb4'];
$result = backup_mysql_sql($config, __DIR__ . '/backups');
var_dump($result);<?php
function backup_mysql_prepared(array $config, string $backupDir, int $batchSize = 1000): array {
$mysqli = new mysqli($config['host'], $config['user'], $config['pass'], $config['db']);
if ($mysqli->connect_errno) return ['success'=>false, 'msg'=>$mysqli->connect_error];
$mysqli->set_charset($config['charset'] ?? 'utf8mb4');
$filename = $backupDir . '/' . $config['db'] . '_' . date('Ymd_His') . '.sql';
$fp = fopen($filename, 'wb');
fwrite($fp, "SET FOREIGN_KEY_CHECKS=0;\nSET NAMES {$config['charset']};\n\n");
$tables = $mysqli->query("SHOW TABLES")->fetch_all(MYSQLI_NUM);
foreach (array_column($tables, 0) as $table) {
// 表結構
$create = $mysqli->query("SHOW CREATE TABLE `{$table}`")->fetch_row();
fwrite($fp, "DROP TABLE IF EXISTS `{$table}`;\n{$create[1]};\n\n");
// 預處理 SELECT
$stmt = $mysqli->prepare("SELECT * FROM `{$table}`");
$stmt->execute();
$res = $stmt->get_result();
$fields = array_column($res->fetch_fields(), 'name');
$fieldList = '`' . implode('`,`', $fields) . '`';
$placeholders = implode(',', array_fill(0, count($fields), '?'));
$insertSql = "INSERT INTO `{$table}` ($fieldList) VALUES ($placeholders)";
$insertStmt = $mysqli->prepare($insertSql);
$bindParams = [];
$bindTypes = '';
foreach ($fields as $i => $f) {
$bindParams[$i] = null; // 佔位
$bindTypes .= 's'; // 統一按字符串綁定，NULL/數字會自動轉換
}
$count = 0;
while ($row = $res->fetch_assoc()) {
foreach ($fields as $i => $f) {
$val = $row[$f];
$bindParams[$i] = $val === null ? null : (string)$val;
}
$insertStmt->bind_param($bindTypes, ...$bindParams);
$insertStmt->execute();
$count++;
if ($count % $batchSize === 0) {
// 可選：每批次寫入文件一條 INSERT (這裡為演示，實際需拼接多值 INSERT)
}
}
fwrite($fp, "\n");
$stmt->close();
$insertStmt->close();
$res->free();
}
fwrite($fp, "SET FOREIGN_KEY_CHECKS=1;\n");
fclose($fp);
$mysqli->close();
return ['success'=>true, 'file'=>$filename];
}# 每天凌晨 2:30 備份
30 2 * * * /usr/local/php/bin/php /var/www/scripts/backup_db.php >> /var/log/backup_db.log 2>&1<?php
// restore_test.php
$mysqli = new mysqli('localhost', 'root', '', 'test_restore');
$mysqli->query("SOURCE /path/to/backup.sql"); // 僅限 mysql client，PHP 中需逐條執行
// 建議用 shell: mysql -u root -p test_restore < backup.sql
// PHP 驗證：隨機抽樣核心表行數、關鍵字段 checksum
?>
