How to Implement mysql backup and volume recovery in php

This article mainly introduces how to implement mysql Backup Recovery and volume sharding in php, including a complete MySQL Backup recovery file and usage example. The annotations contain detailed usage instructions, it is a very practical skill, and a friend in need can

This article mainly introduces how to implement mysql Backup Recovery and volume sharding in php, including a complete MySQL Backup recovery file and usage example. The annotations contain detailed usage instructions, it is a very practical skill, and a friend in need can

This example describes how to use php to restore mysql backups and volumes. Share it with you for your reference. The specific analysis is as follows:

Volume sharding refers to the process of dividing the data to be processed into small files. Here I will introduce you to a php mysql backup and recovery volume sharding class to achieve mysql database volume sharding backup, select a table for backup to import a single SQL file and a SQL volume.

Detailed description of volume-based import classes and ideas

Database Import and export is a necessary function in the background. There are a lot of Database Import and Export functions available on the Internet, but basically a large system contains a lot of what we don't need, and they are all forms of their own backend. What I don't like is to integrate people's things into their own backend. What I need is my own things, so I have referenced a lot, I wrote a volume-based import class to facilitate calling.

Here, the volume-based file ends with '_ v1. SQL' to import a single SQL file and a volume-based SQL statement. You can choose whether to import the current volume to the remaining volume, you only need to call the class directly.

They are the host, user name, password, database name, and database code.

The Code is as follows:

$ Db = new DataManage ('localhost', 'root', 'root', 'test', 'utf8 ');

SQL file, whether to import only a single SQL statement (that is, if there are other volumes are not imported ).

The Code is as follows:

$ Db-> restore ('./backup/20120516211738_all_v1. SQL', false );

Corresponding to how to list the backup SQL files or select SQL and so on, and implement it by yourself, that is not in this category, it is also very simple.

At present, only Database Import is implemented. The function of Database Export is being compiled. The following is the complete class code. The specific ideas and implementation code are described in detail. The Code is as follows:

The Code is as follows:

<? Php
/**
* @ Author yanue
* Note: The volume separation file ends with _ v1. SQL.
* Function: allows you to import a single SQL file and a sub-volume SQL statement. You can select whether to import the remaining sub-volumes to the sub-volume.
* Usage:
*
*
*------------------------------------------------------------------
// Host, user name, password, database name, and database code
$ Db = new DataManage ('localhost', 'root', 'root', 'test', 'utf8 ');
// SQL file, whether to import only a single SQL statement (that is, if other volumes 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;
// Connect to the database
$ This-> db = mysql_connect ($ this-> host, $ this-> username, $ this-> password) or die ("database connection failed .");
// Select the database to use
Mysql_select_db ($ this-> database, $ this-> db) or die ("unable to open the database ");
// Database encoding method
Mysql_query ('set names'. $ this-> charset, $ this-> db );
}

/**
* Import backup data
* Note: The volume-sharing file format is 20120516211738_all_v1. SQL.
*
* @ Param string $ sqlfile
* @ Param bool $ single
*/
Function restore ($ sqlfile, $ single = FALSE ){
// Check whether the file exists
If (! File_exists ($ sqlfile )){
Exit ("the file does not exist! Check ");
}
$ This-> lock ($ this-> database );
// Obtain the database storage location
$ Sqlpath = pathinfo ($ sqlfile );
$ This-> sqldir = $ sqlpath ['dirname'];
// Check whether the Sub-volume exists. Separate SQL statements similar to 20120516211738_all_v1.from _ v. If yes, the sub-volume exists.
$ Volume = explode ("_ v", $ sqlfile );
$ Volume_path = $ volume [0];
Echo "Do not refresh or close the browser to prevent program suspension! The database structure will be damaged.
";
Echo "importing backup data... please wait!
";
If (emptyempty ($ volume [1]) | $ single ){
Echo "importing SQL:". $ sqlfile .'
';
// No volume sharding
If ($ this-> _ import ($ sqlfile )){
Echo "the database has been imported! ";
} Else {
Exit ('database import failed! ');
}
} Else {
// If there is a volume Shard, the current volume is obtained and the remaining volume Shard is executed cyclically.
$ Volume_id = explode (". sq", $ volume [1]);
// The current volume Shard is $ volume_id
$ Volume_id = intval ($ volume_id [0]);
While ($ volume_id ){
$ Tmpfile = $ volume_path. "_ v". $ volume_id. ". SQL ";
// Other sub-volumes exist. Continue to execute
If (file_exists ($ tmpfile )){
// Execute the import Method
Echo "importing volume shards $ volume_id:". $ tmpfile .'
';
If ($ this-> _ import ($ tmpfile )){

} Else {
Exit ("import volume shard $ volume_id:". $ tmpfile. 'failed! The database structure may be damaged! Please try to import from shard 1 ');
}
} Else {
Echo "This volume backup is imported successfully!
";
Return;
}
$ Volume_id ++;
}
}
}

/**
* Import SQL statements to a 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;
}

// Close the 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;
}

// Structure
Function _ destruct (){
Mysql_query ("unlock tables", $ this-> db );
Mysql_close ($ this-> db );
}
}
?>

Mysql backup and recovery volume-based processing, simple call.

How to import data by volume:

Read the SQL file by row and save each row as a complete SQL statement to the array for recycling and insert it into the database. However, when creating a table, the statement is divided into multiple rows, which must be processed separately, it took me a long time. it seems that the article is very long, mainly because the class file is occupied.

Update description:

1. Remove the '-' comment in the SQL file during SQL import, so that the single double quotation marks in SQL cannot be imported.

2. directly execute the SQL statement after reading a single row to avoid combining the SQL statement into the array and then reading and importing the SQL statement from the array to Improve the efficiency.

: Https://github.com/yanue/Dbmanage

The exported SQL file format is as follows:

The Code is as follows: