PHP数据库备份与恢复 PHP操作MySQL数据维护

<?php
$host = 'localhost';
$user = 'your_user';
$password = 'your_password';
$database = 'your_database';

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_errno) {
    die("连接失败: " . $mysqli->connect_error);
}

$tables = array();
$result = $mysqli->query("SHOW TABLES");
while ($row = $result->fetch_row()) {
    $tables[] = $row[0];
}

$sql = '';
foreach ($tables as $table) {
    $result = $mysqli->query("SHOW CREATE TABLE " . $table);
    $row = $result->fetch_row();
    $sql .= "

" . $row[1] . ";

";

    $result = $mysqli->query("SELECT * FROM " . $table);
    $num_fields = $result->field_count;

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

$mysqli->close();

// 保存到文件
$filename = 'db_backup_' . date('YmdHis') . '.sql';
$handle = fopen($filename, 'w+');
fwrite($handle, $sql);
fclose($handle);

echo "备份成功，文件名为：".$filename;
?>

<?php
$host = 'localhost';
$user = 'your_user';
$password = 'your_password';
$database = 'your_database';
$filename = 'db_backup.sql'; // 替换为你的备份文件

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_errno) {
    die("连接失败: " . $mysqli->connect_error);
}

$templine = '';
$lines = file($filename);
foreach ($lines as $line) {
    if (substr($line, 0, 2) == '--' || $line == '')
        continue;

    $templine .= $line;
    if (substr(trim($line), -1, 1) == ';') {
        $mysqli->query($templine);
        $templine = '';
    }
}

echo "数据库恢复成功！";
$mysqli->close();
?>

<?php
$host = 'localhost';
$user = 'your_user';
$password = 'your_password';
$database = 'your_database';
$log_table = 'log_table';
$days = 30; // 保留最近30天的数据

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_errno) {
    die("连接失败: " . $mysqli->connect_error);
}

$date = date('Y-m-d', strtotime('-' . $days . ' days'));
$sql = "DELETE FROM " . $log_table . " WHERE log_time < '" . $date . "'";

if ($mysqli->query($sql)) {
    echo "成功删除 " . $mysqli->affected_rows . " 条过期日志数据。";
} else {
    echo "删除失败: " . $mysqli->error;
}

$mysqli->close();
?>