thnkphp5.0数据库备份还原

** 根据网上搜索到的一些代码,自己整合了一下mysql数据库基于tp5的备份还原简单功能,但是还存在一个问题,在还原数据,使用Db::execute($sql);执行INSERT插入数据的时候,如果INSERT语句中某个变量含有"“符号是,会被当成转移符处理掉,如果某张表存在文件目录数据,在做数据还原的时候,”“会丢失。
针对上面这个问题,尝试过在原始数据做保存的时候,对文件目录用addslashes()函数做”\“处理,这样子初次还原的时候,mysql会执行转移符解析,而把原先addslashes()处理过的双斜杠”\“变成单斜杠”",虽然说值没问题,但是在做二次还原的时候,由于没有双斜杠"\",还是会被当成转移符处理掉。
如:
“D:\xampp\htdocs\tp_base\public” ==addslashes处理=>>>> “D:\xampp\htdocs\tp_base\public”
“D:\xampp\htdocs\tp_base\public” ==初次还原=>>>>>>>> “D:\xampp\htdocs\tp_base\public”
“D:\xampp\htdocs\tp_base\public” ==二次备份还原=>>>> “D:xampphtdocs p_basepublic”**

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

	<?php
	namespace app\admin\model;

	use think\Exception;
	use think\Model;
	use think\Db;
	class SqlModel extends Model
	{
		protected $name = 'Backup';

	    public function doBackUp($Param){
	        try{
	            $insert = [];
	            if($Param['backup_type']=='table'&&isset($Param['backup_table'])&&$Param['backup_table']){
	                $file_name = self::backUpTable($Param['backup_table']);
	                $insert['backup_table'] = $Param['backup_table'];
	            }else{
	                $file_name = self::backUpDatabase();
	            }
	            $file_name = substr($file_name,strpos($file_name,ROOT_PATH)+strlen(ROOT_PATH));
	            /*添加一条备份记录*/
	            $insert['backup_type'] = $Param['backup_type'];
	            $insert['backup_note'] = $Param['backup_note'];
	            $insert['backup_file'] = addslashes($file_name);
	            $insert['create_time'] = time();
	            $Backs = $this->allowField(true)->insert($insert);
	
	            $AddID = $this->getLastInsID();
	
	            if($Backs === false){
	                addLogInfo(session('UID'), 2, '组员【'.session('MemberUser').'】新增备份,ID:'.$AddID, 2);
	                return ['code'=>0, 'data'=>'', 'msg'=>$this->error()];
	            }else{
	                addLogInfo(session('UID'), 2, '组员【'.session('MemberUser').'】新增备份,ID:'.$AddID, 1);
	                return ['code'=>1, 'data'=>'', 'msg'=>'备份成功'];
	            }
	        }catch (\Exception $e){
	            return ['code'=>0, 'data'=>'', 'msg'=>$e->getMessage()];
	        }
	
	    }
	
	    /**
	     * 删除信息
	     * @param $ID
	     * @return array
	     */
	    public function delData($ID){
	        try{
	            $Where = [];
	            $Where['backup_id'] = $ID;
	            $info = $this->where($Where)->find();
	            $Backs = $this->where($Where)->delete();
	
	            if($Backs === false){
	                addLogInfo(session('UID'), 4, '组员【'.session('MemberUser').'】删除备份记录,ID:'.$ID, 2);
	                return ['code'=>0, 'data'=>'', 'msg'=>$this->error()];
	            }else{
	                addLogInfo(session('UID'), 4, '组员【'.session('MemberUser').'】删除备份记录,ID:'.$ID, 1);
	                /*删除文件*/
	                @unlink(ROOT_PATH.stripslashes($info['backup_file']));
	                return ['code'=>1, 'data'=>'', 'msg'=>'删除成功'];
	            }
	        }catch(\Exception $e){
	            return ['code'=>0, 'data'=>'', 'msg'=>$e->getMessage()];
	        }
	    }
	
	    /**
	     * 还原数据表
	     * @param $ID
	     */
	    public function doReduct($ID){
	        try{
	            /*获取备份记录信息*/
	            $where[$this->getPk()] = $ID;
	            $Info = $this->where($where)->find();
	            if(!$Info){
	                return ['code'=>0, 'data'=>'', 'msg'=>"备份信息不存在"];
	            }
	            if($Info['backup_type']=='database'||$Info['backup_table']=='db_backup'){
	                //备份一下备份记录表
	                $file_name = self::backUpTable('db_backup');
	                $file_name = substr($file_name,strpos($file_name,ROOT_PATH)+strlen(ROOT_PATH));
	            }
	            $Backs = self::sqlReback($Info);
	            if($Backs === true){
	                addLogInfo(session('UID'), 4, '组员【'.session('MemberUser').'】还原备份记录,ID:'.$ID, 1);
	                //还原一下备份表
	                if($Info['backup_type']=='database'||$Info['backup_table']=='db_backup'){
	                    if(self::sqlReback(['backup_file'=>addslashes($file_name)])){
	                        @unlink(ROOT_PATH.$file_name);
	                    }
	                }
	                return ['code'=>1, 'data'=>'', 'msg'=>'还原成功'];
	            }else{
	                addLogInfo(session('UID'), 4, '组员【'.session('MemberUser').'】还原备份记录,ID:'.$ID, 2);
	
	                return ['code'=>0, 'data'=>'', 'msg'=>$this->error()];
	            }
	        }catch (\Exception $e){
	            return ['code'=>0, 'data'=>'', 'msg'=>$e->getMessage()];
	        }
	    }
	
	    /**
	     * 备份单张表
	     * @param $table
	     */
	    private static function backUpTable($table){
	        //1.获取数据库信息
	        $info = Db::getConfig();
	        $prefix = $info['prefix'];
	        $dbname = $info['database'];
	        //2、组装头部信息
	        header("Content-type:text/html;charset=utf-8");
	        $path = ROOT_PATH.'/data/';
	        $database = $dbname;   //获取当前数据库
	        $info  = "-- ----------------------------\r\n";
	        $info .= "-- 日期:".date("Y-m-d H:i:s",time())."\r\n";
	        $info .= "-- MySQL - 5.5.52-MariaDB : Database - ".$database."\r\n";
	        $info .= "-- ----------------------------\r\n\r\n";
	        $info .= "SET NAMES utf8;\r\nSET FOREIGN_KEY_CHECKS = 0;\r\n\r\n";
	        //3、检查目录是否存在
	        if (is_dir($path)) {
	            if (is_writable($path)) {
	            } else {
	                throw new Exception('目录不可写');
	            }
	        } else {
	            mkdir($path,0777,true);
	        }
	        //4、保存的文件名称
	        $file_name = $path.$table.'_'.date('Ymd_His').'.sql';
	        file_put_contents($file_name, $info, FILE_APPEND);
	        //5、写入数据
	        $val = $table;
	        $sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$val' AND TABLE_SCHEMA='$dbname'";
	        $res = Db::query($sql);
	        $t_table = str_replace($prefix,'',$val);
	        $pk = Db("$t_table")->getPk();
	        $max_num = Db::table("$val")->order("$pk desc")->value("$pk");
	        //查询表结构
	        $info_table = "-- ----------------------------\r\n";
	        $info_table .= "-- Table structure for `$val`\r\n";
	        $info_table .= "-- ----------------------------\r\n\r\n";
	        $info_table .= "DROP TABLE IF EXISTS `$val`;\r\n";
	        if (count($res) >= 1) {
	            $info_table .= "CREATE TABLE `$val` (\n\r\t";
	            foreach ($res as $kk => $vv) {
	                $info_table .= " `".$vv['COLUMN_NAME']."` ";
	                $info_table .= $vv['COLUMN_TYPE'];
	                //是否允许空值
	                if ($vv['IS_NULLABLE'] == 'NO') {
	                    $info_table .= " NOT NULL ";
	                }
	                //判断主键
	                if ($vv['EXTRA']) {
	                    $info_table .= " AUTO_INCREMENT ";
	                    $key = $vv['COLUMN_NAME'];
	                }
	                //字符集
	                if ($vv['COLLATION_NAME']) {
	                    $info_table .= " COLLATE ".$vv['COLLATION_NAME'];
	                }
	                //默认数值
	                if ($vv['COLUMN_DEFAULT']) {
	                    $info_table .= " DEFAULT ".$vv['COLUMN_DEFAULT'];
	                }
	                //注释
	                if ($vv['COLUMN_COMMENT']) {
	                    $info_table .= " COMMENT '".$vv['COLUMN_COMMENT']."'";
	                }
	                $info_table .= ",\n\r\t";
	            }
	            $info_table .= " PRIMARY KEY (`$key`) USING BTREE";
	            $info_table .= "\n\r) ENGINE = MyISAM DEFAULT CHARSET=utf8;\r\n\r\n";
	
	
	            //查询表数据
	            $info_table .= "-- ----------------------------\r\n";
	            $info_table .= "-- Data for the table `$val`\r\n";
	            $info_table .= "-- ----------------------------\r\n\r\n";
	            file_put_contents($file_name,$info_table,FILE_APPEND);
	            $sql_data = "select * from $val";
	            $data = Db::query($sql_data);
	            $count= count($data);
	            if ($count >= 1) {
	                foreach ($data as $key => $value) {
	                    $sqlStr = "INSERT INTO `$val` VALUES (";
	                    foreach($value as $v_d){
	                        $v_d = str_replace("'","\'",$v_d);
	                        $sqlStr .= "'".$v_d."' , ";
	                    }
	                    //需要特别注意对数据的单引号进行转义处理
	                    //去掉最后一个逗号和空格
	                    $sqlStr = substr($sqlStr,0,strlen($sqlStr)-2);
	                    $sqlStr .= ");\r\n";
	                    file_put_contents($file_name,$sqlStr,FILE_APPEND);
	                }
	            }
	        }
	        $info = "\r\n";
	        file_put_contents($file_name,$info,FILE_APPEND);
	        //6、下载数据到本地
	        ob_end_clean();
	        return $file_name;
	    }
	
	    /**
	     * 备份整个数据库
	     * @return bool
	     */
	    private static function backUpDatabase()
	    {
	        //1.获取数据库信息
	        $info = Db::getConfig();
	        $prefix = $info['prefix'];
	        $dbname = $info['database'];
	        //2.获取数据库所有表
	        $tables = Db::query("show tables");
	        //3、组装头部信息
	        header("Content-type:text/html;charset=utf-8");
	        $path = ROOT_PATH.'/data/';
	        $database = $dbname;   //获取当前数据库
	        $info  = "-- ----------------------------\r\n";
	        $info .= "-- 日期:".date("Y-m-d H:i:s",time())."\r\n";
	        $info .= "-- MySQL - 5.5.52-MariaDB : Database - ".$database."\r\n";
	        $info .= "-- ----------------------------\r\n\r\n";
	        $info .= "SET NAMES utf8;\r\nSET FOREIGN_KEY_CHECKS = 0;\r\n\r\n";
	        //4、检查目录是否存在
	        if (is_dir($path)) {
	            if (is_writable($path)) {
	            } else {
	                throw new Exception('目录不可写');
	            }
	        } else {
	            mkdir($path,0777,true);
	        }
	        //5、保存的文件名称
	        $file_name = $path.$database.'_'.date('Ymd_His').'.sql';
	        file_put_contents($file_name, $info, FILE_APPEND);
	        //6、循环表,写入数据
	        foreach ($tables as $k => $v) {
	            $val = $v["Tables_in_$database"];
	            $sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$val' AND TABLE_SCHEMA='$dbname'";
	            $res = Db::query($sql);
	            $t_table = str_replace($prefix,'',$val);
	            $pk = Db("$t_table")->getPk();
	            $max_num = Db::table("$val")->order("$pk desc")->value("$pk");
	            //查询表结构
	            $info_table = "-- ----------------------------\r\n";
	            $info_table .= "-- Table structure for `$val`\r\n";
	            $info_table .= "-- ----------------------------\r\n\r\n";
	            $info_table .= "DROP TABLE IF EXISTS `$val`;\r\n";
	            if (count($res) < 1) {
	                continue;
	            }
	            $info_table .= "CREATE TABLE `$val` (\n\r\t";
	            foreach ($res as $kk => $vv) {
	                $info_table .= " `".$vv['COLUMN_NAME']."` ";
	                $info_table .= $vv['COLUMN_TYPE'];
	                //是否允许空值
	                if ($vv['IS_NULLABLE'] == 'NO') {
	                    $info_table .= " NOT NULL ";
	                }
	                //判断主键
	                if ($vv['EXTRA']) {
	                    $info_table .= " AUTO_INCREMENT ";
	                    $key = $vv['COLUMN_NAME'];
	                }
	                //字符集
	                if ($vv['COLLATION_NAME']) {
	                    $info_table .= " COLLATE ".$vv['COLLATION_NAME'];
	                }
	                //默认数值
	                if ($vv['COLUMN_DEFAULT']) {
	                    $info_table .= " DEFAULT ".$vv['COLUMN_DEFAULT'];
	                }
	                //注释
	                if ($vv['COLUMN_COMMENT']) {
	                    $info_table .= " COMMENT '".$vv['COLUMN_COMMENT']."'";
	                }
	                $info_table .= ",\n\r\t";
	            }
	            $info_table .= " PRIMARY KEY (`$key`) USING BTREE";
	            $info_table .= "\n\r) ENGINE = MyISAM DEFAULT CHARSET=utf8;\r\n\r\n";
	
	            //查询表数据
	            $info_table .= "-- ----------------------------\r\n";
	            $info_table .= "-- Data for the table `$val`\r\n";
	            $info_table .= "-- ----------------------------\r\n\r\n";
	            file_put_contents($file_name,$info_table,FILE_APPEND);
	            $sql_data = "select * from $val";
	            $data = Db::query($sql_data);
	            $count= count($data);
	            if ($count < 1) {
	                continue;
	            }
	            foreach ($data as $key => $value) {
	                $sqlStr = "INSERT INTO `$val` VALUES (";
	                foreach($value as $v_d){
	                    $v_d = str_replace("'","\'",$v_d);
	                    $sqlStr .= "'".$v_d."' ,";
	                }
	                //需要特别注意对数据的单引号进行转义处理
	                //去掉最后一个逗号和空格
	                $sqlStr = substr($sqlStr,0,strlen($sqlStr)-2);
	                $sqlStr .= ");\r\n";
	                file_put_contents($file_name,$sqlStr,FILE_APPEND);
	            }
	            $info = "\r\n";
	            file_put_contents($file_name,$info,FILE_APPEND);
	        }
	        //7、下载数据到本地
	        ob_end_clean();
	        //TODO
	        return $file_name;
	    }
	
	    /**
	     * 还原备份文件
	     * @param $backup
	     */
	    private static function sqlReback($backup){
	        header("content-type:text/html;charset=utf-8");
	        //1.获取数据库信息
	        $info = Db::getConfig();
	        $dbname = $info['database'];
	
	        $file = ROOT_PATH.stripslashes($backup['backup_file']);    //还原的SQL文件
	        if (!file_exists($file)) {
	            throw new Exception("文件不存在");
	        }
	        $sqldata = file_get_contents($file);    //读取数据文件
	        $sqlFormat = self::sqlSplit($sqldata); //获取SQl语句数组
	        foreach ($sqlFormat as $key=>$value){
	            if($value){
	
	                Db::execute($value);
	            }
	
	        }
	        return true;
	    }
	
	    private static function sqlSplit($sql){
	
	        $sql = preg_replace("/TYPE=(InnoDB|MyISAM|MEMORY)( DEFAULT CHARSET=[^; ]+)?/", "ENGINE=\\1 DEFAULT CHARSET=utf8", $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 static function getTables(){
	        //1.获取数据库信息
	        $info = Db::getConfig();
	        $dbname = $info['database'];
	        //2.获取数据库所有表
	        $tables = Db::query("show tables");
	        //3、循环表,写入数据
	        $result = [];
	        foreach ($tables as $k => $v) {
	            $result[] = $v["Tables_in_$dbname"];
	        }
	        return $result;
	    }
	}
展开阅读全文

没有更多推荐了,返回首页