laravel 12 備份及還原資料庫

在 Laravel 12 中實作資料庫備份與還原，最穩定且常見的做法是封裝 mysqldump 與 mysql 指令。我們將結合 Spatie Activitylog 記錄每一次的操作，並透過 Scheduler 實現自動化。

image 15

建立備份 Model 與 Migration

Bash
php artisan make:model DatabaseBackup -m

Laravel 會產生 xxx_xx_xx_xxxxx_create_database_backups_table.php & Model:DatabaseBackup.php

修改 Migration table: DatabaseBackup 內容

PHP
public function up(): void
{
    Schema::create('database_backups', function (Blueprint $table) {
        $table->id();
        $table->string('filename');
        $table->string('path');
        $table->bigInteger('size'); // 位元組
        $table->string('type'); // 'manual' 或 'scheduled'
        $table->timestamps();
    });
}

建立備份 Service (邏輯核心)

建立 app/Services/BackupService.php 來處理繁瑣的 shell 指令。

PHP
namespace App\Services;

use Illuminate\Support\Facades\Process;
use Illuminate\Support\Facades\Storage;
use App\Models\DatabaseBackup;
use Spatie\Activitylog\Models\Activity;

class BackupService
{
    public function createBackup($type = 'manual')
    {
        $filename = "backup-" . now()->format('Y-m-d-H-i-s') . ".sql";
        $path = storage_path("app/backups/{$filename}");

        if (!file_exists(storage_path('app/backups'))) {
            mkdir(storage_path('app/backups'), 0755, true);
        }

        // 取得資料庫配置
        $config = config('database.connections.mysql');
        
        // 執行 mysqldump
        $process = Process::run("mysqldump -u {$config['username']} -p'{$config['password']}' {$config['database']} > {$path}");

        if ($process->successful()) {
            $backup = DatabaseBackup::create([
                'filename' => $filename,
                'path' => $path,
                'size' => filesize($path),
                'type' => $type
            ]);

            activity('database')
                ->performedOn($backup)
                ->causedBy(auth()->user())
                ->withProperties(['size' => $backup->size])
                ->log("建立了資料庫備份: {$filename}");

            return $backup;
        }

        throw new \Exception("備份失敗: " . $process->errorOutput());
    }

    public function restoreBackup($id)
    {
        $backup = DatabaseBackup::findOrFail($id);
        $config = config('database.connections.mysql');

        // 執行還原
        $process = Process::run("mysql -u {$config['username']} -p'{$config['password']}' {$config['database']} < {$backup->path}");

        if ($process->successful()) {
            activity('database')
                ->performedOn($backup)
                ->causedBy(auth()->user())
                ->log("還原了資料庫版本: {$backup->filename}");

            return true;
        }

        throw new \Exception("還原失敗: " . $process->errorOutput());
    }
}

設定排程與 API Controller

建立 API 控制器:BackupController

Bash
php artisan make:controller --api API\BackupController

Controller (app/Http/Controllers/API/BackupController.php):

PHP
public function store(BackupService $service) {
    return response()->json($service->createBackup('manual'));
}

public function restore($id, BackupService $service) {
    $service->restoreBackup($id);
    return response()->json(['message' => '還原成功']);
}

排程 (routes/console.php):

PHP
use App\Services\BackupService;
use Illuminate\Support\Facades\Schedule;

// 每天凌晨 3 點自動備份
Schedule::call(function (BackupService $service) {
    $service->createBackup('scheduled');
})->dailyAt('03:00');

建立 Queue Job 處理備份

建立 Queue Job 處理備份

首先，建立一個 Job 來處理耗時的備份程序。

Bash
php artisan make:job ProcessDatabaseBackupJob

編輯 app/Jobs/ProcessDatabaseBackupJob.php：

PHP
namespace App\Jobs;

use App\Services\BackupService;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Queue\Queueable;
use Spatie\Activitylog\Models\Activity;

class ProcessDatabaseBackupJob implements ShouldQueue
{
    use Queueable;

    public string $type, $process, $backup_id;
    /**
     * Create a new job instance.
     */
    public function __construct($type = 'manual', $process = 'backup', $backup_id = '')
    {
        $this->type = $type;        // manual / scheduled
        $this->process = $process;  // backup / restore
        $this->backup_id = $backup_id; // only for restore
    }

    /**
     * Execute the job.
     */
    public function handle(BackupService $backupService): void
    {
        if ($this->process === 'backup') {
            // 備份
            $result = $backupService->createBackup($this->type);
        } else if ($this->process === 'restore') {
            // 還原
            if (empty($this->backup_id)) {
                throw new \Exception("還原時必須提供 backup_id");
            }
            $result = $backupService->restoreBackup($this->backup_id);
        } else {
            throw new \Exception("不支援的處理類型: {$this->process}");
        }
    }
}

設定排程 (自動執行 Queue Job)

編輯 routes/console.php：

PHP
use App\Jobs\ProcessDatabaseBackup;
use Illuminate\Support\Facades\Schedule;

// 每天凌晨 3 點派發備份任務到隊列
Schedule::job(new ProcessDatabaseBackup('scheduled','backup',''))->dailyAt('03:00');

MySQL 與 SQlite 建議的整合方案如下：

  1. MySQL：繼續使用 mysqldump --ignore-table。這是最專業的做法，系統日誌與 Tokens 完全不受還原影響。
  2. SQLite：才使用「記憶體回存法」。因為 SQLite 是檔案層級的操作，無法局部排除資料表。

相關文章

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *


目前趨勢

PoorMansTSqlFormatter in SSMS v20
用 T-SQL 將 TableA 的資料新增到資料表 TableB
受保護的內容: 鼎新ERP批號溯源（Lot Tracing）逆向查詢SQL
清除SQL Server Log檔 (交易紀錄)

內容目錄

← 內容索引