PHP数据库备份与恢复，PHP数据库备份恢复_PHP教程
程序员文章站
2024-01-24 21:16:10
...
PHP数据库备份与恢复，PHP数据库备份恢复
先说下关于数据库备份与恢复的原理：
1、查找所有表->2、查找所有字段->3、查找所有数据->4、生成SQL
备份注意点：
2=>需要列出所有字段名，字段类型等相关信息
3=>读取数据出来注意特殊符号的转换addslashes()
4=>把数据格式化生成对应的SQL
mysql_list_tables() 表查询函数，类似mysql_query() 函数 mysql_fetch_field() 字段信息函数，返回句柄 恢复注意点： 1、 注意数据库的大小，过大或者过多的表分段处理
2、 生成的SQL文件名或者存放不易被猜到
3、 备份生成文件可以表或者字段为单位保存
4、可以使用ZIP组件压缩生成的文件以便保持 迷你版代码：
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] . "
"; 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查询错误
"; 61 echo "查询语句：".$sql."
"; 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 ?> 1637 /*-------------界面结束-------------*/}/*---------------------------------*/ 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 /*---------------------分卷结束*/}/*--------------------------------------*/ 103 /*--------备份全部表结束*/}/*---------------------------------------------*/ 104 105 /*--------备份单表------*/elseif($_POST['bfzl']=="danbiao"){/*------------*/ 106 if(!$_POST['tablename']) 107 {$msgs[]="请选择要备份的数据表"; show_msg($msgs); pageend();} 108 /*--------不分卷*/if(!$_POST['fenjuan']){/*-------------------------------*/ 109 $sql=make_header($_POST['tablename']); 110 $d->query("select * from ".$_POST['tablename']); 111 $num_fields=$d->nf(); 112 while($d->nextrecord()) 113 {$sql.=make_record($_POST['tablename'],$num_fields);} 114 $filename=date("Ymd",time())."_".$_POST['tablename'].".sql"; 115 if($_POST['weizhi']=="localpc") down_file($sql,$filename); 116 elseif($_POST['weizhi']=="server") 117 {if(write_file($sql,$filename)) 118 $msgs[]="表-".$_POST['tablename']."-数据备份完成,生成备份文件'./backup/$filename'"; 119 else $msgs[]="备份表-".$_POST['tablename']."-失败"; 120 show_msg($msgs); 121 pageend(); 122 } 123 /*----------------不要卷结束*/}/*------------------------------------*/ 124 /*----------------分卷*/else{/*--------------------------------------*/ 125 if(!$_POST['filesize']) 126 {$msgs[]="请填写备份文件分卷大小"; show_msg($msgs);pageend();} 127 $sql=make_header($_POST['tablename']); $p=1; 128 $filename=date("Ymd",time())."_".$_POST['tablename']; 129 $d->query("select * from ".$_POST['tablename']); 130 $num_fields=$d->nf(); 131 while ($d->nextrecord()) 132 { 133 $sql.=make_record($_POST['tablename'],$num_fields); 134 if(strlen($sql)>=$_POST['filesize']*1000){ 135 $filename.=("_v".$p.".sql"); 136 if(write_file($sql,$filename)) 137 $msgs[]="表-".$_POST['tablename']."-卷-".$p."-数据备份完成,生成备份文件'./backup/$filename'"; 138 else $msgs[]="备份表-".$_POST['tablename']."-失败"; 139 $p++; 140 $filename=date("Ymd",time())."_".$_POST['tablename']; 141 $sql="";} 142 } 143 if($sql!=""){$filename.=("_v".$p.".sql"); 144 if(write_file($sql,$filename)) 145 $msgs[]="表-".$_POST['tablename']."-卷-".$p."-数据备份完成,生成备份文件'./backup/$filename'";} 146 show_msg($msgs); 147 /*----------分卷结束*/}/*--------------------------------------------------*/ 148 /*----------备份单表结束*/}/*----------------------------------------------*/ 149 150 /*---*/}/*-------------主程序结束------------------------------------------*/ 151 152 function write_file($sql,$filename) 153 { 154 $re=true; 155 if(!@$fp=fopen("./backup/".$filename,"w+")) {$re=false; echo "failed to open target file";} 156 if(!@fwrite($fp,$sql)) {$re=false; echo "failed to write file";} 157 if(!@fclose($fp)) {$re=false; echo "failed to close target file";} 158 return $re; 159 } 160 161 function down_file($sql,$filename) 162 { 163 ob_end_clean(); 164 header("Content-Encoding: none"); 165 header("Content-Type: ".(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE') ? 'application/octetstream' : 'application/octet-stream')); 166 167 header("Content-Disposition: ".(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE') ? 'inline; ' : 'attachment; ')."filename=".$filename); 168 169 header("Content-Length: ".strlen($sql)); 170 header("Pragma: no-cache"); 171 172 header("Expires: 0"); 173 echo $sql; 174 $e=ob_get_contents(); 175 ob_end_clean(); 176 } 177 178 function writeable($dir) 179 { 180 181 if(!is_dir($dir)) { 182 @mkdir($dir, 0777); 183 } 184 185 if(is_dir($dir)) 186 { 187 188 if($fp = @fopen("$dir/test.test", 'w')) 189 { 190 @fclose($fp); 191 @unlink("$dir/test.test"); 192 $writeable = 1; 193 } 194 else { 195 $writeable = 0; 196 } 197 198 } 199 200 return $writeable; 201 202 } 203 204 function make_header($table) 205 {global $d; 206 $sql="DROP TABLE IF EXISTS ".$table."\n"; 207 $d->query("show create table ".$table); 208 $d->nextrecord(); 209 $tmp=preg_replace("/\n/","",$d->f("Create Table")); 210 $sql.=$tmp."\n"; 211 return $sql; 212 } 213 214 function make_record($table,$num_fields) 215 {global $d; 216 $comma=""; 217 $sql .= "INSERT INTO ".$table." VALUES("; 218 for($i = 0; $i $num_fields