Linuxphpmysql database backup implementation code. But there is a problem: first, the user running php is apche, for example, nobody, so it generally does not have the permission to access the usrlocalmysqldata Directory. second, even if it can be accessed, 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 $ filename. ");
}
}
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.
Example 1: If you are an apche user running php, such as nobody, you generally do not have the permission to access the/usr/local/mysql/data Directory, that...
