PHP implements MYSQL backup and restore

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
}

2. Add database connection function.

 /**
 * Connect to 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;
 }

3. Query database table collection

/**
 * 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;
 }

4. Query table structure

 /**
 *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]} --rn";
 $sql.="{$temp[1]}";
 $sql.=";--  --rnrn";
 }
 return $sql;
 }

Note: $sql.=";-- --rnrn"; Each SQL statement must be separated by a semicolon (;) , MYSQL import can be recognized. -- -- is the program's identifier for splitting SQL statements. It can be customized but must be a comment statement, otherwise it will affect the SQL statement. rn has no practical meaning and is used for text beauty.

5. INSERT INTO statement

 /**
 * Data insertion 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 --rn";
 $sql.="INSERT INTO `$v` VALUESrn";
 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.="),rn";
 }
 $sql=mb_substr($sql, 0, -3);
 $sql.=";--  --rnrn";
 }

 return $sql;
 }

Notes: 1. When no data is returned, this loop must be jumped out to avoid generating redundant code. 2. When the field value is (NULL), the inserted character is (NULL) instead of ('NULL'), without single quotes. 3.$v2=mysql_real_escape_string($v2), this is necessary escape 4.mb_substr($sql, 0, -1), mb_substr($sql, 0, -3), the last comma (,) must be removed, otherwise the SQL statement will error 5.$sql.=";-- --rnrn", see the note in step 4 for details

6. Backup operation

 /**
 * 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);
 }

7. Restore operation

 /**
 * Restore...
 * @param $filename file path
*/
 function huanyuan($filename) {
 $this->db(); //Connect to the database

 //Delete the 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('--  --', $str);
 array_pop($arr);

 foreach ($arr as $v) {
 $ rs=mysql_query($v);
 if ($rs===false) {
 return false;
 }
 }

 return true;
 }

to see the calling example. 1. Backup example:

$x=new dbBackup();
$x->database='test';
$rs=$x->beifen('db.sql');
var_dump($rs) ;

2, restore example:

$x=new dbBackup();
$x->database='test';
$rs=$x->huanyuan('db.sql');
var_dump($rs) ;

Full code:

/**
 * PHP implements mysql backup and restore
 * Organized Programmer’s Home bbs.it-home.org
*/
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(); //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 the 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('--  --', $str);
 array_pop($arr);

 foreach ($arr as $v) {
 $rs=mysql_query($v);
 if ($rs===false) {
 return false;
 }
 }

 return true ;
 }

 /**
 * Connect to 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.="-- Table structure: {$temp[0 ]} --rn";
 $sql.="{$temp[1]}";
 $sql.=";--  --rnrn";
 }
< p> return $sql;
 }

 /**
 * Data insertion 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 --rn";
 $sql.="INSERT INTO `$v` VALUESrn";
 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.="),rn";
 }
 $sql=mb_substr($sql, 0, - 3);
 $sql.=";--  --rnrn";
 }

 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);
?>

Articles you may be interested in: php database backup class Share a good php database backup class php complete backup database and backup classes of specified tables in the database php mysql database backup class and calling method php implements mysql backup and restore example code php mysql backup code (xml application) php data backup: single table backup, whole table backup, import database