MYSQL数据库推荐没有虚拟主机的小巧的Mysql数据库备份脚本(PHP
《MYSQL数据库推荐没有虚拟主机的小巧的Mysql数据库备份脚本(PHP)》要点: MYSQL数据库最近工作中常常需要备份远程服务器上的Mysql数据库到本机,一开始采用直接备份Mysql的data目录的办法,但由于编码不同的原因经常产生问题.后来朋友推荐我使用一个非常方便小巧的PHP程序――MyDB.一共包含三个文件: MYSQL数据库1. mydb.php //DB类 <? class?db{ var?$linkid; var?$sqlid; var?$record; function?db($host="",$username="",$password="",$database="") ????{ ????if(!$this->linkid)??@$this->linkid?=?mysql_connect($host,?$username,?$password)?or?die("连接服务器失败."); ????@mysql_select_db($database,$this->linkid)?or?die("无法打开数据库"); ????return?$this->linkid;} function?query($sql) ????{if($this->sqlid=mysql_query($sql,$this->linkid))?return?$this->sqlid; ????else?{ ????????$this->err_report($sql,mysql_error); ????return?false;} ????} function?nr($sql_id="") ????{if(!$sql_id)?$sql_id=$this->sqlid; ????return?mysql_num_rows($sql_id);} function?nf($sql_id="") ????{if(!$sql_id)?$sql_id=$this->sqlid; ????return?mysql_num_fields($sql_id);} function?nextrecord($sql_id="") ????{if(!$sql_id)?$sql_id=$this->sqlid; ????if($this->record=mysql_fetch_array($sql_id))??return?$this->record; ????else?return?false; ????} function?f($name) ????{ ????if($this->record[$name])?return?$this->record[$name]; ????else?return?false; ????} function?close()?{mysql_close($this->linkid);} function?lock($tblname,$op="WRITE") ????{if(mysql_query("lock?tables?".$tblname."?".$op))?return?true;?else?return?false;} function?unlock() ????{if(mysql_query("unlock?tables"))?return?true;?else?return?false;} function?ar()?{ ????return?@mysql_affected_rows($this->linkid); ??} function?i_id()?{ ????????return?mysql_insert_id(); ????} function?err_report($sql,$err) ????{ echo?"Mysql查询错误<br>"; echo?"查询语句:".$sql."<br>"; echo?"错误信息:".$err; ????} /****************************************类结束***************************/ }?> 2. backup.php //备份脚本 代码如下: <? global?$mysqlhost,?$mysqluser,?$mysqlpwd,?$mysqldb; $mysqlhost="localhost";?//host?name $mysqluser="root";??????????????//login?name $mysqlpwd="";??????????????//password $mysqldb="";????????//name?of?database include("mydb.php"); $d=new?db($mysqlhost,$mysqluser,$mysqlpwd,$mysqldb); /*--------------界面--------------*/if(!$_POST['act']){/*----------------------*/ $msgs[]="服务器备份目录为backup"; $msgs[]="对于较大的数据表,强烈建议使用分卷备份"; $msgs[]="只有选择备份到服务器,才能使用分卷备份功能"; show_msg($msgs); ?> <form?name="form1"?method="post"?action="backup.php"> ??<table?width="99%"?border="1"?cellpadding='0'?cellspacing='1'> ????<tr?align="center"?class='header'><td?colspan="2">数据备份</td></tr> ????<tr><td?colspan="2">备份方式</td></tr> ????<tr><td><input?type="radio"?name="bfzl"?value="quanbubiao">????????备份全部数据</td><td>备份全部数据表中的数据到一个备份文件</td></tr> ????<tr><td><input?type="radio"?name="bfzl"?value="danbiao">备份单张表数据? ????????<select?name="tablename"><option?value="">请选择</option> ??????????<? ????????$d->query("show?table?status?from?$mysqldb"); ????????while($d->nextrecord()){ ????????echo?"<option?value='".$d->f('Name')."'>".$d->f('Name')."</option>";} ?????????> ????????</select></td><td>备份选中数据表中的数据到单独的备份文件</td></tr> ????<tr><td?colspan="2">使用分卷备份</td></tr> ????<tr><td?colspan="2"><input?type="checkbox"?name="fenjuan"?value="yes"> ????????分卷备份?<input?name="filesize"?type="text"?size="10">K</td></tr> ????<tr><td?colspan="2">选择目标位置</td></tr> ????<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"> ????????备份到本地</td></tr> ????<tr><td?colspan="2"?align='center'><input?type="submit"?name="act"?value="备份"></td></tr> ??</table></form> <?/*-------------界面结束-------------*/}/*---------------------------------*/ /*----*/else{/*--------------主程序-----------------------------------------*/ if($_POST['weizhi']=="localpc"&&$_POST['fenjuan']=='yes') ????{$msgs[]="只有选择备份到服务器,才能使用分卷备份功能"; show_msg($msgs);?pageend();} if($_POST['fenjuan']=="yes"&&!$_POST['filesize']) ????{$msgs[]="您选择了分卷备份功能,但未填写分卷文件大小"; show_msg($msgs);?pageend();} if($_POST['weizhi']=="server"&&!writeable("./backup")) ????{$msgs[]="备份文件存放目录'./backup'不可写,请修改目录属性"; show_msg($msgs);?pageend();} /*----------备份全部表-------------*/if($_POST['bfzl']=="quanbubiao"){/*----*/ /*----不分卷*/if(!$_POST['fenjuan']){/*--------------------------------*/ if(!$tables=$d->query("show?table?status?from?$mysqldb")) ????{$msgs[]="读数据库结构错误";?show_msg($msgs);?pageend();} $sql=""; while($d->nextrecord($tables)) ????{ ????$table=$d->f("Name"); ????$sql.=make_header($table); ????$d->query("select?*?from?$table"); ????$num_fields=$d->nf(); ????while($d->nextrecord()) ????{$sql.=make_record($table,$num_fields);} ????} $filename=date("Ymd",time())."_all.sql"; if($_POST['weizhi']=="localpc")?down_file($sql,$filename); elseif($_POST['weizhi']=="server") ????{if(write_file($sql,$filename)) $msgs[]="全部数据表数据备份完成,生成备份文件'./backup/$filename'"; ????else?$msgs[]="备份全部数据表失败"; ????show_msg($msgs); ????pageend(); ????} /*-----------------不要卷结束*/}/*-----------------------*/ /*-----------------分卷*/else{/*-------------------------*/ if(!$_POST['filesize']) ????{$msgs[]="请填写备份文件分卷大小";?show_msg($msgs);pageend();} if(!$tables=$d->query("show?table?status?from?$mysqldb")) ????{$msgs[]="读数据库结构错误";?show_msg($msgs);?pageend();} $sql="";?$p=1; $filename=date("Ymd",time())."_all"; while($d->nextrecord($tables)) { ????$table=$d->f("Name"); ????$sql.=make_header($table); ????$d->query("select?*?from?$table"); ????$num_fields=$d->nf(); ????while($d->nextrecord()) ????{$sql.=make_record($table,$num_fields); ????if(strlen($sql)>=$_POST['filesize']*1000){ ????????????$filename.=("_v".$p.".sql"); ????????????if(write_file($sql,$filename)) ????????????$msgs[]="全部数据表-卷-".$p."-数据备份完成,生成备份文件'./backup/$filename'"; ????????????else?$msgs[]="备份表-".$_POST['tablename']."-失败"; ????????????$p++; ????????????$filename=date("Ymd",time())."_all"; ????????????$sql="";} ????} } if($sql!=""){$filename.=("_v".$p.".sql");???????? if(write_file($sql,$filename)) $msgs[]="全部数据表-卷-".$p."-数据备份完成,生成备份文件'./backup/$filename'";} show_msg($msgs); /*---------------------分卷结束*/}/*--------------------------------------*/ /*--------备份全部表结束*/}/*---------------------------------------------*/ /*--------备份单表------*/elseif($_POST['bfzl']=="danbiao"){/*------------*/ if(!$_POST['tablename']) ????{$msgs[]="请选择要备份的数据表";?show_msg($msgs);?pageend();} /*--------不分卷*/if(!$_POST['fenjuan']){/*-------------------------------*/ $sql=make_header($_POST['tablename']); $d->query("select?*?from?".$_POST['tablename']); $num_fields=$d->nf(); while($d->nextrecord()) ????{$sql.=make_record($_POST['tablename'],$num_fields);} $filename=date("Ymd",time())."_".$_POST['tablename'].".sql"; if($_POST['weizhi']=="localpc")?down_file($sql,$filename)) $msgs[]="表-".$_POST['tablename']."-数据备份完成,生成备份文件'./backup/$filename'"; ????else?$msgs[]="备份表-".$_POST['tablename']."-失败"; ????show_msg($msgs); ????pageend(); ????} /*----------------不要卷结束*/}/*------------------------------------*/ /*----------------分卷*/else{/*--------------------------------------*/ if(!$_POST['filesize']) ????{$msgs[]="请填写备份文件分卷大小";?show_msg($msgs);pageend();} $sql=make_header($_POST['tablename']);?$p=1;? ????$filename=date("Ymd",time())."_".$_POST['tablename']; ????$d->query("select?*?from?".$_POST['tablename']); ????$num_fields=$d->nf(); ????while?($d->nextrecord())? ????{???? ????????$sql.=make_record($_POST['tablename'],$num_fields); ???????if(strlen($sql)>=$_POST['filesize']*1000){ ????????????$filename.=("_v".$p.".sql"); ????????????if(write_file($sql,$filename)) ????????????$msgs[]="表-".$_POST['tablename']."-卷-".$p."-数据备份完成,time())."_".$_POST['tablename']; ????????????$sql="";} ????} if($sql!=""){$filename.=("_v".$p.".sql");???????? if(write_file($sql,$filename)) $msgs[]="表-".$_POST['tablename']."-卷-".$p."-数据备份完成,生成备份文件'./backup/$filename'";} show_msg($msgs); /*----------分卷结束*/}/*--------------------------------------------------*/ /*----------备份单表结束*/}/*----------------------------------------------*/ /*---*/}/*-------------主程序结束------------------------------------------*/ function?write_file($sql,$filename) { $re=true; if(!@$fp=fopen("./backup/".$filename,"w+"))?{$re=false;?echo?"failed?to?open?target?file";} if(!@fwrite($fp,$sql))?{$re=false;?echo?"failed?to?write?file";} if(!@fclose($fp))?{$re=false;?echo?"failed?to?close?target?file";} return?$re; } function?down_file($sql,$filename) { ????ob_end_clean(); ????header("Content-Encoding:?none"); ????header("Content-Type:?".(strpos($_SERVER['HTTP_USER_AGENT'],?'MSIE')???'application/octetstream'?:?'application/octet-stream')); ????header("Content-Disposition:?".(strpos($_SERVER['HTTP_USER_AGENT'],?'MSIE')???'inline;?'?:?'attachment;?')."filename=".$filename); ????header("Content-Length:?".strlen($sql)); ????header("Pragma:?no-cache"); ????header("Expires:?0"); ????echo?$sql; ????$e=ob_get_contents(); ????ob_end_clean(); } function?writeable($dir) { ????if(!is_dir($dir))?{ ????@mkdir($dir,?0777); ????} ????if(is_dir($dir))? ????{ ????if($fp?=?@fopen("$dir/test.test",?'w')) ????????{ @fclose($fp); ????@unlink("$dir/test.test"); ????$writeable?=?1; }? ????else?{ $writeable?=?0; ????} } ????return?$writeable; } function?make_header($table) {global?$d; $sql="DROP?TABLE?IF?EXISTS?".$table."n"; $d->query("show?create?table?".$table); $d->nextrecord(); $tmp=preg_replace("/n/","",$d->f("Create?Table")); $sql.=$tmp."n"; return?$sql; } function?make_record($table,$num_fields) {global?$d; $comma=""; $sql?.=?"INSERT?INTO?".$table."?VALUES("; for($i?=?0;?$i?<?$num_fields;?$i++)? {$sql?.=?($comma."'".mysql_escape_string($d->record[$i])."'");?$comma?=?",";} $sql?.=?")n"; return?$sql; } function?show_msg($msgs) { $title="提示:"; echo?"<table?width='100%'?border='1'??cellpadding='0'?cellspacing='1'>"; echo?"<tr><td>".$title."</td></tr>"; echo?"<tr><td><br><ul>"; while?(list($k,$v)=each($msgs)) ????{ ????echo?"<li>".$v."</li>"; ????} echo?"</ul></td></tr></table>"; } function?pageend() { exit(); } ?> 3. restore.php //还原脚本 代码如下: <? session_start(); global?$mysqlhost,$mysqldb); /******界面*/if(!$_POST['act']&&!$_SESSION['data_file']){/**********************/ $msgs[]="本功能在恢复备份数据的同时,将全部覆盖原有数据,请确定是否需要恢复,以免造成数据损失"; $msgs[]="数据恢复功能只能恢复由dShop导出的数据文件,其他软件导出格式可能无法识别"; $msgs[]="从本地恢复数据需要服务器支持文件上传并保证数据尺寸小于允许上传的上限,否则只能使用从服务器恢复"; $msgs[]="如果您使用了分卷备份,只需手工导入文件卷1,其他数据文件会由系统自动导入"; show_msg($msgs); ?> <form?action=""?method="post"?enctype="multipart/form-data"?name="restore.php"> <table?width="91%"?border="0"?cellpadding="0"?cellspacing="1"> <tr?align="center"?class="header"><td?colspan="2"?align="center">数据恢复</td></tr> <tr><td?width="33%"><input?type="radio"?name="restorefrom"?value="server"?checked> 从服务器文件恢复?</td><td?width="67%"><select?name="serverfile"> ????<option?value="">-请选择-</option> <? $handle=opendir('./backup'); while?($file?=?readdir($handle))?{ ????if(eregi("^[0-9]{8,8}([0-9a-z_]+)(.sql)$",$file))?echo?"<option?value='$file'>$file</option>";} closedir($handle);? ?> ??</select>?</td></tr> <tr><td><input?type="radio"?name="restorefrom"?value="localpc">???????从本地文件恢复</td> <td><input?type="hidden"?name="MAX_FILE_SIZE"?value="1500000"><input?type="file"?name="myfile"></td></tr> <tr><td?colspan="2"?align="center">?<input?type="submit"?name="act"?value="恢复"></td>??</tr></table></form> <?/**************************界面结束*/}/*************************************/ /****************************主程序*/if($_POST['act']=="恢复"){/**************/ /***************服务器恢复*/if($_POST['restorefrom']=="server"){/**************/ if(!$_POST['serverfile']) ????{$msgs[]="您选择从服务器文件恢复备份,但没有指定备份文件"; ?????show_msg($msgs);?pageend();????} if(!eregi("_v[0-9]+",$_POST['serverfile'])) ????{$filename="./backup/".$_POST['serverfile']; ????if(import($filename))?$msgs[]="备份文件".$_POST['serverfile']."成功导入数据库"; ????else?$msgs[]="备份文件".$_POST['serverfile']."导入失败"; ????show_msg($msgs);?pageend();???????? ????} else ????{ ????$filename="./backup/".$_POST['serverfile']; ????if(import($filename))?$msgs[]="备份文件".$_POST['serverfile']."成功导入数据库"; ????else?{$msgs[]="备份文件".$_POST['serverfile']."导入失败";show_msg($msgs);pageend();} ????$voltmp=explode("_v",$_POST['serverfile']); ????$volname=$voltmp[0]; ????$volnum=explode(".sq",$voltmp[1]); ????$volnum=intval($volnum[0])+1; ????$tmpfile=$volname."_v".$volnum.".sql"; ????if(file_exists("./backup/".$tmpfile)) ????????{ ????????$msgs[]="程序将在3秒钟后自动开始导入此分卷备份的下一部份:文件".$tmpfile.",请勿手动中止程序的运行,以免数据库结构受损"; ????????$_SESSION['data_file']=$tmpfile; ????????show_msg($msgs); ????????sleep(3); ????????echo?"<script?language='javascript'>";? ????????echo?"location='restore.php';";? ????????echo?"</script>";? ????????} ????else ????????{ ????????$msgs[]="此分卷备份全部导入成功"; ????????show_msg($msgs); ????????} ????} /**************服务器恢复结束*/}/********************************************/ /*****************本地恢复*/if($_POST['restorefrom']=="localpc"){/**************/ ????switch?($_FILES['myfile']['error']) ????{ ????case?1: ????case?2: ????$msgs[]="您上传的文件大于服务器限定值,上传未成功"; ????break; ????case?3: ????$msgs[]="未能从本地完整上传备份文件"; ????break; ????case?4: ????$msgs[]="从本地上传备份文件失败"; ????break; ????case?0: ????break; ????} ????if($msgs){show_msg($msgs);pageend();} $fname=date("Ymd",time())."_".sjs(5).".sql"; if?(is_uploaded_file($_FILES['myfile']['tmp_name']))?{ ????copy($_FILES['myfile']['tmp_name'],?"./backup/".$fname);} if?(file_exists("./backup/".$fname))? ????{ ????$msgs[]="本地备份文件上传成功"; ????if(import("./backup/".$fname))?{$msgs[]="本地备份文件成功导入数据库";?unlink("./backup/".$fname);} ????else?$msgs[]="本地备份文件导入数据库失败"; ????} else?($msgs[]="从本地上传备份文件失败"); show_msg($msgs); /****本地恢复结束*****/}/****************************************************/ /****************************主程序结束*/}/**********************************/ /*************************剩余分卷备份恢复**********************************/ if(!$_POST['act']&&$_SESSION['data_file']) { ????$filename="./backup/".$_SESSION['data_file']; ????if(import($filename))?$msgs[]="备份文件".$_SESSION['data_file']."成功导入数据库"; ????else?{$msgs[]="备份文件".$_SESSION['data_file']."导入失败";show_msg($msgs);pageend();} ????$voltmp=explode("_v",$_SESSION['data_file']); ????$volname=$voltmp[0]; ????$volnum=explode(".sq",以免数据库结构受损"; ????????$_SESSION['data_file']=$tmpfile; ????????show_msg($msgs); ????????sleep(3); ????????echo?"<script?language='javascript'>";? ????????echo?"location='restore.php';";? ????????echo?"</script>";? ????????} ????else ????????{ ????????$msgs[]="此分卷备份全部导入成功"; ????????unset($_SESSION['data_file']); ????????show_msg($msgs); ????????} } /**********************剩余分卷备份恢复结束*******************************/ function?import($fname) {global?$d; $sqls=file($fname); foreach($sqls?as?$sql) ????{ ????str_replace("r",$sql); ????str_replace("n",$sql); ????if(!$d->query(trim($sql)))?return?false; ????} return?true; } function?show_msg($msgs) { $title="提示:"; echo?"<table?width='100%'?border='1'??cellpadding='0'?cellspacing='1'>"; echo?"<tr><td>".$title."</td></tr>"; echo?"<tr><td><br><ul>"; while?(list($k,$v)=each($msgs)) ????{ ????echo?"<li>".$v."</li>"; ????} echo?"</ul></td></tr></table>"; } function?pageend() { exit(); } ?> MYSQL数据库文件结构非常清晰,只要在文件2和3里面设置好数据库服务器的地址、用户名、密码就可以备份还原数据了.需要注意的是: MYSQL数据库?使用时候要在同级目录下建一个Backup目录,权限需要可写,用于存放导出的脚本. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |