The example of this article describes the PHP implementation of MySQL database backup and restore classes. Share to everyone for your reference. The specific analysis is as follows:
This is a very simple use of PHP to back up the MySQL database of the class file, we simply configured in the Dbmange of the user name and the connection to the database can be, let's take a look at this example, the code is as follows:
Copy Code code as follows:
<?php
/**
* Date Created: May 21, 2012
*
* 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
var $record;
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 $thisatabase
* @param string $charset
*/
function __construct ($host = ' localhost ', $username = ' root ', $password = ', $thisatabase = ' Test ', $charset = ' UTF8 ')
{
$this->host = $host;
$this->username = $username;
$this->password = $password;
$this->database = $thisatabase;
$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);
}
/*
*------------------------------------------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 = 2000)
{
$dir = $dir? $dir: ' backup/';
$size = $size? $size: 2000;
$sql = ';
Back up only a table
if (! Emptyempty ($tablename))
{
Echo ' is backing up the table '. $tablename. ' <br/> ';
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 * 1000)
{
$file = $filename. "_v". $p. ". SQL";
if ($this->_write_file ($sql, $file, $dir))
{
echo "Table-". $tablename. "-Volume-". $p. "-Data backup complete, generate backup file <span style= ' color: #f00; ' > $dir $filename</span><br/> ";
}
Else
{
echo "Backup table-". $tablename. "-Failure <br/>";
}
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))
{
echo "Table-". $tablename. "-Volume-". $p. "-Data backup complete, generate backup file <span style= ' color: #f00; ' > $dir $filename</span><br/> ";
}
Else
{
echo "Backup Volume-". $p. "-Failure <br/>";
}
}
}
Else
{//Back up all tables
if ($tables = mysql_query ("Show Table status from".) $this->database))
{
Echo Read the database structure successfully! <br/> ";
}
Else
{
Exit ("read the database structure successfully!") <br/> ");
}
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))
{
echo "-Volume-". $p. "-Data backup complete, generate backup file <span style= ' color: #f00; ' > $dir $file</span><br/> ";
}
Else
{
echo "Backup Volume-". $p. "-Failure <br/>";
}
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))
{
echo "-Volume-". $p. "-Data backup complete, generate backup file <span style= ' color: #f00; ' > $dir $filename<br/> ";
}
Else
{
echo "Backup Volume-". $p. "-Failure <br/>";
}
}
}
}
/**
* 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_real_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/';
Folders that do not exist are created
if (! file_exists ($dir)) {
mkdir ($dir);
}
$re = true;
if (! @ $fp = fopen ($dir. $filename, "w+")) {
$re = false;
Echo failed to open the file! ";
}
if (! @fwrite ($FP, $sql)) {
$re = false;
echo "Failed to write file, please write file";
}
if (! @fclose ($fp)) {
$re = false;
echo "Closes file failed!" ";
}
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))
{
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]))
{
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
{
$volume _id = Array ();
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 <span style= ' color: #f00; ' > ". $tmpfile. ' </span><br/> ';
if ($this->_import ($tmpfile))
{
}
Else
{
Exit ("Import volume <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) {
SQL file contains an array of SQL statements
$sqls = Array ();
$f = fopen ($sqlfile, "RB");
Create a table buffer variable
$create = ';
while (! feof ($f)) {
Read each row of SQL
$line = fgets ($f);
If you include a '--' annotation, or a blank line, skip
if (Trim ($line) = = ' | | preg_match ('/--*?/', $line, $match)) {
Continue
}
If the end contains '; ' (That is, a complete SQL statement, here is the INSERT statement) and does not contain ' engine= ' (that is, the last sentence to create the table),
if (! Preg_match ('/;/', $line, $match) | | preg_match ('/engine=/', $line, $match)) {
Save this SQL statement with creating a table SQL connection
$create. = $line;
If you include the last sentence for creating a table
if (Preg_match ('/engine=/', $create, $match)) {
Then merge it into an SQL array
$sqls [] = $create;
Empty the current and prepare for the next table creation
$create = ';
}
Skip this time
Continue
}
$sqls [] = $line;
}
Fclose ($f);
Loop SQL statement array, executed separately
foreach ($sqls as $sql) {
Str_replace ("n", "", $sql);
if (! mysql_query (Trim ($sql))) {
Echo Mysql_error ();
return false;
}
}
return true;
}
/*
*-------------------------------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 () {
mysql_query ("Unlock Tables", $this->db);
Mysql_close ($this->db);
}
}
$db = new Dbmanage (' localhost ', ' root ', ', ', ' Tao ', ' GBK ');
$db->backup (' tao_admin ');
$db->restore ('./backup/20140228222713_tao_admin_v1.sql ');
?>
I hope this article will help you with your PHP program design.
