备份mysql,生成sql,csv,xls,xml文件的PHP程序
Tuesday, July 31st, 2007本程序的功能主要有备份mysql数据库,生成SQL文件,XML文件,CSV文件,XLS文件,将此代码复制粘贴到一个PHP文件中,生成的备份文件都在该同级目录下。
代码如下:
//mysqldb.php
//manage mysql database and table , backup data and create xml and csv file
//Rossy.cn@gmail.com
//2007-07-31 18:34:25
//messages for no table exist in database or no field exist in the table
if (isset($_GET['Tip']) && (!empty($_GET['Tip']))) {
$Tip = trim($_GET['Tip']);
if ($Tip == "t") {
echo "No table in this database
";
} elseif ($Tip == "d") {
echo "No data in this table
";
}
}
error_reporting(0);
define("CURFILE","mysqldb.php"); //current file name
define("Mysql_Host","localhost"); //mysql host name
define("Mysql_User","username"); //mysql user
define("Mysql_Pwd","password"); //mysql password
$link = mysql_connect(Mysql_Host,Mysql_User,Mysql_Pwd); //connect database
//select database
if (isset($_GET['db']) && (!empty($_GET['db']))) {
$db = trim($_GET['db']);
//select table
if (isset($_GET['table']) && (!empty($_GET['table']))) {
$table = trim($_GET['table']);
mysql_select_db($db,$link) or die ('Can\'t use foo : ' . mysql_error());
$query = "Select * FROM `$table` Where 1 = 1";
$result = mysql_query($query) or die ('Query Failed : ' . mysql_error());
if (mysql_num_rows($result)<=0) header("Location:".$file."?Tip=d&db=".$db); //return when table has no field
if (isset($_GET['type']) && (trim($_GET['type'])=="xml")) {
//create xml file
echo "databases –> ".$db." –> ".$table."
";
$filestr = "<"."?xml version=\"1.0\" encoding=\"utf-8\"?".">\n";
$filestr .="<".$table."s>\n";
while ($row = mysql_fetch_array($result)){
$filestr .= "<".$table.">\n";
$fields = mysql_list_fields($db,$table,$link);
$j = 0;
while ($j
$filestr .= "<".$num_fields.">";
$filestr .= $row[$j];
$filestr .= "\n";
$j++;
}
$filestr .= "\n";
}
$filestr .= "";
$xmlfilename = $table.".xml";
$fp = fopen("$xmlfilename","w");
fwrite($fp,$filestr);
fclose($fp);
echo "
create xml successfully! file name is ".$xmlfilename;
} elseif (isset($_GET['type']) && (trim($_GET['type'])=="view")) {
//view table's structure
echo "databases –> ".$db." –> ".$table."
";
echo "
This is the fields in this table
";
while ($row = mysql_fetch_array($result)){
$fields = mysql_list_fields($db,$table,$link);
$k = 0;
while ($k
$type_fields = mysql_field_type($fields,$k);
$lens_fields = mysql_field_len($fields,$k);
$flag_fields = mysql_field_flags($fields,$k);
echo "".$name_fields." (".$type_fields."|".$lens_fields.") ".$flag_fields."
";
$k++;
}
}
} elseif (isset($_GET['type']) && (trim($_GET['type'])=="bk")) {
//backup data into sql file
echo "databases –> ".$db." –> ".$table."
";
$bkfilename = $table.date("Y-m-d-H-i-s").".sql";
$query = "Select * INTO OUTFILE '".$_SERVER['DOCUMENT_ROOT']."/test/".$bkfilename."' FROM $table";
$result = mysql_query($query) or die("backup data failed :".mysql_error());
echo "
backup successfully . file name is :".$bkfilename."
";
} elseif (isset($_GET['type']) && (trim($_GET['type'])=="view")) {
echo "databases –> ".$db." –> ".$table."
";
echo "
This is the fields in this table
";
while ($row = mysql_fetch_array($result)){
$fields = mysql_list_fields($db,$table,$link);
$n = 0;
while ($n
$type_fields = mysql_field_type($fields,$n);
$lens_fields = mysql_field_len($fields,$n);
$flag_fields = mysql_field_flags($fields,$n);
echo "".$name_fields." (".$type_fields."|".$lens_fields.") ".$flag_fields."
";
$n++;
}
}
} elseif (isset($_GET['type']) && (trim($_GET['type'])=="csv")) {
//create csv file
$csvfilename = $table.date("Y-m-d-H-i-s").".csv";
header( "Content-type: application/vnd.ms-excel;charset=utf-8" );
header( "Content-Disposition: csv; filename=".$csvfilename );
$fields = mysql_list_fields($db,$table,$link);
$h = 0;
for ($h = 0; $h < mysql_num_fields($fields); $h++) {
echo '"'.mysql_field_name($result,$h).'"';
if (($h+1)==mysql_num_fields($fields)) break;
else echo ",";
}
echo "\015\012";
while ($row = mysql_fetch_array($result)){
$i = 0;
while ($i
//echo '"'.iconv("BIG5","UTF-8",$row[$i]).'"';
if (($i+1)==mysql_num_fields($fields)) break;
else echo ",";
$i++;
}
echo "\015\012";
}
} elseif (isset($_GET['type']) && (trim($_GET['type'])=="xls")) {
//create xls file
$xlsfilename = $table.date("Y-m-d-H-i-s").".xls";
header( "Content-type: application/vnd.ms-excel;charset=utf-8" );
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header( "Content-Disposition: xls; filename=".$xlsfilename );
$fields = mysql_list_fields($db,$table,$link);
$h = 0;
echo "
| ".mysql_field_name($result,$h)." |
|---|
| ".$row[$i]." |
";
}
} else {
//list tables in select database
$lists=mysql_list_tables($db,$link);
$m=0;
if (mysql_num_rows($lists) == 0) header("Location:".$file."?Tip=t");
echo "databases –> ".$db."
";
while($m
$fields = mysql_list_fields($db,$tb_name,$link);
$field_num = mysql_num_fields($fields);
echo "".($m+1)." ".$tb_name."(".$field_num.") "."(VIEW) || "."(BACKUP) || "."(XML) || "."(CSV) || "."(XLS)
";
$m++;
}
}
} else {
//list database
$db_list = mysql_list_dbs($link);
while ($row = mysql_fetch_object($db_list)) {
$result = mysql_list_tables($row->Database,$link);
$table_num = mysql_num_rows($result);
echo "Database."\">".$row->Database. "(".$table_num.")
\n";
}
}
?>