您现在的位置是:网站首页 > 脚本编程>
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
来 源:千奇博客