扩展类:protected/extensions/mysql_back.php
<?php
/**
* 功能描述:数据备份+还原操作
* @Author:冰封 QQ:574578944
* @date:2012-09-03
*/
class mysql_back {
private $config;
private $content;
private $dbname = array();
const DIR_SEP = DIRECTORY_SEPARATOR;//操作系统的目录分隔符
public function __construct($config) {//初始化相关属性
header ( "Content-type: text/html;charset=utf-8" );
$this->config = $config;
$this->connect();
}
/*
* 连接数据库
* [url=u.php?uid=168369]@access[/url] private
* [url=u.php?uid=5527]@return[/url] void
*/
private function connect() {
if (mysql_connect($this->config['host']. ':' . $this->config['port'], $this->config['username'], $this->config['userPassword'])) {
mysql_query("SET NAMES '{$this->config['charset']}'");
mysql_query("set interactive_timeout=24*3600");
} else {
$this->throwException('无法连接到数据库!');
}
}
/*
* 设置欲备份的数据库
* @param string $dbname 数据库名(支持多个参数.默认为全部的数据库)
* [url=u.php?uid=168369]@access[/url] public
* [url=u.php?uid=5527]@return[/url] void
*/
public function setdbname($dbname = '*') {
if ($dbname == '*') {
$rs = mysql_list_dbs();
$rows = mysql_num_rows($rs);
if($rows){
for($i=0;$i<$rows;$i++){
$dbname = mysql_tablename($rs,$i);
//这些数据库不需要备份
$block = array('information_schema', 'mysql');
if (!in_array($dbname, $block)) {
$this->dbname[] = $dbname;
}
}
} else {
$this->throwException('没有任何数据库!');
}
} else {
$this->dbname = func_get_args();
}
}
/*
* 获取备份文件
* @param string $fileName 文件名
* [url=u.php?uid=168369]@access[/url] private
* [url=u.php?uid=5527]@return[/url] void
*/
private function getfile($fileName) {
$this->content = '';
$fileName = $this->trimPath($this->config['path'] . self::DIR_SEP .$fileName);
if (is_file($fileName)) {
$ext = strrchr($fileName, '.');
if ($ext == '.sql') {
$this->content = file_get_contents($fileName);
} elseif ($ext == '.gz') {
$this->content = implode('', gzfile($fileName));
} else {
$this->throwException('无法识别的文件格式!');
}
} else {
$this->throwException('文件不存在!');
}
}
/*
* 备份文件
* private
*/
private function setFile() {
$recognize = '';
$recognize = implode('_', $this->dbname);
$fileName = $this->trimPath($this->config['path'] . self::DIR_SEP . $recognize.'_'.date('YmdHis') . '_' . mt_rand(100000000,999999999) .'.sql');
$path = $this->setPath($fileName);
if ($path !== true) {
$this->throwException("无法创建备份目录目录 '$path'");
}
if ($this->config['isCompress'] == 0) {
if (!file_put_contents($fileName, $this->content, LOCK_EX)) {
$this->throwException('写入文件失败,请检查磁盘空间或者权限!');
}
} else {
if (function_exists('gzwrite')) {
$fileName .= '.gz';
if ($gz = gzopen($fileName, 'wb')) {
gzwrite($gz, $this->content);
gzclose($gz);
} else {
$this->throwException('写入文件失败,请检查磁盘空间或者权限!');
}
} else {
$this->throwException('没有开启gzip扩展!');
}
}
if ($this->config['isDownload']) {
$this->downloadFile($fileName);
}
}
/*
* 将路径修正为适合操作系统的形式
* @param string $path 路径名称
* [url=u.php?uid=5527]@return[/url] string
*/
private function trimPath($path) {
return str_replace(array('/', '\\', '//', '\\\\'), self::DIR_SEP, $path);
}
/*
* 设置并创建目录
* @param $fileName 路径
* [url=u.php?uid=5527]@return[/url] mixed
* [url=u.php?uid=168369]@access[/url] private
*/
private function setPath($fileName) {
$dirs = explode(self::DIR_SEP, dirname($fileName));
$tmp = '';
foreach ($dirs as $dir) {
$tmp .= $dir . self::DIR_SEP;
if (!file_exists($tmp) && !@mkdir($tmp, 0777))
return $tmp;
}
return true;
}
/*
* 下载文件
* @param string $fileName 路径
*/
private function downloadFile($fileName) {
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);
}
/*
* 给表名或者数据库名加上``
* @param string $str
* [url=u.php?uid=5527]@return[/url] string
* [url=u.php?uid=168369]@access[/url] private
*/
private function backquote($str) {
return "`{$str}`";
}
/*
* 获取数据库的所有表
* @param string $dbname 数据库名
* [url=u.php?uid=5527]@return[/url] array
* [url=u.php?uid=168369]@access[/url] private
*/
private function getTables($dbname){
$rs = mysql_list_tables($dbname);
$rows = mysql_num_rows($rs);
for ($i=0; $i<$rows; $i++) {
$tables[] = mysql_tablename($rs, $i);
}
return $tables;
}
/**
* 将数组按照字节数分割成小数组
*
* @param array $array 数组
* @param int $byte 字节数
* [url=u.php?uid=5527]@return[/url] array
*/
private function chunkArrayByByte($array, $byte = 5120) {
$i=0;
$sum=0;
foreach ($array as $v) {
$sum += strlen($v);
if ($sum < $byte) {
$return[$i][] = $v;
} elseif ($sum == $byte) {
$return[++$i][] = $v;
$sum = 0;
} else {
$return[++$i][] = $v;
$i++;
$sum = 0;
}
}
return $return;
}
/**
* 备份
*
* [url=u.php?uid=168369]@access[/url] public
*/
public function backup() {
$this->content = '/* This file is created by MySQLReback ' . date('Y-m-d H:i:s') . ' */';
foreach ($this->dbname as $dbname) {
$qdbname = $this->backquote($dbname);
$rs = mysql_query("SHOW CREATE DATABASE {$qdbname}");
if ($row = mysql_fetch_row($rs)) {
//建立数据库
$this->content .= "\r\n /* 创建数据库 {$qdbname} */";
//必须设置一个分割符..单用分号是不够安全的.
$this->content .= "\r\n DROP DATABASE IF EXISTS {$qdbname};/* MySQLReback Separation */ {$row[1]};/* MySQLReback Separation */";
mysql_select_db($dbname);
//取得表
$tables = $this->getTables($dbname);
foreach ($tables as $table) {
$table = $this->backquote($table);
$tableRs = mysql_query("SHOW CREATE TABLE {$table}");
if ($tableRow = mysql_fetch_row($tableRs)) {
//建表
$this->content .= "\r\n /* 创建表结构 {$table} */";
$this->content .= "\r\n DROP TABLE IF EXISTS {$table};/* MySQLReback Separation */ {$tableRow[1]};/* MySQLReback Separation */";
//获取数据
$tableDateRs = mysql_query("SELECT * FROM {$table}");
$valuesArr = array();
$values = '';
while ($tableDateRow = mysql_fetch_row($tableDateRs)) {
//组合INSERT的VALUE
foreach ($tableDateRow as &$v) {
$v = "'" . addslashes($v) . "'"; //别忘了转义.
}
$valuesArr[] = '(' . implode(',', $tableDateRow) . ')';
}
$temp = $this->chunkArrayByByte($valuesArr);
if (is_array($temp)) {
foreach ($temp as $v) {
$values = implode(',', $v) . ';/* MySQLReback Separation */';
//空的数据表就不组合SQL语句了..因为没得组合
if ($values != ';/* MySQLReback Separation */') {
$this->content .= "\r\n /* 插入数据 {$table} */";
$this->content .= "\r\n INSERT INTO {$table} VALUES {$values}";
}
}
}
}
}
}else {
$this->throwException('未能找到数据库!');
}
}
if (!empty($this->content)) {
$this->setFile();
}
return true;
}
/**
* 恢复数据库
* @param string $fileName 文件名
* [url=u.php?uid=168369]@access[/url] public
*/
public function recover($fileName) {
$this->getfile($fileName);
if (!empty($this->content)) {
$content = explode(';/* MySQLReback Separation */', $this->content);
foreach ($content as $sql){
$sql = trim($sql);
//空的SQL会被认为是错误的
if (!empty($sql)) {
$rs = mysql_query($sql);
if ($rs) {
//一定要选择数据库.不然多库恢复会出错
if (strstr($sql, 'CREATE DATABASE')) {
$dbnameArr = sscanf($sql, 'CREATE DATABASE %s');
$dbname = trim($dbnameArr[0], '`');
mysql_select_db($dbname);
}
}else {
$this->throwException('备份文件被损坏!' . mysql_error());
}
}
}
} else {
$this->throwException('无法读取备份文件!');
}
return true;
}
/**
* @抛出异常信息
*/
private function throwException($error) {
throw new Exception($error);
}
}
控制器:DataBackController.php
<?php
/**
* 功能描述:后台数据备份+还原等操作
* @Author:冰封 QQ:574578944
* @date:2012-09-03
*/
class DataBackController extends Controller
{
private $config;
private $mr;
public $layout=false;//设置当前默认布局文件为假
public function __construct(){//初始化相关属性
Yii::import('application.extensions.mysql_back', TRUE);//导入Mysql备份类库
$connect_str = parse_url(Yii::app()->db->connectionString);
$re_str = explode('=', implode('=', explode(';', $connect_str['path'])));//取得数据库IP,数据库名
$this->config = array( //设置参数
'host' => $re_str[1],
'dbname'=> $re_str[3],
'port' => 3306,
'username' => Yii::app()->db->username,
'userPassword' => Yii::app()->db->password,
'dbprefix' => Yii::app()->db->tablePrefix,
'charset' => Yii::app()->db->charset,
'path' => './protected/data/backup/', //定义备份文件的路径
'isCompress' => 1, //是否开启gzip压缩{0为不开启1为开启}
'isDownload' => 0 //压缩完成后是否自动下载{0为不自动1为自动}
);
$this->mr = new mysql_back($this->config);
}
/**
* @显示已备份的数据列表
*/
public function actionShow_data(){
$path = $this->config['path'];
$fileArr = $this->MyScandir($path);
$list = array();
foreach ($fileArr as $key => $value){
if($key > 1){
//获取文件创建时间
$fileTime = date('Y-m-d H:i',filemtime($path . $value));
$fileSize = filesize($path.$value)/1024;
//获取文件大小
$fileSize = $fileSize < 1024 ? number_format($fileSize,2).' KB':
number_format($fileSize/1024,2).' MB';
//构建列表数组
$list[]=array(
'name' => $value,
'time' => $fileTime,
'size' => $fileSize
);
}
}
$this->render('/site/data_back',array('data'=>$list));
}
/**
* @备份数据库
*/
public function actionBackup(){
$this->mr->setDBName($this->config['dbname']);
if($this->mr->backup()){
messages::show_msg($this->createUrl('databack/show_data'), '数据库备份成功!!');
}else{
messages::show_msg($this->createUrl('databack/show_data'), '数据库备份失败!!');
}
}
/**
* @删除数据备份
*/
public function actionDelete_back(){
if(unlink($this->config['path'] . $_GET['file'])){
messages::show_msg($this->createUrl('databack/show_data'), '删除备份成功!!');
}else{
messages::show_msg($this->createUrl('databack/show_data'), '删除备份失败!!');
}
}
/**
* @下载备份文件
*/
public function actionDownloadbak(){
$this->download($this->config['path'] . $_GET['file']);
}
/**
* @还原数据库
*/
public function actionrecover(){
$this->mr->setDBName($this->config['dbname']);
if($this->mr->recover($_GET['file'])){
messages::show_msg($this->createUrl('databack/show_data'), '数据还原成功!!');
}else{
messages::show_msg($this->createUrl('databack/show_data'), '数据还原失败!!');
}
}
/**
* @获取目录下文件数组
*/
public function MyScandir($FilePath='./',$Order=0){
$FilePath = opendir($FilePath);
while($filename = readdir($FilePath)) {
$fileArr[] = $filename;
}
$Order == 0 ? sort($fileArr) : rsort($fileArr);
return $fileArr;
}
/**
* @公共下载方法
*/
public function download($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-streamextension');
header('Content-Length: '.filesize($filename));
header('Content-Disposition: attachment; filename='.basename($filename));
readfile($filename);
}
}
视图文件:data_back.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3c.org/TR/1999/REC-html401-19991224/loose.dtd">
<HTML xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php echo Yii::app()->name;?></title>
<meta name="keywords" content="<?php echo Yii::app()->name;?>" />
<meta name="description" content="<?php echo Yii::app()->name;?>" />
<link rel="icon" href="favicon.ico" type="image/x-icon" />
<link type="text/css" rel="stylesheet" href="<?php echo Yii::app()->request->baseUrl; ?>/style/admin/css/common.css" />
</head>
<body>
<strong>数据备份
<table width="100%" border="1" align="center" cellpadding="8" cellspacing="1" bordercolor="#CFCFCF" style="margin-top:8px;">
<tr>
<td colspan="6" background="<?php echo Yii::app()->request->baseUrl; ?>/style/admin/images/tbg.gif">说明:点击备份可将所有的数据表结构和数据完全备份到项目protected/data/backup/目录下(Linux下注意将此目录权限设置为777) </td>
</tr>
<tr bgcolor="#f4fbf4" height="22">
<td height="26" align="center" bgcolor="#FAFAF1" ><span class="STYLE1">备份记录</span></td>
<td height="26" align="center" bgcolor="#FAFAF1" >文件大小</td>
<td height="26" align="center" bgcolor="#FAFAF1" >备份日期</td>
<td height="26" align="center" bgcolor="#FAFAF1" >基本操作</td>
</tr>
<?php foreach($data as $val){ ?>
<tr onMouseMove="javascript:this.bgColor='#FCFDEE';" onMouseOut="javascript:this.bgColor='#FFFFFF';" height="22" >
<td width="32%"><?php echo $val['name']; ?></td>
<td width="17%" align="center"><?php echo $val['size']; ?></td>
<td width="28%" align="center"><?php echo $val['time']; ?></td>
<td width="23%" align="center">
<?php echo CHtml::link('删除',
array('/databack/delete_back',
'file'=>$val['name']
),
array(
"title"=>"删除",
'onclick'=>"{if(confirm('您真的要删除吗?')){return true;}return false;}"
)
);?>
<?php echo CHtml::link('下载', array('/databack/downloadbak','file'=>$val['name']),array("title"=>"点击下载"));?>
<?php echo CHtml::link('还原',
array('/databack/recover',
'file'=>$val['name']
),
array(
"title"=>"还原",
'onclick'=>"{if(confirm('确定要还原吗?')){return true;}return false;}"
)
);?>
</td>
</tr>
<?php } ?>
<tr onMouseMove="javascript:this.bgColor='#FCFDEE';" onMouseOut="javascript:this.bgColor='#FFFFFF';" height="22" >
<td colspan="4" align="center">
<?php $form=$this->beginWidget('CActiveForm', array(
'id'=>'backup-form',
'action'=>$this->createUrl('databack/backup'),
));
?>
<?php echo CHtml::submitButton('一键备份',array('class'=>'submit','name'=>'backup')); ?>
<?php $this->endWidget(); ?>
</td>
</tr>
</table>
</body>
</html>