<?php??
/**?
?* 創建時間： 2012年5月21日?
?*?
?* 說明：分卷文件是以_v1.sql為結尾(20120522021241_all_v1.sql)?
?* 功能：實現mysql數據庫分卷備份,選擇表進行備份,實現單個sql文件及分卷sql導入?
?* 使用方法：?
?*?
?* ------1. 數據庫備份(導出)------------------------------------------------------------?
//分別是主機,用戶名,暗碼,數據庫名,數據庫編碼?
$db = new DBManage ( 'localhost', 'root', 'root', 'test', 'utf8' );?
// 參數：備份哪個表(可選),備份目錄(可選,默認為backup),分卷大小(可選,默認2000,即2M)?
$db->backup ();?
?* ------2. 數據庫恢復(導入)------------------------------------------------------------?
//分別是主機,用戶名,暗碼,數據庫名,數據庫編碼?
$db = new DBManage ( 'localhost', 'root', 'root', 'test', 'utf8' );?
//參數：sql文件?
$db->restore ( './backup/20120516211738_all_v1.sql');?
?*----------------------------------------------------------------------?
?*/?
class DBManage??
{??
??? var $db; // 數據庫連接??
??? var $database; // 所用數據庫??
??? var $sqldir; // 數據庫備份文件夾??
??? var $record;??
??? // 換行符??
??? private $ds = "n";??
??? // 存儲SQL的變量??
??? public $sqlContent = "";??
??? // 每條sql語句的結尾符??
??? public $sqlEnd = ";";??
??? /**?
???? * 初始化?
???? *?
???? * @param string $host?
???? * @param string $username?
???? * @param string $password?
???? * @param string $thisatabase?
???? * @param string $charset?
???? */?
??? function __construct($host = 'localhost', $username = 'root', $password = '', $thisatabase = 'test', $charset = 'utf8')??
??? {??
??????? $this->host = $host;??
??????? $this->username = $username;??
??????? $this->password = $password;??
??????? $this->database = $thisatabase;??
??????? $this->charset = $charset;??
??????? // 連接數據庫??
??????? $this->db = mysql_connect ( $this->host, $this->username, $this->password ) or die ( "數據庫連接失敗." );??
??????? // 選擇使用哪個數據庫??
??????? mysql_select_db ( $this->database, $this->db ) or die ( "無法打開數據庫" );??
??????? // 數據庫編碼方式??
??????? mysql_query ( 'SET NAMES ' . $this->charset, $this->db );??
??? }??
???
??? /*?
????? * ------------------------------------------數據庫備份start----------------------------------------------------------?
????? */?
???
??? /**?
???? * 數據庫備份?
???? * 參數：備份哪個表(可選),備份目錄(可選,默認為backup),分卷大小(可選,默認2000,即2M)?
???? *?
???? * @param $string $dir?
???? * @param int $size?
???? * @param $string $tablename?
???? */?
??? function backup($tablename = '', $dir = '', $size = 2000)??
??? {??
??????? //$dir = $dir ? $dir : 'backup/';??
??????? //$size = $size ? $size : 2000;??
??????? $sql = '';??
??????? // 只備份某個表??
??????? if (! emptyempty ( $tablename ))??
??????? {??
??????????? echo '正在備份表' . $tablename . '<br />';??
??????????? // 插入dump信息??
??????????? $sql = $this->_retrieve();??
??????????? // 插入表結構信息??
??????????? $sql .= $this->_insert_table_structure ( $tablename );??
??????????? // 插入數據??
??????????? $data = mysql_query ( "select * from " . $tablename );??
??????????? // 文件名前面部分??
??????????? $filename = date ( 'YmdHis' ) . "_" . $tablename;??
??????????? // 字段數量??
??????????? $num_fields = mysql_num_fields ( $data );??
??????????? // 第幾分卷??
??????????? $p = 1;??
??????????? // 循環每條記錄??
??????????? while ( $record = mysql_fetch_array ( $data ) )??
??????????? {??
??????????????? // 單條記錄??
??????????????? $sql .= $this->_insert_record ( $tablename, $num_fields, $record );??
??????????????? // 如果大于分卷大小,則寫入文件??
??????????????? if (strlen ( $sql ) >= $size * 1000)??
??????????????? {??
??????????????????? $file = $filename . "_v" . $p . ".sql";??
??????????????????? if ($this->_write_file ( $sql, $file, $dir ))??
??????????????????? {??
??????????????????????? echo "表-" . $tablename . "-卷-" . $p . "-數據備份完成,生成備份文件 <span style='color:#f00;'>$dir$filename</span><br />";??
??????????????????? }??
??????????????????? else?
??????????????????? {??
??????????????????????? echo "備份表-" . $tablename . "-失敗<br />";??
??????????????????? }??
??????????????????? // 下一個分卷??
??????????????????? $p ++;??
??????????????????? // 重置$sql變量為空,重新計算該變量大小??
??????????????????? $sql = "";??
??????????????? }??
??????????? }??
??????????? // sql大小不夠分卷大小??
??????????? if ($sql != "")??
??????????? {??
??????????????? $filename .= "_v" . $p . ".sql";??
??????????????? if ($this->_write_file ( $sql, $filename, $dir ))??
??????????????? {??
??????????????????? echo "表-" . $tablename . "-卷-" . $p . "-數據備份完成,生成備份文件 <span style='color:#f00;'>$dir$filename</span><br />";??
??????????????? }??
??????????????? else?
??????????????? {??
??????????????????? echo "備份卷-" . $p . "-失敗<br />";??
??????????????? }??
??????????? }??
??????? }??
??????? else?
??????? { // 備份全部表??
??????????? if ($tables = mysql_query ( "show table status from " . $this->database ))??
??????????? {??
??????????????? echo "讀取數據庫結構成功!<br />";??
??????????? }??
??????????? else?
??????????? {??
??????????????? exit ( "讀取數據庫結構成功!<br />" );??
??????????? }??
??????????? // 插入dump信息??
??????????? $sql .= $this->_retrieve();??
??????????? // 文件名前面部分??
??????????? $filename = date ( 'YmdHis' ) . "_all";??
??????????? // 查出所有表??
??????????? $tables = mysql_query ( 'SHOW TABLES' );??
??????????? // 第幾分卷??
??????????? $p = 1;??
??????????? // 循環所有表??
??????????? while ( $table = mysql_fetch_array ( $tables ) )??
??????????? {??
??????????????? // 獲取表名??
??????????????? $tablename = $table [0];??
??????????????? // 獲取表結構??
??????????????? $sql .= $this->_insert_table_structure ( $tablename );??
??????????????? $data = mysql_query ( "select * from " . $tablename );??
??????????????? $num_fields = mysql_num_fields ( $data );??
???
??????????????? // 循環每條記錄??
??????????????? while ( $record = mysql_fetch_array ( $data ) )??
??????????????? {??
??????????????????? // 單條記錄??
??????????????????? $sql .= $this->_insert_record ( $tablename, $num_fields, $record );??
??????????????????? // 如果大于分卷大小,則寫入文件??
??????????????????? if (strlen ( $sql ) >= $size * 1000)??
??????????????????? {??
???
??????????????????????? $file = $filename . "_v" . $p . ".sql";??
??????????????????????? // 寫入文件??
??????????????????????? if ($this->_write_file ( $sql, $file, $dir ))??
??????????????????????? {??
??????????????????????????? echo "-卷-" . $p . "-數據備份完成,生成備份文件<span style='color:#f00;'>$dir$file</span><br />";??
??????????????????????? }??
??????????????????????? else?
??????????????????????? {??
??????????????????????????? echo "備份卷-" . $p . "-失敗<br />";??
??????????????????????? }??
??????????????????????? // 下一個分卷??
??????????????????????? $p ++;??
??????????????????????? // 重置$sql變量為空,重新計算該變量大小??
??????????????????????? $sql = "";??
??????????????????? }??
??????????????? }??
??????????? }??
??????????? // sql大小不夠分卷大小??
??????????? if ($sql != "")??
??????????? {??
??????????????? $filename .= "_v" . $p . ".sql";??
??????????????? if ($this->_write_file ( $sql, $filename, $dir ))??
??????????????? {??
??????????????????? echo "-卷-" . $p . "-數據備份完成,生成備份文件 <span style='color:#f00;'>$dir$filename<br />";??
??????????????? }??
??????????????? else?
??????????????? {??
??????????????????? echo "備份卷-" . $p . "-失敗<br />";??
??????????????? }??
??????????? }??
??????? }??
??? }??
???
??? /**?
???? * 插入數據庫備份基礎信息?
???? *?
???? * @return string?
???? */?
??? private function _retrieve() {??
??????? $value = '';??
??????? $value .= '--' . $this->ds;??
??????? $value .= '-- MySQL database dump' . $this->ds;??
??????? $value .= '-- Created by DBManage class, Power By yanue. ' . $this->ds;??
??????? $value .= '-- http://yanue.net ' . $this->ds;??
??????? $value .= '--' . $this->ds;??
??????? $value .= '-- 主機: ' . $this->host . $this->ds;??
??????? $value .= '-- 生成日期: ' . date ( 'Y' ) . ' 年? ' . date ( 'm' ) . ' 月 ' . date ( 'd' ) . ' 日 ' . date ( 'H:i' ) . $this->ds;??
??????? $value .= '-- MySQL版本: ' . mysql_get_server_info () . $this->ds;??
??????? $value .= '-- PHP 版本: ' . phpversion () . $this->ds;??
??????? $value .= $this->ds;??
??????? $value .= '--' . $this->ds;??
??????? $value .= '-- 數據庫: `' . $this->database . '`' . $this->ds;??
??????? $value .= '--' . $this->ds . $this->ds;??
??????? $value .= '-- -------------------------------------------------------';??
??????? $value .= $this->ds . $this->ds;??
??????? return $value;??
??? }??
???
??? /**?
???? * 插入表結構?
???? *?
???? * @param unknown_type $table?
???? * @return string?
???? */?
??? private function _insert_table_structure($table) {??
??????? $sql = '';??
??????? $sql .= "--" . $this->ds;??
??????? $sql .= "-- 表的結構" . $table . $this->ds;??
??????? $sql .= "--" . $this->ds . $this->ds;??
???
??????? // 如果存在則刪除表??
??????? $sql .= "DROP TABLE IF EXISTS `" . $table . '`' . $this->sqlEnd . $this->ds;??
??????? // 獲取詳細表信息??
??????? $res = mysql_query ( 'SHOW CREATE TABLE `' . $table . '`' );??
??????? $row = mysql_fetch_array ( $res );??
??????? $sql .= $row [1];??
??????? $sql .= $this->sqlEnd . $this->ds;??
??????? // 加上??
??????? $sql .= $this->ds;??
??????? $sql .= "--" . $this->ds;??
??????? $sql .= "-- 轉存表中的數據 " . $table . $this->ds;??
??????? $sql .= "--" . $this->ds;??
??????? $sql .= $this->ds;??
??????? return $sql;??
??? }??
???
??? /**?
???? * 插入單條記錄?
???? *?
???? * @param string $table?
???? * @param int $num_fields?
???? * @param array $record?
???? * @return string?
???? */?
??? private function _insert_record($table, $num_fields, $record) {??
??????? // sql字段逗號分割??
??????? $insert = $comma = "";??
??????? $insert .= "INSERT INTO `" . $table . "` VALUES(";??
??????? // 循環每個子段下面的內容??
??????? for($i = 0; $i < $num_fields; $i ++) {??
??????????? $insert .= ($comma . "'" .mysql_real_escape_string ( $record [$i] ) . "'");??
??????????? $comma = ",";??
??????? }??
??????? $insert .= ");" . $this->ds;??
??????? return $insert;??
??? }??
???
??? /**?
???? * 寫入文件?
???? *?
???? * @param string $sql?
???? * @param string $filename?
???? * @param string $dir?
???? * @return boolean?
???? */?
??? private function _write_file($sql, $filename, $dir) {??
??????? $dir = $dir ? $dir : './backup/';??
??????? // 不存在文件夾則創建??
??????? if (! file_exists ( $dir )) {??
??????????? mkdir ( $dir );??
??????? }??
??????? $re = true;??
??????? if (! @$fp = fopen ( $dir . $filename, "w+" )) {??
??????????? $re = false;??
??????????? echo "打開文件失敗!";??
??????? }??
??????? if (! @fwrite ( $fp, $sql )) {??
??????????? $re = false;??
??????????? echo "寫入文件失敗,請文件是否可寫";??
??????? }??
??????? if (! @fclose ( $fp )) {??
??????????? $re = false;??
??????????? echo "關閉文件失敗!";??
??????? }??
??????? return $re;??
??? }??
???
??? /*?
????? *?
????? * -------------------------------上：數據庫導出-----------分割線----------下：數據庫導入--------------------------------?
????? */?
???
??? /**?
???? * 導入備份數據?
???? * 說明：分卷文件格式20120516211738_all_v1.sql?
???? * 參數：文件路徑(必填)?
???? *?
???? * @param string $sqlfile?
???? */?
??? function restore($sqlfile)??
??? {??
??????? // 檢測文件是否存在??
??????? if (! file_exists ( $sqlfile ))??
??????? {??
??????????? exit ( "文件不存在!請檢查" );??
??????? }??
??????? $this->lock ( $this->database );??
??????? // 獲取數據庫存儲位置??
??????? $sqlpath = pathinfo ( $sqlfile );??
??????? $this->sqldir = $sqlpath ['dirname'];??
??????? // 檢測是否包含分卷,將類似20120516211738_all_v1.sql從_v分開,有則說明有分卷??
??????? $volume = explode ( "_v", $sqlfile );??
??????? $volume_path = $volume [0];??
??????? echo "請勿刷新及關閉瀏覽器以防止程序被中止,如有不慎!將導致數據庫結構受損<br />";??
??????? echo "正在導入備份數據,請稍等!<br />";??
??????? if (emptyempty ( $volume [1] ))??
??????? {??
??????????? echo "正在導入sql：<span style='color:#f00;'>" . $sqlfile . '</span><br />';??
??????????? // 沒有分卷??
??????????? if ($this->_import ( $sqlfile )) {??
??????????????? echo "數據庫導入成功!";??
??????????? }??
??????????? else?
??????????? {??
??????????????? exit ( '數據庫導入失敗!' );??
??????????? }??
??????? }??
??????? else?
??????? {??
??????????? //$volume_id = array();??
??????????? // 存在分卷,則獲取當前是第幾分卷,循環執行余下分卷??
??????????? $volume_id = explode ( ".sq", $volume [1] );??
??????????? // 當前分卷為$volume_id??
??????????? $volume_id = intval ( $volume_id [0] );??
??????????? while ( $volume_id )??
??????????? {??
??????????????? $tmpfile = $volume_path . "_v" . $volume_id . ".sql";??
??????????????? // 存在其他分卷,繼續執行??
??????????????? if (file_exists ( $tmpfile )) {??
??????????????????? // 執行導入方法??
??????????????????? echo "正在導入分卷<span style='color:#f00;'>" . $tmpfile . '</span><br />';??
??????????????????? if ($this->_import ( $tmpfile ))??
??????????????????? {??
???
??????????????????? }??
??????????????????? else?
??????????????????? {??
??????????????????????? exit ( "導入分卷<span style='color:#f00;'>" . $tmpfile . '</span>失敗!可能是數據庫結構已損壞!請嘗試從分卷1開始導入' );??
??????????????????? }??
??????????????? }??
??????????????? else?
??????????????? {??
??????????????????? echo "此分卷備份全部導入成功!<br />";??
??????????????????? return;??
??????????????? }??
??????????????? $volume_id++;??
??????????? }??
??????? }??
??? }??
???
??? /**?
???? * 將sql導入到數據庫(普通導入)?
???? *?
???? * @param string $sqlfile?
???? * @return boolean?
???? */?
??? private function _import($sqlfile) {??
??????? // sql文件包含的sql語句數組??
??????? $sqls = array ();??
??????? $f = fopen ( $sqlfile, "rb" );??
??????? // 創建表緩沖變量??
??????? $create = '';??
??????? while ( ! feof ( $f ) ) {??
??????????? // 讀取每一行sql??
??????????? $line = fgets ( $f );??
??????????? // 如果包含'-- '等注釋,或為空白行,則跳過??
??????????? if (trim ( $line ) == '' || preg_match ( '/--*?/', $line, $match )) {??
??????????????? continue;??
??????????? }??
??????????? // 如果結尾包含';'(即為一個完整的sql語句,這里是插入語句),并且不包含'ENGINE='(即創建表的最后一句),??
??????????? if (! preg_match ( '/;/', $line, $match ) || preg_match ( '/ENGINE=/', $line, $match )) {??
??????????????? // 將本次sql語句與創建表sql連接存起來??
??????????????? $create .= $line;??
??????????????? // 如果包含了創建表的最后一句??
??????????????? if (preg_match ( '/ENGINE=/', $create, $match )) {??
??????????????????? // 則將其合并到sql數組??
??????????????????? $sqls [] = $create;??
??????????????????? // 清空當前,準備下一個表的創建??
??????????????????? $create = '';??
??????????????? }??
??????????????? // 跳過本次??
??????????????? continue;??
??????????? }??
??????????? $sqls [] = $line;??
??????? }??
??????? fclose ( $f );??
??????? // 循環sql語句數組,分別執行??
??????? foreach ( $sqls as $sql ) {??
??????????? str_replace ( "n", "", $sql );??
??????????? if (! mysql_query ( trim ( $sql ) )) {??
??????????????? echo mysql_error ();??
??????????????? return false;??
??????????? }??
??????? }??
??????? return true;??
??? }??
???
??? /*?
????? * -------------------------------數據庫導入end---------------------------------?
????? */?
???
??? // 關閉數據庫連接??
??? private function close() {??
??????? mysql_close ( $this->db );??
??? }??
???
??? // 鎖定數據庫,以免備份或導入時出錯??
??? private function lock($tablename, $op = "WRITE") {??
??????? if (mysql_query ( "lock tables " . $tablename . " " . $op ))??
??????????? return true;??
??????? else?
??????????? return false;??
??? }??
???
??? // 解鎖??
??? private function unlock() {??
??????? if (mysql_query ( "unlock tables" ))??
??????????? return true;??
??????? else?
??????????? return false;??
??? }??
???
??? // 析構??
??? function __destruct() {??
??????? mysql_query ( "unlock tables", $this->db );??
??????? mysql_close ( $this->db );??
??? }
}
?
$db = new DBManage ( 'localhost', 'root', '', 'tao', 'gbk' );?
//$db->backup ('tao_admin');??
$db->restore ( './backup/20140228222713_tao_admin_v1.sql');
?>