Using php to export a mysql database backup to an SQL example _ PHP Tutorial

Use php to export the mysql database backup to an SQL example. A good example of using php to export a mysql database backup to an SQL example, I hope it will help you. Using php code to back up a database can make website management very convenient. I use php to export the mysql database backup as an SQL example. I hope it will be helpful to you.

Using php code to back up a database makes website management very convenient. we can back up a database directly in the background.
Key technologies:

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

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

3. select * from table names fetch all records and concatenate them into insert into... statements in a loop.

Function:

Effect of the exported SQL statement

Code:

The code is as follows:

Header ("Content-type: text/html; charset = utf-8 "); // Configuration information $ Pai_dbhost = 'localhost '; $ Pai_dbname = 'ftdm '; $ User_dbuser = 'root '; $ Mongo_dbpwd = 'root '; $ Pai_db_language = 'utf8 '; $ To_file_name = "ftdm. SQL "; // END configuration // Link to the database $ Link = mysql_connect ($ pai_dbhost, $ pai_dbuser, $ pai_dbpwd ); Mysql_select_db ($ pai_dbname ); // Select encoding Mysql_query ("set names". $ pai_db_language ); // Tables in the database $ Tables = mysql_list_tables ($ pai_dbname ); // Record these tables to an array $ TabList = array (); While ($ row = mysql_fetch_row ($ tables )){ $ TabList [] = $ row [0]; } Echo "running, please wait... "; $ Info = "-- ---------------------------- rn "; $ Info. = "-- date:". date ("Y-m-d H: I: s", time (). "rn "; $ Info. = "-- Power by generation Ruibo (http://www.daixiaorui.com/read/34.html) rn "; $ Info. = "-- only used for testing and learning. this program is not suitable for processing large amounts of data rn "; $ 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 a 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) <1) continue; // $ 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! "; ?>

Bytes. Using php code for database backup can make website management very convenient...