项目中需要对Yii下的项目的数据库实现备份，找了很多方法，都是查数据库写文件的方法。看来也只能先这样考虑了。比较担忧数据库很大的时候会不会备份和还原超时或者失败。建议专业人士还是直接使用mysqldump的方法或者其他mysql自带的方法进行数据库的备份。一些数据库工具也带有备份的功能。在这里抛砖引玉，请求各位大神的指点或者建议.
<?php
/**
* 功能描述:数据备份+还原操作
* @Author:
* @date:
*/
class MysqlBack {
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();
}
/*
* 连接数据库
* @access private
* @return void
*/
private function connect(){
if(mysql_connect($this->config['host']. ':' . $this->config['port'], $this->config['db_username'], $this->config['db_password'])){
mysql_query("SET NAMES '{$this->config['charset']}'");
mysql_query("set interactive_timeout=24*3600");
}else{
$this->throwException('无法连接到数据库!');
}
}
/*
* 设置欲备份的数据库
* @param string $dbname 数据库名(支持多个参数.默认为全部的数据库)
* @access public
* @return 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 文件名
* @access private
* @return 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('文件不存在!');
}
}
/*
* 备份文件
* @access 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 路径名称
* @return string
*/
private function trimPath($path){
return str_replace(array('/', '\\', '//', '\\\\'), self::DIR_SEP, $path);
}
/*
* 设置并创建目录
* @param $fileName 路径
* @return mixed
* @access 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
* @return string
* @access private
*/
private function backquote($str){
return "`{$str}`";
}
/*
* 获取数据库的所有表
* @param string $dbname 数据库名
* @return array
* @access private
*/
private function getTables($dbname){
// $rs = mysql_list_tables($dbname); //该方法已弃用
$rs = mysql_query("show tables");
$rows = mysql_num_rows($rs);
for ($i=0; $i<$rows; $i++){
$tables[] = mysql_tablename($rs, $i);
}
return $tables;
}
/**
* 将数组按照字节数分割成小数组
*
* @param array $array 数组
* @param int $byte 字节数
* @return array
*/
private function chunkArrayByByte($array, $byte = 5120){
$i=0;
$sum=0;
$return = '';
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;
}
/**
* 备份
*
* @access public
*/
public function backup(){
$this->content = '/* This file is created by MySQLReback ' . date('Y-m-d H:i:s') . ' */';
$this->content .= $this->fkHeader() ."/* MySQLReback Separation */";
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 */ USE {$qdbname};/* 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->content .= $this->fkFooter();
$this->setFile();
}
return true;
}
/**
* 恢复数据库
* @param string $fileName 文件名
* @access 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);
}
//备份还原的开始的时候。关闭外键和索引
public function fkHeader(){
$str = "SET @ORIG_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;";
/* $str = "SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;";
$str .= "SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;";
$str .= "SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;";
$str .= "SET NAMES utf8;";
$str .= "SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;";
$str .= "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;";
$str .= "SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';";
$str .= "SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;";*/
return $str;
}
//备份还原的结束的时候。开启外键和索引
public function fkFooter(){
$str = "SET FOREIGN_KEY_CHECKS=@ORIG_FOREIGN_KEY_CHECKS;";
/* $str = "SET SQL_MODE=@OLD_SQL_MODE;";
$str .= "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;";
$str .= "SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;";
$str .= "SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;";
$str .= "SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;";
$str .= "SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;";
$str .= "SET SQL_NOTES=@OLD_SQL_NOTES;";*/
return $str;
}
}
<?php
/**
* 功能描述:后台数据备份+还原等操作
*/
class DataBackController extends UController{
private $config;
private $mr;
// public $layout=false;//设置当前默认布局文件为假
//初始化相关属性
/* public function __construct(){
Yii::import('application.extensions.MysqlBack', 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,
'db_username' => Yii::app()->db->username,
'db_password' => Yii::app()->db->password,
'db_prefix' => Yii::app()->db->tablePrefix,
'charset' => Yii::app()->db->charset,
'path' => Yii::app()->basePath . '/../protected/data/backup/', //定义备份文件的路径
'isCompress' => 1, //是否开启gzip压缩{0为不开启1为开启}
'isDownload' => 0 //压缩完成后是否自动下载{0为不自动1为自动}
);
$this->mr = new MysqlBack($this->config);
}*/
//替代上面的方法的临时方法----求解求解。。。
public function initNew(){
Yii::import('application.extensions.MysqlBack', 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,
'db_username' => Yii::app()->db->username,
'db_password' => Yii::app()->db->password,
'db_prefix' => Yii::app()->db->tablePrefix,
'charset' => Yii::app()->db->charset,
'path' => Yii::app()->basePath . '/data/backup/', //定义备份文件的路径
'isCompress' => 1, //是否开启gzip压缩{0为不开启1为开启}
'isDownload' => 0 //压缩完成后是否自动下载{0为不自动1为自动}
);
$this->mr = new MysqlBack($this->config); //实例化
}
// @显示已备份的数据列表--列表页面
public function actionIndex(){
$this->initNew();
$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
);
rsort($list); //倒序排列--最新的时间在最上面
}
}
$this->render('index',array('data'=>$list));
}
// @备份数据库
public function actionBackup(){
$this->initNew();
$this->mr->setDBName($this->config['dbname']);
if($this->mr->backup()){
echo '<h2 style="color:green;text-align:center;margin:50px;font-family:Microsoft Yahei;">数据库备份成功!!!</h2>';
Yii::app()->end();
}else{
echo '<h2 style="color:red;text-align:center;margin:50px;font-family:Microsoft Yahei;">数据库备份失败???</h2>';
Yii::app()->end();
// Message::show_msg($this->createUrl('/admin/system/dataBack/'), '数据库备份失败！!');
}
}
//@删除数据备份
public function actionDelBack(){
$this->initNew();
chmod($this->config['path'], 777);
//需要增加写入删除文件夹的方法
if(unlink($this->config['path'] . $_GET['file'])){
echo '<h2 style="color:green;text-align:center;margin:50px;font-family:Microsoft Yahei;">删除备份成功!!!</h2>';
Yii::app()->end();
// Message::show_msg($this->createUrl('/admin/system/dataBack/'), '删除备份成功！!');
}else{
echo '<h2 style="color:red;text-align:center;margin:50px;font-family:Microsoft Yahei;">删除备份失败???</h2>';
Yii::app()->end();
// Message::show_msg($this->createUrl('/admin/system/dataBack/'), '删除备份失败！!');
}
}
// @下载备份文件
public function actionDownloadBack(){
$this->initNew();
$this->download($this->config['path'] . $_GET['file']);
}
//还原数据库
public function actionRecover(){
$this->initNew();
$this->mr->setDBName($this->config['dbname']);
if($this->mr->recover($_GET['file'])){
echo '<h2 style="color:green;text-align:center;margin:50px;font-family:Microsoft Yahei;">数据还原成功!!!</h2>';
Yii::app()->end();
}else{
echo '<h2 style="color:red;text-align:center;margin:50px;font-family:Microsoft Yahei;">数据还原失败???</h2>';
Yii::app()->end();
}
}
// @获取目录下文件数组
public function MyScandir($FilePath='./',$Order=0){
$this->initNew();
$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);
}
}
<div id="toolBar">
<div id="toolBtns">
<a href="/admin/system/dataBack/backup/" id="addBtn" class="btn btn-info"><i class="glyphicon glyphicon-plus"></i>一键备份</a>
</div>
</div>
<form action=" method="post" id="formList">
<table id="tableList" class="tableList">
<thead>
<tr>
<th width="160px"><i class="glyphicon glyphicon-list"></i>操作</th>
<th>备份记录</th>
<th>文件大小</th>
<th>备份日期</th>
</tr>
</thead>
<tbody>
<?php foreach($data as $val) { ?>
<tr>
<td class="tdHandle">
<a href="/admin/system/dataBack/delBack/file/<?php echo $val['name'];?>" class="del"><i class="glyphicon glyphicon-edit"></i>删除</a>
<a href="/admin/system/dataBack/downloadBack/file/<?php echo $val['name'];?>" class="download"><i class="glyphicon glyphicon-edit"></i>下载</a>
<a href="/admin/system/dataBack/recover/file/<?php echo $val['name'];?>" class="recover"><i class="glyphicon glyphicon-edit"></i>还原</a>
</td>
<td><?php echo $val['name']; ?></td>
<td><?php echo $val['size']; ?></td>
<td><?php echo $val['time']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</form>
<script type="text/javascript">
//添加
$('#addBtn').click(function() {
opendia = $.dialog.open($(this).attr('href'), {
title: $(this).text() + '---30秒后自动关闭',
width: 500,
height:200,
time: 30,
close: function () { //关闭的时候刷新浏览器
window.parent.$.dialog.open.origin.location.reload();
}
});
return false;
});
//编辑单条记录
$('.tdHandle a.recover').click(function() {
opendia = $.dialog.open($(this).attr('href'), {
title: '还原数据库: ' + $(this).parents("tr:eq(0)").find("td:eq(1)").text() + '---30秒后自动关闭',
width: 500,
height: 200,
time: 30,
close: function () { //关闭的时候刷新浏览器
window.parent.$.dialog.open.origin.location.reload();
}
});
return false;
});
//删除单条记录
$('.tdHandle a.del').click(function() {
opendia = $.dialog.open($(this).attr('href'), {
title: '删除备份数据: ' + $(this).parents("tr:eq(0)").find("td:eq(1)").text() + '---3秒后自动关闭',
width: 500,
height: 200,
time: 3,
close: function () { //关闭的时候刷新浏览器
window.parent.$.dialog.open.origin.location.reload();
}
});
return false;
});
</script>