MySQL database backup using PHP

1. to instantiate dbbak, you need to tell it two things: Where is the data server ($ connectid) and the directory to which it is backed up ($ backupdir ):

Require_once ('dbbak. php ');
Require_once ('tablebak. php ');
$ Connectid = mysql_connect ('localhost', 'root', '123 ');
$ Backupdir = 'data ';
$ Dbbak = new dbbak ($ connectid, $ backupdir );

2. Then you can start backing up the database. You can not only specify the database to be backed up, but also set to back up only those tables in detail:
2.1 if you want to back up all the tables in the mybbs database, you just need:

$ Dbbak-> backupdb ('mybbs ');

2.2 If you only want to back up the board, face, and friendlist tables in the mybbs library, you can use a one-dimensional array to specify:

$ Dbbak-> backupdb ('mybbs ', array ('board', 'face', 'dsdsite '));

2.3 if you only want to back up a table, such as the Board table:
$ Dbbak-> backupdb ('mybbs ', 'board ');
3. data recovery:
In three cases: 2.1, 2.1, and 2.3, you only need to modify the following statement and replace backupdb with restoredb to recover the data:

$ Dbbak-> restoredb ('mybbs ');
SQLCode
$ Dbbak-> restoredb ('mybbs ', array ('board', 'face', 'dsdsite '));
PHP code
$ Dbbak-> restoredb ('mybbs ', 'board ');
PHP code
Require_once ('tablebak. php ');
Class dbbak {
VaR $ _ mysql_link_id;
VaR $ _ datadir;
VaR $ _ tablelist;
VaR $ _ tablebak;

Function dbbak ($ _ mysql_link_id, $ datadir)
{
((! Is_string ($ datadir) | strlen ($ datadir) = 0) & Die ('error: $ datadir is not a string ');
! Is_dir ($ datadir) & mkdir ($ datadir );
$ This-> _ datadir = $ datadir;
$ This-> _ mysql_link_id = $ _ mysql_link_id;
}

Function backupdb ($ dbname, $ tablename = NULL)
{
((! Is_string ($ dbname) | strlen ($ dbname) = 0) & Die ('$ dbname must be a string value ');
// Step 1: select the database:
Mysql_select_db ($ dbname );
// Step 2: Create a database backup directory
$ Dbdir = $ this-> _ datadir. directory_separator. $ dbname;
! Is_dir ($ dbdir) & mkdir ($ dbdir );
// Step 3: get the names of all tables in the database and start the backup table.
$ This-> _ tablebak = new tablebak ($ this-> _ mysql_link_id, $ dbdir );
If (is_null ($ tablename) {// backup all table in the DB
$ This-> _ backupalltable ($ dbname );
Return;
}
If (is_string ($ tablename )){
(Strlen ($ tablename) = 0) & Die ('....');
$ This-> _ backuponetable ($ dbname, $ tablename );
Return;
}
If (is_array ($ tablename )){
Foreach ($ tablename as $ table ){
((! Is_string ($ table) | strlen ($ table) = 0) & Die ('....');
}
$ This-> _ backupsometalbe ($ dbname, $ tablename );
Return;
}
}

Function restoredb ($ dbname, $ tablename = NULL ){
((! Is_string ($ dbname) | strlen ($ dbname) = 0) & Die ('$ dbname must be a string value ');
// Step 1: Check whether a database exists and connect to it:
@ Mysql_select_db ($ dbname) | die ("the database <B> $ dbname </B> dose not exists ");
// Step 2: Check whether the Database Backup directory exists
$ Dbdir = $ this-> _ datadir. directory_separator. $ dbname;
! Is_dir ($ dbdir) & Die ("$ dbdir not exists ");
// Step 3: Start restore
$ This-> _ tablebak = new tablebak ($ this-> _ mysql_link_id, $ dbdir );
If (is_null ($ tablename) {// backup all table in the DB
$ This-> _ restorealltable ($ dbname );
Return;
}
If (is_string ($ tablename )){
(Strlen ($ tablename) = 0) & Die ('....');
$ This-> _ restoreonetable ($ dbname, $ tablename );
Return;
}
If (is_array ($ tablename )){
Foreach ($ tablename as $ table ){
((! Is_string ($ table) | strlen ($ table) = 0) & Die ('....');
}
$ This-> _ restoresometalbe ($ dbname, $ tablename );
Return;
}
}

Function _ gettablelist ($ dbname)
{
$ Tablelist = array ();
$ Result = mysql_list_tables ($ dbname, $ this-> _ mysql_link_id );
For ($ I = 0; $ I <mysql_num_rows ($ result); $ I ++ ){
Array_push ($ tablelist, mysql_tablename ($ result, $ I ));
}
Mysql_free_result ($ result );
Return $ tablelist;
}

Function _ backupalltable ($ dbname)
{
Foreach ($ this-> _ gettablelist ($ dbname) as $ tablename ){
$ This-> _ tablebak-> backuptable ($ tablename );
}
}

Function _ backuponetable ($ dbname, $ tablename)
{
! In_array ($ tablename, $ this-> _ gettablelist ($ dbname )) & Die ("the specified table name <B> $ tablename </B> does not exist in the Database ");
$ This-> _ tablebak-> backuptable ($ tablename );
}

function _ backupsometalbe ($ dbname, $ tablenamelist)
{< br> foreach ($ tablenamelist as $ tablename) {
! In_array ($ tablename, $ this-> _ gettablelist ($ dbname )) & Die ("the specified table name $ tablename does not exist in the Database");
}< br> foreach ($ tablenamelist as $ tablename) {
$ this-> _ tablebak-> backuptable ($ tablename);
}< BR >}

Function _ restorealltable ($ dbname)
{
// Step 1: Check whether all data table backup files exist and whether they can be written:
Foreach ($ this-> _ gettablelist ($ dbname) as $ tablename ){
$ Tablebakfile = $ this-> _ datadir. directory_separator
. $ Dbname. directory_separator
. $ Tablename. directory_separator
. $ Tablename. '. SQL ';
! Is_writeable ($ tablebakfile) & Die ("$ tablebakfile not exists or unwirteable ");
}
// Step 2: Start restore
Foreach ($ this-> _ gettablelist ($ dbname) as $ tablename ){
$ Tablebakfile = $ this-> _ datadir. directory_separator
. $ Dbname. directory_separator
. $ Tablename. directory_separator
. $ Tablename. '. SQL ';
$ This-> _ tablebak-> restoretable ($ tablename, $ tablebakfile );
}
}

Function _ restoreonetable ($ dbname, $ tablename)
{
// Step 1: Check whether a data table exists:
! In_array ($ tablename, $ this-> _ gettablelist ($ dbname )) & Die ("the specified table name <B> $ tablename </B> does not exist in the Database ");
// Step 2: Check whether the data table backup file exists and whether it is writable:
$ Tablebakfile = $ this-> _ datadir. directory_separator
. $ Dbname. directory_separator
. $ Tablename. directory_separator
. $ Tablename. '. SQL ';
! Is_writeable ($ tablebakfile) & Die ("$ tablebakfile not exists or unwirteable ");
// Step 3: Start restore
$ This-> _ tablebak-> restoretable ($ tablename, $ tablebakfile );
}
Function _ restoresometalbe ($ dbname, $ tablenamelist)
{
// Step 1: Check whether a data table exists:
Foreach ($ tablenamelist as $ tablename ){
! In_array ($ tablename, $ this-> _ gettablelist ($ dbname )) & Die ("the specified table name <B> $ tablename </B> does not exist in the Database ");
}
// Step 2: Check whether the data table backup file exists and whether it is writable:
Foreach ($ tablenamelist as $ tablename ){
$ Tablebakfile = $ this-> _ datadir. directory_separator
. $ Dbname. directory_separator
. $ Tablename. directory_separator
. $ Tablename. '. SQL ';
! Is_writeable ($ tablebakfile) & Die ("$ tablebakfile not exists or unwirteable ");
}
// Step 3: Start restore:
Foreach ($ tablenamelist as $ tablename ){
$ Tablebakfile = $ this-> _ datadir. directory_separator
. $ Dbname. directory_separator
. $ Tablename. directory_separator
. $ Tablename. '. SQL ';
$ This-> _ tablebak-> restoretable ($ tablename, $ tablebakfile );
}
}
}
?> Copy code The Code is as follows: <? PHP
// Only dbbak can call this class
Class tablebak {
VaR $ _ mysql_link_id;
VaR $ _ dbdir;
// Private $ _ dbmanager;
Function tablebak ($ mysql_link_id, $ dbdir)
{
$ This-> _ mysql_link_id = $ mysql_link_id;
$ This-> _ dbdir = $ dbdir;
}

Function backuptable ($ tablename)
{
// Step 1: name of the Backup Directory for creating the table:
$ Tabledir = $ this-> _ dbdir. directory_separator. $ tablename;
! Is_dir ($ tabledir) & mkdir ($ tabledir );
// Step 2: Start backup:
$ This-> _ backuptable ($ tablename, $ tabledir );
}

Function restoretable ($ tablename, $ tablebakfile)
{
Set_time_limit (0 );
$ Filearray = @ file ($ tablebakfile) or die ("can open file $ tablebakfile ");
$ Num = count ($ filearray );
Mysql_unbuffered_query ("delete from $ tablename ");
$ SQL = $ filearray [0];
For ($ I = 1; $ I <$ num-1; $ I ++ ){
Mysql_unbuffered_query ($ SQL. $ filearray [$ I]) or (die (mysql_error ()));
}
Return true;
}

Function _ getfieldinfo ($ tablename ){
$ Fieldinfo = array ();
$ SQL = "select * from $ tablename limit 1 ";
$ Result = mysql_query ($ SQL, $ this-> _ mysql_link_id );
$ Num_field = mysql_num_fields ($ result );
For ($ I = 0; $ I <$ num_field; $ I ++ ){
$ Field_name = mysql_field_name ($ result, $ I );
$ Field_type = mysql_field_type ($ result, $ I );
$ Fieldinfo [$ field_name] = $ field_type;
}
Mysql_free_result ($ result );
Return $ fieldinfo;
}
Function _ quoterow ($ fieldinfo, $ row ){
Foreach ($ row as $ field_name => $ field_value ){
$ Field_value = strval ($ field_value );
Switch ($ fieldinfo [$ field_name]) {
Case "blob": $ row [$ field_name] = "'". mysql_escape_string ($ field_value). "'"; break;
Case "string": $ row [$ field_name] = "'". mysql_escape_string ($ field_value). "'"; break;
Case "date": $ row [$ field_name] = "'". mysql_escape_string ($ field_value). "'"; break;
Case "datetime": $ row [$ field_name] = "'". mysql_escape_string ($ field_value). "'"; break;
Case "time": $ row [$ field_name] = "'". mysql_escape_string ($ field_value). "'"; break;
Case "unknown": $ row [$ field_name] = "'". mysql_escape_string ($ field_value). "'"; break;
Case "int": $ row [$ field_name] = intval ($ field_value); break;
Case "real": $ row [$ field_name] = intval ($ field_value); break;
Case "timestamp": $ row [$ field_name] = intval ($ field_value); break;
Default: $ row [$ field_name] = intval ($ field_value); break;
}
}
Return $ row;
}
Function _ backuptable ($ tablename, $ tabledir)
{
// Obtain the table field type:
$ Fieldinfo = $ this-> _ getfieldinfo ($ tablename );

// Step 1: Construct the First Half of the insert statement and write it to the file:
$ Fields = array_keys ($ fieldinfo );
$ Fields = implode (',', $ fields );
$ Sqltext = "insert into $ tablename ($ fields) values \ r \ n ";
$ Datafile = $ tabledir. directory_separator. $ tablename. '. SQL ';
(! $ Handle = fopen ($ datafile, 'w') & Die ("can not open file <B> $ datafile </B> ");
(! Fwrite ($ handle, $ sqltext) & Die ("can not write data to file <B> $ datafile </B> ");
Fclose ($ handle );

// Step 2: Get the data and write it to the file:
// Retrieve table resources:
Set_time_limit (0 );
$ SQL = "select * from $ tablename ";
$ Result = mysql_query ($ SQL, $ this-> _ mysql_link_id );
// Open the data backup file: $ tablename. xml
$ Datafile = $ tabledir. directory_separator. $ tablename. '. SQL ';
(! $ Handle = fopen ($ datafile, 'A') & Die ("can not open file <B> $ datafile </B> ");
// Obtain table records one by one and write them into files:
While ($ ROW = mysql_fetch_assoc ($ result )){
$ ROW = $ this-> _ quoterow ($ fieldinfo, $ row );
$ Record = '('. implode (',', $ row). "); \ r \ n ";
(! Fwrite ($ handle, $ record) & Die ("can not write data to file <B> $ datafile </B> ");
}
Mysql_free_result ($ result );
// Close the file:
Fclose ($ handle );

Return true;
}

}
?>

Back up the mybbs database:

SQL code
// Example 1 backup:
Require_once ('dbbak. php ');
Require_once ('tablebak. php ');
$ Connectid = mysql_connect ('localhost', 'root', '123 ');
$ Backupdir = 'data ';
$ Dbbak = new dbbak ($ connectid, $ backupdir );
$ Dbbak-> backupdb ('mybbs ');

Restore the mybbs database:

Copy code The Code is as follows: require_once ('dbbak. php ');
Require_once ('tablebak. php ');
$ Connectid = mysql_connect ('localhost', 'root', '123 ');
$ Backupdir = 'data ';
$ Dbbak = new dbbak ($ connectid, $ backupdir );
$ Dbbak-> restoredb ('mybbs ');