您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

PHP MySQL 數據庫備份類實戰：結構數據分離、分卷壓縮、自動清理全流程

編輯：PHP 相關知識

設計目標

表結構（DDL）與數據（INSERT）分離存儲，便於單獨審閱或遷移結構。
單表數據超過分卷大小（默認 2MB）自動切分多文件，避免單文件過大導致導入超時。
備份完成自動打包 ZIP，並清理臨時 SQL 文件。
支援多表批量備份與單表備份兩種模式。

核心類結構 (DbBackUp)

<?php
class DbBackUp {
  private $conn, $dbName, $host, $tag = '_b';

  public function __construct($host='localhost', $dbUser='root', $dbPwd='', $dbName="seocheck", $charset='utf8') {
    @ob_start(); @set_time_limit(0);
    $this->conn = mysql_connect($host, $dbUser, $dbPwd, true);
    if (!$this->conn) die("數據庫連接失敗！");
    mysql_query("SET NAMES $charset", $this->conn);
    mysql_select_db($dbName, $this->conn) or die("選擇數據庫失敗！");
    $this->host = $host; $this->dbName = $dbName;
  }

  // 獲取所有表名
  public function getTableNames() {
    $tables = [];
    $result = mysql_list_tables($this->dbName, $this->conn);
    while ($row = mysql_fetch_row($result)) $tables[] = $row[0];
    return $tables;
  }

  // 獲取表結構 (SHOW CREATE TABLE)
  public function getFieldsByTable($table) {
    $res = mysql_query("SHOW CREATE TABLE `$table`", $this->conn);
    $row = mysql_fetch_assoc($res);
    $sql = $row['Create Table'];
    // 格式化：移除原建表頭、統一縮進、補分號
    $sql = preg_replace("/CREATE TABLE `$table` \(/", "", $sql);
    $sql = "--\n-- Table structure for table `$table`\n--\n\nCREATE TABLE IF NOT EXISTS `$table` ( $sql";
    $sql = str_replace(",", ", ", $sql);
    $sql = preg_replace("/\`\)\) ENGINE=/", "`) ) ENGINE=", $sql);
    $sql .= ";\n\n";
    return [$table => $sql];
  }

  // 獲取表數據並生成 INSERT 語句
  public function getDataByTable($table) {
    $res = mysql_query("SELECT * FROM `$table`", $this->conn);
    $rows = [];
    while ($r = mysql_fetch_assoc($res)) $rows[] = $r;
    if (!$rows) return '';

    $keys = array_map(fn($k) => "`$k`", array_keys($rows[0]));
    $sql = "INSERT INTO `$table` (" . implode(', ', $keys) . ") VALUES\n";
    foreach ($rows as $i => $row) {
      $vals = [];
      foreach ($row as $v) $vals[] = is_numeric($v) ? $v : "'" . addslashes($v) . "'";
      $sql .= "(" . implode(', ', $vals) . ")";
      $sql .= ($i === count($rows)-1) ? ";\n\n-- --------------------------------------------------------\n\n" : ",\n";
    }
    return $sql;
  }

  // 主備份入口
  public function getBackUpDataByTable($tables, $path='', $fileName='seocheck', $subsection=2) {
    if (empty($tables)) $this->_showMsg('未指定備份表', true);
    $path = $path ?: $_SERVER['DOCUMENT_ROOT'] . "/core/Runtime/Data/{$fileName}Demo/";
    if (!is_dir($path)) mkdir($path, 0777, true);

    $mysql_info = $this->_retrieve(); // 文件頭信息

    if (is_array($tables)) {
      $this->_showMsg('開始備份多表...');
      $this->writeFileByBackUpData($path . $this->dbName . '_table.sql', $mysql_info);
      foreach ($tables as $table) {
        $this->_showMsg("處理表: $table");
        $struct = $this->getFieldsByTable($table);
        $this->writeFileByBackUpData($path . $this->dbName . '_table.sql', $struct[$table]);
        $this->_insertSqlByTableForAll($path, $table, $subsection);
      }
    } else {
      // 單表模式
    }
  }

