In recent work, I often need to back up the Mysql database on the remote server to the local machine. At first, I used the method of directly backing up the Mysql data directory, but problems often occurred due to different encodings. Later, a friend recommended me to use a very convenient and compact PHP program-MyDB. Contains three files in total:
1. mydb.php //DB class
The code is as follows:
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查询错误
"; echo "查询语句：".$sql."
"; echo "错误信息：".$err; } /****************************************类结束***************************/ }?>
2. backup.php // Backup script
The code is as follows:
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); elseif($_POST['weizhi']=="server") {if(write_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."-数据备份完成,生成备份文件'./backup/$filename'"; else $msgs[]="备份表-".$_POST['tablename']."-失败"; $p++; $filename=date("Ymd",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 "
| ".$title." |
|
3. restore.php //Restore script
The code is as follows:
"; echo "location='restore.php';"; echo ""; } 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",$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 ""; } 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 "
| ".$title." |
|
The file structure is very clear. As long as the address, user name, and password of the database server are set in files 2 and 3, the data can be backed up and restored. It should be noted that:
·When using it, a Backup directory must be created under the same level directory with writable permissions to store exported scripts.
·When the backup database is relatively large, the server script timeout should be increased.
·Supports volume backup. When restoring, just select the first script of the volume backup and all scripts will be automatically restored.
·The size of the volume file should not be too large, preferably no more than 2MB.
·For security reasons, remember to delete the script from the server when not in use.
【Related recommendations】
2.Teach you how to start and stop one of the Mysql services
3.Example tutorial for processing special sql statements in mysql
4.Detailed explanation of how to write sql statements to delete tables in different databases
5.Teach you how to run multiple MySQL services on one machine
The above is the detailed content of Small Mysql database backup script without virtual host. For more information, please follow other related articles on the PHP Chinese website!