Laravel 数据库（单个表或者整个数据库）备份（前后端分离）

Memory_wz0125

已于 2025-01-06 09:38:09 修改

阅读量818

点赞数

分类专栏： Laravel 笔记 API 文章标签： laravel 数据备份

于 2019-12-11 10:02:53 首次发布

本文链接：https://blog.youkuaiyun.com/Memory_wz0125/article/details/103487240

版权

笔记同时被 3 个专栏收录

7 篇文章

订阅专栏

Laravel

6 篇文章

订阅专栏

API

6 篇文章

订阅专栏

本文档详细介绍了在 Laravel6 框架下，如何使用 Homestead 和 MySql 进行数据库的备份、恢复、查看备份列表、下载备份及删除备份等操作。配置主要涉及 config/filesystems.php 文件，同时提供了控制器的相关方法说明。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

环境：laravel6 + homestead + MySql

配置：config/filesystems.php

        'backup' => [
            'driver' => 'local',
            'root' => base_path('storage'),
            'url' => env('APP_URL') . '/storage',
        ],

控制器声明：

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Storage;
use Ifsnop\Mysqldump as IMysqldump;

class BackController extends Controller
{
    //
    protected $directory = 'backup';
}

备份

    public function back(Request $rquest)
    {
        $table = $request->tablename;
        $mysql = Config::get('database.connections.mysql'); //从配置文件中获取数据库信息
        $filename = date("Y-m-d_H-i-s") . "-" . $mysql['database'] . ".sql"; //生成sql文件名   
        if ($tables == "all") {
            exec("mysqldump  -u" . $mysql['username'] . " -p" . $mysql['password'] . " --default-character-set=utf8 " . $mysql['database'] . " -n > " ."路径"); //所有表结构和数据
         } else {
        $info = exec("mysqldump -u" . $mysql['username'] . " -p" . $mysql['password'] . " " . $mysql['database'] . " -n --tables " . $table . " >/home/vagrant/code/User_Admin/storage/backup/" . $filename); //指定表结构和数据
        dd($info);
         }
         }
    }

恢复：

public function recover(Request $request)
    {
        # code...
        // $filename = $request->filename;//正式使用时替换
        $filename = "2019-12-10_11-19-55-admin.sql";//测试使用
        $disk = Storage::disk('backup');

        $exists = $disk->exists($this->directory . '/' . $filename);
        // dd($exists);
        if (!$exists) {
            return $this->error("文件不存在");;
        }

        //导入sql文件操作
        $sql = file_get_contents(storage_path() . "/backup/" . $filename);
        // dd($sql);
        $result = DB::unprepared($sql);
        // dd($result);
        if ($result != 1) {
            return back()->with('error', '数据库恢复失败');
        }
        return $this->success('数据恢复成功', 200);
    }

备份列表：

   public function index()
    {
        # code...
        $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)); //最后修改时间
            }
        }
        dump($backup);
    }


  //单位换算
    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];
    }

备份下载：

    public function download(Request $request)
    {
        # code...
        $filename = $request->filename;
        $disk = Storage::disk('backup');

        $exists = $disk->exists($this->directory . '/' . $filename);
        if (!$exists) {
            return error('文件不存在');
        }
        //完整路径下载
        return response()->download(storage_path() . '/' . $this->directory . '/' . $filename);
    }

备份删除：

   public function delete(Request $request)
    {
        # code...
        //$filename = $request->filename;
        $filename = "2019-12-10_11-19-37-admin.sql";
        $disk = Storage::disk('backup');
        $files = $disk->files($this->directory);

        dump($disk);
        dump($files);
        $exists = $disk->exists($this->directory . '/' . $filename);

        dump($exists);
        if (!$exists) {
            return $this->error("文件不存在");;
        }

        // 删除单条文件
        $result = $disk->delete($this->directory . '/' . $filename);
        // dd($result);
        return $this->success("删除成功", 200);
    }