tp5备份数据库
<?php
/**
* 备份数据库的扩展类
*/
class Baksql {
private $config = [];
private $handler;
private $tables = array(); //需要备份的表
private $begin; //开始时间
private $error; //错误信息
public function __construct($config, $uniacid) {
$fileName = ROOT_PATH . 'public' . DS . 'static' . DS . 'data/'; //默认目录
if (!empty($uniacid)) {
$fileName .= $uniacid . '/';
}
$dir = iconv("UTF-8", "GBK", $fileName);
if (!file_exists($dir)) {
mkdir($dir, 0777, true);
}
$config['path'] = $fileName; //默认目录
$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() . "<br/>");
}
}
/**
* 查询
* @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);
echo '还原成功!花费时间', 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 '删除成功';
}
}
}
?>
<?php
namespace lib;
use think\Loader;
/**
* Description of Qrcode
*
* @author lsf
*/
class Backup {
private $sql;
//put your code here
public function __construct($uniacid="") {
Loader::import("baksql/Baksql", EXTEND_PATH);
$this->sql = new \Baksql(\think\Config::get("database"),$uniacid);
}
//数据库备份
public function bak($type = "", $name = "", $table = array()) {
switch ($type) {
case "backup": //备份
$res = $this->sql->backup($table);
if ($res) {
return array("success" => true, "message" => "", "data" => $res);
} else {
return array("success" => false, "message" => $this->sql->getError());
}
case "dowonload": //下载
return $this->sql->downloadFile($name);
case "restore": //还原
$info = $this->sql->restore($name);
return array("success" => true, "data" => $info);
case "del": //删除
return $this->sql->delfilename($name);
default: //获取备份文件列表
return array("success" => true, "data" => $this->sql->get_filelist());
}
}
}
