先说下关于数据库备份与恢复的原理：

1、查找所有表->2、查找所有字段->3、查找所有数据->4、生成SQL

备份注意点：

2=>需要列出所有字段名，字段类型等相关信息

3=>读取数据出来注意特殊符号的转换addslashes()

4=>把数据格式化生成对应的SQL

<?php$mysql_dbname = "php100";mysql_connect("localhost", "root", "");mysql_select_db($mysql_dbname);$sql_list = mysql_list_tables($mysql_dbname);while ($row_data = mysql_fetch_row($sql_list)) {echo $row_data[0] . "<br>";get_table_fd($row_data[0], "sql");//  $fp=fopen("sql/$tablename.sql","w+");//  fwrite($fp,$field);//  fclose($fp);}function get_table_fd($tablename) {$field = "CREATE TABLE `$tablename`(\n";$result = mysql_query("select * from $tablename");while ($meta = mysql_fetch_field($result)) {if ($meta->not_null)$not_null = "not_null";$field .= "`$meta->name` $meta->type($meta->max_length) $not_null; \n";}$field .= ")\n";return $field;}?>

在网上找到个不错的类库，可直接拿来用，记录一下

1、db.php 数据库类

2、backup.php 备份

3、restore.php 恢复

下载附件

db.php

 1 <? 2 class db{ 3  4 var $linkid; 5 var $sqlid; 6 var $record; 7  8 function db($host="",$username="",$passWord="",$database="") 9     {10     if(!$this->linkid)  @$this->linkid = mysql_connect($host, $username, $password) or die("连接服务器失败.");11     @mysql_select_db($database,$this->linkid) or die("无法打开数据库");12     mysql_query('set names gbk');13     return $this->linkid;}14 15 function query($sql)16     {if($this->sqlid=mysql_query($sql,$this->linkid)) return $this->sqlid;17     else {18         $this->err_report($sql,mysql_error);19     return false;}20     }21 22 function nr($sql_id="")23     {if(!$sql_id) $sql_id=$this->sqlid;24     return mysql_num_rows($sql_id);}25 26 function nf($sql_id="")27     {if(!$sql_id) $sql_id=$this->sqlid;28     return mysql_num_fields($sql_id);}29 30 function nextrecord($sql_id="")31     {if(!$sql_id) $sql_id=$this->sqlid;32     if($this->record=mysql_fetch_array($sql_id))  return $this->record;33     else return false;34     }35 36 function f($name)37     {38     if($this->record[$name]) return $this->record[$name];39     else return false;40     }41 42 function close() {mysql_close($this->linkid);}43 44 function lock($tblname,$op="WRITE")45     {if(mysql_query("lock tables ".$tblname." ".$op)) return true; else return false;}46 47 function unlock()48     {if(mysql_query("unlock tables")) return true; else return false;}49 50 function ar() {51     return @mysql_affected_rows($this->linkid);52   }53 54 function i_id() {55         return mysql_insert_id();56     }57 58 function err_report($sql,$err)59     {60 echo "Mysql查询错误<br>";61 echo "查询语句：".$sql."<br>";62 echo "错误信息：".$err;63     }64 }?>

backup.php

  1 <?  2 global $mysqlhost, $mysqluser, $mysqlpwd, $mysqldb;  3 $mysqlhost=""; //host name  4 $mysqluser="";              //login name  5 $mysqlpwd="";              //password  6 $mysqldb="";        //name of database  7   8 include("mydb.php");  9 $d=new db($mysqlhost,$mysqluser,$mysqlpwd,$mysqldb); 10 /*--------------界面--------------*/if(!$_POST['act']){/*----------------------*/ 11 $msgs[]="服务器备份目录为backup"; 12 $msgs[]="对于较大的数据表，强烈建议使用分卷备份"; 13 $msgs[]="只有选择备份到服务器，才能使用分卷备份功能"; 14 show_msg($msgs); 15 ?> 16 <form name="form1" method="post" action="backup.php"> 17   <table width="99%" border="1" cellpadding='0' cellspacing='1'> 18     <tr align="center" class='header'><td colspan="2">数据备份</td></tr> 19     <tr><td colspan="2">备份方式</td></tr> 20     <tr><td><input type="radio" name="bfzl" value="quanbubiao">        备份全部数据</td><td>备份全部数据表中的数据到一个备份文件</td></tr> 21     <tr><td><input type="radio" name="bfzl" value="danbiao">备份单张表数据  22         <select name="tablename"><option value="">请选择</option> 23           <? 24         $d->query("show table status from $mysqldb"); 25         while($d->nextrecord()){ 26         echo "<option value='".$d->f('Name')."'>".$d->f('Name')."</option>";} 27         ?> 28         </select></td><td>备份选中数据表中的数据到单独的备份文件</td></tr> 29     <tr><td colspan="2">使用分卷备份</td></tr> 30     <tr><td colspan="2"><input type="checkbox" name="fenjuan" value="yes"> 31         分卷备份 <input name="filesize" type="text" size="10">K</td></tr> 32     <tr><td colspan="2">选择目标位置</td></tr> 33     <tr><td colspan="2"><input type="radio" name="weizhi" value="server" checked>备份到服务器</td></tr><tr class="cells"><td colspan='2'> <input type="radio" name="weizhi" value="localpc"> 34         备份到本地</td></tr> 35     <tr><td colspan="2" align='center'><input type="submit" name="act" value="备份"></td></tr> 36   </table></form> 37 <?/*-------------界面结束-------------*/}/*---------------------------------*/ 38 /*----*/else{/*--------------主程序-----------------------------------------*/ 39 if($_POST['weizhi']=="localpc"&&$_POST['fenjuan']=='yes') 40     {$msgs[]="只有选择备份到服务器，才能使用分卷备份功能"; 41 show_msg($msgs); pageend();} 42 if($_POST['fenjuan']=="yes"&&!$_POST['filesize']) 43     {$msgs[]="您选择了分卷备份功能，但未填写分卷文件大小"; 44 show_msg($msgs); pageend();} 45 if($_POST['weizhi']=="server"&&!writeable("./backup")) 46     {$msgs[]="备份文件存放目录'./backup'不可写，请修改目录属性"; 47 show_msg($msgs); pageend();} 48  49 /*----------备份全部表-------------*/if($_POST['bfzl']=="quanbubiao"){/*----*/ 50 /*----不分卷*/if(!$_POST['fenjuan']){/*--------------------------------*/ 51 if(!$tables=$d->query("show table status from $mysqldb")) 52     {$msgs[]="读数据库结构错误"; show_msg($msgs); pageend();} 53 $sql=""; 54 while($d->nextrecord($tables)) 55     { 56     $table=$d->f("Name"); 57     $sql.=make_header($table); 58     $d->query("select * from $table"); 59     $num_fields=$d->nf(); 60     while($d->nextrecord()) 61     {$sql.=make_record($table,$num_fields);} 62     } 63 $filename=date("Ymd",time())."_all.sql"; 64 if($_POST['weizhi']=="localpc") down_file($sql,$filename); 65 elseif($_POST['weizhi']=="server") 66     {if(write_file($sql,$filename)) 67 $msgs[]="全部数据表数据备份完成,生成备份文件'./backup/$filename'"; 68     else $msgs[]="备份全部数据表失败"; 69     show_msg($msgs); 70     pageend(); 71     } 72 /*-----------------不要卷结束*/}/*-----------------------*/ 73 /*-----------------分卷*/else{/*-------------------------*/ 74 if(!$_POST['filesize']) 75     {$msgs[]="请填写备份文件分卷大小"; show_msg($msgs);pageend();} 76 if(!$tables=$d->query("show table status from $mysqldb")) 77     {$msgs[]="读数据库结构错误"; show_msg($msgs); pageend();} 78 $sql=""; $p=1; 79 $filename=date("Ymd",time())."_all"; 80 while($d->nextrecord($tables)) 81 { 82     $table=$d->f("Name"); 83     $sql.=make_header($table); 84     $d->query("select * from $table"); 85     $num_fields=$d->nf(); 86     while($d->nextrecord()) 87     {$sql.=make_record($table,$num_fields); 88     if(strlen($sql)>=$_POST['filesize']*1000){ 89             $filename.=("_v".$p.".sql"); 90             if(write_file($sql,$filename)) 91             $msgs[]="全部数据表-卷-".$p."-数据备份完成,生成备份文件'./backup/$filename'"; 92             else $msgs[]="备份表-".$_POST['tablename']."-失败"; 93             $p++; 94             $filename=date("Ymd",time())."_all"; 95             $sql="";} 96     } 97 } 98 if($sql!=""){$filename.=("_v".$p.".sql");         99 if(write_file($sql,$filename))100 $msgs[]="全部数据表-卷-".$p."-数据备份完成,生成备份文件'./backup/$filename'";}101 show_msg($msgs);102 /*---------------------分卷结束*/}/*--