请问如何备份虚拟主机上的数据库 PHPMyAdmin就有备份功能啊你可以复制库、表为另一个名称的库、表(包含数据) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我的做法是将MYSQL导出生成SQL语句,恢复的时候先删除表数据,然后再运行这些SQL语句。 可以用phpmyadmin导出SQL语句,可以只导出数据,类似insert into table....; 也可以只导出表结构,类似Create Table....; 也可以既导出结构又导出数据. 虚拟主机的mysql一般不对外面的host开方,而且不能对文件操作,所以只能用phpmyadmin了不过也可以这样,先把所有的数据导入文件/tmp/1.tmp然后,再作一个php的程序把/1.tmp copy到当前网页下所需的是unix/linux环境 仿照phpMyAdmin写的一个简单MYSQL备份、还原程序,已测试通过(不适合复杂结构),仅供参考!config.php文件:<?php$dbhost="localhost";$dbuser="root";$dbpass="";$dbname="test";$filename="backup_".$dbname."_2002_10_16.sql";$conn = mysql_pconnect($dbhost,$dbuser,$dbpass);@mysql_select_db($dbname,$conn) or die(mysql_error());set_time_limit(0);?>backup.php文件:<?phpinclude("config.php");$tables = mysql_list_tables($dbname);$num_tables = @mysql_numrows($tables);//header("Content-type: text/plain");header("Content-Type: application/octetstream");if(preg_match("/MSIE 5.5/", $HTTP_USER_AGENT)){ header("Content-Disposition: filename=$filename");}else{ header("Content-Disposition: attachment;filename=$filename");}echo "# \n";echo "# Database backup file \n";echo "# copyright @ phpteam 20002-2003 \n";echo "# \n";echo "# Host : " . $dbhost ."\n";echo "# Database: " . $dbname ."\n";echo "# Date : " . date("d-M-Y",time()) . "\n";echo "# \n\n\n";$i=0;while ($i<$num_tables){ $table = mysql_tablename($tables,$i); echo "# \n"; echo "# Table structure for table '$table' \n"; echo "# \n\n"; // table structure $struc = "DROP TABLE IF EXISTS ".$table.";\n"; $struc .= "CREATE TABLE $table (\n"; $query = "SHOW FIELDS FROM $table"; $result = mysql_query($query); while ($row=mysql_fetch_array($result)) { $struc .= " $row[Field] $row[Type]"; if(!empty($row["Default"])) { $struc .= " DEFAULT '$row[Default]'"; } if($row["Null"] != "YES") { $struc .= " NOT NULL"; } if($row["Extra"] != "") { $struc .= " $row[Extra]"; } $struc .= ",\n"; } $query = "SHOW KEYS FROM $table"; $result = mysql_query($query); $pkey_exist = mysql_num_rows($result); if($pkey_exist!=0) { $pkey = mysql_result($result,0,'column_name'); $struc .= " PRIMARY KEY ($pkey)\n"; } else { // replace ,\n $struc = substr($struc,0, strlen($struc)-2); $struc .= "\n"; } echo $struc; echo ");\n\n"; // data echo "# \n"; echo "# Dumping data for table '$table' \n"; echo "# \n\n"; $query = "select * from $table"; $result = mysql_query($query); $j = 0; while($row=mysql_fetch_row($result)) { $data .= "INSERT INTO $table VALUES("; for($k=0; $k<mysql_num_fields($result);$k++) { if (!isset($row[$k])) { $data .= " NULL,"; } elseif($row[$k]!="") { $data .= " '".addslashes($row[$k])."',"; } else { $data .= " '',"; } } // replace ,\n $data = substr($data,0, strlen($data)-1); $data .= ");\n"; $j++; } echo $data."\n"; $data = ""; $i++;}?>import.php文件:<?phpinclude("config.php");$fp = fopen($filename, "r");while($buffer = fgets($fp, 10000)){ $buffer = trim($buffer); if(!empty($buffer) && substr($buffer,0,1) != "#") { if(substr($buffer,-2,2)==");") { $query .= $buffer; $result = mysql_query($query); $query = ""; } else { $query .= $buffer; } }}fclose($fp);echo $dbname." has restored!";?> 我是直接用命令.导出exec($path->mysqldir."/mysqldump ".$dbuser." ".$dbpass." ".$CONFIG->dbname." > ".$path->backupdbdir."/".$str.$SAS_SESSION[userlogin][uloginid].".sql");.导入$create_db=$path->mysqldir."/mysqladmin ".$dbuser." ".$dbpass." create ".$CONFIG->dbname; exec($create_db); $sql=$path->mysqldir."/mysql ".$dbuser." ".$dbpass." ".$CONFIG->dbname." < ".$path->backupdbdir."/".$form[isselect]; exec($sql); 将csv文件导入数据库的问题,很怪异,大家看看 正则表达式的问题? php 生成HTML的问题 netbeans for php、Zend Studio和写字板打开某些php文件,中文乱码 正则表达式多余空格怎么去掉? php 如何得到一个 domain 的 dns 主机的 ip ? PHP下mysql的text字段更新问题 如何获取 Http POST过来的数据 怎么用php连接mssql后,读下一条记录? js问题 强烈建议!!!!!!! 用PHP进行B/S开发时,如何将PHP和HTML完全分割?
不过也可以这样,先把所有的数据导入文件/tmp/1.tmp
然后,再作一个php的程序把/1.tmp copy到当前网页下
所需的是unix/linux环境
config.php文件:
<?php
$dbhost="localhost";
$dbuser="root";
$dbpass="";
$dbname="test";
$filename="backup_".$dbname."_2002_10_16.sql";
$conn = mysql_pconnect($dbhost,$dbuser,$dbpass);
@mysql_select_db($dbname,$conn) or die(mysql_error());
set_time_limit(0);
?>backup.php文件:
<?php
include("config.php");
$tables = mysql_list_tables($dbname);
$num_tables = @mysql_numrows($tables);
//header("Content-type: text/plain");
header("Content-Type: application/octetstream");
if(preg_match("/MSIE 5.5/", $HTTP_USER_AGENT))
{
header("Content-Disposition: filename=$filename");
}
else
{
header("Content-Disposition: attachment;filename=$filename");
}echo "# \n";
echo "# Database backup file \n";
echo "# copyright @ phpteam 20002-2003 \n";
echo "# \n";
echo "# Host : " . $dbhost ."\n";
echo "# Database: " . $dbname ."\n";
echo "# Date : " . date("d-M-Y",time()) . "\n";
echo "# \n\n\n";$i=0;
while ($i<$num_tables)
{
$table = mysql_tablename($tables,$i);
echo "# \n";
echo "# Table structure for table '$table' \n";
echo "# \n\n";
// table structure
$struc = "DROP TABLE IF EXISTS ".$table.";\n";
$struc .= "CREATE TABLE $table (\n";
$query = "SHOW FIELDS FROM $table";
$result = mysql_query($query);
while ($row=mysql_fetch_array($result))
{
$struc .= " $row[Field] $row[Type]";
if(!empty($row["Default"]))
{
$struc .= " DEFAULT '$row[Default]'";
}
if($row["Null"] != "YES")
{
$struc .= " NOT NULL";
}
if($row["Extra"] != "")
{
$struc .= " $row[Extra]";
}
$struc .= ",\n";
}
$query = "SHOW KEYS FROM $table";
$result = mysql_query($query);
$pkey_exist = mysql_num_rows($result); if($pkey_exist!=0)
{
$pkey = mysql_result($result,0,'column_name');
$struc .= " PRIMARY KEY ($pkey)\n";
}
else
{
// replace ,\n
$struc = substr($struc,0, strlen($struc)-2);
$struc .= "\n";
}
echo $struc;
echo ");\n\n"; // data
echo "# \n";
echo "# Dumping data for table '$table' \n";
echo "# \n\n";
$query = "select * from $table";
$result = mysql_query($query);
$j = 0;
while($row=mysql_fetch_row($result))
{
$data .= "INSERT INTO $table VALUES(";
for($k=0; $k<mysql_num_fields($result);$k++)
{
if (!isset($row[$k]))
{
$data .= " NULL,";
}
elseif($row[$k]!="")
{
$data .= " '".addslashes($row[$k])."',";
}
else
{
$data .= " '',";
}
}
// replace ,\n
$data = substr($data,0, strlen($data)-1);
$data .= ");\n";
$j++;
}
echo $data."\n";
$data = "";
$i++;
}
?>import.php文件:
<?php
include("config.php");
$fp = fopen($filename, "r");
while($buffer = fgets($fp, 10000))
{
$buffer = trim($buffer);
if(!empty($buffer) && substr($buffer,0,1) != "#")
{
if(substr($buffer,-2,2)==");")
{
$query .= $buffer;
$result = mysql_query($query);
$query = "";
}
else
{
$query .= $buffer;
}
}
}
fclose($fp);
echo $dbname." has restored!";
?>
.导出
exec($path->mysqldir."/mysqldump ".$dbuser." ".$dbpass." ".$CONFIG->dbname." > ".$path->backupdbdir."/".$str.$SAS_SESSION[userlogin][uloginid].".sql");
.导入
$create_db=$path->mysqldir."/mysqladmin ".$dbuser." ".$dbpass." create ".$CONFIG->dbname;
exec($create_db);
$sql=$path->mysqldir."/mysql ".$dbuser." ".$dbpass." ".$CONFIG->dbname." < ".$path->backupdbdir."/".$form[isselect];
exec($sql);