Recommend a compact Mysql database backup script without a virtual host (PHP)
- 2020-05-06 11:46:30
- OfStack
Recently, I often need to backup Mysql database on remote server to the machine. At the beginning, I used to directly backup data directory of Mysql, but it often caused problems due to different encoding. Later, my friend recommended me to use a very convenient and small PHP program -- MyDB. There are three files:
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(" Connection to 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 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;
}
/**************************************** End of the class ***************************/
}?>
Es19en.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[]=" The server backup directory is backup";
$msgs[]=" For larger data tables, it is strongly recommended to use a partitioned backup ";
$msgs[]=" You can only use the split backup feature if you choose to backup to the server ";
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"> The data backup </td></tr>
<tr><td colspan="2"> backups </td></tr>
<tr><td><input type="radio" name="bfzl" value="quanbubiao"> Back up all data </td><td> Backup all data in the data table to a backup file </td></tr>
<tr><td><input type="radio" name="bfzl" value="danbiao"> Backup single table data
<select name="tablename"><option value=""> Please select a </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 data table to a separate backup file </td></tr>
<tr><td colspan="2"> Use a partitioned backup </td></tr>
<tr><td colspan="2"><input type="checkbox" name="fenjuan" value="yes">
Volume classification backup <input name="filesize" type="text" size="10">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=" The backup "></td></tr>
</table></form>
<?/*------------- End of the interface -------------*/}/*---------------------------------*/
/*----*/else{/*-------------- The main program -----------------------------------------*/
if($_POST['weizhi']=="localpc"&&$_POST['fenjuan']=='yes')
{$msgs[]=" You can only use the split backup feature if you choose to backup to the server ";
show_msg($msgs); pageend();}
if($_POST['fenjuan']=="yes"&&!$_POST['filesize'])
{$msgs[]=" You selected the partition backup function, but did not fill in the partition file size ";
show_msg($msgs); pageend();}
if($_POST['weizhi']=="server"&&!writeable("./backup"))
{$msgs[]=" Backup file storage directory './backup' Unable to write, please modify the directory properties ";
show_msg($msgs); pageend();}
/*---------- Backup all tables -------------*/if($_POST['bfzl']=="quanbubiao"){/*----*/
/*---- No volume classification */if(!$_POST['fenjuan']){/*--------------------------------*/
if(!$tables=$d->query("show table status from $mysqldb"))
{$msgs[]=" Error reading database structure "; 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 completed , Generate backup files './backup/$filename'";
else $msgs[]=" Failed to backup all data tables ";
show_msg($msgs);
pageend();
}
/*----------------- Don't wind up */}/*-----------------------*/
/*----------------- Volume classification */else{/*-------------------------*/
if(!$_POST['filesize'])
{$msgs[]=" Please fill in the backup file size "; show_msg($msgs);pageend();}
if(!$tables=$d->query("show table status from $mysqldb"))
{$msgs[]=" Error reading database structure "; 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[]=" Full data sheet - volume -".$p."- Data backup completed , Generate backup files './backup/$filename'";
else $msgs[]=" The backup table -".$_POST['tablename']."- failure ";
$p++;
$filename=date("Ymd",time())."_all";
$sql="";}
}
}
if($sql!=""){$filename.=("_v".$p.".sql");
if(write_file($sql,$filename))
$msgs[]=" Full data sheet - volume -".$p."- Data backup completed , Generate backup files './backup/$filename'";}
show_msg($msgs);
/*--------------------- End of the volume classification */}/*--------------------------------------*/
/*-------- The backup of all tables ends */}/*---------------------------------------------*/
/*-------- The backup single table ------*/elseif($_POST['bfzl']=="danbiao"){/*------------*/
if(!$_POST['tablename'])
{$msgs[]=" Select the data table to backup "; show_msg($msgs); pageend();}
/*-------- No volume classification */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 completed , Generate backup files './backup/$filename'";
else $msgs[]=" The backup table -".$_POST['tablename']."- failure ";
show_msg($msgs);
pageend();
}
/*---------------- Don't wind up */}/*------------------------------------*/
/*---------------- Volume classification */else{/*--------------------------------------*/
if(!$_POST['filesize'])
{$msgs[]=" Please fill in the backup file size "; 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[]=" table -".$_POST['tablename']."- volume -".$p."- Data backup completed , Generate backup files './backup/$filename'";
else $msgs[]=" The 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 completed , Generate backup files './backup/$filename'";}
show_msg($msgs);
/*---------- End of the volume classification */}/*--------------------------------------------------*/
/*---------- End of backup single table */}/*----------------------------------------------*/
/*---*/}/*------------- End of main program ------------------------------------------*/
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=" Tip: ";
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();
}
?>
Es26en.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 function will cover all the original data while restoring the backup data. Please determine whether recovery is needed to avoid data loss ";
$msgs[]=" The data recovery function can only be restored by dShop Exported data files, other software export format may not be recognized ";
$msgs[]=" Recovery from the local data requires the server to support file upload and ensure that the data size is below the permitted upload limit, otherwise recovery from the server can only be used ";
$msgs[]=" If you use a partitioned backup, simply import the file volume manually 1 , other data files will be imported automatically 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>
Restore from server file </td><td width="67%"><select name="serverfile">
<option value="">- Please select a -</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>
<?/************************** End of the interface */}/*************************************/
/**************************** The 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 the 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 imported database ";
else $msgs[]=" Backup file ".$_POST['serverfile']." Import failure ";
show_msg($msgs); pageend();
}
else
{
$filename="./backup/".$_POST['serverfile'];
if(import($filename)) $msgs[]=" Backup file ".$_POST['serverfile']." Successfully imported database ";
else {$msgs[]=" Backup file ".$_POST['serverfile']." Import failure ";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[]=" Program will be 3 Automatically starts importing the next part of the partition backup in seconds: the file ".$tmpfile." , do not manually abort the program to avoid damage to the database structure ";
$_SESSION['data_file']=$tmpfile;
show_msg($msgs);
sleep(3);
echo "<script language='javascript'>";
echo "location='restore.php';";
echo "</script>";
}
else
{
$msgs[]=" This sub-volume backup was imported successfully ";
show_msg($msgs);
}
}
/************** Server recovery ends */}/********************************************/
/***************** Local recovery */if($_POST['restorefrom']=="localpc"){/**************/
switch ($_FILES['myfile']['error'])
{
case 1:
case 2:
$msgs[]=" The file you uploaded is larger than the server limit. The upload was unsuccessful ";
break;
case 3:
$msgs[]=" Failed to upload the backup file fully from the local location ";
break;
case 4:
$msgs[]=" Failed to upload backup file from local ";
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[]=" Local backup file uploaded successfully ";
if(import("./backup/".$fname)) {$msgs[]=" The local backup file was successfully imported into the database "; unlink("./backup/".$fname);}
else $msgs[]=" The local backup file import to the database failed ";
}
else ($msgs[]=" Failed to upload backup file from local ");
show_msg($msgs);
/**** End of local recovery *****/}/****************************************************/
/**************************** End of main program */}/**********************************/
/************************* Backup and restore the remaining volumes **********************************/
if(!$_POST['act']&&$_SESSION['data_file'])
{
$filename="./backup/".$_SESSION['data_file'];
if(import($filename)) $msgs[]=" Backup file ".$_SESSION['data_file']." Successfully imported database ";
else {$msgs[]=" Backup file ".$_SESSION['data_file']." Import failure ";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[]=" Program will be 3 Automatically starts importing the next part of the partition backup in seconds: the file ".$tmpfile." , do not manually abort the program to avoid damage to the database structure ";
$_SESSION['data_file']=$tmpfile;
show_msg($msgs);
sleep(3);
echo "<script language='javascript'>";
echo "location='restore.php';";
echo "</script>";
}
else
{
$msgs[]=" This sub-volume backup was imported successfully ";
unset($_SESSION['data_file']);
show_msg($msgs);
}
}
/********************** The remaining volume backup and restore is over *******************************/
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=" Tip: ";
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 you set up the address, user name, password of the database server in files 2 and 3, you can backup and restore the data. One thing to note:
· create an Backup directory in the same level directory with writable permissions to hold exported scripts.
· when the backup database is large, the server script timeout should be increased.
· support for sub-volume backup. When restoring, all scripts will be automatically restored as long as the first script of the sub-volume backup is selected.
· the file size should not be too large, preferably no larger than 2MB.
· to be safe, scripts need not be deleted from the server.
Package files to download