But there is a problem:
First, the user who runs php is an apche user. For example, if it is a nobody, it generally has no permission to access the/usr/local/mysql/data directory.
Second, even if you can access it, how can you copy the files in the/usr/local/mysql/data directory? Because mysql does not run access when it is running, the nobody user has the permission to stop mysql services, which is impossible!
The more I thought about the problem, the more I couldn't find a way to see if I could start to operate the database in php. So I checked 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.
/****** Back up the 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 ";
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 ";
$ 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 implementation operations ******/
Well, since we have written all the code, how can we implement backup in a specific program? Let's look at the following code.
/* Back up the database */
// Note: The following database operations use the phplib DB class.
// Define the data table to be saved, the prefix, and where to save the data table
$ 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://www.jb51.net/article/17423.htm
// 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'], 'msi ')? 'Application/etetstream': 'application/octet-stream '));
Header ('content-Disposition: '. (strpos ($ HTTP_SERVER_VARS ['HTTP _ USER_AGENT'], 'msi ')? 'Inline; ': 'attachment;'). 'filename = "'. $ local_filename );
Header ('content-Length: '. strlen ($ sqldump ));
Header ('pragma: no-cache ');
Header ('expires: 0 ');
Echo $ sqldump;
}
// Save to local end
// Save it to the 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 the backup database */
Well, this is basically the end of the process. One of the problems involved is how to recover data to the database. I think this is not complicated, however, it is better to restore data from the client and the server.