tp5 mysql数据库备份还原_tp5实现数据库拷贝

tp5实现数据库拷贝

龙行    PHP    2019-1-7    2051    0评论

一个数据库拷贝实现功能,还是挺好的

backsql拷贝实现类

namespace expand;

class Baksql {

private $config=[];

private $handler;

private $tables = array();//需要备份的表

private $begin; //开始时间

private $error;//错误信息

public function __construct($config) {

$config['path'] = WEB_PATH."/databakss/"; //拷贝地址默认目录

$config["sqlbakname"]=date("YmdHis",time()).".sql";//默认保存文件名

$this->config = $config;

$this->begin = microtime(true);

header("Content-type: text/html;charset=utf-8");

$this->connect();

}

//首次进行pdo连接

private function connect() {

try{

$this->handler =new \PDO("{$this->config['type']}:host={$this->config['hostname']};port={$this->config['hostport']};dbname={$this->config['database']};",

$this->config['username'],

$this->config['password'],

array(

\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$this->config['charset']};",

\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,

\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC

));

}catch (PDOException $e) {

die ("Error!: " . $e->getMessage() . "
");

}

}

/**

* 查询

* @param string $sql

* @return mixed

*/

private function query($sql = '')

{

$stmt = $this->handler->query($sql);

$stmt->setFetchMode(\PDO::FETCH_NUM);

$list = $stmt->fetchAll();

return $list;

}

/**

* 获取全部表

* @param string $dbName

* @return array

*/

private function get_dbname($dbName = '*') {

$sql = 'SHOW TABLES';

$list = $this->query($sql);

$tables = array();

foreach ($list as $value){

$tables[] = $value[0];

}

return $tables;

}

/**

* 获取表定义语句

* @param string $table

* @return mixed

*/

private function get_dbhead($table = '')

{

$sql = "SHOW CREATE TABLE `{$table}`";

$ddl = $this->query($sql)[0][1] . ';';

return $ddl;

}

/**

* 获取表数据

* @param string $table

* @return mixed

*/

private function get_dbdata($table = '')

{

$sql = "SHOW COLUMNS FROM `{$table}`";

$list = $this->query($sql);

//字段

$columns = '';

//需要返回的SQL

$query = '';

foreach ($list as $value){

$columns .= "`{$value[0]}`,";

}

$columns = substr($columns, 0, -1);

$data = $this->query("SELECT * FROM `{$table}`");

foreach ($data as $value){

$dataSql = '';

foreach ($value as $v){

$dataSql .= "'{$v}',";

}

$dataSql = substr($dataSql, 0, -1);

$query .= "INSERT INTO `{$table}` ({$columns}) VALUES ({$dataSql});\r\n";

}

return $query;

}

/**

* 写入文件

* @param array $tables

* @param array $ddl

* @param array $data

*/

private function writeToFile($tables = array(), $ddl = array(), $data = array())

{

$str = "/*\r\nMySQL Database Backup Tools\r\n";

$str .= "Server:{$this->config['hostname']}:{$this->config['hostport']}\r\n";

$str .= "Database:{$this->config['database']}\r\n";

$str .= "Data:" . date('Y-m-d H:i:s', time()) . "\r\n*/\r\n";

$str .= "SET FOREIGN_KEY_CHECKS=0;\r\n";

$i = 0;

foreach ($tables as $table)

{

$str .= "-- ----------------------------\r\n";

$str .= "-- Table structure for {$table}\r\n";

$str .= "-- ----------------------------\r\n";

$str .= "DROP TABLE IF EXISTS `{$table}`;\r\n";

$str .= $ddl[$i] . "\r\n";

$str .= "-- ----------------------------\r\n";

$str .= "-- Records of {$table}\r\n";

$str .= "-- ----------------------------\r\n";

$str .= $data[$i] . "\r\n";

$i++;

}

if(!file_exists($this->config['path'])){mkdir($this->config['path']);}

return file_put_contents($this->config['path'].$this->config['sqlbakname'], $str) ? '备份成功!花费时间' . round(microtime(true) - $this->begin,2) . 'ms' : '备份失败!';

}

/**

* 设置要备份的表

* @param array $tables

*/

private function setTables($tables = array())

{

if (!empty($tables) && is_array($tables)){

//备份指定表

$this->tables = $tables;

}else{

//备份全部表

$this->tables = $this->get_dbname();

}

}

/**

* 备份

* @param array $tables

* @return bool

*/

public function backup($tables = array())

{

//存储表定义语句的数组

$ddl = array();

//存储数据的数组

$data = array();

$this->setTables($tables);

if (!empty($this->tables)){

foreach ($this->tables as $table){

$ddl[] = $this->get_dbhead($table);

$data[] = $this->get_dbdata($table);

}

//开始写入

return $this->writeToFile($this->tables, $ddl, $data);

}else{

$this->error = '数据库中没有表!';

return false;

}

}

/**

* 错误信息

* @return mixed

*/

public function getError()

{

return $this->error;

}

public function restore($filename = '')

{

$path=$this->config['path'].$filename;

if (!file_exists($path))

{

$this->error('SQL文件不存在!');

return false;

}

else

{

$sql = $this->parseSQL($path);

//dump($sql);die;

try

{

$this->handler->exec($sql);

return '还原成功!花费时间'. round(microtime(true) - $this->begin,2) . 'ms';

}

catch (PDOException $e)

{

$this->error = $e->getMessage();

return false;

}

}

}

/**

* 解析SQL文件为SQL语句数组

* @param string $path

* @return array|mixed|string

*/

private function parseSQL($path = '')

{

$sql = file_get_contents($path);

$sql = explode("\r\n", $sql);

//先消除--注释

$sql = array_filter($sql, function ($data)

{

if (empty($data) || preg_match('/^--.*/', $data))

{

return false;

}

else

{

return true;

}

});

$sql = implode('', $sql);

//删除/**/注释

$sql = preg_replace('/\/\*.*\*\//', '', $sql);

return $sql;

}

/**

* 下载备份

* @param string $fileName

* @return array|mixed|string

*/

public function downloadFile($fileName) {

$fileName=$this->config['path'].$fileName;

if (file_exists($fileName)){

ob_end_clean();

header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

header('Content-Description: File Transfer');

header('Content-Type: application/octet-stream');

header('Content-Length: ' . filesize($fileName));

header('Content-Disposition: attachment; filename=' . basename($fileName));

readfile($fileName);

}else{

$this->error="文件有错误！";

}

}

/**

* 获取文件是时间

* @param string $file

* @return string

*/

private function getfiletime($file){

$path=$this->config['path'].$file;

$a = filemtime($path);

$time = date("Y-m-d H:i:s", $a);

return $time;

}

/**

* 获取文件是大小

* @param string $file

* @return string

*/

private function getfilesize($file){

$perms=stat($this->config['path'].$file);

$size = $perms['size'];

$a = ['B', 'KB', 'MB', 'GB', 'TB'];

$pos = 0;

while ($size >= 1024) {

$size /= 1024;

$pos++;

}

return round($size, 2). $a[$pos];

}

/**

* 获取文件列表

* @param string $Order 级别

* @return array

*/

public function get_filelist($Order = 0) {

$FilePath=$this->config['path'];

$FilePath = opendir($FilePath);

$FileAndFolderAyy=array();

$i=1;

while (false !== ($filename = readdir($FilePath))) {

if ($filename!="." && $filename!=".."){

$i++;

$FileAndFolderAyy[$i]['name'] = $filename;

$FileAndFolderAyy[$i]['time'] = $this->getfiletime($filename);

$FileAndFolderAyy[$i]['size'] = $this->getfilesize($filename);

}

}

$Order == 0 ? sort($FileAndFolderAyy) : rsort($FileAndFolderAyy);

return $FileAndFolderAyy;

}

public function delfilename($filename){

$path=$this->config['path'].$filename;

if (@unlink($path)) {return '删除成功';}

}

}

