php mysql數據庫備份類

本文章主要是介紹關於用php對mysql數據庫，與數據表以及數據進行備份了，代碼寫得有一點長不過行行都是實例了，好了費話不說多了我們來看看這款mysql數據庫備份代碼的吧。

本文章主要是介紹關於用php對mysql數據庫，與數據表以及數據進行備份了，代碼寫得有一點長不過行行都是實例了，好了費話不說多了我們來看看這款mysql數據庫備份代碼的吧。

這個程序僅僅備份和恢復數據，使用方法很簡單，實例化DbBak，然後調用bakupDb和restoreDb方法：
   1、實例化DbBak需要告訴它兩件事：數據服務器在哪裡($connectid)、備份到哪個目錄($backupDir)：

require_once('DbBak.php');
<?php
//只有DbBak才能調用這個類
class TableBak{
        var $_mysql_link_id;
        var $_dbDir;
        //private $_DbManager;
        function TableBak($mysql_link_id,$dbDir)
        {
                $this->_mysql_link_id = $mysql_link_id;
                $this->_dbDir = $dbDir;
        }
       
        function backupTable($tableName)
        {
                //step1:創建表的備份目錄名：
                $tableDir = $this->_dbDir.DIRECTORY_SEPARATOR.$tableName;
                !is_dir($tableDir) && mkdir($tableDir);
                //step2:開始備份：
                $this->_backupTable($tableName,$tableDir);
        }
       
        function restoreTable($tableName,$tableBakFile)
        {
                set_time_limit(0);
                $fileArray = @file($tableBakFile) or die("can open file $tableBakFile");
                $num = count($fileArray);
                mysql_unbuffered_query("DELETE FROM $tableName");
                $sql = $fileArray[0];
                for ($i=1;$i<$num-1;$i++){                       
                        mysql_unbuffered_query($sql.$fileArray[$i]) or (die (mysql_error()));
                }
                return true;
        }
       
        function _getFieldInfo($tableName){
                $fieldInfo = array();
                $sql="SELECT * FROM $tableName LIMIT 1";
                $result = mysql_query($sql,$this->_mysql_link_id);
                $num_field=mysql_num_fields($result);
                for($i=0;$i<$num_field;$i++){
                        $field_name=mysql_field_name($result,$i);
                        $field_type=mysql_field_type($result,$i);
                        $fieldInfo[$field_name] = $field_type;
                }
                mysql_free_result($result);
                return $fieldInfo;
        }
        function _quoteRow($fieldInfo,$row){
                foreach ($row as $field_name=>$field_value){
                        $field_value=strval($field_value);
                        switch($fieldInfo[$field_name]){ 
                                case "blob":     $row[$field_name] = "'".mysql_escape_string($field_value)."'";break;                         
                                case "string":   $row[$field_name] = "'".mysql_escape_string($field_value)."'";break; 
                                case "date":          $row[$field_name] = "'".mysql_escape_string($field_value)."'";break; 
                                case "datetime": $row[$field_name] = "'".mysql_escape_string($field_value)."'";break; 
                                case "time":          $row[$field_name] = "'".mysql_escape_string($field_value)."'";break; 
                                case "unknown":  $row[$field_name] = "'".mysql_escape_string($field_value)."'";break;                                         
                                case "int":          $row[$field_name] = intval($field_value); break;
                                case "real":     $row[$field_name] = intval($field_value); break;
                                case "timestamp":$row[$field_name] = intval($field_value); break;
                                default:                  $row[$field_name] = intval($field_value); break;
                        }
                }
                return $row;
        }
        function _backupTable($tableName,$tableDir)
        {
                //取得表的字段類型：
                $fieldInfo = $this->_getFieldInfo($tableName);
               
                //step1:構造INSERT語句前半部分 並寫入文件：
                $fields = array_keys($fieldInfo);
                $fields = implode(',',$fields);
                $sqltext="INSERT INTO $tableName($fields)VALUES rn";
                $datafile = $tableDir.DIRECTORY_SEPARATOR.$tableName.'.sql';
                (!$handle = fopen($datafile,'w')) && die("can not open file <b>$datafile</b>");
                (!fwrite($handle, $sqltext))  && die("can not write data to file <b>$datafile</b>");
                fclose($handle);
               
                //step2:取得數據 並寫入文件：
                //取出表資源：
                set_time_limit(0);
                $sql = "select * from $tableName";
                $result = mysql_query($sql,$this->_mysql_link_id);
                //打開數據備份文件:$tableName.xml
                $datafile = $tableDir.DIRECTORY_SEPARATOR.$tableName.'.sql';
                (!$handle = fopen($datafile,'a')) && die("can not open file <b>$datafile</b>");
                //逐條取得表記錄並寫入文件：
                while ($row = mysql_fetch_assoc($result)) {
                        $row = $this->_quoteRow($fieldInfo,$row);       
                        $record='(' . implode(',',$row) . ");rn";
                        (!fwrite($handle, $record))  && die("can not write data to file <b>$datafile</b>");
                }
                mysql_free_result($result);
                //關閉文件：
                fclose($handle);
               
                return true;
        }

}
?>
require_once('TableBak.php');
這個文件代碼

