laravel 数据库手动备份与还原

laravel导入sql文件

使用DB::unprepared(file_get_contents(‘sql文件完整路径’));

laravel文件下载

使用 return response()->download('文件完整路径');

先展示效果

image.png

代码

<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Support\Facades\Storage;
use Ifsnop\Mysqldump as IMysqldump;
use Illuminate\Support\Facades\DB;

class BackupController extends AdminController
{
    protected $directory = 'backup';
    
    public function index()
    {
        $disk = Storage::disk('backup');
        // 获取目录下的文件
        $files = $disk->files($this->directory);

        $backup = [];
        foreach ($files as $k => $v) {
            if (substr($v,strpos($v,'.')+1) === 'sql') {
                $backup[$k]['filename'] = substr($v,strpos($v,'/')+1);
                $backup[$k]['filesize'] = $this->count_size($disk->size($v));            //文件大小
                $backup[$k]['time'] = date('Y-m-d H:i:s', $disk->lastModified($v)); //最后修改时间
            }
        }
        
        return view('admin.backup.backup', compact('backup'));
    }
    
    
    //删除
    public function destroy($filename)
    {
        
        $disk = Storage::disk('backup');

        $exists = $disk->exists($this->directory.'/'.$filename);
        if (!$exists) {
            return back()->with('error','文件不存在');
        }
        // 删除单条文件
        $result = $disk->delete($this->directory.'/'.$filename);
        
        return $result ? $this->success() : $this->error('删除失败！');
        
    }
    
    public function store()
    {
        //备份数据库配置
        $dumpSettings = array(
            'compress' => IMysqldump\Mysqldump::NONE,
            'no-data' => false,
            'add-drop-table' => true,
            'single-transaction' => true,
            'lock-tables' => true,
            'add-locks' => true,
            'extended-insert' => true,
            'disable-foreign-keys-check' => true,
            'skip-triggers' => false,
            'add-drop-trigger' => true,
            'databases' => true,
            'add-drop-database' => true,
            'hex-blob' => true
        );
        try {
            $dump = new IMysqldump\Mysqldump(
                'mysql:host=' . env('DB_HOST') . ';dbname=' . env('DB_DATABASE'),
                env('DB_USERNAME'),
                env('DB_PASSWORD'),
                $dumpSettings
            );
            $prefix = 'sitename-backup';
            $filename = date('Y') . '-' . date('m') . '-' . date('d') . '-' . date('H') . '-' . date('i') . '-' . date('s');
            $name = $prefix . $filename . ".sql";
            $dump->start(storage_path() . "/backup/" . $name);
        } catch (\Exception $e) {
            echo 'mysqldump-php error: ' . $e->getMessage();
        }
      
        return redirect('admin/backup')->with('success','数据库备份成功');
    }
    
    public function recover($filename)
    {
        $disk = Storage::disk('backup');
    
        $exists = $disk->exists($this->directory.'/'.$filename);
        if (!$exists) {
            return back()->with('error','sql文件不存在');
        }
        
        //导入sql文件操作
        $sql = file_get_contents(storage_path()."/backup/".$filename);
        $result = DB::unprepared($sql);
        
        if ($result != 1) {
            return back()->with('error', '数据库恢复失败');
        }
        
        return redirect('admin/backup')->with('success', '数据库恢复成功');
    }
    
    public function download($filename)
    {
        $disk = Storage::disk('backup');

        $exists = $disk->exists($this->directory.'/'.$filename);
        if (!$exists) {
            return back()->with('error','文件不存在');
        }
        //完整路径下载
        return response()->download(storage_path().'/'.$this->directory.'/'.$filename);
    }
    
    //单位换算
    public function count_size($bit)
    {
        $type = array('Bytes','KB','MB','GB','TB');
        for($i = 0; $bit >= 1024; $i++)
        {
            $bit/=1024;
        }
        return (floor($bit*100)/100).$type[$i];
    }
}