您现在的位置是：网站首页 > 脚本编程>
thinkphp实现数据库备份
简介
数据库备份是一件重要的事情，我们经常到服务器上去备份数据库文件，或者是用phpmyadmin进行数据库备份，小编感觉这样很麻烦所以写下在自己后台实现对数据库的备份。代码有些零乱望各位将就看吧！！
经过艰苦的奋战终于写出来了
效果图：
源码如下：
<?php
/**
* 数据库控制器
* @author 李玉生 <826782664@qq.com>
*/
namespace Admin\Controller;
use Think\Auth;
use Think\Db;
class DatabaseController extends AuthController
{
//列表
public function lists()
{
$db = Db::getInstance();
$tabs = $db->query('SHOW TABLE STATUS');
$total = 0;
foreach ($tabs as $k => $v) {
$tabs[$k]['size'] = format_bytes($v['data_length']+$v['index_length']);
$total+=$v['data_length'] + $v['index_length'];
}
$total=format_bytes($total);
$this->assign("list", $tabs);
$this->assign("total", $total);
$this->assign("tables", count($tabs));
$this->display();
}
//备份
public function backup() {
$db = Db::getInstance();
if (!IS_POST)
$this->error("访问出错啦");
header('Content-Type:application/json; charset=utf-8');
function_exists('set_time_limit') && set_time_limit(0); //防止备份数据过程超时
$tables = empty($_POST['table']) ? array() : $_POST['table'];
if (count($tables) == 0 && !isset($_POST['systemBackup'])) {
die(json_encode(array("status" => 0, "info" => "请先选择要备份的表")));
}
$time = time();
if (isset($_POST['systemBackup'])) {
if ($_SESSION['aid'] != 1) {
die(json_encode(array("status" => 0, "info" => "只有超级管理员账号登录后方可自动备份操作")));
}
$type = "系统自动备份";
$tables = $this->getAllTableName();
$path = DatabaseBackDir."SYSTEM_" . date("Ym");
if (file_exists($path . "_1.sql")) {
die(json_encode(array("status" => 0, "info" => "本月度系统已经进行了自动备份操作")));
}
} else {
$type = "管理员后台手动备份";
$path = DatabaseBackDir."CUSTOM_" . date("Ymd") . "_" . randCode(5);
}
$pre = "# -----------------------------------------------------------\n" .
"# PHP-Amateur database backup files\n" .
"# Type: {$type}\n";
$bdTable = $this->bakupTable($tables); //取得表结构信息
$outPut = "";
$file_n = 1;
$backedTable = array();
foreach ($tables as $table) {
$backedTable[] = $table;
$outPut.="\n\n# 数据库表：{$table} 数据信息\n";
$tableInfo = $db->query("SHOW TABLE STATUS LIKE '{$table}'");
$page = ceil($tableInfo[0]['rows'] / 10000) - 1;
for ($i = 0; $i <= $page; $i++) {
$query = $db->query("SELECT * FROM {$table} LIMIT " . ($i * 10000) . ", 10000");
foreach ($query as $val) {
$temSql = "";
$tn = 0;
$temSql = '';
foreach ($val as $v) {
$temSql.=$tn == 0 ? "" : ",";
$temSql.=$v == '' ? "''" : "'{$v}'";
$tn++;
}
$temSql = "INSERT INTO `{$table}` VALUES ({$temSql});\n";
$sqlNo = "\n# Time: " . date("Y-m-d H:i:s") . "\n" .
"# -----------------------------------------------------------\n" .
"# 当前SQL卷标：#{$file_n}\n# -----------------------------------------------------------\n\n\n";
if ($file_n == 1) {
$sqlNo = "# Description:当前SQL文件包含了表：" . implode("、", $tables) . "的结构信息，表：" . implode("、", $backedTable) . "的数据" . $sqlNo;
} else {
$sqlNo = "# Description:当前SQL文件包含了表：" . implode("、", $backedTable) . "的数据" . $sqlNo;
}
if (strlen($pre) + strlen($sqlNo) + strlen($bdTable) + strlen($outPut) + strlen($temSql) > C("sqlFileSize")) {
$file = $path . "_" . $file_n . ".sql";
$outPut = $file_n == 1 ? $pre . $sqlNo . $bdTable . $outPut : $pre . $sqlNo . $outPut;
file_put_contents($file, $outPut, FILE_APPEND);
$bdTable = $outPut = "";
$backedTable = array();
$backedTable[] = $table;
$file_n++;
}
$outPut.=$temSql;
}
}
}
if (strlen($bdTable . $outPut) > 0) {
$sqlNo = "\n# Time: " . date("Y-m-d H:i:s") . "\n" .
"# -----------------------------------------------------------\n" .
"# 当前SQL卷标：#{$file_n}\n# -----------------------------------------------------------\n\n\n";
if ($file_n == 1) {
$sqlNo = "# Description:当前SQL文件包含了表：" . implode("、", $tables) . "的结构信息，表：" . implode("、", $backedTable) . "的数据" . $sqlNo;
} else {
$sqlNo = "# Description:当前SQL文件包含了表：" . implode("、", $backedTable) . "的数据" . $sqlNo;
}
$file = $path . "_" . $file_n . ".sql";
$outPut = $file_n == 1 ? $pre . $sqlNo . $bdTable . $outPut : $pre . $sqlNo . $outPut;
file_put_contents($file, $outPut, FILE_APPEND);
$file_n++;
}
$time = time() - $time;
echo json_encode(array("status" => 1, "info" => "成功备份所选数据库表结构和数据，本次备份共生成了" . ($file_n - 1) . "个SQL文件。耗时：{$time} 秒", "url" => U('Database/restore')));
}
//还原数据库
public function restore()
{
$data = $this->getSqlFilesList();
$this->assign("list", $data['list']);
$this->assign("total", $data['size']);
$this->assign("files", count($data['list']));
$this->display();
}
//执行还原数据库操作
public function restoreData()
{
$db = Db::getInstance();
function_exists('set_time_limit') && set_time_limit(0); //防止备份数据过程超时
//取得需要导入的sql文件
$files = isset($_SESSION['cacheRestore']) ? $_SESSION['cacheRestore']['files'] : self::getRestoreFiles();
//取得上次文件导入到sql的句柄位置
$position = isset($_SESSION['cacheRestore']['position']) ? $_SESSION['cacheRestore']['position'] : 0;
$execute = 0;
foreach ($files as $fileKey => $sqlFile) {
$file = DatabaseBackDir. $sqlFile;
if (!file_exists($file))
continue;
$file = fopen($file, "r");
$sql = "";
fseek($file, $position); //将文件指针指向上次位置
while (!feof($file)) {
$tem = trim(fgets($file));
//过滤掉空行、以#号注释掉的行、以--注释掉的行
if (empty($tem) || $tem[0] == '#' || ($tem[0] == '-' && $tem[1] == '-'))
continue;
//统计一行字符串的长度
$end = (int) (strlen($tem) - 1);
//检测一行字符串最后有个字符是否是分号，是分号则一条sql语句结束，否则sql还有一部分在下一行中
if ($tem[$end] == ";") {
$sql.=$tem;
$db->execute($sql);
$sql = "";
$execute++;
if ($execute > 500) {
$_SESSION['cacheRestore']['position'] = ftell($file);
$imported = isset($_SESSION['cacheRestore']['imported']) ? $_SESSION['cacheRestore']['imported'] : 0;
$imported+=$execute;
$_SESSION['cacheRestore']['imported'] = $imported;
echo json_encode(array("status" => 1, "info" => '如果导入SQL文件卷较大(多)导入时间可能需要几分钟甚至更久，请耐心等待导入完成，导入期间请勿刷新本页，当前导入进度：<font color="red">已经导入' . $imported . '条Sql</font>', "url" => U('Database/restoreData', array(randCode() => randCode()))));
exit;
}
} else {
$sql.=$tem;
}
}
fclose($file);
unset($_SESSION['cacheRestore']['files'][$fileKey]);
$position = 0;
}
$time = time() - $_SESSION['cacheRestore']['time'];
unset($_SESSION['cacheRestore']);
echo json_encode(array("status" => 1, "info" => "导入成功，耗时：{$time} 秒钟"));
}
//备份文件删除
public function delsqlfiles()
{
if (IS_POST) {
if (empty($_POST['name']) || count($_POST['name']) == 0)
$rs=array("status" => 0, "info" => "请先选择要删除的文件");
$files = $_POST['name'];
delDirAndFile(DatabaseBackDir. $files);
$rs=array("status" => 1, "info" => "已删除：" . $files, "url" => U('Database/restore'));
}
$this->ajaxreturn($rs);
}
/**
+----------------------------------------------------------
* 功能：获取数据库中所有表名
+----------------------------------------------------------
* @return array
+----------------------------------------------------------
*/
public function getAllTableName() {
$db = Db::getInstance();
$tabs = $db->execute('SHOW TABLE STATUS');
$arr = array();
foreach ($tabs as $tab) {
$arr[] = $tab['Name'];
}
unset($tabs);
return $arr;
}
/**
+----------------------------------------------------------
* 功能：读取数据库表结构信息
+----------------------------------------------------------
* @return string
+----------------------------------------------------------
*/
public function bakupTable($table_list) {
$db = Db::getInstance();
$db->execute("SET SQL_QUOTE_SHOW_CREATE = 1");
$outPut = '';
if (!is_array($table_list) || empty($table_list)) {
return false;
}
foreach ($table_list as $table) {
$outPut.="# 数据库表：{$table} 结构信息\n";
$outPut .= "DROP TABLE IF EXISTS `{$table}`;\n";
$tmp = $db->query("SHOW CREATE TABLE `{$table}`");
$outPut .= trim($tmp[0]["create table"]) . ";\n\n";
}
return $outPut;
}
/**
+----------------------------------------------------------
* 功能：读取已经备份SQL文件列表，并按备份时间倒序，名称升序排列
+----------------------------------------------------------
* @return array
+----------------------------------------------------------
*/
public function getSqlFilesList() {
$list = array();
$size = 0;
$handle = opendir(DatabaseBackDir);
while ($file = readdir($handle)) {
if (preg_match('#\.sql$#i', $file)) {
$fp = fopen(DatabaseBackDir. "$file", 'rb');
$bakinfo = fread($fp, 2000);
fclose($fp);
$detail = explode("\n", $bakinfo);
$bk = array();
$bk['name'] = $file;
$bk['type'] = substr($detail[2], 8);
$bk['description'] = substr($detail[3], 14);
$bk['time'] = substr($detail[4], 8);
$_size = filesize(DatabaseBackDir. "$file");
$bk['size'] = format_bytes($_size);
$size+=$_size;
$bk['pre'] = substr($file, 0, strrpos($file, '_'));
$bk['num'] = substr($file, strrpos($file, '_') + 1, strrpos($file, '.') - 1 - strrpos($file, '_'));
$mtime = filemtime(DatabaseBackDir. "$file");
$list[$mtime][$file] = $bk;
}
}
closedir($handle);
krsort($list); //按备份时间倒序排列
$newArr = array();
foreach ($list as $k => $array) {
ksort($array); //按备份文件名称顺序排列
foreach ($array as $arr) {
$newArr[] = $arr;
}
}
unset($list);
return array("list" => $newArr, "size" => format_bytes($size));
}
/**
+----------------------------------------------------------
* 读取要导入的sql文件列表并排序后插入SESSION中
+----------------------------------------------------------
*/
static private function getRestoreFiles() {
$_SESSION['cacheRestore']['time'] = time();
if (empty($_GET['sqlPre']))
die(json_encode(array("status" => 0, "info" => "错误的请求")));
//获取sql文件前缀
$sqlPre = $_GET['sqlPre'];
$handle = opendir(DatabaseBackDir);
$sqlFiles = array();
while ($file = readdir($handle)) {
//获取以$sqlPre为前缀的所有sql文件
if (preg_match('#\.sql$#i', $file) && preg_match('#' . $sqlPre . '#i', $file))
$sqlFiles[] = $file;
}
closedir($handle);
if (count($sqlFiles) == 0)
die(json_encode(array("status" => 0, "info" => "错误的请求，不存在对应的SQL文件")));
//将要还原的sql文件按顺序组成数组，防止先导入不带表结构的sql文件
$files = array();
foreach ($sqlFiles as $sqlFile) {
$k = str_replace(".sql", "", str_replace($sqlPre . "_", "", $sqlFile));
$files[$k] = $sqlFile;
}
unset($sqlFiles, $sqlPre);
ksort($files);
$_SESSION['cacheRestore']['files'] = $files;
return $files;
}
//下载文件
public function download(){
$file=$_GET['file'];
$file_dir =DatabaseBackDir.$file;
$filename=pathinfo($file_dir);
if (!file_exists($file_dir)){
header("Content-type: text/html; charset=utf-8");
echo "File not found!";
exit;
} else {
$file = fopen($file_dir,"r");
header("Content-type: application/octet-stream");
header("Accept-Ranges: bytes");
header("Accept-Length: ".filesize($file_dir));
header("Content-Disposition: attachment; filename=".time().$filename['basename']);
echo fread($file, filesize($file_dir));
fclose($file);
}
}
}
打赏本站，你说多少就多少
本文地址：https://www.qi522.com/view/27.html
来 源：千奇博客
