程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> 網頁編程 >> PHP編程 >> 關於PHP編程 >> php實例之mysql數據備份

php實例之mysql數據備份

編輯:關於PHP編程

本代碼實現了表結構和數據完全分開,默認有一個文件會記錄所有表的結構,然後表中數據的備份 如果超過分卷的大小則會分成多個文件,不然則一個文件

備份:表結構和數據完全分開,默認有一個文件會記錄所有表的結構,然後表中數據的備份 如果超過分卷的大小則會分成多個文件,不然則一個文件,參考了別人的代碼,不過寫的嘛,差強 人意,以後慢慢改吧。。。

代碼如下:

<?php

/*
 * Created on 2014
 * Link for [email protected]
 * This is seocheck backup class
 */
class DbBackUp {
    private $conn;
    private $dbName;
    private $host;
    private $tag = '_b';
    //構造方法 鏈接數據庫
    public function __construct($host='localhost', $dbUser='root', $dbPwd='', $dbName="seocheck", $charset='utf8') {
        @ob_start();
        @set_time_limit(0);
        $this->conn = mysql_connect($host, $dbUser, $dbPwd, true);
        if(!$this->conn) die("數據庫系統連接失敗!");
        mysql_query("set names ".$charset, $this->conn);
        mysql_select_db($dbName, $this->conn) or die("數據庫連接失敗!");
        $this->host = $host;
        $this->dbName = $dbName;
    }
 

    //獲取數據庫所有表名
    public function getTableNames () {
        $tables = array();
        $result = mysql_list_tables($this->dbName, $this->conn);
        if(!$result) die('MySQL Error: ' . mysql_error());
        while($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
        return $tables;
    }

    //獲取數據庫表的字段信息
    public function getFieldsByTable ($table) {
        $fields = array();
        $str = '';
        $res = mysql_query("SHOW CREATE TABLE `{$table}`", $this->conn);
        if(!$res) die('MySQL Error: ' . mysql_error());
        while($rows = mysql_fetch_assoc($res)) {
            $str = str_replace("CREATE TABLE `{$table}` (", "", $rows['Create Table']);//DROP TABLE IF EXISTS `{$table}`\n
            $str = "--\n-- Table structure for table `{$table}`\n--\n\nCREATE TABLE IF NOT EXISTS `{$table}` ( ".$str;
            $str = str_replace(",", ", ", $str);
            $str = str_replace("`) ) ENGINE=InnoDB ", "`)\n ) ENGINE=InnoDB ", $str);
            $str .=";\n\n";
            //$str = $str.";\n\n--\n-- Dumping data for table `{$table}`\n--\n\n";
            $fields[$rows['Table']] = $str;
        }
        return $fields;
    } www.jbxue.com

    //獲取表中的數據
    public function getDataByTable($table) {
        $data = array();
        $str = '';
        $res = mysql_query("SELECT * FROM `{$table}`", $this->conn);
        if(!$res) die('MySQL Error: ' . mysql_error());
        while($rows = mysql_fetch_assoc($res)) {
            if(!empty($rows)) {
                $data[] = $rows;
            }
        }
        $keys = array_keys($data[0]);
        foreach ($keys as $k=>$v) {
            $keys[$k] = '`'.$v.'`';
        }
        $key = join(', ', $keys);
        $str = "INSERT INTO `{$table}` ({$key}) VALUES\n";
        foreach ($data as $k=>$v) {
            $str.="(";
            while (list($key, $val) = each($v)) {
                if(!is_numeric($val)) {
                    $str.= "'".$val."', ";
                } else {
                    $str.= $val.', ';
                }
            }
            $str = substr($str, 0, -2);// 後邊有空格 所以從-2 開始截取
            if($k+1 == count($data)) {
                $str.=");\n\n-- --------------------------------------------------------\n\n";
            } else {
                $str.="),\n";
            }
        }
        return $str;
    }

     //備份數據庫
    public function getBackUpDataByTable ($tables, $path='', $fileName = 'seocheck', $subsection = '2') {
        if(empty($tables)) $this->_showMsg('未能指定要備份的表!!!', true);
        $page = 0;//卷數
        $path = empty($path) ? $_SERVER['DOCUMENT_ROOT'].'/core/Runtime/Data/'.$fileName.'Demo/' : $path;
        if(!file_exists($path)) {
            mkdir($path, 0777, true);
        } www.jbxue.com
        $mysql_info = $this->_retrieve();
        $fieldsByTable = array();
        if(is_array($tables)) {
            $this->_showMsg('開始備份,數據正在初始化中,請勿關閉浏覽器...');
            $fw = $this->writeFileByBackUpData($path.$this->dbName.'_table.sql', $mysql_info, $method="ab+");
            if($fw !== false) {
                $this->_showMsg('備份數據庫基本信息成功。。。');
            }
            foreach ($tables as $table) {
                $tableInfo = $this->getFieldsByTable($table);
                if(!empty($tableInfo)) {
                    $this->_showMsg('獲取表['.$table.']結構成功。。。');
                    $fw = $this->writeFileByBackUpData($path.$this->dbName.'_table.sql', $tableInfo[$table], $method="ab+");
                    if($fw === false) {
                        $this->_showMsg('備份表['.$table.']結構失敗。。。', true);
                    } else {
                        $this->_showMsg('備份表['.$table.']結構成功,開始獲取數據。。。');
                    };
                } else {
                    $this->_showMsg('獲取數據庫['.$this->dbName.']表結構失敗,請稍後再試!。。。', true);
                }
                $this->_insertSqlByTableForAll($path, $table, $subsection);
            }
        } else {
            $this->_showMsg('開始備份,數據正在初始化中,請勿關閉浏覽器...');
            $tableInfo = $this->getFieldsByTable($tables);
            if(!empty($tableInfo)) {
                $this->_showMsg('獲取表['.$tables.']結構成功。。。');
                $fw = $this->writeFileByBackUpData($path.$this->dbName.'_'.$tables.'_table.sql', $mysql_info.$tableInfo[$tables]);
                if($fw === false) {
                    $this->_showMsg('備份表['.$tables.']結構失敗。。。', true);
                } else {
                    $this->_showMsg('備份表['.$tables.']結構成功,開始獲取數據。。。');
                }
            } else {
                $this->_showMsg('獲取表['.$tables.']結構失敗,請稍後再試!。。。', true);
            }
            $res = $this->_insertSqlByTableForAll($path, $tables, $subsection);
        }
    }

    //數據庫基本信息
    private function _retrieve() {
        $backUp  = '';
        $backUp .= '--' . "\n";
        $backUp .= '-- MySQL database dump' . "\n";
        $backUp .= '-- Created by DbBackUp class, Power By chujiu. ' . "\n";
        $backUp .= '--' . "\n";
        $backUp .= '-- 主機: ' . $this->host . "\n";
        $backUp .= '-- 生成日期: ' . date ( 'Y' ) . ' 年  ' . date ( 'm' ) . ' 月 ' . date ( 'd' ) . ' 日 ' . date ( 'H:i' ) . "\n";
        $backUp .= '-- MySQL版本: ' . mysql_get_server_info () . "\n";
        $backUp .= '-- PHP 版本: ' . phpversion () . "\n";
        $backUp .= "\n\n";
        $backUp .= "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n";
        $backUp .= "SET time_zone = '+00:00';\n\n";
        $backUp .= "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n";
        $backUp .= "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n";
        $backUp .= "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n";
        $backUp .= "/*!40101 SET NAMES utf8*/;\n\n";
        $backUp .= "--\n-- Database: `{$this->dbName}`\n--\n\n-- --------------------------------------------------------\n\n";
        return $backUp;
    }

    /**
     * 插入單條記錄
     *
     * @param string $row
     */
    private function _insertSql($row, $table) {
        // sql字段逗號分割
        $insert = '';
        $insert .= "INSERT INTO `" . $table . "` VALUES(";
        foreach($row as $key=>$val) {
            $insert .= "'".$val."',";
        }
        $insert = substr($insert, 0 ,-1);
         $insert .= ");" . "\n";
        return $insert;
    }

    /**
     * 生成一個表的inser語句
     * @param string $table
     * @param string $subsection 分卷大小
     */
    private function _insertSqlByTableForAll($path, $table, $subsection) {
        $i = 0;
        $insertSqlByTable = '';
        $res = mysql_query("SELECT * FROM `{$table}`", $this->conn);
        if(!$res) die('MySQL Error: ' . mysql_error());
        while($rows = mysql_fetch_assoc($res)) {
            $insertSqlByTable .= $this->_insertSql($rows, $table);
            $size = strlen($insertSqlByTable);
            if($size > $subsection*1024*1024) {
                $fw = $this->writeFileByBackUpData($path.$table.$i.$this->tag.'.sql', $insertSqlByTable);
                if($fw === false) $this->_showMsg('數據庫表['.$table.'],卷 '.$i.' 寫入文件失敗,請稍後再試!!!',true);
                $this->_showMsg('數據庫表['.$table.'],卷 '.$i.' 備份成功!備份文件:[ '.$path.$table.$i.$this->tag.'.sql ]');
                $insertSqlByTable = '';
                $i+=1;
            }
        }
        // insertSqlByTable大小不夠分卷大小
        if ($insertSqlByTable != "") {
            $fw = $this->writeFileByBackUpData($path.$table.$this->tag.'.sql', $insertSqlByTable);
            if($fw === false) $this->_showMsg('數據庫表['.$table.']寫入文件失敗,請稍後再試!!!備份文件:[ '.$path.$table.$this->tag.'.sql ]',true);
            $this->_showMsg('數據庫表['.$table.'] 備份成功!備份文件:[ '.$path.$table.$this->tag.'.sql ]');
        }
        $this->_showMsg('數據庫表['.$table.']全部備份成功!');
    }

    // 寫入文件
    public function writeFileByBackUpData($fileName, $data, $method="rb+", $iflock=1, $check=1, $chmod=1){
        $check && @strpos($fileName, '..')!==false && exit('Forbidden');
        @touch($fileName);
        $handle = @fopen($fileName, $method);
        if($iflock) {
            @flock($handle,LOCK_EX);
        }
        $fw = @fwrite($handle,$data);
        if($method == "rb+") ftruncate($handle, strlen($data));
        fclose($handle);
        $chmod && @chmod($fileName,0777);
        return $fw;
    }

    /**
     * path: 生成壓縮包的路徑
     * fileName : 要壓縮的文件名 通常和path 同一目錄
     */
    public function createZipByBackUpFile($path) {
        $db_base_files = $this->getFileByBackUpDir($path);
        if(!empty($db_base_files)) {
            $zip = new ZipArchive;
            if($zip->open($path.$this->dbName.date('Ymd').'.zip', ZipArchive::CREATE | ZIPARCHIVE::OVERWRITE) !== true) 
                die ("cannot open".$this->dbName.date('Ymd')."zip for writing.");
            foreach ($db_base_files as $key => $value) {
                if(is_file($value)) {
                    $file_name = basename($value);
                    $info[] = $zip->addFile($value, $file_name);// 避免壓縮包裡有文件的路徑
                }
            }
            $zip->close();
            if(file_exists($path.$this->dbName.date('Ymd').'.zip'))
            foreach ($db_base_files as $val) {
                unlink($val);
            }
            if(count(array_filter($info)) > 0) return true;
        }
        return false;
    }

    //獲取文件
    public function getFileByBackUpDir($path) {
        $info = array();
        $db_base_files = array();
        if( @file_exists($path) && is_dir($path) ) {
            if ($dh = opendir($path)) {
                while (($file = readdir($dh)) !== false) {
                    if($file != '.' && $file != '..') {
                        if( strripos($file, 'seocheck') !== false ) {
                            $db_base_files[] = $path.$file;
                        }
                    }
                }
                closedir($dh);
            }
        }
        return $db_base_files;
    }

    /**
     * @path: 生成壓縮包的路徑
     * @fileName : 要解壓的文件名 默認解壓到path 目錄
     */
    public function uncompressZip($path, $zipName) {
        $path = empty($path) ? $_SERVER['DOCUMENT_ROOT'].'/core/Runtime/Data/' : $path;
        $zip = new ZipArchive;
        if ($zip->open($path.$zipName) === TRUE) {
            $zip->extractTo($path);
            $zip->close();
            return true;
        } else {
            return false;
        }
    }

    //導入數據庫
    public function importingDataBySqlFile () {

    }

    //  及時輸出信息
    private function _showMsg($msg,$err=false){
        if($err === true) {
            echo "<p style='font-size:14px;'><span style='color:red;'>ERROR: --- " . $msg . "</span></p>";exit;
        }
        echo "<p style='font-size:14px;'><span style='color:green;'>OK: --- " . $msg . "</span></p>";
    }

    // 鎖定數據庫,以免備份或導入時出錯
    private function lock($table, $op = "WRITE") {
        if (mysql_query ( "lock tables " . $table . " " . $op ))
            return true;
        else
            return false;
    }

    // 解鎖
    private function unlock() {
        if (mysql_query ( "unlock tables" ))
            return true;
        else
            return false;
    }

    // 析構
    public function __destruct() {
        if($this->conn){
            mysql_query ( "unlock tables", $this->conn );
            mysql_close ( $this->conn );
        }
    }
}
?>

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved