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.
Implementation Code:
Copy codeThe Code is as follows:
<? Php
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... <br/> ";
$ Info = "-- ---------------------------- \ r \ n ";
$ Info. = "-- date:". date ("Y-m-d H: I: s", time (). "\ r \ n ";
$ Info. = "-- only used for testing and learning. This program is not suitable for processing large amounts of data \ r \ n ";
$ Info. = "-- ---------------------------- \ r \ n ";
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 = "-- ---------------------------- \ r \ n ";
$ Info. = "-- Table structure for '". $ val. "' \ r \ n ";
$ Info. = "-- ---------------------------- \ r \ n ";
$ Info. = "drop table if exists '". $ val. "'; \ r \ n ";
$ SqlStr = $ info. $ row [1]. "; \ r \ n ";
// 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 = "-- ---------------------------- \ r \ n ";
$ Info. = "-- Records for '". $ val. "' \ r \ n ";
$ Info. = "-- ---------------------------- \ r \ n ";
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. = "); \ r \ n ";
File_put_contents ($ to_file_name, $ sqlStr, FILE_APPEND );
}
// Release resources
Mysql_free_result ($ res );
File_put_contents ($ to_file_name, "\ r \ n", FILE_APPEND );
}
Echo "OK! ";
?>