math_cms/database_backups/backup.php

<?php

require_once __DIR__ . '/../vendor/autoload.php';
$app = require_once __DIR__ . '/../bootstrap/app.php';
$kernel = $app->make(Illuminate\Contracts\Console\Kernel::class);
$kernel->bootstrap();

use Illuminate\Support\Facades\DB;

echo "开始备份数据库...\n\n";

// 创建备份内容
$tables = DB::select('SHOW TABLES');
$dump = "-- MySQL Database Backup\n";
$dump .= "-- Generated on: " . date('Y-m-d H:i:s') . "\n";
$dump .= "-- Database: math\n\n";

foreach ($tables as $table) {
    $tableName = array_values((array)$table)[0];

    echo "备份表: $tableName\n";

    // 获取建表语句
    $createTable = DB::select('SHOW CREATE TABLE ' . $tableName);
    if (isset($createTable[0])) {
        $dump .= "--\n-- Table structure for table `$tableName`\n--\n";
        $dump .= "DROP TABLE IF EXISTS `$tableName`;\n";
        $dump .= $createTable[0]->{'Create Table'} . ";\n\n";
    }

    // 获取表数据
    $dump .= "--\n-- Dumping data for table `$tableName`\n--\n";

    // 分批获取数据以避免内存问题
    $offset = 0;
    $limit = 1000;

    do {
        $data = DB::table($tableName)->offset($offset)->limit($limit)->get();

        foreach ($data as $row) {
            $values = [];
            $columns = [];

            foreach ((array)$row as $key => $value) {
                if (is_numeric($key)) continue; // 跳过数字索引

                $columns[] = "`$key`";

                if ($value === null) {
                    $values[] = 'NULL';
                } elseif (is_string($value)) {
                    // 处理特殊字符
                    $value = str_replace(["\n", "\r", "\t"], ["\\n", "\\r", "\\t"], $value);
                    $values[] = "'" . addslashes($value) . "'";
                } elseif (is_bool($value)) {
                    $values[] = $value ? '1' : '0';
                } elseif (is_float($value) || is_double($value)) {
                    $values[] = sprintf('%F', $value);
                } else {
                    $values[] = $value;
                }
            }

            $dump .= "INSERT INTO `$tableName` (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $values) . ");\n";
        }

        $offset += $limit;
    } while ($data->count() == $limit);

    $dump .= "\n";
}

// 保存备份文件
$filename = __DIR__ . '/backup_math_' . date('Y-m-d_H-i-s') . '.sql';
file_put_contents($filename, $dump);

echo "\n备份完成！\n";
echo "备份文件: $filename\n";
echo "文件大小: " . number_format(filesize($filename) / 1024 / 1024, 2) . " MB\n";