雖然 phpMyAdmin、Navicat 等 GUI 工具備份方便,但在自動化部署、排程備份、無圖形介面的伺服器環境中,使用 PHP 程式化備份更具彈性。本文提供兩種實作方案供參考。
此方法逐步查詢資料表結構與資料,組合成完整 SQL 檔案,邏輯清晰易懂。
<?php
$host = 'localhost';
$user = 'root';
$password = '';
$dbname = 'dbname';
$conn = mysql_connect($host, $user, $password);
mysql_select_db($dbname, $conn);
mysql_query('SET NAMES utf8', $conn);
$sql = "SET NAMES utf8;\n";
$tables = mysql_query('SHOW TABLES', $conn);
while ($table = mysql_fetch_array($tables)) {
$tableName = $table[0];
// 取得建表語句
$create = mysql_query("SHOW CREATE TABLE `$tableName`", $conn);
$createRow = mysql_fetch_array($create);
$sql .= $createRow['Create Table'] . ";\n\n";
// 取得資料並產生 INSERT 語句
$data = mysql_query("SELECT * FROM `$tableName`", $conn);
while ($row = mysql_fetch_assoc($data)) {
$keys = array_keys($row);
$keys = array_map('addslashes', $keys);
$keys = '`' . implode('`,`', $keys) . '`';
$vals = array_values($row);
$vals = array_map('addslashes', $vals);
$vals = "'" . implode("','", $vals) . "'";
$sql .= "INSERT INTO `$tableName`($keys) VALUES($vals);\n";
}
$sql .= "\n";
}
$filename = $dbname . date('Ymd') . '.sql';
file_put_contents($filename, $sql);
echo "資料備份成功,生成檔案:$filename";
?>
將備份邏輯封裝為 backup_tables() 函式,支援指定資料表、自動命名、錯誤處理,適合整合至專案中。
<?php
function backup_tables($host, $user, $pass, $name, $tables = '*') {
$link = mysql_connect($host, $user, $pass);
mysql_select_db($name, $link);
// 取得目標資料表清單
if ($tables == '*') {
$tables = [];
$result = mysql_query('SHOW TABLES', $link);
while ($row = mysql_fetch_row($result)) {
$tables[] = $row[0];
}
} else {
$tables = is_array($tables) ? $tables : explode(',', $tables);
}
$return = '';
foreach ($tables as $table) {
$result = mysql_query("SELECT * FROM `$table`", $link);
$numFields = mysql_num_fields($result);
$return .= "DROP TABLE `$table`;\n";
$createRow = mysql_fetch_row(mysql_query("SHOW CREATE TABLE `$table`", $link));
$return .= "\n\n" . $createRow[1] . ";\n\n";
for ($i = 0; $i < $numFields; $i++) {
while ($row = mysql_fetch_row($result)) {
$return .= "INSERT INTO `$table` VALUES(";
for ($j = 0; $j < $numFields; $j++) {
$row[$j] = addslashes($row[$j]);
$row[$j] = str_replace("\n", "\\n", $row[$j]);
$return .= isset($row[$j]) ? '"' . $row[$j] . '"' : '""';
if ($j < $numFields - 1) $return .= ',';
}
$return .= ");\n";
}
}
$return .= "\n\n\n";
}
$filename = 'db-backup-' . time() . '-' . md5(implode(',', $tables)) . '.sql';
$handle = fopen($filename, 'w+');
fwrite($handle, $return);
fclose($handle);
}
// 使用範例
backup_tables('localhost', 'root', '', 'dbname');
?>
mysql_* 函式,PHP 7.0+ 已移除。實務上請改用 mysqli 或 PDOmysqldump 指令SET NAMES utf8mb4)與資料表編碼一致// 使用 mysqldump 指令(推薦生產環境)
$cmd = "mysqldump -h$host -u$user -p$password $dbname > backup_$(date +%F).sql";
exec($cmd, $output, $returnVar);
// 或使用 PHP 封裝庫:spatie/db-dumper
// composer require spatie/db-dumper
use Spatie\DbDumper\Databases\MySql;
$dumper = new MySql();
$dumper->setDbName($dbname)
->setUserName($user)
->setPassword($password)
->dumpToFile($filename);