Use PHP to back up the MySQL database with the most complete code

CompleteCodeUsing PHP to back up MySQL Databases

1. mydb. php // dB class
2. Backup. php // backup script
3. Restore. php // restore the script

Mydb. php

<?
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 ("failed to connect to the server .");
@ 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;
}
/*************************************** * Class end ***************************/
}?>

 

 

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 );
/* -------------- Interface -------------- */If (! $ _ Post ['ac']) {/* -------------------- */
$ Msgs [] = "the server Backup Directory is backup ";
$ Msgs [] = "we strongly recommend that you use volume-based backup for large data tables ";
$ Msgs [] = "the volume-based backup function can be used only when you select to back up data 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"> data backup </TD> </tr>
<Tr> <TD colspan = "2"> Backup mode </TD> </tr>
<Tr> <TD> <input type = "radio" name = "bfzl" value = "quanbubiao"> back up all data </TD> <TD> back up all data in the data table one backup file </TD> </tr>
<Tr> <TD> <input type = "radio" name = "bfzl" value = "danbiao"> back up data in a single table
<Select name = "tablename"> <option value = ""> 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> back up data in the selected data table to a separate backup file </TD> </tr>
<Tr> <TD colspan = "2"> back up data by volume </TD> </tr>
<Tr> <TD colspan = "2"> <input type = "checkbox" name = "fenjuan" value = "yes">
Volume-based backup <input name = "filesize" type = "text" size = "10"> K </TD> </tr>
<Tr> <TD colspan = "2"> select the target location </TD> </tr>
<Tr> <TD colspan = "2"> <input type = "radio" name = "Weizhi" value = "server" Checked> back up data to the server </TD> </tr> <tr class = "cells"> <TD colspan = '2'> <input type = "radio" name = "Weizhi" value = "localpc">
Back up data to a local device </TD> </tr>
<Tr> <TD colspan = "2" align = 'center'> <input type = "Submit" name = "act" value = "backup"> </TD>/ tr>
</Table> </form>
<? /* ------------- Interface End -------------*/}/*---------------------------------*/
/* ---- */Else {/* -------------- master Program -----------------------------------------*/
If ($ _ post ['weizhi'] = "localpc" & $ _ post ['fenjuan'] = 'yes ')
{$ Msgs [] = "the volume-based backup function can be used only when backup is selected to the server ";
Show_msg ($ MSGs); pageend ();}
If ($ _ post ['fenjuance'] = "yes "&&! $ _ Post ['filesize'])
{$ Msgs [] = "you have selected the volume-based backup function, but you have not entered the volume-based file size ";
Show_msg ($ MSGs); pageend ();}
If ($ _ post ['weizhi'] = "server "&&! Writeable ("./backup "))
{$ Msgs [] = "the backup file storage directory './backup' cannot be written. Please modify the Directory attribute ";
Show_msg ($ MSGs); pageend ();}

/* ---------- Back up all tables ------------- */if ($ _ post ['bfzl'] = "quanbubiao "){/*----*/
/* ---- No volume sharding */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 tables are backed up and the backup file './backup/$ filename' is generated '";
Else $ msgs [] = "failed to back up all data tables ";
Show_msg ($ MSGs );
Pageend ();
}
/* --------------- Do not end the volume */}/*-----------------------*/
/* --------------- Volume sharding */else {/*-------------------------*/
If (! $ _ Post ['filesize'])
{$ Msgs [] = "fill in the backup file volume size"; show_msg ($ MSGs); pageend ();}
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 is complete, and the backup file './backup/$ filename' is generated '";
Else $ msgs [] = "backup table-". $ _ post ['tablename']. "-failed ";
$ 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 is complete and the backup file './backup/$ filename '";}
Show_msg ($ MSGs );
/* --------------------- End of volume splitting */}/*--------------------------------------*/
/* -------- Backup of all tables ends */}/*---------------------------------------------*/

/* -------- Back up a single table ------ */elseif ($ _ post ['bfzl'] = "danbiao "){/*------------*/
If (! $ _ Post ['tablename'])
{$ Msgs [] = "select the data table to be backed up"; show_msg ($ MSGs); pageend ();}
/* -------- Not sharded */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']. "-after data backup is complete, the backup file './backup/$ filename' is generated '";
Else $ msgs [] = "backup table-". $ _ post ['tablename']. "-failed ";
Show_msg ($ MSGs );
Pageend ();
}
/* ---------------- Do not end the volume */}/*------------------------------------*/
/* -------------- Volume sharding */else {/*--------------------------------------*/
If (! $ _ Post ['filesize'])
{$ Msgs [] = "fill in the backup file volume 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. "-after data backup is completed, a backup file is generated '. /backup/$ filename '";
Else $ msgs [] = "backup table-". $ _ post ['tablename']. "-failed ";
$ 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. "-after data backup is completed, a backup file is generated '. /backup/$ filename '";}
Show_msg ($ MSGs );
/* ---------- Split end */}/*--------------------------------------------------*/
/* ---------- End of backup single table */}/*----------------------------------------------*/

