The recent work often need to back up the MySQL database on the remote server to the local computer, a direct backup of the MySQL data directory, but because of different coding reasons often arise. Later, my friend recommended me to use a very handy and compact PHP program--mydb. Contains three files altogether:
1. mydb.php//db class
?
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 ("Connection server failed.");
@mysql_select_db ($database, $this->linkid) or Die ("Unable to open database");
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 is 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 query error <br>";
echo "query statement:". $sql. " <br> ";
echo "error message:". $err;
}
/**************************************** class End ***************************/
}?>
2. backup.php//Backup script
?
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);
/*--------------Interface--------------*/if (!$_post[' act ') {/*----------------------* *
$MSGS []= Server backup directory is backup];
$MSGS []=] for large data tables, it is highly recommended to use a split-volume backup.
$MSGS []=] only choose to back up to the server to use the split-volume backup function ";
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" > Data backup </td></tr>
<TR><TD colspan= "2" > Backup mode </td></tr>
<tr><td><input type= "Radio" name= "Bfzl" value= "Quanbubiao" > Backup all Data </td><td> back up all data tables Data to a backup file </td></tr>
<tr><td><input type= "Radio" name= "Bfzl" value= "Danbiao" > Backup single sheet data
<select name= "tablename" ><option value= "" > Please select </option>
?
$d->query ("Show Table status from $mysqldb");
while ($d->nextrecord ()) {
echo "<option value= '". $d->f (' Name '). "' > ". $d->f (' Name ')." </option> ";}
?>
</select></td><td> backup the data in the selected datasheet to a separate backup file </td></tr>
<TR><TD colspan= "2" > Using split-volume backup </td></tr>
<TR><TD colspan= "2" ><input type= "checkbox" Name= "Fenjuan" value= "yes" >
Split-volume backup <input name= "filesize" type= "text" size= "ten" >K</td></tr>
<TR><TD colspan= "2" > select target Location </td></tr>
<TR><TD colspan= "2" ><input type= "Radio" name= "Weizhi" value= "Server" checked> Backup to Server </td> </TR><TR class= "Cells" ><td colspan= ' 2 ' > <input type= "Radio" name= "Weizhi" value= "LOCALPC" >
Backup to local </td></tr>
<TR><TD colspan= "2" align= ' center ' ><input type= "submit" name= "Act" value= "Backup" ></td></tr >
</table></form>
<?/*-------------interface End-------------*/}/*---------------------------------* *
/*----*/else{/*--------------Main program-----------------------------------------* *
if ($_post[' Weizhi ']== "LOCALPC" &&$_post[' Fenjuan ']== ' yes ')
{$msgs []=] only choose to back up to the server to use the split-volume backup function ";
Show_msg ($MSGS); Pageend ();}
if ($_post[' Fenjuan ']== "yes" &&!$_post[' filesize '])
{$MSGS []=] You have selected the volume backup function, but not the volume file size ";
Show_msg ($MSGS); Pageend ();}
if ($_post[' Weizhi ']== "Server" &&!writeable ("./backup"))
{$MSGS []= backup file storage directory './backup ' not writable, please modify the directory attribute ';
Show_msg ($MSGS); Pageend ();}
/*----------back up all Tables-------------*/if ($_post[' Bfzl ']== "Quanbubiao") {/*----* *
/*----does not */if (!$_post[' Fenjuan ')) {/* *--------------------------------
if (! $tables = $d->query ("Show Table status from $mysqldb")
{$MSGS []= read database structure ERROR] 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 []=] All data table data backup complete, generate backup file './backup/$filename ' ";
else $msgs []=] failed to back up all data tables;
Show_msg ($MSGS);
Pageend ();
}
/*-----------------do not roll over */}/*-----------------------* *
/*-----------------*/else{/*-------------------------* *
if (!$_post[' filesize '])
{$msgs []= "Please fill in the backup file volume size"; show_msg ($MSGS);p ageend ();}
if (! $tables = $d->query ("Show Table status from $mysqldb")
{$MSGS []= read database structure ERROR] 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 []=] All data tables-volumes-". $p." -Data backup complete, generate backup file './backup/$filename ' ";
else $MSGS []= Backup Table-". $_post[' tablename ']." -Failure ";
$p + +;
$filename =date ("Ymd", Time ()). " _all ";
$sql = "";}
}
}
if ($sql!= "") {$filename. = ("_v". $p. ") SQL ");
if (Write_file ($sql, $filename))
$MSGS []=] All data tables-volumes-". $p." -Data backup complete, generate backup file './backup/$filename ';}
Show_msg ($MSGS);
/*---------------------The end of the */}/*--------------------------------------* *
/*--------Backup All tables End */}/*---------------------------------------------* *
/*--------Backup sheet------*/elseif ($_post[' Bfzl ']== "Danbiao") {/*------------* *
if (!$_post[' tablename '])
{$msgs []=] Select the datasheet to back up, show_msg ($MSGS); Pageend ();}
/*--------does not */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 []= table-". $_post[' tablename ']." -Data backup complete, generate backup file './backup/$filename ' ";
else $MSGS []= Backup Table-". $_post[' tablename ']." -Failure ";
Show_msg ($MSGS);
Pageend ();
}
/*----------------do not roll over */}/*------------------------------------* *
/*----------------*/else{/*--------------------------------------* *
if (!$_post[' filesize '])
{$msgs []= "Please fill in the backup file volume size"; show_msg ($MSGS);p ageend ();}
$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 []= table-". $_post[' tablename ']." -Volume-". $p." -Data backup complete, generate backup file './backup/$filename ' ";
else $MSGS []= Backup Table-". $_post[' tablename ']." -Failure ";
$p + +;
$filename =date ("Ymd", Time ()). " _ ". $_post[' tablename '];
$sql = "";}
}
if ($sql!= "") {$filename. = ("_v". $p. ") SQL ");
if (Write_file ($sql, $filename))
$msgs []= table-". $_post[' tablename ']." -Volume-". $p." -Data backup complete, generate backup file './backup/$filename ';}
Show_msg ($MSGS);
/*----------The end of the */}/*--------------------------------------------------* *
/*----------Backup sheet End */}/*----------------------------------------------* *
/*---*/}/*-------------main program End------------------------------------------* *
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 = "Hint:";
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//Restore Script
?
Session_Start ();
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);
/****** interface */if (!$_post[' act ']&&!$_session[' data_file ']) {/**********************/
$MSGS []=] This feature in the recovery of backup data at the same time, will all overwrite the original data, please determine whether the need to restore, so as not to cause data loss ";
$MSGS []= Data Recovery feature can only recover data files exported by Dshop, other software export formats may not be recognized ";
$MSGS []=] restore data from the local need server support file upload and ensure that the data size is less than the maximum allowable upload, otherwise you can only use recovery from the server;
$MSGS []=] If you use a split-volume backup, you only need to manually import file Volume 1, other data files will be automatically imported by the system ";
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" > Data Recovery </td></tr>
<TR><TD width= "33%" ><input type= "Radio" name= "Restorefrom" value= "Server" checked>
Recovering from server files </td><td width= "67%" ><select name= "Serverfile" >
<option value= "" >-Please choose-</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" > Restore from local file </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= "restore" ></td> </tr ></table></form>
<?/************************** interface End */}/*************************************/
/**************************** Main program */if ($_post[' act ']== "restore") {/**************/
/*************** Server Recovery */if ($_post[' restorefrom ']== "server") {/**************/
if (!$_post[' serverfile '])
{$MSGS []=] you chose to restore the backup from the server file, but did not specify a backup file;
Show_msg ($MSGS); Pageend (); }
if (!eregi ("_v[0-9]+", $_post[' Serverfile '))
{$filename = "./backup/". $_post[' Serverfile ');
if (import ($filename)) $msgs []= backup file. $_post[' Serverfile ']. " Successfully import Database ";
else $msgs []= "Backup File". $_post[' Serverfile ']. " Import Failed ";
Show_msg ($MSGS); Pageend ();
}
Else
{
$filename = "./backup/". $_post[' Serverfile '];
if (import ($filename)) $msgs []= backup file. $_post[' Serverfile ']. " Successfully import Database ";
else {$msgs []= "Backup File". $_post[' Serverfile ']. " Import Failed "; Show_msg ($MSGS);p ageend ();}
$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 []=] will automatically start importing the next part of this volume backup after 3 seconds: File ". $tmpfile.", do not manually discontinue the operation of the program to avoid the database structure damage ";
$_session[' data_file ']= $tmpfile;
Show_msg ($MSGS);
Sleep (3);
echo "<script language= ' JavaScript ' >";
echo "location= ' restore.php ';";
echo "</script>";
}
Else
{
$MSGS []= The full import of this volume backup succeeded];
Show_msg ($MSGS);
}
}
/************** Server Recovery End */}/********************************************/
/***************** Local recovery */if ($_post[' restorefrom ']== "LOCALPC") {/**************/
Switch ($_files[' myfile '] [' ERROR '])
{
Case 1:
Case 2:
$MSGS []=] You upload the file is larger than the server limit, upload did not succeed ";
Break
Case 3:
$msgs []=] failed to upload the backup file from the local whole;
Break
Case 4:
$msgs []=] failed to upload the backup file from the local area;
Break
Case 0:
Break
}
if ($msgs) {show_msg ($msgs);p ageend ();}
$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 []= "Local backup file uploaded successfully";
if (Import ("./backup/". $fname)) {$msgs []= Local backup file successfully imported to database] unlink ("./backup/". $fname);}
else $MSGS []= Local backup file failed to import the database];
}
Else ($msgs []= failed to upload backup files from local);
Show_msg ($MSGS);
/**** Local recovery End *****/}/****************************************************/
/**************************** Main program End */}/**********************************/
/************************* remaining split-volume backup recovery **********************************/
if (!$_post[' act ']&&$_session[' data_file ')]
{
$filename = "./backup/". $_session[' data_file '];
if (import ($filename)) $msgs []= backup file. $_session[' data_file ']. " Successfully import Database ";
else {$msgs []= "Backup File". $_session[' Data_file ']. " Import Failed "; Show_msg ($MSGS);p ageend ();}
$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 []=] will automatically start importing the next part of this volume backup after 3 seconds: File ". $tmpfile.", do not manually discontinue the operation of the program to avoid the database structure damage ";
$_session[' data_file ']= $tmpfile;
Show_msg ($MSGS);
Sleep (3);
echo "<script language= ' JavaScript ' >";
echo "location= ' restore.php ';";
echo "</script>";
}
Else
{
$MSGS []= The full import of this volume backup succeeded];
unset ($_session[' data_file '));
Show_msg ($MSGS);
}
}
/********************** remaining split-volume backup recovery ended *******************************/
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 = "Hint:";
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 ();
}
?>
The file structure is very clear, as long as the database server's address, user name, password can be set up in Files 2 and 3 to back up the restored data. It is to be noted that:
• Use time to build a backup directory under the same level directory, the permissions need to be writable, for storing exported scripts.
• When the backup database is large, the server script timeout time to be larger.
• Support for volume backup, restore all scripts automatically when you select the first script for a split-volume backup.
• Volume file size Not too large, preferably not more than 2MB.
• For security reasons, the script does not have to remember to remove it from the server.
Package file Download
