Use php to export mysql database backup to sql example

Use php to export mysql database backup to sql example_PHP tutorial

WBOY

Release： 2016-07-13 10:47:00

Original

840 people have browsed it

A good example of using php to export mysql database backup to sql, I hope it will be helpful to everyone.

Using PHP code to implement database backup can make website management very convenient. We can directly enter the background operation to complete the database backup.
Key technologies:

1. First, you need to get the tables in the database using the function mysql_list_tables(), and then you can save all the obtained table names into an array.

2. show create table table name can get the table structure.

3. select * from table name, take out all the records, and use a loop to splice them into insert into... statements.

Function screenshot:

Use php to export mysql database backup to sql example_PHP tutorial

Effect of exported sql statement

Specific code:

The code is as follows

Copy code

header("Content-type:text/html;charset=utf-8");

//Configuration information

$cfg_dbhost = 'localhost';

$cfg_dbname = 'ftdm';

$cfg_dbuser = 'root';

$cfg_dbpwd = 'root';

$cfg_db_language = 'utf8';

$to_file_name = "ftdm.sql";

// END configuration

//Link database

$link = mysql_connect($cfg_dbhost,$cfg_dbuser,$cfg_dbpwd);

mysql_select_db($cfg_dbname);

//Select encoding

mysql_query("set names ".$cfg_db_language);

//What tables are there in the database

$tables = mysql_list_tables($cfg_dbname);

//Record these tables into an array

$tabList = array();

while($row = mysql_fetch_row($tables)){

$tabList[] = $row[0];

}

echo "Running, please wait patiently...
";

$info = "--------------------------------rn";

$info .= "-- Date:".date("Y-m-d H:i:s",time())."rn";

$info .= "-- Power by Daixiaorui Blog (http://www.daixiaorui.com/read/34.html)rn";

$info .= "--Only for testing and learning, this program is not suitable for processing extremely large amounts of datarn";

$info .= "--------------------------------rnrn";

file_put_contents($to_file_name,$info,FILE_APPEND);

//Export the table structure of each table to a file

foreach($tabList as $val){

$sql = "show create table ".$val;

$res = mysql_query($sql,$link);

$row = mysql_fetch_array($res);

$info = "--------------------------------rn";

$info .= "-- Table structure for `".$val."`rn";

$info .= "--------------------------------rn";

$info .= "DROP TABLE IF EXISTS `".$val."`;rn";

$sqlStr = $info.$row[1].";rnrn";

//Append to file

file_put_contents($to_file_name,$sqlStr,FILE_APPEND);

//Release resources

mysql_free_result($res);

}

//Export the data of each table to file

foreach($tabList as $val){

$sql = "select * from ".$val;

$res = mysql_query($sql,$link);

//If there is no data in the table, continue to the next table

if(mysql_num_rows($res)

$info = "--------------------------------rn";

$info .= "-- Records for `".$val."`rn";

$info .= "--------------------------------rn";

file_put_contents($to_file_name,$info,FILE_APPEND);

//Read data

while($row = mysql_fetch_row($res)){

$sqlStr = "INSERT INTO `".$val."` VALUES (";

foreach($row as $zd){

$sqlStr .= "'".$zd."', ";

}

//Remove the last comma and space

$sqlStr = substr($sqlStr,0,strlen($sqlStr)-2);

$sqlStr .= ");rn";

file_put_contents($to_file_name,$sqlStr,FILE_APPEND);

}

//Release resources

mysql_free_result($res);

file_put_contents($to_file_name,"rn",FILE_APPEND);

}

echo "OK!";