Detailed description of MYSQL Backup Recovery (implemented in PHP)

How to restore a MYSQL backup
The sample code uses PHP, which you are familiar with. Of course, you have read and understood the ideas in the sample code. I believe that you can quickly write the sample code in a language you are familiar.
1. Create a dbBackup class and set the default parameters.
Copy codeThe Code is as follows: class dbBackup {
Public $ host = 'localhost'; // database address
Public $ user = 'root'; // Login Name
Public $ pwd = ''; // Password
Public $ database; // database Name
Public $ charset = 'utf8'; // database connection code: mysql_set_charset
}

2. Add the database connection function.
Copy codeThe Code is as follows :/**
* Connect to the database...
*/
Function db (){
$ Con = mysql_connect ($ this-> host, $ this-> user, $ this-> pwd );
If (! $ Con ){
Die ('could not connect ');
}

$ Db_selected = mysql_select_db ($ this-> database, $ con );
If (! $ Db_selected ){
Die ('can \'t use select db ');
}

Mysql_set_charset ($ this-> charset); // sets the Encoding

Return $ con;
}

Iii. query database table sets
Copy codeThe Code is as follows :/**
* Table set...
*/
Function tblist (){
$ List = array ();

$ Rs = mysql_query ("show tables from $ this-> database ");
While ($ temp = mysql_fetch_row ($ rs )){
$ List [] = $ temp [0];
}

Return $ list;
}

Iv. query the table structure
Copy codeThe Code is as follows :/**
* Table Structure SQL...
*/
Function sqlcreate (){
$ SQL = '';

$ Tb = $ this-> tblist ();
Foreach ($ tb as $ v ){
$ Rs = mysql_query ("show create table $ v ");
$ Temp = mysql_fetch_row ($ rs );
$ SQL. = "-- table structure: {$ temp [0]} -- \ r \ n ";
$ SQL. = "{$ temp [1]}";
$ SQL. = "; -- <xjx> -- \ r \ n ";
}
Return $ SQL;
}

Note:$ SQL. = "; -- <xjx> -- \ r \ n"; each SQL statement must be separated by a semicolon (;) before MYSQL import can be identified. -- <Xjx> -- it is the identification that the program separates SQL statements. It can be customized but must be a comment statement. Otherwise, the SQL statement is affected. \ R \ n has no practical significance for text Aesthetics

