This article describes the PHP implementation of the MySQL backup recovery method of the volume processing. Share to everyone for your reference. The specific analysis is as follows:
Processing is the grasp of the data to be processed into a small file for processing, here I would like to introduce a PHP MySQL backup recovery processing class, to achieve MySQL database backup, select the table for backup, to achieve a single SQL file and the volume of SQL import.
Sub-volume import class and the idea of detailed
Database import Export is a necessary feature in the background, a search on the internet, there are many about the database import export, but basically a large system, including many we do not need, and they are the form of their own backstage, I do not like to take people's things into their own backstage, I need is their own things, So the reference to a lot, I wrote a about the introduction of the volume of the class to facilitate the call, welcome everyone to Pat Bricks.
Here for the volume file is the ' _v1.sql ' end, the implementation of a single SQL file and the volume of SQL import, the split-volume import can choose whether the current volume import the remaining volumes, we need to directly call the class to complete.
Host, user name, password, database name, database code, respectively
Copy Code code as follows:
$db = new Datamanage (' localhost ', ' root ', ' root ', ' test ', ' UTF8 ');
SQL file, whether to import only a single SQL (that is, if there are other volumes that are not imported).
Copy Code code as follows:
$db->restore ('./backup/20120516211738_all_v1.sql ', false);
corresponding to how to list the backup of the SQL file or choose SQL, and so on, the implementation of their own, that is not in this category, but also very simple.
There are currently only implemented database import, about the database export, writing function, the following is the complete class code, concrete ideas and implementation of the code inside all have instructions, here is not to repeat, the code is as follows:
Copy Code code as follows:
<?php
/**
* @author Yanue
* Description: The _v1.sql is the end of the volume file
* Function: Implement a single SQL file and the volume of SQL import, the split-volume import can choose whether the current volume to import the remaining volumes
* Use method:
*
*
* ------------------------------------------------------------------
Host, user name, password, database name, database code, respectively
$db = new Datamanage (' localhost ', ' root ', ' root ', ' test ', ' UTF8 ');
SQL file, whether to import only single SQL (that is, if there are other volumes that are not imported)
$db->restore ('./backup/20120516211738_all_v1.sql ', false);
*----------------------------------------------------------------------
*/
Class Datamanage {
var $db; Database connection
var $database; The database used
var $sqldir; Database backup folder
/**
* Initialization
*
* @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;
Connecting to a database
$this->db = mysql_connect ($this->host, $this->username, $this->password) or Die ("database connection failed.");
Choose which database to use
mysql_select_db ($this->database, $this->db) or Die ("Cannot open database");
How the database is encoded
mysql_query (' SET NAMES '. $this->charset, $this->db);
}
/**
* Import Backup Data
* Description: Volume file format 20120516211738_all_v1.sql
*
* @param string $sqlfile
* @param bool $single
*/
function Restore ($sqlfile, $single = FALSE) {
Detect if a file exists
if (! file_exists ($sqlfile)) {
Exit ("file does not exist!") Please check ");
}
$this->lock ($this->database);
Get Database storage location
$sqlpath = PathInfo ($sqlfile);
$this->sqldir = $sqlpath [' dirname '];
Detects if a volume is included, separates similar 20120516211738_all_v1.sql from _v, and indicates a split volume
$volume = Explode ("_v", $sqlfile);
$volume _path = $volume [0];
echo "Do not refresh and close the browser to prevent the program from being aborted, if careless!" will cause the database structure to be damaged <br/> ";
ECHO is importing backup data, please wait a moment! <br/> ";
if (Emptyempty ($volume [1]) | | $single) {
echo "is importing Sql:<span style= ' color: #f00; ' > ". $sqlfile. ' </span><br/> ';
No sub-volume
if ($this->_import ($sqlfile)) {
echo "Database import succeeded!" ";
} else {
Exit (' Database import failed! ' );
}
} else {
A volume is present, the current is the first few volumes, and the remaining volumes are cycled
$volume _id = Explode (". Sq", $volume [1]);
Current sub-volume is $volume_id
$volume _id = intval ($volume _id [0]);
while ($volume _id) {
$tmpfile = $volume _path. "_v". $volume _id. ". SQL";
Additional volumes exist, continue execution
if (file_exists ($tmpfile)) {
To perform an import method
ECHO is importing the volume $volume_id:<span style= ' color: #f00; ' > ". $tmpfile. ' </span><br/> ';
if ($this->_import ($tmpfile)) {
} else {
Exit ("Import volume $volume_id:<span style= ' color: #f00; ' > ". $tmpfile. ' </span> failed! The database structure may be corrupted! Please try to import ' from Volume 1 ');
}
} else {
echo "This volume backup was successfully imported!" <br/> ";
Return
}
$volume _id + +;
}
}
}
/**
* Import SQL into the database (normal import)
*
* @param string $sqlfile
* @return Boolean
*/
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;
}
To close a database connection
Private Function Close () {
Mysql_close ($this->db);
}
Lock the database to prevent 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;
}
destructor
function __destruct () {
mysql_query ("Unlock Tables", $this->db);
Mysql_close ($this->db);
}
}
?>
MySQL backup restores the volume processing, the invocation is simple.
Sub-volume Import ideas:
Read the SQL file by row, save each row as a complete SQL statement to an array and then loop through the Insert database. But it took me a long time to create a table statement with more than one line, which needs to be handled separately. Feel the article is very long ah, mainly that class file to occupy.
Update Description:
1. Eliminate SQL Import when excluding the comments in the SQL file '-' so that the single double quotes in SQL can not be imported
2. Direct-line read SQL execution, avoid the combination of SQL statements into the array and then read the import SQL from the array, improve efficiency.
Download Address: Https://github.com/yanue/Dbmanage
The exported SQL file format is as follows:
Copy Code code as follows:
--
--MySQL database dump
--Created by Dbmanage class and power by Yanue.
--
--Host: localhost
--Date Created: 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 (a) unsigned not NULL auto_increment,
' Content ' text not NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=2 DEFAULT Charset=utf8;
--
--the data in the Dump table AA
--
INSERT into ' AA ' VALUES (' 1 ', ' <p id= "test" ><span class= ' hahh ' style= ' line-height ":;" > I am testing data hehe </span></p> ');
The following is the class code:
Copy Code code as follows:
<?php
/**
* @author Yanue
* @copyright Copyright (c) yanue.net
* @version 1.1
* Date Created: May 21, 2012
Update Time: October 6, 2012
Update Note: 1. Eliminate SQL Import by excluding annotations in SQL Files '--' to resolve SQL single double quotes cannot be imported
2. Single-row read SQL direct execution, avoid the combination of SQL statements into the array and then read the import SQL from the array, improve efficiency
* Description: The volume file is _v1.sql (20120522021241_all_v1.sql)
* Function: To achieve the MySQL database of the volume backup, select the table for backup, implementation of a single SQL file and the volume of SQL import
* Use method:
*
*------1. Database backup (Export)------------------------------------------------------------
Host, user name, password, database name, database code, respectively
$db = new Dbmanage (' localhost ', ' root ', ' root ', ' test ', ' UTF8 ');
Parameters: Which table to back up (optional), backup directory (optional, default to Backup), volume size (optional, default 2000, or 2M)
$db->backup ();
*------2. Database recovery (Import)------------------------------------------------------------
Host, user name, password, database name, database code, respectively
$db = new Dbmanage (' localhost ', ' root ', ' root ', ' test ', ' UTF8 ');
Parameters: SQL file
$db->restore ('./backup/20120516211738_all_v1.sql ');
*----------------------------------------------------------------------
*/
Class Dbmanage {
var $db; Database connection
var $database; The database used
var $sqldir; Database backup folder
Line feed
Private $ds = "n";
variables that store SQL
Public $sqlContent = "";
The end of each SQL statement
Public $sqlEnd = ";";
/**
* Initialization
*
* @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);//No time limit
@ob_end_flush ();
Connecting to a database
$this->db = @mysql_connect ($this->host, $this->username, $this->password) or Die (' <p class= ' Dbdebug "&G") T;<span class= "Err" >mysql Connect Error: </span> '. Mysql_error (). ' </p> ');
Choose which database to use
mysql_select_db ($this->database, $this->db) or Die (' <p class= "dbdebug" ><span class= "Err" >mysql Connect error:</span> '. Mysql_error (). ' </p> ');
How the database is encoded
mysql_query (' SET NAMES '. $this->charset, $this->db);
}
/*
* New Query database table
*/
function Gettables () {
$res = mysql_query ("show TABLES");
$tables = Array ();
while ($row = Mysql_fetch_array ($res)) {
$tables [] = $row [0];
}
return $tables;
}
/*
*
*------------------------------------------Database backup start----------------------------------------------------------
*/
/**
* Database backup
* Parameters: Which table to back up (optional), backup directory (optional, default to Backup), volume size (optional, default 2000, that is, 2M)
*
* @param $string $dir
* @param int $size
* @param $string $tablename
*/
function backup ($tablename = ', $dir, $size) {
$dir = $dir? $dir: './backup/';
Create a table of contents
if (! Is_dir ($dir)) {
mkdir ($dir, 0777, True) or Die (' Create folder failed ');
}
$size = $size? $size: 2048;
$sql = ';
Back up only a table
if (! Emptyempty ($tablename)) {
if (@mysql_num_rows ("show TABLES like", $tablename. "mysql_query") = = 1) {
} else {
$this->_showmsg (' Table-<b> '. $tablename. </b>-does not exist, please check! ', true);
Die ();
}
$this->_showmsg (' Backing up the table <span class= ' imp ' > '. $tablename. ' </span> ');
Insert Dump Information
$sql = $this->_retrieve ();
Insert Table structure Information
$sql. = $this->_insert_table_structure ($tablename);
Inserting data
$data = mysql_query ("SELECT * from".) $tablename);
Previous section of file name
$filename = Date (' Ymdhis '). "_" . $tablename;
Number of fields
$num _fields = Mysql_num_fields ($data);
First few volumes
$p = 1;
Loop each record
while ($record = Mysql_fetch_array ($data)) {
Single record
$sql. = $this->_insert_record ($tablename, $num _fields, $record);
Write file if it is greater than the size of the split volume
if (strlen ($sql) >= $size * 1024) {
$file = $filename. "_v". $p. ". SQL";
if ($this->_write_file ($sql, $file, $dir)) {
$this->_showmsg ("Table-<b>". $tablename. "</b>-volume-<b>". $p. "</b>-data backup complete, backup file [<span class= ' imp ' >". $dir. $file. " </span>] ");
} else {
$this->_showmsg ("Backup Table-<b>". $tablename. "</b>-failed", true);
return false;
}
Next Sub-volume
$p + +;
Reset the $sql variable to be empty, recalculate the variable size
$sql = "";
}
}
Clean up data in time
Unset ($data, $record);
SQL size is not large enough to be divided into volumes
if ($sql!= "") {
$filename. = "_v". $p. ". SQL";
if ($this->_write_file ($sql, $filename, $dir)) {
$this->_showmsg ("Table-<b>". $tablename. "</b>-volume-<b>". $p. "</b>-data backup complete, backup file [<span class= ' imp ' >". $dir. $filename. " </span>] ");
} else {
$this->_showmsg ("Backup Volume-<b>". $p. "</b>-failure <br/>");
return false;
}
}
$this->_showmsg ("Congratulations!") <span class= ' imp ' > Backup Success </span> ');
} else {
$this->_showmsg (' being backed up ');
Back up all Tables
if ($tables = mysql_query ("Show Table status from".) $this->database)) {
$this->_showmsg ("read the database structure successfully!") ");
} else {
$this->_showmsg ("Failed to read the database structure!) ");
Exit (0);
}
Insert Dump Information
$sql. = $this->_retrieve ();
Previous section of file name
$filename = Date (' Ymdhis '). "_all";
Find All Tables
$tables = mysql_query (' Show Tables ');
First few volumes
$p = 1;
Loop All Tables
while ($table = Mysql_fetch_array ($tables)) {
Get table name
$tablename = $table [0];
Get table structure
$sql. = $this->_insert_table_structure ($tablename);
$data = mysql_query ("SELECT * from".) $tablename);
$num _fields = Mysql_num_fields ($data);
Loop each record
while ($record = Mysql_fetch_array ($data)) {
Single record
$sql. = $this->_insert_record ($tablename, $num _fields, $record);
Write file if it is greater than the size of the split volume
if (strlen ($sql) >= $size * 1000) {
$file = $filename. "_v". $p. ". SQL";
Write to File
if ($this->_write_file ($sql, $file, $dir)) {
$this->_showmsg ("-Volume-<b>". $p. "</b>-data backup complete, backup file [<span class= ' imp ' >". $dir. $file. " </span>] ");
} else {
$this->_showmsg ("Volume-<b>". $p. "</b>-Backup Failed!", true);
return false;
}
Next Sub-volume
$p + +;
Reset the $sql variable to be empty, recalculate the variable size
$sql = "";
}
}
}
SQL size is not large enough to be divided into volumes
if ($sql!= "") {
$filename. = "_v". $p. ". SQL";
if ($this->_write_file ($sql, $filename, $dir)) {
$this->_showmsg ("-Volume-<b>". $p. "</b>-data backup complete, backup file [<span class= ' imp ' >". $dir. $filename. " </span>] ");
} else {
$this->_showmsg ("Volume-<b>". $p. "</b>-Backup Failed", true);
return false;
}
}
$this->_showmsg ("Congratulations!") <span class= ' imp ' > Backup Success </span> ');
}
}
Timely output information
Private Function _showmsg ($msg, $err =false) {
$err = $err? "<span class= ' err ' >ERROR:</span>": ';
echo "<p class= ' Dbdebug ' >". $err. $msg. " </p> ";
Flush ();
}
/**
* Insert Database backup base 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. = '--Host: '. $this->host. $this->ds;
$value. = '--Date generated: '. Date (' Y '). ' Year '. Date (' m '). ' Month '. Date (' d '). ' Day '. Date (' H:i '). $this->ds;
$value. = '--MySQL version: '. Mysql_get_server_info (). $this->ds;
$value. = '--PHP version: '. Phpversion (). $this->ds;
$value. = $this->ds;
$value. = '--'. $this->ds;
$value. = '--database: '. $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. = "--the structure of a table." $table. $this->ds;
$sql. = "--". $this->ds. $this->ds;
Delete table if present
$sql. = "DROP TABLE IF EXISTS '". $table. '`' . $this->sqlend. $this->ds;
Get detailed table information
$res = mysql_query (' Show CREATE TABLE '. $table. '`' );
$row = Mysql_fetch_array ($res);
$sql. = $row [1];
$sql. = $this->sqlend. $this->ds;
Plus
$sql. = $this->ds;
$sql. = "--". $this->ds;
$sql. = "--dump data in a table." $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 split
$insert = ';
$comma = "";
$insert. = "INSERT INTO". $table. "' VALUES (";
Loop below each sub-paragraph
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 a table of contents
if (! Is_dir ($dir)) {
mkdir ($dir, 0777, true);
}
$re = true;
if (! @ $fp = fopen ($dir. $filename, "w+")) {
$re = false;
$this->_showmsg ("Open SQL file failed!") ", true);
}
if (! @fwrite ($FP, $sql)) {
$re = false;
$this->_showmsg ("Write SQL file failed, please file is writable", true);
}
if (! @fclose ($fp)) {
$re = false;
$this->_showmsg ("Shutdown SQL file failed!") ", true);
}
return $re;
}
/*
*
*-------------------------------: Database export-----------Split line----------: Database import--------------------------------
*/
/**
* Import Backup Data
* Description: Volume file format 20120516211738_all_v1.sql
* Parameter: File path (required)
*
* @param string $sqlfile
*/
function Restore ($sqlfile) {
Detect if a file exists
if (! file_exists ($sqlfile)) {
$this->_showmsg ("SQL file does not exist!") Please check ", true";
Exit ();
}
$this->lock ($this->database);
Get Database storage location
$sqlpath = PathInfo ($sqlfile);
$this->sqldir = $sqlpath [' dirname '];
Detects if a volume is included, separates similar 20120516211738_all_v1.sql from _v, and indicates a split volume
$volume = Explode ("_v", $sqlfile);
$volume _path = $volume [0];
$this->_showmsg ("Do not refresh and close the browser to prevent the program from being aborted, if careless!") will cause damage to the database structure ");
$this->_showmsg ("You are importing backup data, wait a minute!") ");
if (Emptyempty ($volume [1])) {
$this->_showmsg ("Importing Sql:<span class= ' imp ' >". $sqlfile. ' </span> ');
No sub-volume
if ($this->_import ($sqlfile)) {
$this->_showmsg ("Database import succeeded!") ");
} else {
$this->_showmsg (' Database import failed! ', true);
Exit ();
}
} else {
A volume is present, the current is the first few volumes, and the remaining volumes are cycled
$volume _id = Explode (". Sq", $volume [1]);
Current sub-volume is $volume_id
$volume _id = intval ($volume _id [0]);
while ($volume _id) {
$tmpfile = $volume _path. "_v". $volume _id. ". SQL";
Additional volumes exist, continue execution
if (file_exists ($tmpfile)) {
To perform an import method
$this->msg. = "Importing a volume $volume _id: <span style= ' color: #f00; ' > ". $tmpfile. ' </span><br/> ';
if ($this->_import ($tmpfile)) {
} else {
$volume _id = $volume _id? $volume _id:1;
Exit ("Import Volume: <span style= ' color: #f00; ' > ". $tmpfile. ' </span> failed! The database structure may be corrupted! Please try to import ' from Volume 1 ');
}
} else {
$this->msg. = "This volume backup was successfully imported!" <br/> ";
Return
}
$volume _id + +;
}
}if (Emptyempty ($volume [1])) {
$this->_showmsg ("Importing Sql:<span class= ' imp ' >". $sqlfile. ' </span> ');
No sub-volume
if ($this->_import ($sqlfile)) {
$this->_showmsg ("Database import succeeded!") ");
} else {
$this->_showmsg (' Database import failed! ', true);
Exit ();
}
} else {
A volume is present, the current is the first few volumes, and the remaining volumes are cycled
$volume _id = Explode (". Sq", $volume [1]);
Current sub-volume is $volume_id
$volume _id = intval ($volume _id [0]);
while ($volume _id) {
$tmpfile = $volume _path. "_v". $volume _id. ". SQL";
Additional volumes exist, continue execution
if (file_exists ($tmpfile)) {
To perform an import method
$this->msg. = "Importing a volume $volume _id: <span style= ' color: #f00; ' > ". $tmpfile. ' </span><br/> ';
if ($this->_import ($tmpfile)) {
} else {
$volume _id = $volume _id? $volume _id:1;
Exit ("Import Volume: <span style= ' color: #f00; ' > ". $tmpfile. ' </span> failed! The database structure may be corrupted! Please try to import ' from Volume 1 ');
}
} else {
$this->msg. = "This volume backup was successfully imported!" <br/> ";
Return
}
$volume _id + +;
}
}
}
/**
* Import SQL into the database (normal import)
*
* @param string $sqlfile
* @return Boolean
*/
Private Function _import ($sqlfile) {
SQL file contains an array of SQL statements
$sqls = Array ();
$f = fopen ($sqlfile, "RB");
Create a table buffer variable
$create _table = ';
while (! feof ($f)) {
Read each row of SQL
$line = fgets ($f);
This step in order to create a table to synthesize the 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 of the creation table)
if (! Preg_match ('/;/', $line) | | | preg_match ('/engine=/', $line)) {
Save this SQL statement with creating a table SQL connection
$create _table. = $line;
If you include the last sentence for creating a table
if (Preg_match ('/engine=/', $create _table)) {
Execute SQL statement Creation table
$this->_insert_into ($create _table);
Empty the current and prepare for the next table creation
$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 Import End---------------------------------
*/
To close a database connection
Private Function Close () {
Mysql_close ($this->db);
}
Lock the database to prevent 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;
}
destructor
function __destruct () {
if ($this->db) {
mysql_query ("Unlock Tables", $this->db);
Mysql_close ($this->db);
}
}
}
?>
I hope this article will help you with your PHP program design.
