分卷处理就是把握们要处理的数据分成一个个小文件进行处理了,下面我来给大家介绍一个php mysql备份恢复分卷处理类,实现mysql数据库分卷备份,选择表进行备份,实现单个sql文件及分卷sql导入.
分卷导入类及思路详解
数据库导入导出是一个后台必要拥有的功能,网上一搜,有很多关于数据库导入导出的,但基本上一个大的系统,包含了许多我们并不需要的,而且他们都是自己的后台的形式,我并不喜欢的是拿人家的东西整合到自己的后台,我需要的是自己东西,于是参照了很多,自己写了一个关于分卷导入类,以方便调用,欢迎大家拍砖.
这里针对分卷文件是以‘_v1.sql’为结尾,实现单个sql文件及分卷sql导入,分卷导入可选择是否当前分卷导入余下分卷,我们只需要直接调用类即可完成.
分别是主机,用户名,密码,数据库名,数据库编码
$db = new DataManage ( 'localhost', 'root', 'root', 'test', 'utf8' );
sql文件,是否只导入单个sql(即如果有其他分卷也不导入).
$db->restore ( './backup/20120516211738_all_v1.sql', false );对应如何去列出备份的sql文件或选择sql之类的,自己去实现,那个不在这个范畴了,也很简单的.
还有目前只实现了数据库导入,关于数据库导出的,正在编写功能,下面是完整的类代码,具体思路及实现代码里面都有说明,这里不在赘述,代码如下:
- <?php
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- class DataManage {
- var $db;
- var $database;
- var $sqldir;
-
-
-
-
-
-
-
-
-
-
- function __construct($host = 'localhost', $username = 'root', $password = '', $database = 'test', $charset = 'utf8') {
- $this->host = $host;
- $this->username = $username;
- $this->password = $password;
- $this->database = $database;
- $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 );
- }
-
-
-
-
-
-
-
-
- function restore($sqlfile, $single = FALSE) {
-
- if (! file_exists ( $sqlfile )) {
- exit ( "文件不存在!请检查" );
- }
- $this->lock ( $this->database );
-
- $sqlpath = pathinfo ( $sqlfile );
- $this->sqldir = $sqlpath ['dirname'];
-
- $volume = explode ( "_v", $sqlfile );
- $volume_path = $volume [0];
- echo "请勿刷新及关闭浏览器以防止程序被中止,如有不慎!将导致数据库结构受损<br />";
- echo "正在导入备份数据,请稍等!<br />";
- if (emptyempty ( $volume [1] ) || $single) {
- echo "正在导入sql:<span style='color:#f00;'>" . $sqlfile . '</span><br />';
-
- if ($this->_import ( $sqlfile )) {
- echo "数据库导入成功!";
- } else {
- exit ( '数据库导入失败!' );
- }
- } else {
-
- $volume_id = explode ( ".sq", $volume [1] );
-
- $volume_id = intval ( $volume_id [0] );
- while ( $volume_id ) {
- $tmpfile = $volume_path . "_v" . $volume_id . ".sql";
-
- if (file_exists ( $tmpfile )) {
-
- echo "正在导入分卷$volume_id:<span style='color:#f00;'>" . $tmpfile . '</span><br />';
- if ($this->_import ( $tmpfile )) {
-
- } else {
- exit ( "导入分卷$volume_id:<span style='color:#f00;'>" . $tmpfile . '</span>失败!可能是数据库结构已损坏!请尝试从分卷1开始导入' );
- }
- } else {
- echo "此分卷备份全部导入成功!<br />";
- return;
- }
- $volume_id ++;
- }
- }
- }
-
-
-
-
-
-
-
- private function _import($sqlfile) {
- $name = basename ( $sqlfile );
- $sqls = file ( $sqlfile );
- foreach ( $sqls as $sql ) {
- str_replace ( "r", "", $sql );
- str_replace ( "n", "", $sql );
- if (! mysql_query ( trim ( $sql ), $this->db ))
- return false;
- }
- return true;
- }
-
-
- 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 );
- }
- }
- ?>
mysql备份恢复分卷处理,调用简单.
分卷导入思路:按行读取sql文件,将每一行当作完整的sql语句存到数组再循环执行插入数据库就可以了,但是在创建表语句分了多行,这个需要单独处理,就这个花了我好长时间的.感觉文章好长啊,主要是那个类文件给占用了.
更新说明:
1.去除sql导入的时候排除sql文件里面的注释’– ‘ 从而解决sql中单双引号不能导入
2.单行读取后的sql直接执行,避免重新将sql语句组合到数组中再从数组中读取导入sql,提高效率.
下载地址: https://github.com/yanue/Dbmanage
导出后的sql文件格式如下:
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- DROP TABLE IF EXISTS `aa`;
- CREATE TABLE `aa` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `content` text NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-
-
-
-
-
- INSERT INTO `aa` VALUES('1','<p id="test"><span class='hahh' style="line-height:;">我是测试数据 呵呵</span></p>');
下面是类代码:
- <?php
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- class DbManage {
- var $db;
- var $database;
- var $sqldir;
-
- private $ds = "n";
-
- public $sqlContent = "";
-
- public $sqlEnd = ";";
-
-
-
-
-
-
-
-
-
-
- function __construct($host = 'localhost', $username = 'root', $password = '', $database = 'test', $charset = 'utf8') {
- $this->host = $host;
- $this->username = $username;
- $this->password = $password;
- $this->database = $database;
- $this->charset = $charset;
- set_time_limit(0);
- @ob_end_flush();
-
- $this->db = @mysql_connect ( $this->host, $this->username, $this->password ) or die( '<p class="dbDebug"><span class="err">Mysql Connect Error : </span>'.mysql_error().'</p>');
-
- mysql_select_db ( $this->database, $this->db ) or die('<p class="dbDebug"><span class="err">Mysql Connect Error:</span>'.mysql_error().'</p>');
-
- mysql_query ( 'SET NAMES ' . $this->charset, $this->db );
-
- }
-
-
-
-
- function getTables() {
- $res = mysql_query ( "SHOW TABLES" );
- $tables = array ();
- while ( $row = mysql_fetch_array ( $res ) ) {
- $tables [] = $row [0];
- }
- return $tables;
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- function backup($tablename = '', $dir, $size) {
- $dir = $dir ? $dir : './backup/';
-
- if (! is_dir ( $dir )) {
- mkdir ( $dir, 0777, true ) or die ( '创建文件夹失败' );
- }
- $size = $size ? $size : 2048;
- $sql = '';
-
- if (! emptyempty ( $tablename )) {
- if(@mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$tablename."'")) == 1) {
- } else {
- $this->_showMsg('表-<b>' . $tablename .'</b>-不存在,请检查!',true);
- die();
- }
- $this->_showMsg('正在备份表 <span class="imp">' . $tablename.'</span>');
-
- $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 * 1024) {
- $file = $filename . "_v" . $p . ".sql";
- if ($this->_write_file ( $sql, $file, $dir )) {
- $this->_showMsg("表-<b>" . $tablename . "</b>-卷-<b>" . $p . "</b>-数据备份完成,备份文件 [ <span class='imp'>" .$dir . $file ."</span> ]");
- } else {
- $this->_showMsg("备份表 -<b>" . $tablename . "</b>- 失败",true);
- return false;
- }
-
- $p ++;
-
- $sql = "";
- }
- }
-
- unset($data,$record);
-
- if ($sql != "") {
- $filename .= "_v" . $p . ".sql";
- if ($this->_write_file ( $sql, $filename, $dir )) {
- $this->_showMsg( "表-<b>" . $tablename . "</b>-卷-<b>" . $p . "</b>-数据备份完成,备份文件 [ <span class='imp'>" .$dir . $filename ."</span> ]");
- } else {
- $this->_showMsg("备份卷-<b>" . $p . "</b>-失败<br />");
- return false;
- }
- }
- $this->_showMsg("恭喜您! <span class='imp'>备份成功</span>");
- } else {
- $this->_showMsg('正在备份');
-
- if ($tables = mysql_query ( "show table status from " . $this->database )) {
- $this->_showMsg("读取数据库结构成功!");
- } else {
- $this->_showMsg("读取数据库结构失败!");
- exit ( 0 );
- }
-
- $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 )) {
- $this->_showMsg("-卷-<b>" . $p . "</b>-数据备份完成,备份文件 [ <span class='imp'>".$dir.$file."</span> ]");
- } else {
- $this->_showMsg("卷-<b>" . $p . "</b>-备份失败!",true);
- return false;
- }
-
- $p ++;
-
- $sql = "";
- }
- }
- }
-
- if ($sql != "") {
- $filename .= "_v" . $p . ".sql";
- if ($this->_write_file ( $sql, $filename, $dir )) {
- $this->_showMsg("-卷-<b>" . $p . "</b>-数据备份完成,备份文件 [ <span class='imp'>".$dir.$filename."</span> ]");
- } else {
- $this->_showMsg("卷-<b>" . $p . "</b>-备份失败",true);
- return false;
- }
- }
- $this->_showMsg("恭喜您! <span class='imp'>备份成功</span>");
- }
- }
-
-
- private function _showMsg($msg,$err=false){
- $err = $err ? "<span class='err'>ERROR:</span>" : '' ;
- echo "<p class='dbDebug'>".$err . $msg."</p>";
- flush();
-
- }
-
-
-
-
-
-
- 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;
- }
-
-
-
-
-
-
-
- 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;
- }
-
-
-
-
-
-
-
-
-
- private function _insert_record($table, $num_fields, $record) {
-
- $insert = '';
- $comma = "";
- $insert .= "INSERT INTO `" . $table . "` VALUES(";
-
- for($i = 0; $i < $num_fields; $i ++) {
- $insert .= ($comma . "'" . mysql_escape_string ( $record [$i] ) . "'");
- $comma = ",";
- }
- $insert .= ");" . $this->ds;
- return $insert;
- }
-
-
-
-
-
-
-
-
-
- private function _write_file($sql, $filename, $dir) {
- $dir = $dir ? $dir : './backup/';
-
- if (! is_dir ( $dir )) {
- mkdir ( $dir, 0777, true );
- }
- $re = true;
- if (! @$fp = fopen ( $dir . $filename, "w+" )) {
- $re = false;
- $this->_showMsg("打开sql文件失败!",true);
- }
- if (! @fwrite ( $fp, $sql )) {
- $re = false;
- $this->_showMsg("写入sql文件失败,请文件是否可写",true);
- }
- if (! @fclose ( $fp )) {
- $re = false;
- $this->_showMsg("关闭sql文件失败!",true);
- }
- return $re;
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- function restore($sqlfile) {
-
- if (! file_exists ( $sqlfile )) {
- $this->_showMsg("sql文件不存在!请检查",true);
- exit ();
- }
- $this->lock ( $this->database );
-
- $sqlpath = pathinfo ( $sqlfile );
- $this->sqldir = $sqlpath ['dirname'];
-
- $volume = explode ( "_v", $sqlfile );
- $volume_path = $volume [0];
- $this->_showMsg("请勿刷新及关闭浏览器以防止程序被中止,如有不慎!将导致数据库结构受损");
- $this->_showMsg("正在导入备份数据,请稍等!");
- if (emptyempty ( $volume [1] )) {
- $this->_showMsg ( "正在导入sql:<span class='imp'>" . $sqlfile . '</span>');
-
- if ($this->_import ( $sqlfile )) {
- $this->_showMsg( "数据库导入成功!");
- } else {
- $this->_showMsg('数据库导入失败!',true);
- exit ();
- }
- } else {
-
- $volume_id = explode ( ".sq", $volume [1] );
-
- $volume_id = intval ( $volume_id [0] );
- while ( $volume_id ) {
- $tmpfile = $volume_path . "_v" . $volume_id . ".sql";
-
- if (file_exists ( $tmpfile )) {
-
- $this->msg .= "正在导入分卷 $volume_id :<span style='color:#f00;'>" . $tmpfile . '</span><br />';
- if ($this->_import ( $tmpfile )) {
-
- } else {
- $volume_id = $volume_id ? $volume_id :1;
- exit ( "导入分卷:<span style='color:#f00;'>" . $tmpfile . '</span>失败!可能是数据库结构已损坏!请尝试从分卷1开始导入' );
- }
- } else {
- $this->msg .= "此分卷备份全部导入成功!<br />";
- return;
- }
- $volume_id ++;
- }
- }if (emptyempty ( $volume [1] )) {
- $this->_showMsg ( "正在导入sql:<span class='imp'>" . $sqlfile . '</span>');
-
- if ($this->_import ( $sqlfile )) {
- $this->_showMsg( "数据库导入成功!");
- } else {
- $this->_showMsg('数据库导入失败!',true);
- exit ();
- }
- } else {
-
- $volume_id = explode ( ".sq", $volume [1] );
-
- $volume_id = intval ( $volume_id [0] );
- while ( $volume_id ) {
- $tmpfile = $volume_path . "_v" . $volume_id . ".sql";
-
- if (file_exists ( $tmpfile )) {
-
- $this->msg .= "正在导入分卷 $volume_id :<span style='color:#f00;'>" . $tmpfile . '</span><br />';
- if ($this->_import ( $tmpfile )) {
-
- } else {
- $volume_id = $volume_id ? $volume_id :1;
- exit ( "导入分卷:<span style='color:#f00;'>" . $tmpfile . '</span>失败!可能是数据库结构已损坏!请尝试从分卷1开始导入' );
- }
- } else {
- $this->msg .= "此分卷备份全部导入成功!<br />";
- return;
- }
- $volume_id ++;
- }
- }
- }
-
-
-
-
-
-
-
- private function _import($sqlfile) {
-
- $sqls = array ();
- $f = fopen ( $sqlfile, "rb" );
-
- $create_table = '';
- while ( ! feof ( $f ) ) {
-
- $line = fgets ( $f );
-
-
- if (! preg_match ( '/;/', $line ) || preg_match ( '/ENGINE=/', $line )) {
-
- $create_table .= $line;
-
- if (preg_match ( '/ENGINE=/', $create_table)) {
-
- $this->_insert_into($create_table);
-
- $create_table = '';
- }
-
- continue;
- }
-
- $this->_insert_into($line);
- }
- fclose ( $f );
- return true;
- }
-
-
- private function _insert_into($sql){
- if (! mysql_query ( trim ( $sql ) )) {
- $this->msg .= mysql_error ();
- return false;
- }
- }
-
-
-
-
-
-
- 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() {
- if($this->db){
- mysql_query ( "unlock tables", $this->db );
- mysql_close ( $this->db );
- }
- }
-
- }
- ?>
|