require_once('TableBak.php');
class DbBak {
        var $_mysql_link_id;
        var $_dataDir;
        var $_tableList;
        var $_TableBak;
       
        function DbBak($_mysql_link_id,$dataDir)
        {
                ( (!is_string($dataDir)) || strlen($dataDir)==0) && die('error:$datadir is not a string');
                !is_dir($dataDir) && mkdir($dataDir);
                $this->_dataDir = $dataDir;
                $this->_mysql_link_id = $_mysql_link_id;
        }
       
        function backupDb($dbName,$tableName=null)
        {
                ( (!is_string($dbName)) || strlen($dbName)==0 ) && die('$dbName must be a string value');
                //step1:選擇數據庫：
                mysql_select_db($dbName);
                //step2:創建數據庫備份目錄
                $dbDir = $this->_dataDir.DIRECTORY_SEPARATOR.$dbName;
                !is_dir($dbDir) && mkdir($dbDir);
                //step3:得到數據庫所有表名 並開始備份表
                $this->_TableBak = new TableBak($this->_mysql_link_id,$dbDir);
                if(is_null($tableName)){//backup all table in the db
                        $this->_backupAllTable($dbName);
                        return;
                }
                if(is_string($tableName)){
                        (strlen($tableName)==0) && die('....');
                        $this->_backupOneTable($dbName,$tableName);
                        return;
                }
                if (is_array($tableName)){
                        foreach ($tableName as $table){
                                ( (!is_string($table)) || strlen($table)==0 ) && die('....');
                        }
                        $this->_backupSomeTalbe($dbName,$tableName);
                        return;
                }
        }
       
        function restoreDb($dbName,$tableName=null){
                ( (!is_string($dbName)) || strlen($dbName)==0 ) && die('$dbName must be a string value');
                //step1:檢查是否存在數據庫 並連接：
                @mysql_select_db($dbName) || die("the database <b>$dbName</b> dose not exists");
                //step2:檢查是否存在數據庫備份目錄
                $dbDir = $this->_dataDir.DIRECTORY_SEPARATOR.$dbName;
                !is_dir($dbDir) && die("$dbDir not exists");
                //step3:start restore
                $this->_TableBak = new TableBak($this->_mysql_link_id,$dbDir);
                if(is_null($tableName)){//backup all table in the db
                        $this->_restoreAllTable($dbName);
                        return;
                }
                if(is_string($tableName)){
                        (strlen($tableName)==0) && die('....');
                        $this->_restoreOneTable($dbName,$tableName);
                        return;
                }
                if (is_array($tableName)){
                        foreach ($tableName as $table){
                                ( (!is_string($table)) || strlen($table)==0 ) && die('....');
                        }
                        $this->_restoreSomeTalbe($dbName,$tableName);
                        return;
                }
        }
       
        function _getTableList($dbName)
        {
                $tableList = array();
                $result=mysql_list_tables($dbName,$this->_mysql_link_id);
                for ($i = 0; $i < mysql_num_rows($result); $i++){
                array_push($tableList,mysql_tablename($result, $i));
                }
                mysql_free_result($result);
                return $tableList;
        }
       
        function _backupAllTable($dbName)
        {
                foreach ($this->_getTableList($dbName) as $tableName){
                        $this->_TableBak->backupTable($tableName);
                }
        }
       
        function _backupOneTable($dbName,$tableName)
        {
                !in_array($tableName,$this->_getTableList($dbName)) && die("指定的表名<b>$tableName</b>在數據庫中不存在");
                $this->_TableBak->backupTable($tableName);
        }
       
        function _backupSomeTalbe($dbName,$TableNameList)
        {
                foreach ($TableNameList as $tableName){
                        !in_array($tableName,$this->_getTableList($dbName)) && die("指定的表名<b>$tableName</b>在數據庫中不存在");
                }
                foreach ($TableNameList as $tableName){
                        $this->_TableBak->backupTable($tableName);
                }
        }
       
