YII下数据库的备份与还原

1.扩展类:protected/extensions/mysql_back.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);
    }
}

2.控制器:DataBackController.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);
    }   
}

3.视图文件: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>

转载自： http://blog.csdn.net/xiaowuc/article/details/11675485