这是一个将指定数据库里的所有表备份为一个SQL文件,可下载。这个源码来自dedecms程序,功能挺多,也很实用,但是代码的质量还有待提高
1
<!?
php
2
3 /* ***** 备份数据库结构 ***** */
4
5 /*
6 函数名称:table2sql()
7 函数功能:把表的结构转换成为SQL
8 函数参数:$table: 要进行提取的表名
9 返 回 值:返回提取后的结果,SQL集合
10 函数作者:heiyeluren
11 */
12
13 function table2sql( $table )
14 {
15 global $db ;
16 $tabledump = " DROP TABLE IF EXISTS $table ;\n " ;
17 $createtable = $db ---> query( " SHOW CREATE TABLE $table " );
18 $create = $db -> fetch_row( $createtable );
19 $tabledump .= $create [ 1 ] . " ;\n\n " ;
20 return $tabledump ;
21 }
22
23
24 /* ***** 备份数据库结构和所有数据 ***** */
25 /*
26 函数名称:data2sql()
27 函数功能:把表的结构和数据转换成为SQL
28 函数参数:$table: 要进行提取的表名
29 返 回 值:返回提取后的结果,SQL集合
30 函数作者:heiyeluren
31 */
32 function data2sql( $table )
33 {
34 global $db ;
35 $tabledump = " DROP TABLE IF EXISTS $table ;\n " ;
36 $createtable = $db -> query( " SHOW CREATE TABLE $table " );
37 $create = $db -> fetch_row( $createtable );
38 $tabledump .= $create [ 1 ] . " ;\n\n " ;
39
40 $rows = $db -> query( " SELECT * FROM $table " );
41 $numfields = $db -> num_fields( $rows );
42 $numrows = $db -> num_rows( $rows );
43 while ( $row = $db -> fetch_row( $rows ))
44 {
45 $comma = "" ;
46 $tabledump .= " INSERT INTO $table VALUES( " ;
47 for ( $i = 0 ; $i < $numfields ; $i ++ )
48 {
49 $tabledump .= $comma . " ' " . mysql_escape_string ( $row [ $i ]) . " ' " ;
50 $comma = " , " ;
51 }
52 $tabledump .= " );\n " ;
53 }
54 $tabledump .= " \n " ;
55
56 return $tabledump ;
57 }
58 ?>
59
60 <!--? php
61 $host = " localhost " ; // 主机名
62 $user = " root " ; // MYSQL用户名
63 $password = " root " ; // 密码
64 $dbname = " dedecmsv4 " ; // 备份的数据库
65
66 mysql_connect ( $host , $user , $password );
67 mysql_select_db ( $dbname );
68
69 $q1 = mysql_query ( " show tables " );
70 while ( $t = mysql_fetch_array ( $q1 )){
71 $table = $t [ 0 ];
72 $q2 = mysql_query ( " show create table ` $table ` " );
73 $sql = mysql_fetch_array ( $q2 );
74 $mysql .= $sql [ ' Create Table ' ] . " ;\r\n\r\n " ; # DDL
75
76 $q3 = mysql_query ( " select * from ` $table ` " );
77 while ( $data = mysql_fetch_assoc ( $q3 ))
78 {
79 $keys = array_keys ( $data );
80 $keys = array_map ( ' addslashes ' , $keys );
81 $keys = join ( ' `,` ' , $keys );
82 $keys = " ` " . $keys . " ` " ;
83 $vals = array_values ( $data );
84 $vals = array_map ( ' addslashes ' , $vals );
85 $vals = join ( " ',' " , $vals );
86 $vals = " ' " . $vals . " ' " ;
87
88 $mysql .= " insert into ` $table `( $keys ) values( $vals );\r\n " ;
89 }
90 $mysql .= " \r\n " ;
91
92 }
93 $filename = date ( ' Ymd ' ) . " _ " . $dbname . " .sql " ; // 文件名为当天的日期
94 $fp = fopen ( $filename , ' w ' );
95 fputs ( $fp , $mysql );
96 fclose ( $fp );
97 echo " 数据备份成功,生成备份文件 " . $filename ;
98 ?>
2
3 /* ***** 备份数据库结构 ***** */
4
5 /*
6 函数名称:table2sql()
7 函数功能:把表的结构转换成为SQL
8 函数参数:$table: 要进行提取的表名
9 返 回 值:返回提取后的结果,SQL集合
10 函数作者:heiyeluren
11 */
12
13 function table2sql( $table )
14 {
15 global $db ;
16 $tabledump = " DROP TABLE IF EXISTS $table ;\n " ;
17 $createtable = $db ---> query( " SHOW CREATE TABLE $table " );
18 $create = $db -> fetch_row( $createtable );
19 $tabledump .= $create [ 1 ] . " ;\n\n " ;
20 return $tabledump ;
21 }
22
23
24 /* ***** 备份数据库结构和所有数据 ***** */
25 /*
26 函数名称:data2sql()
27 函数功能:把表的结构和数据转换成为SQL
28 函数参数:$table: 要进行提取的表名
29 返 回 值:返回提取后的结果,SQL集合
30 函数作者:heiyeluren
31 */
32 function data2sql( $table )
33 {
34 global $db ;
35 $tabledump = " DROP TABLE IF EXISTS $table ;\n " ;
36 $createtable = $db -> query( " SHOW CREATE TABLE $table " );
37 $create = $db -> fetch_row( $createtable );
38 $tabledump .= $create [ 1 ] . " ;\n\n " ;
39
40 $rows = $db -> query( " SELECT * FROM $table " );
41 $numfields = $db -> num_fields( $rows );
42 $numrows = $db -> num_rows( $rows );
43 while ( $row = $db -> fetch_row( $rows ))
44 {
45 $comma = "" ;
46 $tabledump .= " INSERT INTO $table VALUES( " ;
47 for ( $i = 0 ; $i < $numfields ; $i ++ )
48 {
49 $tabledump .= $comma . " ' " . mysql_escape_string ( $row [ $i ]) . " ' " ;
50 $comma = " , " ;
51 }
52 $tabledump .= " );\n " ;
53 }
54 $tabledump .= " \n " ;
55
56 return $tabledump ;
57 }
58 ?>
59
60 <!--? php
61 $host = " localhost " ; // 主机名
62 $user = " root " ; // MYSQL用户名
63 $password = " root " ; // 密码
64 $dbname = " dedecmsv4 " ; // 备份的数据库
65
66 mysql_connect ( $host , $user , $password );
67 mysql_select_db ( $dbname );
68
69 $q1 = mysql_query ( " show tables " );
70 while ( $t = mysql_fetch_array ( $q1 )){
71 $table = $t [ 0 ];
72 $q2 = mysql_query ( " show create table ` $table ` " );
73 $sql = mysql_fetch_array ( $q2 );
74 $mysql .= $sql [ ' Create Table ' ] . " ;\r\n\r\n " ; # DDL
75
76 $q3 = mysql_query ( " select * from ` $table ` " );
77 while ( $data = mysql_fetch_assoc ( $q3 ))
78 {
79 $keys = array_keys ( $data );
80 $keys = array_map ( ' addslashes ' , $keys );
81 $keys = join ( ' `,` ' , $keys );
82 $keys = " ` " . $keys . " ` " ;
83 $vals = array_values ( $data );
84 $vals = array_map ( ' addslashes ' , $vals );
85 $vals = join ( " ',' " , $vals );
86 $vals = " ' " . $vals . " ' " ;
87
88 $mysql .= " insert into ` $table `( $keys ) values( $vals );\r\n " ;
89 }
90 $mysql .= " \r\n " ;
91
92 }
93 $filename = date ( ' Ymd ' ) . " _ " . $dbname . " .sql " ; // 文件名为当天的日期
94 $fp = fopen ( $filename , ' w ' );
95 fputs ( $fp , $mysql );
96 fclose ( $fp );
97 echo " 数据备份成功,生成备份文件 " . $filename ;
98 ?>