备份数据库
- WBOY原創
- 2016-06-07 11:45:35890瀏覽
全写在模块内了，可备份整个库，也可以选择部分表备份
纠正一处错误，361行empty 用错了<?php <br />
<br>
class BaksqlAction extends CommonAction {<br>
<br>
public $config = ''; //相关配置<br>
public $model = ''; //实例化一个model<br>
public $content; //内容<br>
public $dbName = ''; //数据库名<br>
public $dir_sep = '/'; //路径符号<br>
<br>
//初始化数据<br>
<br>
function _initialize() {<br>
parent::_initialize();<br>
header("Content-type: text/html;charset=utf-8");<br>
set_time_limit(0); //不超时<br>
ini_set('memory_limit','500M');<br>
$this->config = array(<br>
'path' => C('DB_BACKUP'), //备份文件存在哪里<br>
'isCompress' => 0, //是否开启gzip压缩 【未测试】<br>
'isDownload' => 0 //备份完成后是否下载文件 【未测试】<br>
);<br>
$this->dbName = C('DB_NAME'); //当前数据库名称<br>
$this->model = new Model();<br>
//$sql = 'set interactive_timeout=24*3600'; //空闲多少秒后 断开链接<br>
//$this->model>execute($sql);<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 已备份数据列表<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
function index() {<br>
$path = $this->config['path'];<br>
$fileArr = $this->MyScandir($path);<br>
foreach ($fileArr as $key => $value) {<br>
if ($key > 1) {<br>
//获取文件创建时间<br>
$fileTime = date('Y-m-d H:i:s', filemtime($path . '/' . $value));<br>
$fileSize = filesize($path . '/' . $value) / 1024;<br>
//获取文件大小<br>
$fileSize = $fileSize
number_format($fileSize / 1024, 2) . ' MB';<br>
//构建列表数组<br>
$list[] = array(<br>
'name' => $value,<br>
'time' => $fileTime,<br>
'size' => $fileSize<br>
);<br>
}<br>
}<br>
$this->assign('list', $list);<br>
$this->display();<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 获取数据表<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
function tablist() {<br>
$list = $this->model->query("SHOW TABLE STATUS FROM {$this->dbName}"); //得到表的信息<br>
//echo $Backup->getLastSql();<br>
$this->assign('list', $list);<br>
$this->display();<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 备份整个数据库<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
function backall() {<br>
$tables = $this->getTables();<br>
if ($this->backup($tables)) {<br>
$this->success('数据库备份成功！', '/public/ok');<br>
} else {<br>
$this->error('数据库备份失败！');<br>
}<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 按表备份，可批量<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
function backtables() {<br>
$tab = $_REQUEST['tab'];<br>
if (is_array($tab))<br>
$tables = $tab;<br>
else<br>
$tables[] = $tab;<br>
if ($this->backup($tables)) {<br>
if (is_array($tab))<br>
$this->success('数据库备份成功！');<br>
else<br>
$this->success('数据库备份成功！', '/public/ok');<br>
} else {<br>
$this->error('数据库备份失败！');<br>
}<br>
}<br>
<br>
//还原数据库<br>
function recover() {<br>
if ($this->recover_file($_GET['file'])) {<br>
$this->success('数据还原成功！', '/public/ok');<br>
} else {<br>
$this->error('数据还原失败！');<br>
}<br>
}<br>
<br>
//删除数据备份<br>
function deletebak() {<br>
if (unlink($this->config['path'] . $this->dir_sep . $_GET['file'])) {<br>
$this->success('删除备份成功！', '/public/ok');<br>
} else {<br>
$this->error('删除备份失败！');<br>
}<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 下载备份文件<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
function downloadBak() {<br>
$file_name = $_GET['file'];<br>
$file_dir = $this->config['path'];<br>
if (!file_exists($file_dir . "/" . $file_name)) { //检查文件是否存在<br>
return false;<br>
exit;<br>
} else {<br>
$file = fopen($file_dir . "/" . $file_name, "r"); // 打开文件<br>
// 输入文件标签<br>
header('Content-Encoding: none');<br>
header("Content-type: application/octet-stream");<br>
header("Accept-Ranges: bytes");<br>
header("Accept-Length: " . filesize($file_dir . "/" . $file_name));<br>
header('Content-Transfer-Encoding: binary');<br>
header("Content-Disposition: attachment; filename=" . $file_name); //以真实文件名提供给浏览器下载<br>
header('Pragma: no-cache');<br>
header('Expires: 0');<br>
//输出文件内容<br>
echo fread($file, filesize($file_dir . "/" . $file_name));<br>
fclose($file);<br>
exit;<br>
}<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 获取 目录下文件数组<br>
* +------------------------------------------------------------------------<br>
* * @ $FilePath 目录路径<br>
* * @ $Order 排序<br>
* +------------------------------------------------------------------------<br>
* * @ 获取指定目录下的文件列表，返回数组<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
private function MyScandir($FilePath = './', $Order = 0) {<br>
$FilePath = opendir($FilePath);<br>
while ($filename = readdir($FilePath)) {<br>
$fileArr[] = $filename;<br>
}<br>
$Order == 0 ? sort($fileArr) : rsort($fileArr);<br>
return $fileArr;<br>
}<br>
<br>
/* * ******************************************************************************************** */<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 读取备份文件<br>
* +------------------------------------------------------------------------<br>
* * @ $fileName 文件名<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
private function getFile($fileName) {<br>
$this->content = '';<br>
$fileName = $this->trimPath($this->config['path'] . $this->dir_sep . $fileName);<br>
if (is_file($fileName)) {<br>
$ext = strrchr($fileName, '.');<br>
if ($ext == '.sql') {<br>
$this->content = file_get_contents($fileName);<br>
} elseif ($ext == '.gz') {<br>
$this->content = implode('', gzfile($fileName));<br>
} else {<br>
$this->error('无法识别的文件格式!');<br>
}<br>
} else {<br>
$this->error('文件不存在!');<br>
}<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 把数据写入磁盘<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
private function setFile() {<br>
$recognize = '';<br>
$recognize = $this->dbName;<br>
$fileName = $this->trimPath($this->config['path'] . $this->dir_sep . $recognize . '_' . date('YmdHis') . '_' . mt_rand(100000000, 999999999) . '.sql');<br>
$path = $this->setPath($fileName);<br>
if ($path !== true) {<br>
$this->error("无法创建备份目录目录 '$path'");<br>
}<br>
if ($this->config['isCompress'] == 0) {<br>
if (!file_put_contents($fileName, $this->content, LOCK_EX)) {<br>
$this->error('写入文件失败,请检查磁盘空间或者权限!');<br>
}<br>
} else {<br>
if (function_exists('gzwrite')) {<br>
$fileName .= '.gz';<br>
if ($gz = gzopen($fileName, 'wb')) {<br>
gzwrite($gz, $this->content);<br>
gzclose($gz);<br>
} else {<br>
$this->error('写入文件失败,请检查磁盘空间或者权限!');<br>
}<br>
} else {<br>
$this->error('没有开启gzip扩展!');<br>
}<br>
}<br>
if ($this->config['isDownload']) {<br>
$this->downloadFile($fileName);<br>
}<br>
}<br>
<br>
private function trimPath($path) {<br>
return str_replace(array('/', '\\', '//', '\\\\'), $this->dir_sep, $path);<br>
}<br>
<br>
private function setPath($fileName) {<br>
$dirs = explode($this->dir_sep, dirname($fileName));<br>
$tmp = '';<br>
foreach ($dirs as $dir) {<br>
$tmp .= $dir . $this->dir_sep;<br>
if (!file_exists($tmp) && !@mkdir($tmp, 0777))<br>
return $tmp;<br>
}<br>
return true;<br>
}<br>
<br>
//未测试<br>
private function downloadFile($fileName) {<br>
ob_end_clean();<br>
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");<br>
header('Content-Description: File Transfer');<br>
header('Content-Type: application/octet-stream');<br>
header('Content-Length: ' . filesize($fileName));<br>
header('Content-Disposition: attachment; filename=' . basename($fileName));<br>
readfile($fileName);<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 给字符串添加 ` `<br>
* +------------------------------------------------------------------------<br>
* * @ $str 字符串<br>
* +------------------------------------------------------------------------<br>
* * @ 返回 `$str`<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
private function backquote($str) {<br>
return "`{$str}`";<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 获取数据库的所有表<br>
* +------------------------------------------------------------------------<br>
* * @ $dbName 数据库名称<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
private function getTables($dbName = '') {<br>
if (!empty($dbName)) {<br>
$sql = 'SHOW TABLES FROM ' . $dbName;<br>
} else {<br>
$sql = 'SHOW TABLES ';<br>
}<br>
$result = $this->model->query($sql);<br>
$info = array();<br>
foreach ($result as $key => $val) {<br>
$info[$key] = current($val);<br>
}<br>
return $info;<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 把传过来的数据 按指定长度分割成数组<br>
* +------------------------------------------------------------------------<br>
* * @ $array 要分割的数据<br>
* * @ $byte 要分割的长度<br>
* +------------------------------------------------------------------------<br>
* * @ 把数组按指定长度分割,并返回分割后的数组<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
private function chunkArrayByByte($array, $byte = 5120) {<br>
$i = 0;<br>
$sum = 0;<br>
$return = array();<br>
foreach ($array as $v) {<br>
$sum += strlen($v);<br>
if ($sum
$return[$i][] = $v;<br>
} elseif ($sum == $byte) {<br>
$return[++$i][] = $v;<br>
$sum = 0;<br>
} else {<br>
$return[++$i][] = $v;<br>
$i++;<br>
$sum = 0;<br>
}<br>
}<br>
return $return;<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 备份数据 { 备份每张表、视图及数据 }<br>
* +------------------------------------------------------------------------<br>
* * @ $tables 需要备份的表数组<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
private function backup($tables) {<br>
if (empty($tables))<br>
$this->error('没有需要备份的数据表!');<br>
$this->content = '/* This file is created by MySQLReback ' . date('Y-m-d H:i:s') . ' */';<br>
foreach ($tables as $i => $table) {<br>
$table = $this->backquote($table); //为表名增加 ``<br>
$tableRs = $this->model->query("SHOW CREATE TABLE {$table}"); //获取当前表的创建语句<br>
if (!empty($tableRs[0]["Create View"])) {<br>
$this->content .= "\r\n /* 创建视图结构 {$table} */";<br>
$this->content .= "\r\n DROP VIEW IF EXISTS {$table};/* MySQLReback Separation */ " . $tableRs[0]["Create View"] . ";/* MySQLReback Separation */";<br>
}<br>
if (!empty($tableRs[0]["Create Table"])) {<br>
$this->content .= "\r\n /* 创建表结构 {$table} */";<br>
$this->content .= "\r\n DROP TABLE IF EXISTS {$table};/* MySQLReback Separation */ " . $tableRs[0]["Create Table"] . ";/* MySQLReback Separation */";<br>
$tableDateRow = $this->model->query("SELECT * FROM {$table}");<br>
$valuesArr = array();<br>
$values = '';<br>
if (false != $tableDateRow) {<br>
foreach ($tableDateRow as &$y) {<br>
foreach ($y as &$v) {<br>
if ($v=='') //纠正empty 为0的时候 返回tree<br>
$v = 'null'; //为空设为null<br>
else<br>
$v = "'" . mysql_escape_string($v) . "'"; //非空 加转意符<br>
}<br>
$valuesArr[] = '(' . implode(',', $y) . ')';<br>
}<br>
}<br>
$temp = $this->chunkArrayByByte($valuesArr);<br>
if (is_array($temp)) {<br>
foreach ($temp as $v) {<br>
$values = implode(',', $v) . ';/* MySQLReback Separation */';<br>
if ($values != ';/* MySQLReback Separation */') {<br>
$this->content .= "\r\n /* 插入数据 {$table} */";<br>
$this->content .= "\r\n INSERT INTO {$table} VALUES {$values}";<br>
}<br>
}<br>
}<br>
// dump($this->content);<br>
// exit;<br>
}<br>
}<br>
<br>
if (!empty($this->content)) {<br>
$this->setFile();<br>
}<br>
return true;<br>
}<br>
<br>
/* -<br>
* +------------------------------------------------------------------------<br>
* * @ 还原数据<br>
* +------------------------------------------------------------------------<br>
* * @ $fileName 文件名<br>
* +------------------------------------------------------------------------<br>
*/<br>
<br>
private function recover_file($fileName) {<br>
$this->getFile($fileName);<br>
if (!empty($this->content)) {<br>
$content = explode(';/* MySQLReback Separation */', $this->content);<br>
foreach ($content as $i => $sql) {<br>
$sql = trim($sql);<br>
if (!empty($sql)) {<br>
$mes = $this->model->execute($sql);<br>
if (false === $mes) { //如果 null 写入失败，换成 ''<br>
$table_change = array('null' => '\'\'');<br>
$sql = strtr($sql, $table_change);<br>
$mes = $this->model->execute($sql);<br>
}<br>
if (false === $mes) { //如果遇到错误、记录错误<br>
$log_text = '以下代码还原遇到问题:';<br>
$log_text.="\r\n $sql";<br>
set_log($log_text);<br>
}<br>
}<br>
}<br>
} else {<br>
$this->error('无法读取备份文件!');<br>
}<br>
return true;<br>
}<br>
<br>
}<br>
?>
AD：真正免费，域名+虚机+企业邮箱=0元