Php exports the full mysql database to generate SQL files

The following is the detailed code for php to export the full mysql database to generate an SQL file. It is helpful to back up data during php programming.

The following is the detailed code for php to export the full mysql database to generate an SQL file. It is helpful to back up data during php programming.

It was found online and changed.

File Name: db_backup.php

The source code is as follows:

The Code is as follows:

Ini_set ("max_execution_time", "180"); // avoid excessive data volume and incomplete export.

/*

Program function: mysql database backup function
Author: Tang xiaogang
Note:
This program is mainly extracted from mysqladmin and made some adjustments. I hope it will be helpful for you to back up data during php programming.
If you do not want to back up the structure, please screen out this sentence: echo get_table_structure ($ dbname, $ table, $ crlf). "; $ crlf ";
If you do not want to back up the content, please screen out this sentence: echo get_table_content ($ dbname, $ table, $ crlf );

Revised by: He Jinsheng
Modification time: 2009/11/7
Modify content: added the get_table_structure function and commented out the get_table_def function to obtain more details during table creation (for example: ENGINE = InnoDB AUTO_INCREMENT = 80 default charset = utf8 COLLATE = utf8_unicode_ci COMMENT = 'item Information Change information ')
*/

$ Host = ""; // database address

$ Dbname = ""; // configure the database name here

$ Username = ""; // User Name

$ Passw = ""; // configure the password here

$ Filename = date ("Y-m-d_H-i-s"). "-". $ dbname. ". SQL ";
Header ("Content-disposition: filename =". $ filename); // saved file name
Header ("Content-type: application/octetstream ");
Header ("Pragma: no-cache ");
Header ("Expires: 0 ");

// Back up data
$ I = 0;
$ Crlf = "\ r \ n ";
Global $ dbconn;
$ Dbconn = mysql_connect ($ host, $ username, $ passw]); // Database host, user name, password
$ Db = mysql_select_db ($ dbname, $ dbconn );
Mysql_query ("set names 'utf8 '");
$ Tables = mysql_list_tables ($ dbname, $ dbconn );
$ Num_tables = @ mysql_numrows ($ tables );
Print "-- filename =". $ filename;
While ($ I <$ num_tables)
{
$ Table = mysql_tablename ($ tables, $ I );
Print $ crlf;
Echo get_table_structure ($ dbname, $ table, $ crlf). "; $ crlf ";
// Echo get_table_def ($ dbname, $ table, $ crlf). "; $ crlf ";
Echo get_table_content ($ dbname, $ table, $ crlf );
$ I ++;
}

/* Add a new table to obtain the detailed table structure */
Function get_table_structure ($ db, $ table, $ crlf)
{
Global $ drop;

$ Schema_create = "";
If (! Empty ($ drop) {$ schema_create. = "drop table if exists '$ table'; $ crlf ";}
$ Result = mysql_db_query ($ db, "show create table $ table ");
$ Row = mysql_fetch_array ($ result );
$ Schema_create. = $ crlf. "--". $ row [0]. $ crlf;
$ Schema_create. = $ row [1]. $ crlf;
Return $ schema_create;
}

/*
// The original structure of the database obtained by others is incomplete
Function get_table_def ($ db, $ table, $ crlf)
{
Global $ drop;

$ Schema_create = "";
If (! Empty ($ drop ))
$ Schema_create. = "drop table if exists '$ table'; $ crlf ";

$ Schema_create. = "create table '$ table' ($ crlf ";
$ Result = mysql_db_query ($ db, "SHOW full fields from $ table ");
While ($ row = mysql_fetch_array ($ result ))
{
$ Schema_create. = "'$ row [Field]' $ row [Type]";

If (isset ($ row ["Default"]) & (! Empty ($ row ["Default"]) | $ row ["Default"] = "0 "))
$ Schema_create. = "DEFAULT '$ row [Default]'";
If ($ row ["Null"]! = "YES ")
$ Schema_create. = "not null ";
If ($ row ["Extra"]! = "")
$ Schema_create. = "$ row [Extra]";
If ($ row ["Comment"]! = "")
$ Schema_create. = "Comment '$ row [Comment]'";
$ Schema_create. = ", $ crlf ";
}
$ Schema_create = ereg_replace (",". $ crlf. "$", "", $ schema_create );
$ Result = mysql_db_query ($ db, "show keys from $ table ");
While ($ row = mysql_fetch_array ($ result ))
{
$ Kname = $ row ['key _ name'];
If ($ kname! = "PRIMARY") & ($ row ['non _ unique'] = 0 ))
$ Kname = "UNIQUE | $ kname ";
If (! Isset ($ index [$ kname])
$ Index [$ kname] = array ();
$ Index [$ kname] [] = $ row ['column _ name'];
}

While (list ($ x, $ columns) = @ each ($ index ))
{
$ Schema_create. = ", $ crlf ";
If ($ x = "PRIMARY ")
$ Schema_create. = "primary key (". implode ($ columns ,",").")";
Elseif (substr ($ x, 0, 6) = "UNIQUE ")
$ Schema_create. = "UNIQUE". substr ($ x, 7). "(". implode ($ columns ,",").")";
Else
$ Schema_create. = "KEY $ x (". implode ($ columns ,",").")";
}

$ Schema_create. = "$ crlf )";
Return (stripslashes ($ schema_create ));
}
*/

// Obtain table content
Function get_table_content ($ db, $ table, $ crlf)
{
$ Schema_create = "";
$ Temp = "";
$ Result = mysql_db_query ($ db, "SELECT * FROM $ table ");
$ I = 0;
While ($ row = mysql_fetch_row ($ result ))
{
$ Schema_insert = "insert into '$ table' VALUES (";
For ($ j = 0; $ j {
If (! Isset ($ row [$ j])
$ Schema_insert. = "NULL ,";
Elseif ($ row [$ j]! = "")
$ Schema_insert. = "'". addslashes ($ row [$ j]). "',";
Else
$ Schema_insert. = "'',";
}
$ Schema_insert = ereg_replace (", $", "", $ schema_insert );
$ Schema_insert. = "); $ crlf ";
$ Temp = $ temp. $ schema_insert;
$ I ++;
}
Return $ temp;
}
?>