Php exports the full mysql database to generate SQL files

Source: Internet
Author: User
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;
}
?>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.