yk19880829 2012-09-28 04:18:31 6198次浏览 5条回复 5 0 0

扩展类:protected/extensions/mysql_back.php

<?php
/**
 * 功能描述:数据备份+还原操作
 * @Author:冰封<574578944@qq.com>
 * @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();
    }
        
    /*
     * 连接数据库
     * @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);
        $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;
        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') . ' */';
        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 文件名
     * @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);
    }
}
  • 回复于 2012-09-28 04:19 举报

    控制器:DataBackController.php

    <?php
    /**
     * 功能描述:后台数据备份+还原等操作
     * @Author:冰封<574578944@qq.com>
     * @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,
    		   '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 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);
    	}	
    }
    
  • 回复于 2012-09-28 04:22 举报

    视图文件: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>当前位置:</strong>后台管理—&gt;数据备份
    <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>
    

    [attach]923[/attach]
    写的不好地方,大家有好的建议或者修复即使更帖,谢谢大家多提宝贵意见!当然大家也可以根据自己项目调整即可!

  • 回复于 2012-09-28 04:41 举报

    mark。谢谢分享。打个包放上来更好了,嘿嘿

  • 回复于 2012-09-28 06:02 举报

    谢谢分享啊

  • 回复于 2012-09-28 06:24 举报

    代码有点长,等有时间了再看。先顶一下!

您需要登录后才可以回复。登录 | 立即注册