?>

使用方法

namespace app\admin\controller;

use think\Controller;

use think\db\Query;

class Database extends Common

{

public function _initialize()

{

parent::_initialize();

}

/**

* @Description: todo(数据库列表)

*/

public function index()

{

$dataList = db()->query("SHOW TABLE STATUS");

$this->assign('dataList', $dataList);

return $this->fetch();

}

/**

* @Title: backup

* @Description: todo(备份数据库)

* @throws

*/

public function backup()

{

if (request()->isPost()){

$id = input('id');

if (isset($id) && !empty($id)){

$table_arr = explode(',', $id); //备份数据表

$sql = new \expand\Baksql(\think\Config::get("database"));

$res = $sql->backup($table_arr);

return ajaxReturn($res, url('index'));

}

}

}

/**

* @Title: reduction

* @Description: todo(备份列表)

* @throws

*/

public function reduction()

{

$sql = new \expand\Baksql(\think\Config::get("database"));

$dataList = $sql->get_filelist();

$this->assign('dataList', $dataList);

return $this->fetch();

}

/**

* @Title: restore

* @Description: todo(还原数据库)

* @throws

*/

public function restore()

{

if (request()->isPost()){

$name = input('id');

$sql = new \expand\Baksql(\think\Config::get("database"));

$res = $sql->restore($name);

return ajaxReturn($res, url('reduction'));

}

}

/**

* @Title: dowonload

* @Description: todo(下载备份)

* @throws

*/

public function dowonload()

{

$table = input('table');

$sql = new \expand\Baksql(\think\Config::get("database"));

$sql->downloadFile($table);

}

/**

* @Title: delete

* @Description: todo(删除备份)

* @throws

*/

public function delete()

{

if (request()->isPost()){

$name = input('id');

$sql = new \expand\Baksql(\think\Config::get("database"));

$res = $sql->delfilename($name);

return ajaxReturn($res, url('reduction'));

}

}

}

评论一下

赞助站长

赞助站长X

版权申明：此文如未标注转载均为本站原创，自由转载请表明出处《龙行博客》。

本文网址：https://www.liaotaoo.cn/149.html