laravel 数据库手动备份与还原

水深无声 2021-10-01 08:34 799阅读 0赞

### laravel导入sql文件 ###

> 使用DB::unprepared(file\_get\_contents(‘sql文件完整路径’));

### laravel文件下载 ###

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

先展示效果

![6587214-0b29f21d0be6f590.png][]

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];
        }
    }

[6587214-0b29f21d0be6f590.png]: /images/20210804/5f932ff1efd14dbcbd6d92c0f4ca4938.png