PHP implementation MySQL database backup and restore class instance _php skill

Source: Internet
Author: User
Tags explode mysql version strlen

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.