        function _restoreAllTable($dbName)
        {
                //step1:檢查是否存在所有數據表的備份文件 以及是否可寫：
                foreach ($this->_getTableList($dbName) as $tableName){
                        $tableBakFile = $this->_dataDir.DIRECTORY_SEPARATOR
                                                       . $dbName.DIRECTORY_SEPARATOR
                                                      . $tableName.DIRECTORY_SEPARATOR
                                               . $tableName.'.sql';
                        !is_writeable ($tableBakFile) && die("$tableBakFile not exists or unwirteable");
                }
                //step2:start restore
                foreach ($this->_getTableList($dbName) as $tableName){
                        $tableBakFile = $this->_dataDir.DIRECTORY_SEPARATOR
                                                      . $dbName.DIRECTORY_SEPARATOR
                                                      . $tableName.DIRECTORY_SEPARATOR
                                                      . $tableName.'.sql';
                        $this->_TableBak->restoreTable($tableName,$tableBakFile);
                }
        }
       
        function _restoreOneTable($dbName,$tableName)
        {
                //step1:檢查是否存在數據表:
                !in_array($tableName,$this->_getTableList($dbName)) && die("指定的表名<b>$tableName</b>在數據庫中不存在");
                //step2:檢查是否存在數據表備份文件 以及是否可寫：
                $tableBakFile = $this->_dataDir.DIRECTORY_SEPARATOR
                                            . $dbName.DIRECTORY_SEPARATOR
                                             . $tableName.DIRECTORY_SEPARATOR
                                            . $tableName.'.sql';
                !is_writeable ($tableBakFile) && die("$tableBakFile not exists or unwirteable");
                //step3:start restore
                $this->_TableBak->restoreTable($tableName,$tableBakFile);
        }
        function _restoreSomeTalbe($dbName,$TableNameList)
        {
                //step1:檢查是否存在數據表:
                foreach ($TableNameList as $tableName){
                        !in_array($tableName,$this->_getTableList($dbName)) && die("指定的表名<b>$tableName</b>在數據庫中不存在");
                }
                //step2:檢查是否存在數據表備份文件 以及是否可寫：
                foreach ($TableNameList as $tableName){
                        $tableBakFile = $this->_dataDir.DIRECTORY_SEPARATOR
                                                      . $dbName.DIRECTORY_SEPARATOR
                                                      . $tableName.DIRECTORY_SEPARATOR
                                                      . $tableName.'.sql';
                        !is_writeable ($tableBakFile) && die("$tableBakFile not exists or unwirteable");
                }
                //step3:start restore:
                foreach ($TableNameList as $tableName){
                        $tableBakFile = $this->_dataDir.DIRECTORY_SEPARATOR
                                                      . $dbName.DIRECTORY_SEPARATOR
                                                      . $tableName.DIRECTORY_SEPARATOR
                                                      . $tableName.'.sql';
                        $this->_TableBak->restoreTable($tableName,$tableBakFile);
                }
        }
}
?>

$connectid = mysql_connect('localhost','root','123456');

$backupDir = 'data';

$DbBak = new DbBak($connectid,$backupDir);
復制代碼2、然後就可以開始備份數據庫了，你不僅能夠指定備份那個數據庫，而且能詳細設置只備份那幾個表：
  2.1如果你想備份mybbs庫中的所有表，只要這樣： $DbBak->backupDb('mybbs');
復制代碼2.2如果你只想備份mybbs庫中的board、face、friendlist表，可以用一個一維數組指定： $DbBak->backupDb('mybbs',array('board','face','friendsite'));
復制代碼2.3如果只想備份一個表，比如board表： $DbBak->backupDb('mybbs','board');
復制代碼3，數據恢復：
對於2.1、2.1、2.3三種情況，只要相應的修改下語句，把backupDb換成restoreDb就能實現數據恢復了： $DbBak->restoreDb('mybbs');
復制代碼$DbBak->restoreDb('mybbs',array('board','face','friendsite'));
復制代碼$DbBak->restoreDb('mybbs','board');

測試實例

//example 1 backup:
require_once('DbBak.php');
require_once('TableBak.php');
$connectid = mysql_connect('localhost','root','123456');
$backupDir = 'data';
$DbBak = new DbBak($connectid,$backupDir);
$DbBak->backupDb('mybbs');

require_once('DbBak.php');
require_once('TableBak.php');
$connectid = mysql_connect('localhost','root','123456');
$backupDir = 'data';
$DbBak = new DbBak($connectid,$backupDir);
$DbBak->restoreDb('mybbs');