|
The exported sql file format is as follows:
--
-- MySQL database dump
-- Created by DBManage class, Power By yanue.
--
-- Host: localhost
-- Generated date: October 06, 2012 22:32
-- MySQL version: 5.1.50-community
-- PHP version: 5.3.9-ZS5.6.0
--
-- Database: `test`
--
-------------------------------------------------- ----------
--
-- Table structure aa
--
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;
--
-- Transfer data in the table aa
--
INSERT INTO `aa` VALUES('1',' I am test data haha< ;/span> ');The following is the class code:
/**
* @author yanue
* @copyright Copyright (c) 2012 yanue.net
* @link http://yanue.net/archives/174.html
* @version 1.1
* Creation time: May 21, 2012
Updated: October 6, 2012
Update instructions: 1. Remove the comment '--' in the sql file when importing sql to solve the problem that single and double quotes in sql cannot be imported
2. Directly execute the sql after reading a single row, avoiding the need to recombine the sql statement into the array and then read and import the sql from the array to improve efficiency
* Note: The volume file ends with _v1.sql (20120522021241_all_v1.sql)
* Function: realize mysql database volume backup, select tables for backup, realize single sql file and volume sql import
* How to use:
*
* ------1. Database backup (export) ---------------------------------- --------------------------
//They are the host, user name, password, database name, and database encoding
$db = new DBManage ( 'localhost', 'root', 'root', 'test', 'utf8' );
// Parameters: Which table to back up (optional), backup directory (optional, default is backup), volume size (optional, default is 2000, which is 2M)
$db->backup ();
* ------2. Database recovery (import) ---------------------------------- --------------------------
//They are the host, user name, password, database name, and database encoding
$db = new DBManage ( 'localhost', 'root', 'root', 'test', 'utf8' );
//Parameter: sql file
$db->restore ('./backup/20120516211738_all_v1.sql');
*------------------------------------------------ --------------------------
*/
class DbManage {
var $db; // Database connection
var $database; // Database used
var $sqldir; // Database backup folder
// Line break
Private $ds = "n";
// Variables to store SQL
Public $sqlContent = "";
//The ending character of each sql statement
Public $sqlEnd = ";";
/**
* 初始化
*
* @param string $host
* @param string $username
* @param string $password
* @param string $database
* @param string $charset
*/
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( ' Mysql Connect Error : '.mysql_error().' ');
// 选择使用哪个数据库
mysql_select_db ( $this->database, $this->db ) or die('Mysql Connect Error:'.mysql_error().' ');
// 数据库编码方式
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;
}
/*
*
* ------------------------------------------数据库备份start----------------------------------------------------------
*/
/**
* Database backup
* Parameters: Which table to back up (optional), backup directory (optional, default is backup), volume size (optional, default is 2000, which is 2M)
*
* @param $string $dir
* @param int $size
* @param $string $tablename
*/
Function backup($tablename = '', $dir, $size) {
$dir = $dir ? $dir : './backup/';
//Create directory
If (! is_dir ( $dir )) {
mkdir ( $dir, 0777, true ) or die ( 'Failed to create folder' );
}
$size = $size ? $size : 2048;
$sql = '';
// Back up only a certain table
If (! empty ( $tablename )) {
If(@mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$tablename."'")) == 1) {
} else {
$this->_showMsg('Table-' . $tablename .'-does not exist, please check!',true);
die();
}
$this->_showMsg('Backing up table ' . $tablename.'');
// Insert dump information
$sql = $this->_retrieve ();
//Insert table structure information
$sql .= $this->_insert_table_structure ( $tablename );
// Insert data
$data = mysql_query ( "select * from " . $tablename );
// The first part of the file name
$filename = date ( 'YmdHis' ) . "_" . $tablename;
//Number of fields
$num_fields = mysql_num_fields ($data);
// Ji Ji Volume >
$p = 1;
// Loop through each record
while ( $record = mysql_fetch_array ( $data ) ) {
//Single record
$sql .= $this->_insert_record ( $tablename, $num_fields, $record );
// If it is larger than the volume size, write the file
If (strlen ( $sql ) >= $size * 1024) {
$file = $filename . "_v" . $p . ".sql";
If ($this->_write_file ($sql, $file, $dir)) {
$this->_showMsg("Table-" . $tablename . "-Volume-" . $p . "-data backup completed, backup file [ " .$dir . $file ." ]");
} else {
$this->_showMsg("Backup table -" . $tablename . "- failed",true);
return false;
}
$p ++;
//Reset the $sql variable to empty and recalculate the size of the variable
$sql = "";
}
}
//Clear data promptly
unset($data,$record);
// The size of the sql is not enough.
if ($sql != "") {
$filename .= "_v" . $p . ".sql";
If ($this->_write_file ( $sql, $filename, $dir )) {
$this->_showMsg( "Table-" . $tablename . "-Volume-" . $p . "-data backup completed, backup file [ " .$dir . $filename ." ]");
} else {
$this->_showMsg("Backup Volume-" . $p . "-Failed
");
return false;
}
}
$this->_showMsg("Congratulations! Backup successful");
} else {
$this->_showMsg('Backing up');
// Back up all tables
If ($tables = mysql_query ( "show table status from " . $this->database )) {
} else {
$this->_showMsg("Failed to read database structure!");
exit ( 0 );
}
// Insert dump information
$sql .= $this->_retrieve ();
//The first part of the file name
$filename = date ( 'YmdHis' ) . "_all";
// Find out all tables $tables = mysql_query ( 'SHOW TABLES' );
// Ji Ji Volume >
$p = 1;
// Loop through all tables
while ( $table = mysql_fetch_array ( $tables ) ) {
$tablename = $table [0];
// Get the table structure
$sql .= $this->_insert_table_structure ( $tablename );
$data = mysql_query ( "select * from " . $tablename );
$num_fields = mysql_num_fields ($data);
// Loop through each record
while ( $record = mysql_fetch_array ( $data ) ) {
//Single record
$sql .= $this->_insert_record ($tablename, $num_fields, $record);
// If it is larger than the volume size, write the file
If (strlen ( $sql ) >= $size * 1000) {
$file = $filename . "_v" . $p . ".sql";
If ($this->_write_file ($sql, $file, $dir)) {
$this->_showMsg("-Volume-" . $p . "-Data backup completed, backup file[".$dir.$ file." ]");
} else {
$this->_showMsg("Volume-" . $p . "-Backup failed!",true);
return false;
}
// Next sub -roll >
$p ++;
//Reset the $sql variable to empty and recalculate the size of the variable
$sql = "";
}
}
}
// The size of the sql is not enough.
if ($sql != "") {
$filename .= "_v" . $p . ".sql";
If ($this->_write_file ($sql, $filename, $dir)) {
$this->_showMsg("-Volume-" . $p . "-Data backup completed, backup file [ ".$dir.$ filename." ]");
} else {
$this->_showMsg("Volume-" . $p . "-Backup failed",true);
return false;
}
}
$this->_showMsg("Congratulations! Backup successful");
}
}
// Output information in time
Private function _showMsg($msg,$err=false){
$err = $err ? "ERROR:" : '' ;
echo " ".$err . $msg." ";
flush();
}
/**
* Insert basic database backup information
*
* @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 .= '-- 主机: ' . $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;
}
/**
* Insert table structure
*
* @param unknown_type $table
* @return string
*/
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;
}
/**
* Insert a single record
*
* @param string $table
* @param int $num_fields
* @param array $record
* @return string
*/
Private function _insert_record($table, $num_fields, $record) {
// sql field comma separated
$insert = '';
$comma = "";
$insert .= "INSERT INTO `" . $table . "` VALUES(";
// Loop the content under each subsection
for($i = 0; $i < $num_fields; $i ++) {
$insert .= ($comma . "'" . mysql_escape_string ( $record [$i] ) . "'");
$comma = ",";
}
$insert .= ");" . $this->ds;
return $insert;
}
/**
*Write to file
*
* @param string $sql
* @param string $filename
* @param string $dir
* @return boolean
*/
Private function _write_file($sql, $filename, $dir) {
$dir = $dir ? $dir : './backup/';
//Create directory
If (! is_dir ( $dir )) {
mkdir ( $dir, 0777, true );
}
$re = true;
If (! @$fp = fopen ( $dir . $filename, "w+" )) {
$re = false;
$this->_showMsg("Failed to open sql file!",true);
}
If (! @fwrite ( $fp, $sql )) {
$re = false;
$ This-& gt; _ShowMSG ("Write in the SQL file failed, please write whether the file can be written", true);
}
If (! @fclose ( $fp )) {
$re = false;
$this->_showMsg("Failed to close sql file!",true);
}
return $re;
}
/*
*
* ---------------------------- Top: Database export ----------- dividing line - ---------Next: Database import--------------------------------
*/
/**
* Import backup data
* Description: Volume file format 20120516211738_all_v1.sql
* Parameter: File path (required)
*
* @param string $sqlfile
*/
Function restore($sqlfile) {
// Check whether the file exists
If (! file_exists ( $sqlfile )) {
$this->_showMsg("The sql file does not exist! Please check",true);
exit ();
}
$this->lock ( $this->database );
// Get the database storage location
$sqlpath = pathinfo ($sqlfile);
$this->sqldir = $sqlpath ['dirname'];
// Check whether it contains sub-volumes, separate files like 20120516211738_all_v1.sql from _v, if there are sub-volumes, it means there are sub-volumes
$volume = explode ( "_v", $sqlfile );
$volume_path = $volume [0];
$this->_showMsg("Do not refresh and close the browser to prevent the program from being terminated. If you are not careful! The database structure will be damaged");
$this->_showMsg("Importing backup data, please wait!");
If (empty ( $volume [1] )) {
$this->_showMsg ( "Importing sql: " . $sqlfile . '');
// No scroll
If ($this->_import ( $sqlfile )) {
$this->_showMsg( "Database imported successfully!");
} else {
$ This-& gt; _ShowMSG ('database instruction failed!', True);
exit ();
}
} else {
// There is a scroll, then the current is the current roll, and the remaining scrolls are performed by the cycle
$volume_id = explode ( ".sq", $volume [1] );
//The current volume is $volume_id
$volume_id = intval ( $volume_id [0] );
while ( $volume_id ) {
$tmpfile = $volume_path . "_v" . $volume_id . ".sql";
// Other volumes exist, continue execution
If (file_exists ( $tmpfile )) {
// Execute the import method
$this->msg .= "Importing volume $volume_id: " . $tmpfile . '
';
If ($this->_import ($tmpfile)) {
} else {
$volume_id = $volume_id ? $volume_id :1;
exit ( "Import volume: " . $tmpfile . 'Failed! The database structure may be damaged! Please try to import from volume 1' );
}
} else {
$this->msg .= "All backups of this volume have been imported successfully!
";
return;
}
$volume_id ++;
}
}if (empty ( $volume [1] )) {
$this->_showMsg ( "Importing sql: " . $sqlfile . '');
// No scroll
If ($this->_import ( $sqlfile )) {
$this->_showMsg( "Database imported successfully!");
} else {
exit ();
}
} else {
// There is a scroll, then the current is the current roll, and the remaining scrolls are performed by the cycle
$volume_id = explode ( ".sq", $volume [1] );
//The current volume is $volume_id
$volume_id = intval ( $volume_id [0] );
while ( $volume_id ) {
$tmpfile = $volume_path . "_v" . $volume_id . ".sql";
// Other volumes exist, continue execution
If (file_exists ( $tmpfile )) {
// Execute the import method
$this->msg .= "Importing volume $volume_id: " . $tmpfile . '
';
If ($this->_import ($tmpfile)) {
} else {
$volume_id = $volume_id ? $volume_id :1;
exit ( "Import volume: " . $tmpfile . 'Failed! The database structure may be damaged! Please try to import from volume 1' );
}
} else {
$this->msg .= "All backups of this volume have been imported successfully!
";
return;
}
$volume_id ++;
}
}
}
/**
* Import sql into the database (normal import)
*
* @param string $sqlfile
* @return boolean
*/
Private function _import($sqlfile) {
// SQL statement array contained in the sql file
$sqls = array ();
$f = fopen ( $sqlfile, "rb" );
//Create table buffer variable
$create_table = '';
while ( ! feof ( $f ) ) {
// Read each row of sql
$line = fgets ( $f );
// This step is to synthesize the created table into a complete sql statement
// If the end does not contain ';' (that is, a complete sql statement, here is the insert statement), and does not contain 'ENGINE=' (that is, the last sentence to create a table)
If (! preg_match ( '/;/', $line ) || preg_match ( '/ENGINE=/', $line )) {
// Save this sql statement and the created table sql connection
$create_table .= $line;
// If it contains the last sentence of creating a table
If (preg_match ( '/ENGINE=/', $create_table)) {
//Execute sql statement to create table
$this->_insert_into($create_table);
//Clear the current table and prepare to create the next table
$create_table = '';
}
// Skip this time
continue;
}
//Execute sql statement
$this->_insert_into($line);
}
fclose ( $f );
return true;
}
//Insert a single sql statement
Private function _insert_into($sql){
If (! mysql_query ( trim ( $sql ) )) {
$this->msg .= mysql_error ();
return false;
}
}
/*
* -------------------------------Database importend--------------- ------------------
*/
// Close database connection
private function close() {
mysql_close ($this->db);
}
// Lock the database to avoid errors during backup or import
Private function lock($tablename, $op = "WRITE") {
If (mysql_query ( "lock tables " . $tablename . " " . $op ))
return true;
else
return false;
}
// Unlock
private function unlock() {
if (mysql_query ( "unlock tables" ))
return true;
else
return false;
}
// Destruction
Function __destruct() {
If($this->db){
mysql_query ( "unlock tables", $this->db );
mysql_close ($this->db);
}
}
}
|
