** 根据网上搜索到的一些代码，自己整合了一下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;
}
}
扫一扫
292
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
