桃源资源网

php实例分享之mysql数据备份
网络编程 2024/11/6 佚名
3 2

备份:表结构和数据完全分开，默认有一个文件会记录所有表的结构，然后表中数据的备份 如果超过分卷的大小则会分成多个文件，不然则一个文件，参考了别人的代码，不过写的嘛，差强 人意，以后慢慢改吧。。。

代码如下：

复制代码 代码如下:
<?php
/*
 * Created on 2014
 * Link for 527891885@qq.com
 * 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;
    }

    //获取表中的数据
    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);
        }
        $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 );
        }
    }
}
?>

标签：
php,mysql,数据备份
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
桃源资源网 Design By www.nqtax.com

评论“php实例分享之mysql数据备份”

暂无“php实例分享之mysql数据备份”评论...
www.nqtax.com 桃源资源网
19,905影音资源
44,626技术资源
1,817软件资源
378,645站长资源

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
桃源资源网 Design By www.nqtax.com