5. insert into statement
Copy codeThe Code is as follows :/**
* Insert SQL data...
*/
Function sqlinsert (){
$ SQL = '';

$ Tb = $ this-> tblist ();
Foreach ($ tb as $ v ){
$ Rs = mysql_query ("SELECT * FROM $ v ");
If (! Mysql_num_rows ($ rs) {// No data is returned
Continue;
}
$ SQL. = "-- table data: $ v -- \ r \ n ";
$ SQL. = "INSERT INTO '$ V' VALUES \ r \ n ";
While ($ temp = mysql_fetch_row ($ rs )){
$ SQL. = '(';
Foreach ($ temp as $ v2 ){
If ($ v2 === null ){
$ SQL. = "NULL ,";
}
Else {
$ V2 = mysql_real_escape_string ($ v2 );
$ SQL. = "'$ v2 ',";
}
}
$ SQL = mb_substr ($ SQL, 0,-1 );
$ SQL. = "), \ r \ n ";
}
$ SQL = mb_substr ($ SQL, 0,-3 );
$ SQL. = "; -- <xjx> -- \ r \ n ";
}

Return $ SQL;
}

Note:
1. Skip this loop when no data is returned to avoid generating redundant code
2. When the field value is (NULL), the inserted character is (NULL) instead of ('null') without single quotation marks. 3. $ v2 = mysql_real_escape_string ($ v2), which is a necessary escape
4. mb_substr ($ SQL, 0,-1), mb_substr ($ SQL, 0,-3), the last comma (,) must be removed; otherwise, an error occurs in the SQL statement 5. $ SQL. = "; -- <xjx> -- \ r \ n", see Step 4 Note.

Vi. Backup operations
Copy codeThe Code is as follows :/**
* Backup...
* @ Param $ filename file path
*/
Function beifen ($ filename ){
$ This-> db (); // connect to the database

$ SQL = $ this-> sqlcreate ();
$ Sql2 = $ this-> sqlinsert ();
$ Data = $ SQL. $ sql2;

Return file_put_contents ($ filename, $ data );
}

VII. Restore operations
Copy codeThe Code is as follows :/**
* Restore...
* @ Param $ filename file path
*/
Function huanyuan ($ filename ){
$ This-> db (); // connect to the database

// Delete a data table
$ List = $ this-> tblist ();
$ Tb = '';
Foreach ($ list as $ v ){
$ Tb. = "'$ V ',";
}
$ Tb = mb_substr ($ tb, 0,-1 );
If ($ tb ){
$ Rs = mysql_query ("drop table $ tb ");
If ($ rs = false ){
Return false;
}
}

// Execute SQL
$ Str = file_get_contents ($ filename );
$ Arr = explode ('-- <xjx> --', $ str );
Array_pop ($ arr );

Foreach ($ arr as $ v ){
$ Rs = mysql_query ($ v );
If ($ rs = false ){
Return false;
}
}

Return true;
}

Backup example:
Copy codeThe Code is as follows: $ x = new dbBackup ();
$ X-> database = 'test ';
$ Rs = $ x-> beifen ('db. SQL ');
Var_dump ($ rs );

Restoration example:
Copy codeThe Code is as follows: $ x = new dbBackup ();
$ X-> database = 'test ';
$ Rs = $ x-> huanyuan ('db. SQL ');
Var_dump ($ rs );

Complete code:
Copy codeThe Code is as follows: class dbBackup {
Public $ host = 'localhost'; // database address
Public $ user = 'root'; // Login Name
Public $ pwd = ''; // Password
Public $ database; // database Name
Public $ charset = 'utf8'; // database connection code: mysql_set_charset

/**
* Backup...
* @ Param $ filename file path
*/
Function beifen ($ filename ){
$ This-> db (); // connect to the database

$ SQL = $ this-> sqlcreate ();
$ Sql2 = $ this-> sqlinsert ();
$ Data = $ SQL. $ sql2;

Return file_put_contents ($ filename, $ data );
}

/**
* Restore...
* @ Param $ filename file path
*/
Function huanyuan ($ filename ){
$ This-> db (); // connect to the database

// Delete a data table
$ List = $ this-> tblist ();
$ Tb = '';
Foreach ($ list as $ v ){
$ Tb. = "'$ V ',";
}
$ Tb = mb_substr ($ tb, 0,-1 );
If ($ tb ){
$ Rs = mysql_query ("drop table $ tb ");
If ($ rs = false ){
Return false;
}
}

// Execute SQL
$ Str = file_get_contents ($ filename );
$ Arr = explode ('-- <xjx> --', $ str );
Array_pop ($ arr );

Foreach ($ arr as $ v ){
$ Rs = mysql_query ($ v );
If ($ rs = false ){
Return false;
}
}

Return true;
}

/**
* Connect to the database...
*/
Function db (){
$ Con = mysql_connect ($ this-> host, $ this-> user, $ this-> pwd );
If (! $ Con ){
Die ('could not connect ');
}

$ Db_selected = mysql_select_db ($ this-> database, $ con );
If (! $ Db_selected ){
Die ('can \'t use select db ');
}

Mysql_set_charset ($ this-> charset); // sets the Encoding

Return $ con;
}

/**
* Table set...
*/
Function tblist (){
$ List = array ();

$ Rs = mysql_query ("show tables from $ this-> database ");
While ($ temp = mysql_fetch_row ($ rs )){
$ List [] = $ temp [0];
}

Return $ list;
}

/**
* Table Structure SQL...
*/
Function sqlcreate (){
$ SQL = '';

$ Tb = $ this-> tblist ();
Foreach ($ tb as $ v ){
$ Rs = mysql_query ("show create table $ v ");
$ Temp = mysql_fetch_row ($ rs );
$ SQL. = "-- table structure: {$ temp [0]} -- \ r \ n ";
$ SQL. = "{$ temp [1]}";
$ SQL. = "; -- <xjx> -- \ r \ n ";
}
Return $ SQL;
}

/**
* Insert SQL data...
*/
Function sqlinsert (){
$ SQL = '';

$ Tb = $ this-> tblist ();
Foreach ($ tb as $ v ){
$ Rs = mysql_query ("SELECT * FROM $ v ");
If (! Mysql_num_rows ($ rs) {// No data is returned
Continue;
}
$ SQL. = "-- table data: $ v -- \ r \ n ";
$ SQL. = "INSERT INTO '$ V' VALUES \ r \ n ";
While ($ temp = mysql_fetch_row ($ rs )){
$ SQL. = '(';
Foreach ($ temp as $ v2 ){
If ($ v2 === null ){
$ SQL. = "NULL ,";
}
Else {
$ V2 = mysql_real_escape_string ($ v2 );
$ SQL. = "'$ v2 ',";
}
}
$ SQL = mb_substr ($ SQL, 0,-1 );
$ SQL. = "), \ r \ n ";
}
$ SQL = mb_substr ($ SQL, 0,-3 );
$ SQL. = "; -- <xjx> -- \ r \ n ";
}

Return $ SQL;
}
}
// Backup
// $ X = new dbBackup ();
// $ X-> database = 'test ';
// $ Rs = $ x-> beifen ('db. SQL ');
// Var_dump ($ rs );
// Restore
// $ X = new dbBackup ();
// $ X-> database = 'test ';
// $ Rs = $ x-> huanyuan ('db. SQL ');
// Var_dump ($ rs );