玖叶教程网

前端编程开发入门

2024年04月12日

tp5备份、还原数据库

第一种：使用扩展

安装

composer require tp5er/tp5-databackup dev-master

引入类文件

use \tp5er\Backup;

基本配置信息，默认传入下面参数

$config=array(
    'path'     => './Data/',//数据库备份路径
    'part'     => 20971520,//数据库备份卷大小
    'compress' => 0,//数据库备份文件是否启用压缩 0不压缩 1 压缩
    'level'    => 9 //数据库备份文件压缩级别 1普通 4 一般  9最高
);

使用方法

//实例化
$db= new Backup($config);
//数据类表列表
return $this->fetch('index',['list'=>$db->dataList()]);
//备份文件列表
return $this->fetch('importlist',['list'=>$db->fileList()]);
//备份表
$start= $db->setFile($file)->backup($tables[$id], $start);
//导入表
$start= $db->setFile($file)->import($start);
//删除备份文件
$db->delFile($time);
//修复表
$db->repair($tables);
//优化表
$db->optimize($tables);
//下载
$db->downloadFile($time);

第二种：自己写的方法

Database.php

<?php
namespace app\admin\controller;
use think\Db;
class Database extends Common
{
    protected $db = '', $datadir =  './public/Data/';
    function _initialize(){
        parent::_initialize();
        $db=db('');
        $this->db =   Db::connect();
    }
    public function database(){
        if(request()->isPost()){
            $dbtables = $this->db->query("SHOW TABLE STATUS LIKE '".config('prefix')."%'");
            $total = 0;
            foreach ($dbtables as $k => $v) {
                $dbtables[$k]['size'] = format_bytes($v['Data_length']);
                $total += $v['Data_length'] + $v['Index_length'];
            }
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$dbtables,'total'=>format_bytes($total),'tableNum'=>count($dbtables),'rel'=>1];
        }
        return view();
    }
    //优化
    public function optimize() {
        $batchFlag = input('param.batchFlag', 0, 'intval');
        //批量删除
        if ($batchFlag) {
            $table = input('key', array());
        }else {
            $table[] = input('tableName' , '');
        }

        if (empty($table)) {
            $result['msg'] = '请选择要优化的表!';
            $result['code'] = 0;
            return $result;
        }

        $strTable = implode(',', $table);
        if (!DB::query("OPTIMIZE TABLE {$strTable} ")) {
            $strTable = '';
        }
        $result['msg'] = '优化表成功!';
        $result['url'] = url('database');
        $result['code'] = 1;
        return $result;
    }
    //修复
    public function repair() {
        $batchFlag = input('param.batchFlag', 0, 'intval');
        //批量删除
        if ($batchFlag) {
            $table = input('key', array());
        }else {
            $table[] = input('tableName' , '');
        }

        if (empty($table)) {
            $result['msg'] = '请选择要修复的表!';
            $result['code'] = 0;
            return $result;
        }

        $strTable = implode(',', $table);
        if (!DB::query("REPAIR TABLE {$strTable} ")) {
            $strTable = '';
        }
        $result['msg'] = '修复表成功!';
        $result['url'] = url('database');
        $result['code'] = 1;
        return $result;
    }
    //备份
    public function backup(){
        $puttables = input('post.tables/a');
        if(empty($puttables)) {
            $dataList = $this->db->query("SHOW TABLE STATUS LIKE '".config('prefix')."%'");
            foreach ($dataList as $row){
                $table[]= $row['Name'];
            }
        }else{
            $table=input('tables/a');
        }
        $sql = "-- SQL Backup\n-- Time:".toDate(time())."\n-- backup \n\n";
        foreach($table as $key=>$table) {
            $sql .= "--\n-- 表的结构 `$table`\n-- \n";
            $sql .= "DROP TABLE IF EXISTS `$table`;\n";
            $info = $this->db->query("SHOW CREATE TABLE  $table");
            $sql .= str_replace(array('USING BTREE','ROW_FORMAT=DYNAMIC'),'',$info[0]['Create Table']).";\n";
            $result = $this->db->query("SELECT * FROM $table");
            if($result)$sql .= "\n-- \n-- 导出`$table`表中的数据 `$table`\n--\n";
            foreach($result as $key=>$val) {
                foreach ($val as $k=>$field){
                    if(is_string($field)) {
                        $val[$k] = '\''. $this->db->escape_string($field).'\'';
                    }elseif($field==0){
                        $val[$k] = 0;
                    } elseif(empty($field)){
                        $val[$k] = 'NULL';
                    }
                }
                $sql .= "INSERT INTO `$table` VALUES (".implode(',', $val).");\n";
            }
        }

        $filename = empty($fileName)? date('YmdH').'_'.rand_string(10) : $fileName;
        $r= file_put_contents($this->datadir . $filename.'.sql', trim($sql));
        exit(json_encode(array('code'=>1,'msg'=>"成功备份数据库")));
    }
    //备份列表
    public function restore(){
        if(request()->isPost()){
            $pattern = "*.sql";
            $filelist = glob($this->datadir.$pattern);
            $fileArray = array();
            foreach ($filelist  as $i => $file) {
                //只读取文件
                if (is_file($file)) {
                    $_size = filesize($file);
                    $name = basename($file);
                    $pre = substr($name, 0, strrpos($name, '_'));
                    $number = str_replace(array($pre. '_', '.sql'), array('', ''), $name);
                    $fileArray[] = array(
                        'name' => $name,
                        'pre' => $pre,
                        'time' => date('Y-m-d h:i',filemtime($file)),
                        'sortSize' => byte_format($_size),
                        'size' => $_size,
                        'number' => $number,
                    );
                }
            }
            if(empty($fileArray)) $fileArray = array();
            return ['code'=>0,'msg'=>'获取成功!','data'=>$fileArray,'rel'=>1];
        }
        return view();
    }
    //执行还原数据库操作
    public function restoreData() {
        header('Content-Type: text/html; charset=UTF-8');
        $filename = input('sqlfilepre');
        $file = $this->datadir.$filename;

        //读取数据文件
        $sqldata = file_get_contents($file);
        $sqlFormat = $this->sql_split($sqldata,config('prefix'));

        foreach ((array)$sqlFormat as $sql){
            $sql = trim($sql);
            if (strstr($sql, 'CREATE TABLE')){
                preg_match('/CREATE TABLE `([^ ]*)`/', $sql, $matches);
                $ret = $this->excuteQuery($sql);
            }else{
                $ret =$this->excuteQuery($sql);
            }
        }
        $result['msg'] = '数据库还原成功!';
        $result['url'] = url('database/database');
        $result['code'] = 1;
        return $result;
    }

    public function excuteQuery($sql='')
    {
        if(empty($sql)) {$this->error('空表');}
        $queryType = 'INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|SELECT .* INTO|COPY|ALTER|GRANT|TRUNCATE|REVOKE|LOCK|UNLOCK';
        if (preg_match('/^\s*"?(' . $queryType . ')\s+/i', $sql)) {
            $data['result'] = $this->db->execute($sql);
            $data['type'] = 'execute';
        }else {
            $data['result'] = $this->db->query($sql);
            $data['type'] = 'query';
        }
        $data['dberror'] = $this->db->getError();
        return $data;
    }
    function  sql_split($sql,$tablepre) {
        if($tablepre != "yiyoncms_") $sql = str_replace("yiyoncms_", $tablepre, $sql);
        //$sql = preg_replace("/TYPE=(InnoDB|MyISAM|MEMORY)( DEFAULT CHARSET=[^; ]+)?/", "ENGINE=\\1 DEFAULT CHARSET=utf8",$sql);

        if($r_tablepre != $s_tablepre) $sql = str_replace($s_tablepre, $r_tablepre, $sql);
        $sql = str_replace("\r", "\n", $sql);
        $ret = array();
        $num = 0;
        $queriesarray = explode(";\n", trim($sql));
        unset($sql);
        foreach($queriesarray as $query)
        {
            $ret[$num] = '';
            $queries = explode("\n", trim($query));
            $queries = array_filter($queries);
            foreach($queries as $query)
            {
                $str1 = substr($query, 0, 1);
                if($str1 != '#' && $str1 != '-') $ret[$num] .= $query;
            }
            $num++;
        }
        return $ret;
    }
    //下载
    public function downFile() {
        $file = $this->request->param('file');
        $type = $this->request->param('type');
        if (empty($file) || empty($type) || !in_array($type, array("zip", "sql"))) {
            $this->error("下载地址不存在");
        }
        $path = array("zip" => $this->datadir."zipdata/", "sql" => $this->datadir);
        $filePath = $path[$type] . $file;
        if (!file_exists($filePath)) {
            $this->error("该文件不存在，可能是被删除");
        }
        $filename = basename($filePath);
        header("Content-type: application/octet-stream");
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header("Content-Length: " . filesize($filePath));
        readfile($filePath);
    }
    //删除sql文件
    public function delSqlFiles() {
        $batchFlag = input('param.batchFlag', 0, 'intval');
        //批量删除
        if ($batchFlag) {
            $files = input('key', array());
        }else {
            $files[] = input('sqlfilename' , '');
        }
        if (empty($files)) {
            $result['msg'] = '请选择要删除的sql文件!';
            $result['code'] = 0;
            return $result;
        }

        foreach ($files as $file) {
            $a = unlink($this->datadir.'/' . $file);
        }
        if($a){
            $result['msg'] = '删除成功!';
            $result['url'] = url('restore');
            $result['code'] = 1;
            return $result;
        }else{
            $result['msg'] = '删除失败!';
            $result['code'] = 0;
            return $result;
        }
    }
}
//format_bytes函数
/**
 * PHP格式化字节大小
 * @param  number $size      字节数
 * @param  string $delimiter 数字和单位分隔符
 * @return string            格式化后的带单位的大小
 */
function format_bytes($size, $delimiter = '') {
    $units = array('B', 'KB', 'MB', 'GB', 'TB', 'PB');
    for ($i = 0; $size >= 1024 && $i < 5; $i++) $size /= 1024;
    return round($size, 2) . $delimiter . $units[$i];
}

html文件（模版是layui，大家根据自己的情况进行修改）

restore.html

{include file="common/head"/}
<div class="admin-main layui-anim layui-anim-upbit">
    <fieldset class="layui-elem-field layui-field-title">
        <legend>备份文件列表</legend>
    </fieldset>
    <table class="layui-table" id="list" lay-filter="list"></table>
</div>
{include file="common/foot"/}
<script type="text/html" id="size">
    {{d.sortSize}}
</script>
<script type="text/html" id="action">
    <a class="layui-btn layui-btn-normal layui-btn-xs" lay-event="recover">恢复</a>
    <a href="{:url('downFile')}?type=sql&file={{d.name}}" class="layui-btn layui-btn-xs">下载</a>
    <a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del">{:lang('del')}</a>
</script>
<script>
    layui.use('table', function() {
        var table = layui.table, $ = layui.jquery;
        table.render({
            elem: '#list'
            ,url: '{:url("restore")}',
            method:'post'
            ,cols: [[
                {field:'name', title: '文件名称', width:250}
                ,{field:'size', title: '文件大小', width:200,sort:true,templet:'#size'}
                ,{field:'time', title: '备份时间', width:200,sort:true}
                ,{field:'number', title: '卷号', width:150}
                ,{width:160, align:'center', toolbar: '#action'}
            ]]
        });
        table.on('tool(list)', function(obj) {
            var data = obj.data;
            if (obj.event === 'recover') {
                loading = layer.load(1, {shade: [0.1, '#fff']});
                layer.confirm('确认要导入数据吗？',{icon: 0}, function (index) {
                    $.post("{:url('restoreData')}",{sqlfilepre:data.name},function(res){
                        layer.close(loading);
                        if(res.code==1){
                            layer.msg(res.msg, {time: 1000,icon:1});
                        }else{
                            layer.msg(res.msg, {time: 1000,icon:2});
                        }
                    });
                    layer.close(index);
                });
            }else if(obj.event === 'del'){
                layer.confirm('确认要删除该备份文件吗？', {icon: 3}, function (index) {
                    loading = layer.load(1, {shade: [0.1, '#fff']});
                    $.post('{:url("delSqlFiles")}',{sqlfilename: data.name}, function (res) {
                        layer.close(loading);
                        if (res.code == 1) {
                            layer.msg(res.msg, {time: 1000,icon:1});
                            obj.del();
                        }else{
                            layer.msg(res.info,{time: 1000,icon:2});
                        }
                    });
                    layer.close(index);
                });
            }
        });
    });
</script>

database.html

{include file="common/head"/}
<style>
    .layui-elem-quote {
        border-left: 5px solid #1E9FFF;
    }
</style>
<div class="admin-main layui-anim layui-anim-upbit">
    <fieldset class="layui-elem-field layui-field-title">
        <legend>数据{:lang('list')}</legend>
    </fieldset>
    <blockquote class="layui-elem-quote">
       数据库中共有<i class="count"></i>张表，共计<i class="size"></i>
        <a href="javascript:void(0)" id="backUp" class="layui-btn layui-btn-sm pull-right layui-btn-normal">备份</a>
    </blockquote>
    <table class="layui-table" id="list" lay-filter="list"></table>
</div>
{include file="common/foot"/}
<script type="text/html" id="size">
    {{d.size}}
</script>
<script type="text/html" id="action">
    <a class="layui-btn layui-btn-normal layui-btn-xs" lay-event="optimize">优化</a>
    <a class="layui-btn layui-btn-xs" lay-event="repair">修复</a>
</script>
<script>
    layui.use('table', function() {
        var table = layui.table, $ = layui.jquery;
        table.render({
            id: 'database',
            elem: '#list',
            url: '{:url("database")}',
            method: 'post',
            cols: [[
                {checkbox:true,fixed: true},
                {field: 'Name', title: '数据库表', width: 150, fixed: true,sort: true},
                {field: 'Rows', title: '记录条数', width: 150,sort: true},
                {field: 'Data_length', title: '占用空间', width: 150,templet:'#size',sort: true},
                {field: 'Engine', title: '类型', width: 110,sort: true},
                {field: 'Collation', title: '编码', width: 150,sort: true},
                {field: 'Create_time', title: '创建时间', width: 180,sort: true},
                {field: 'Comment', title: '说明', width: 180},
                {width: 160, align: 'center', toolbar: '#action'}
            ]],
            done: function(res, curr, count){
                $('.count').html(res.tableNum);
                $('.size').html(res.total);
            }
        });
        table.on('tool(list)', function(obj) {
            var data = obj.data;
            loading = layer.load(1, {shade: [0.1, '#fff']});
            if (obj.event === 'optimize') {
                $.post("{:url('database/optimize')}",{tableName:data.Name},function(res){
                    layer.close(loading);
                    if(res.code > 0){
                        layer.msg(res.msg,{time:1000,icon:1},function(){
                            window.location.href = res.url;
                        });
                    }else{
                        layer.msg(res.msg,{time:1000,icon:2});
                    }
                });
            }else if(obj.event === 'repair'){
                $.post("{:url('database/repair')}",{tableName:data.Name},function(res){
                    layer.close(loading);
                    if(res.code > 0){
                        layer.msg(res.msg,{time:1000,icon:1},function(){
                            window.location.href = res.url;
                        });
                    }else{
                        layer.msg(res.msg,{time:1000,icon:2});
                    }
                });
            }
        });

        $('#backUp').click(function(){
            var obj = $(this);
            var checkStatus = table.checkStatus('database'); //test即为参数id设定的值
            var a = [];
            $(checkStatus.data).each(function(i,o){
                a.push(o.Name);
            });
            obj.addClass('layui-btn-disabled');
            obj.html('备份进行中...');
            $.post("{:url('database/backup')}",{tables:a},function(data){
                data = eval('('+data+')');
                if(data.code==1){
                    obj.removeClass('layui-btn-disabled');
                    obj.html('备份');
                    layer.msg(data.msg,{time:1000,icon:1});
                }else{
                    layer.msg(data.msg,{time:1000,icon:2});
                }
            });
        })


    });
</script>

标签：sqltrim 

作者:gojiuye , 分类:技术精选 , 浏览:25 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言