/* --- */}/* ------------- The main program ends ------------------------------------------*/

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 '], 'msi ')? 'Application/etetstream': 'application/octet-stream '));

Header ("content-Disposition:". (strpos ($ _ server ['HTTP _ user_agent '], 'msi ')? '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 = "prompt :";
Echo "<Table width = '000000' 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 ();
}
?>
 

 

Restore. php

<?
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 ['ac'] &! $ _ Session ['data _ file']) {/***********************/
$ Msgs [] = "This function overwrites the original data while restoring the backup data. Please determine whether to recover the data to avoid data loss ";
$ Msgs [] = "the data recovery function can only restore data files exported by dshop. Other export formats may not be recognized by other software ";
$ Msgs [] = "restoring data locally requires the server to support file upload and ensure that the data size is smaller than the maximum size allowed for uploading. Otherwise, you can only restore data from the server ";
$ Msgs [] = "if you use volume-based backup, you only need to manually import file volume 1, and 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>
Recover from a Server File </TD> <TD width = "67%"> <select name = "serverfile">
<Option value = "">-select-</option>
<?
$ Handle = opendir ('./backup ');
While ($ file = readdir ($ handle )){
If (eregi ("^ [0-9] {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"> recover 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 */}/******* ******************************/
/**************************** Main program */if ($ _ post [' act '] = "Restore ") {/**************/
/*************** Server recovery */if ($ _ post ['restorefrom '] = "server ") {/**************/
If (! $ _ Post ['serverfile'])
{$ Msgs [] = "you choose to recover the backup from the server file, but no backup file is specified ";
Show_msg ($ MSGs); pageend ();}
If (! Eregi ("_ V [0-9] +", $ _ post ['serverfile'])
{$ Filename = "./backup/". $ _ post ['serverfile'];
If (Import ($ filename) $ msgs [] = "backup file". $ _ post ['serverfile']. "database imported successfully ";
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']. "database imported successfully ";
Else {$ msgs [] = "backup file". $ _ post ['serverfile']. "Import failed"; 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 [] = "the program will automatically start importing the next part of this volume backup in 3 seconds: File ". $ tmpfile. ", do not manually stop 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 volume backup is all 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 has been greater than the server's limit. Upload Failed ";
Break;
Case 3:
$ Msgs [] = "failed to upload the backup file from the local device ";
Break;
Case 4:
$ Msgs [] = "failed to upload the backup file locally ";
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 [] = "The local backup file has been uploaded successfully ";
If (Import (". /backup /". $ fname) {$ msgs [] = "The local backup file has been imported to the Database"; unlink (". /backup /". $ fname );}
Else $ msgs [] = "failed to import local backup file to Database ";
}
Else ($ msgs [] = "failed to upload the backup file locally ");
Show_msg ($ MSGs );
/***** Local recovery ended *****/}/************************ ****************************/
/***************************** Main program ends */}/**** ******************************/
********** ************************/
If (! $ _ Post ['act '] & $ _ session ['data _ file'])
{
$ Filename = "./backup/". $ _ session ['data _ file'];
If (Import ($ filename) $ msgs [] = "backup file". $ _ session ['data _ file']. "database imported successfully ";
Else {$ msgs [] = "backup file". $ _ session ['data _ file']. "Import failed"; 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 [] = "the program will automatically start importing the next part of this volume backup in 3 seconds: File ". $ tmpfile. ", do not manually stop 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 volume backup is all imported successfully ";
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 = "prompt :";
Echo "<Table width = '000000' 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. You only need to set the address, user name, and password of the database server in files 2 and 3 to back up and restore the data. Note that:

· Create a backup directory in the same directory as the backup directory. The permission must be writable and used to store exported scripts.
· When the backup database is large, the server script timeout time must be increased.
· Supports volume-based backup. You only need to select the first script for volume-based backup to restore all the scripts automatically.
· The size of the volume file should not be too large. It is best not to exceed 2 MB.
· To ensure security, you do not need to delete the script from the server.