How to Use PHP to implement MySQL backup?

The following article describes how to use PHP to implement MySQL backup. We all know that using PHP to implement MySQL backup is quite frequent, the following article describes how to use PHP to implement MySQL backup.

I have read phpMyadmin and Discuz! So I copied Discuz! The following method is used to back up the database. Thanks to Discuz! Developers)

There are two ways to back up the database: one is to back up the structure of the database, the other is to back up the structure and all the data, of course, the second method is good, however, I have done everything to consider possible needs.

Backup database structure

Function Name: table2sql ()

Function: converts the table structure to SQL

Function parameter: $ table: name of the table to be extracted

Return Value: return the extracted results, SQL set

Function Author: heiyeluren

 
 

  
  
function table2sql($table)   
  
  
{  
  
  
global $db;  
  
  
$tabledump = "DROP TABLE IF EXISTS $table;\n";  
  
  
$createtable = $db->query("SHOW CREATE TABLE $table");  
  
  
$create = $db->fetch_row($createtable);  
  
  
$tabledump .= $create[1].";\n\n";   
  
  
return $tabledump;  
  
  
}  
 
 

Back up the database structure and all data /*

Function Name: data2sql ()

Function: converts the table structure and data into SQL statements.

Function parameter: $ table: name of the table to be extracted

Return Value: return the extracted results, SQL set

Function Author: heiyeluren

 
 

  
  
function data2sql($table)   
  
  
{  
  
  
global $db;  
  
  
$tabledump = "DROP TABLE IF EXISTS $table;\n";  
  
  
$createtable = $db->query("SHOW CREATE TABLE $table");  
  
  
$create = $db->fetch_row($createtable);  
  
  
$tabledump .= $create[1].";\n\n";  
  
  
$rows = $db->query("SELECT * FROM $table");  
  
  
$numfields = $db->num_fields($rows);  
  
  
$numrows = $db->num_rows($rows);  
  
  
while ($row = $db->fetch_row($rows))  
  
  
{  
  
  
$comma = "";  
  
  
$tabledump .= "INSERT INTO $table VALUES(";  
  
  
for($i = 0; $i < $numfields; $i++)   
  
  
{  
  
  
$tabledump .= $comma."'".MySQL_escape_string($row[$i])."'";  
  
  
$comma = ",";  
  
  
}  
  
  
$tabledump .= ");\n";  
  
  
}  
  
  
$tabledump .= "\n";  
  
  
return $tabledump;  
  
  
}  
 
 

Specific MySQL operations

Well, since we have written all the code, how can we implement MySQL backup in a specific program? Let's look at the following code.

Back up database

Note: The following database operations use the phplib DB class.

Define the data table to be saved, the prefix, and where to save

$ Tables = array ('us _ sort ', 'us _ download', 'us _ article', 'us _ guestbook'); // defines the data table to be saved, an array

$ Prefix = 'us _ '; // prefix of the. SQL file to be saved

$ Saveto = 'server'; // Where to save it, whether it is local or on the server. The default value is server.

$ Back_mode = 'all'; // you can specify whether to back up all the data or only store the database structure.

$ Admin = 'heiyeluren'; // Administrator name

$ Admin_email = 'heiyeluren @ 163.com '; // administrator email

// Define the data storage file name

$ Local_filename = $ prefix. date ('ymd _ His ').'. SQL "';

If (! $ Filename) {$ filename = $ db_backup_path. $ prefix. date ('ymd _ His _ '). create_check_code (4). ". SQL ";}

$ Filename = $ prefix. date (Ymd_His). create_check _ code (6). ". SQL"; // file name saved on the server

// Pay attention to the create_check_code () function next to it. This is a function that generates random codes. For details, refer:

// Http://blog.csdn.net/heiyeshuwu/archive/2005/01/26/268446.aspx

// Obtain the database structure and data content

 
 

  
  
foreach($tables as $table)   
  
  
{  
  
  
if ($back_mode == 'all') { $sqldump .= data2sql($table); }  
  
  
if ($back_mode == 'table') { $sqldump .= table2sql($table); }  
  
  
} 
 
 

// Save the data if it is not empty

If (trim ($ sqldump ))

{

// Write the start information

$ Sqldump =

"# -------------------------------------------------------- \ N ".

"# Data table backup \ n ".

"# \ N ".

"# Server: $ db-> Host \ n ".

"# Database: $ db-> Database \ n ".

"# Backup number:". create_sess_id (). "\ n". // here is a function for generating session IDs.

"# Backup time:". time_to_date ('', 6)." \ n ". // This is the function for obtaining the current time.

"# \ N ".

"# Administrator: $ admin ($ admin_email) \ n". // administrator username and email address

"# $ Copyright \ n ".

"# -------------------------------------------------------- \ N ".

$ Sqldump;

Save to local

 
 

  
  
if($saveto == "local")   
  
  
{  
  
  
ob_end_clean();  
  
  
header('Content-Encoding: none');  
  
  
header('Content-Type: '.(strpos($HTTP_SERVER_VARS['HTTP_USER_AGENT'], 'MSIE') ? 
'application/octetstream' : 'application/octet-stream'));  
  
  
header('Content-Disposition: '.(strpos($HTTP_SERVER_VARS['HTTP_USER_AGENT'], 'MSIE') ? 
'inline; ' : 'attachment; ').'filename="'.$local_filename);  
  
  
header('Content-Length: '.strlen($sqldump));  
  
  
header('Pragma: no-cache');  
  
  
header('Expires: 0');  
  
  
echo $sqldump;  
  
  
}  
 
 

Save to local end

Save on server

 
 

  
  
if($saveto == "server")   
  
  
{  
  
  
if($filename != "")   
  
  
{  
  
  
@$fp = fopen($filename, "w+");  
  
  
if ($fp)  
  
  
{  
  
  
@flock($fp, 3);  
  
  
if(@!fwrite($fp, $sqldump))   
  
  
{  
  
  
@fclose($fp);  
 
 

Exit_msg ("the data file cannot be saved to the server. Check whether you have the write permission on the directory attribute. ");

}

Else

{

Exit_msg ("data is successfully backed up to the server <a href = \" $ filename \ "> $ filename </a>. ");

}

}

Else

{

Exit_msg ("unable to open the directory you specified". $ filename. ", check whether the directory exists or has the corresponding permission ");

}

}

Else

{

Exit_msg ("You have not entered the backup file name. Please return and modify it. ");

}

}

// Save to the end of the server

}

Else

{

Exit_msg ("the data table has no content ");

}

End of Database Backup

The above content is an introduction to implementing MySQL backup in PHP. I hope you will get some benefits.