Hands-on teaching you to implement a backup restore of MySQL
Sample code with my familiar PHP, of course, you read and understand the ideas, I believe you can quickly use your familiar language to write out.
First, create a new Dbbackup class and set the default parameters.
Copy Code code 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 encoding: Mysql_set_charset
}
Second, add database connection function.
Copy Code code 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); Set encoding
return $con;
}
Third, query database table collection
Copy Code code as follows:
/**
* Table Collection ...
*/
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 table structure
Copy Code code 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. = "--Structure of the table: {$temp [0]}--\r\n";
$sql. = "{$temp [1]}";
$sql. = ";--<xjx>--\r\n\r\n";
}
return $sql;
}
Note: $sql. = ";--<xjx>--\r\n\r\n";Each SQL must be followed by a semicolon (;) split, MySQL import can be recognized. --<xjx>--is the identity of the program to split the SQL statement, can be customized but must be an annotation statement, otherwise affect the SQL statement. \ r \ n No practical meaning for text aesthetics
v. INSERT into statement
Copy Code code as follows:
/**
* Data inserted into SQL ...
*/
function Sqlinsert () {
$sql = ';
$TB = $this->tblist ();
foreach ($tb as $v) {
$rs =mysql_query ("SELECT * from $v");
if (!mysql_num_rows ($rs)) {//No data 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 $v 2) {
if ($v 2===null) {
$sql. = "NULL,";
}
else {
$v 2=mysql_real_escape_string ($v 2);
$sql. = "' $v 2 ',";
}
}
$sql =mb_substr ($sql, 0,-1);
$sql. = "), \ r \ n";
}
$sql =mb_substr ($sql, 0,-3);
$sql. = ";--<xjx>--\r\n\r\n";
}
return $sql;
}
Note:
1. No data return must jump out of this cycle, to avoid generating redundant code
2. When the field value is (null), the caret is identifier (NULL) instead of (' null '), and there is no single quotation mark. 3. $v 2=Mysql_real_escape_string($v 2), which is necessary to escape
4.MB_SUBSTR ($sql, 0,-1), Mb_substr ($sql, 0,-3), you must remove the last comma (,) or the SQL statement fails5.$sql. = ";--<xjx>--\r\n\r\n", as detailed in step fourth note
Vi. Backup operations
Copy Code code as follows:
/**
* Backup ...
* @param $filename file path
*/
function Beifen ($filename) {
$this->db (); Connecting to a database
$sql = $this->sqlcreate ();
$sql 2= $this->sqlinsert ();
$data = $sql. $sql 2;
Return file_put_contents ($filename, $data);
}
Seven, restore operation
Copy Code code as follows:
/**
* Restore ...
* @param $filename file path
*/
function Huanyuan ($filename) {
$this->db (); Connecting to a database
Delete 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 Examples:
Copy Code code as follows:
$x =new dbBackup ();
$x->database= ' test ';
$rs = $x->beifen (' Db.sql ');
Var_dump ($RS);
Restore Example:
Copy Code code as follows:
$x =new dbBackup ();
$x->database= ' test ';
$rs = $x->huanyuan (' Db.sql ');
Var_dump ($RS);
Complete code:
Copy Code code 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 encoding: Mysql_set_charset
/**
* Backup ...
* @param $filename file path
*/
function Beifen ($filename) {
$this->db (); Connecting to a database
$sql = $this->sqlcreate ();
$sql 2= $this->sqlinsert ();
$data = $sql. $sql 2;
Return file_put_contents ($filename, $data);
}
/**
* Restore ...
* @param $filename file path
*/
function Huanyuan ($filename) {
$this->db (); Connecting to a database
Delete 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); Set encoding
return $con;
}
/**
* Table Collection ...
*/
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. = "--Structure of the table: {$temp [0]}--\r\n";
$sql. = "{$temp [1]}";
$sql. = ";--<xjx>--\r\n\r\n";
}
return $sql;
}
/**
* Data inserted into SQL ...
*/
function Sqlinsert () {
$sql = ';
$TB = $this->tblist ();
foreach ($tb as $v) {
$rs =mysql_query ("SELECT * from $v");
if (!mysql_num_rows ($rs)) {//No data 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 $v 2) {
if ($v 2===null) {
$sql. = "NULL,";
}
else {
$v 2=mysql_real_escape_string ($v 2);
$sql. = "' $v 2 ',";
}
}
$sql =mb_substr ($sql, 0,-1);
$sql. = "), \ r \ n";
}
$sql =mb_substr ($sql, 0,-3);
$sql. = ";--<xjx>--\r\n\r\n";
}
return $sql;
}
}
Backup
$x =new dbBackup ();
$x->database= ' test ';
$rs = $x->beifen (' Db.sql ');
Var_dump ($RS);
Restores
$x =new dbBackup ();
$x->database= ' test ';
$rs = $x->huanyuan (' Db.sql ');
Var_dump ($RS);
