php实现数据备份

PHP实现数据备份的方法

使用MySQL命令行工具

通过PHP的exec()或shell_exec()函数调用系统命令实现MySQL数据库备份。以下代码示例展示如何备份指定数据库：

$dbHost = 'localhost';
$dbUser = 'username';
$dbPass = 'password';
$dbName = 'database_name';
$backupFile = 'backup_' . date("Y-m-d-H-i-s") . '.sql';

$command = "mysqldump --host=$dbHost --user=$dbUser --password=$dbPass $dbName > $backupFile";
exec($command, $output, $returnVar);

if ($returnVar === 0) {
    echo "数据库备份成功，文件保存在: $backupFile";
} else {
    echo "数据库备份失败";
}

使用PHP原生MySQL函数

对于无法使用命令行工具的环境，可以通过PHP的MySQL函数逐表备份数据：

$conn = new mysqli('localhost', 'username', 'password', 'database_name');
$tables = array();
$result = $conn->query("SHOW TABLES");

while ($row = $result->fetch_row()) {
    $tables[] = $row[0];
}

$backupContent = "";
foreach ($tables as $table) {
    $result = $conn->query("SELECT * FROM $table");
    $numFields = $result->field_count;

    $backupContent .= "DROP TABLE IF EXISTS $table;";
    $row2 = $conn->query("SHOW CREATE TABLE $table")->fetch_row();
    $backupContent .= "\n\n" . $row2[1] . ";\n\n";

    while ($row = $result->fetch_row()) {
        $backupContent .= "INSERT INTO $table VALUES(";
        for ($j = 0; $j < $numFields; $j++) {
            $row[$j] = addslashes($row[$j]);
            $row[$j] = str_replace("\n", "\\n", $row[$j]);
            if (isset($row[$j])) {
                $backupContent .= '"' . $row[$j] . '"';
            } else {
                $backupContent .= '""';
            }
            if ($j < ($numFields - 1)) {
                $backupContent .= ',';
            }
        }
        $backupContent .= ");\n";
    }
    $backupContent .= "\n\n\n";
}

$backupFile = 'backup_' . date("Y-m-d-H-i-s") . '.sql';
$handle = fopen($backupFile, 'w+');
fwrite($handle, $backupContent);
fclose($handle);

使用PDO实现数据备份

对于使用PDO连接数据库的情况，可以采用以下方法：

try {
    $pdo = new PDO('mysql:host=localhost;dbname=database_name', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    $backupContent = "";

    foreach ($tables as $table) {
        $backupContent .= "DROP TABLE IF EXISTS `$table`;\n";
        $createTable = $pdo->query("SHOW CREATE TABLE `$table`")->fetch(PDO::FETCH_ASSOC);
        $backupContent .= $createTable['Create Table'] . ";\n\n";

        $rows = $pdo->query("SELECT * FROM `$table`")->fetchAll(PDO::FETCH_ASSOC);
        if (count($rows) > 0) {
            $columns = array_keys($rows[0]);
            $backupContent .= "INSERT INTO `$table` (`" . implode('`, `', $columns) . "`) VALUES \n";

            $values = array();
            foreach ($rows as $row) {
                $rowValues = array();
                foreach ($row as $value) {
                    $rowValues[] = $pdo->quote($value);
                }
                $values[] = "(" . implode(', ', $rowValues) . ")";
            }
            $backupContent .= implode(",\n", $values) . ";\n\n";
        }
    }

    $backupFile = 'backup_' . date("Y-m-d-H-i-s") . '.sql';
    file_put_contents($backupFile, $backupContent);

} catch (PDOException $e) {
    echo "备份失败: " . $e->getMessage();
}

定时自动备份

结合Linux的crontab可以实现定时自动备份：

# 编辑crontab
crontab -e

# 添加以下内容，每天凌晨3点执行备份
0 3 * * * /usr/bin/php /path/to/backup_script.php

备份文件压缩

为节省存储空间，可以对备份文件进行压缩：

$backupFile = 'backup_' . date("Y-m-d-H-i-s") . '.sql';
$gzBackupFile = $backupFile . '.gz';

// 创建备份文件内容...
// $backupContent = ...

file_put_contents($backupFile, $backupContent);
$gz = gzopen($gzBackupFile, 'w9');
gzwrite($gz, file_get_contents($backupFile));
gzclose($gz);
unlink($backupFile); // 删除原始SQL文件

备份到远程服务器

通过FTP将备份文件传输到远程服务器：

$ftpServer = 'ftp.example.com';
$ftpUser = 'username';
$ftpPass = 'password';
$remoteFile = 'backups/' . basename($backupFile);

$connId = ftp_connect($ftpServer);
$loginResult = ftp_login($connId, $ftpUser, $ftpPass);

if (ftp_put($connId, $remoteFile, $backupFile, FTP_BINARY)) {
    echo "备份文件已成功上传到远程服务器";
} else {
    echo "上传备份文件失败";
}

ftp_close($connId);