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