  // 分卷寫入數據
  private function _insertSqlByTableForAll($path, $table, $subsection) {
    $res = mysql_query("SELECT * FROM `$table`", $this->conn);
    $buffer = ''; $part = 0;
    while ($row = mysql_fetch_assoc($res)) {
      $buffer .= $this->_insertSql($row, $table);
      if (strlen($buffer) > $subsection * 1024 * 1024) {
        $this->writeFileByBackUpData($path . $table . $part . $this->tag . '.sql', $buffer);
        $this->_showMsg("表 $table 卷 $part 寫入完成");
        $buffer = ''; $part++;
      }
    }
    if ($buffer) {
      $this->writeFileByBackUpData($path . $table . $this->tag . '.sql', $buffer);
      $this->_showMsg("表 $table 最後一卷寫入完成");
    }
  }

  // 單條 INSERT
  private function _insertSql($row, $table) {
    $vals = [];
    foreach ($row as $v) $vals[] = is_numeric($v) ? $v : "'" . addslashes($v) . "'";
    return "INSERT INTO `$table` VALUES(" . implode(',', $vals) . ");\n";
  }

  // 文件頭信息
  private function _retrieve() {
    return "--\n-- MySQL dump by DbBackUp\n-- Host: {$this->host}\n-- Date: " . date('Y-m-d H:i') . "\n-- MySQL: " . mysql_get_server_info() . "\n-- PHP: " . phpversion() . "\n\nSET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\nSET time_zone = '+00:00';\n\n";
  }

  // 寫文件（支援追加、鎖定、權限）
  public function writeFileByBackUpData($file, $data, $mode='ab+') {
    @touch($file);
    $fp = @fopen($file, $mode);
    if ($fp) {
      flock($fp, LOCK_EX);
      fwrite($fp, $data);
      if ($mode === 'rb+') ftruncate($fp, strlen($data));
      fclose($fp);
      @chmod($file, 0777);
    }
    return $fp;
  }

  // 打包 ZIP 並清理源文件
  public function createZipByBackUpFile($path) {
    $files = $this->getFileByBackUpDir($path);
    if (!$files) return false;
    $zip = new ZipArchive;
    $zipName = $path . $this->dbName . date('Ymd') . '.zip';
    if ($zip->open($zipName, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) return false;
    foreach ($files as $f) {
      if (is_file($f)) $zip->addFile($f, basename($f));
    }
    $zip->close();
    if (file_exists($zipName)) {
      foreach ($files as $f) @unlink($f);
      return true;
    }
    return false;
  }

  // 掃描備份目錄
  public function getFileByBackUpDir($path) {
    $list = [];
    if (is_dir($path) && $dh = opendir($path)) {
      while (($f = readdir($dh)) !== false) {
        if ($f !== '.' && $f !== '..' && stripos($f, 'seocheck') !== false) $list[] = $path . $f;
      }
      closedir($dh);
    }
    return $list;
  }

  // 解壓 ZIP
  public function uncompressZip($path, $zipName) {
    $zip = new ZipArchive;
    if ($zip->open($path . $zipName) === true) {
      $zip->extractTo($path);
      $zip->close();
      return true;
    }
    return false;
  }

  // 即時輸出進度
  private function _showMsg($msg, $err = false) {
    $color = $err ? 'red' : 'green';
    echo "" . ($err ? 'ERROR' : 'OK') . ": $msg";
    if ($err) exit;
  }

  public function __destruct() {
    if ($this->conn) { mysql_query("UNLOCK TABLES", $this->conn); mysql_close($this->conn); }
  }
}

使用示例

<?php
require 'DbBackUp.php';
$db = new DbBackUp('localhost', 'root', 'password', 'my_database');
$tables = $db->getTableNames(); // 或指定 ['users', 'orders']
$db->getBackUpDataByTable($tables, '/data/backup/', 'my_db', 5); // 5MB 分卷
$db->createZipByBackUpFile('/data/backup/');
echo "備份完成！";