Two methods of backup/recovery in MySQL database _ MySQL

The following describes two methods for restoring MySQL database backup. Method 1 :? Php ** function: simple method for restoring data backup files *. one backup file is generated every day on the basis of the date. The last backup file on the current day prevails. * submit a form to perform the operation, * The following describes two methods for restoring MySQL database backup. Method 1: ___ FCKpd ___ 0 method 2 The following describes two methods for MySQL database backup/recovery.

Method 1:

The following describes two methods for backing up and recovering MySQL databases. Method 1: ___FCKpd___0 Method 2: I want to directly back up the database in the PHP background, so I have never thought of any idea. at the beginning, I was thinking about using php to access the directory where the server installed mysql, for example, in the/usr/local/mysql/data Directory, the following files are directly backed up, but a problem occurs: 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. 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: convert the table structure into SQL function parameters: $ table: return value of the table name to be extracted: return the extracted result. author of the SQL set function: 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: Convert the table structure and data into SQL function parameters: $ table: return value of the table name to be extracted: return the extracted result. author of the SQL set function: 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 ($ row S); 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, so how do we implement backup in a specific program? let's look at the following code. /* Back up the database * // note: the database operation uses the DB Class of phplib // defines 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 $ saveto = 'server'; // where to save the SQL file, whether it is local or on the server. the default value is server $ back_mode = 'all '; // whether to save all the backups or only save the database structure $ admin = 'heiyeluren'; // administrator name $ admin_email = 'heiyeluren @ 163.com '; // administrator email // defines the data storage file name $ local_filename = $ prefix. date ('ymd _ H Is ').'. 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 "; // The name of the file stored on the server // note the create_check_code () function, this is a function that generates random code // gets the database structure and data content foreach ($ tables as $ table) {if ($ back_mode = 'all') {$ sqldump. = data2sql ($ table);} if ($ back_mode = 'table') {$ sqldump. = table2sql ($ table) ;}// if the data content is not empty Start to save if (trim ($ sqldump) {// write the Start information $ sqldump = "# ------------------------------------------------------ \ n ". "# data table backup \ n ". "# \ n ". "# Server: $ db-> Host \ n ". "# Database: $ db-> Database \ n ". "# backup No :". create_sess_id (). "\ n ". // Here is a function for generating the session id "# backup time :". time_to_date ('', 6 ). "\ n ". // Here is the function for obtaining the current time "# \ n ". "# Administrator: $ admin ($ admin_email) \ n ". // the administrator's username and email address "# $ copyright \ n ". "#-------------------------------- ------------------------ \ N ". $ sqldump; // save it to the local if ($ saveto = "local") {ob_end_clean (); header ('content-Encoding: none '); header ('content-Type :'. (strpos ($ HTTP_SERVER_VARS ['http _ USER_AGENT '], 'msie ')? 'Application/etetstream': '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 to 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 (" The Directory you specified cannot be opened ". $ filename. ", please confirm 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 server end} else {exit_msg (" No content in the data table ");}/* end of 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. POST ["tbl_name"] is an array of the names of the prepared tables * The following describes two methods for backing up and recovering MySQL databases. Method 1: ___FCKpd___0 Method 2: I want to directly back up the database in the PHP background, so I have never thought of any idea. at the beginning, I was thinking about using php to access the directory where the server installed mysql, for example, in the/usr/local/mysql/data Directory, the following files are directly backed up, but a problem occurs: 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. 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. ___FCKpd___1 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. POST ["sqlfile"] is the name of the pre-recovery data file * Note: This backup does not have a structure backup, only data backup ** format of the backup file: * 'table name 1' {Data 1} 'table name 2' {Data 2}' table name 3' {DATA 3 }}... ** created AT: * E-mail: kingerq AT msn.com * Source: http://blog.csdn.net/kingerq */include (".. /inc/globals. inc. php "); // omit the package file db_mysql.inc and MYSQL connection information set_time_limit (0); $ dbdir =" d:/site/dbbak /"; // use the absolute path $ txtname = array (); if ( The following describes two methods for backing up and recovering MySQL databases. Method 1: ___FCKpd___0 Method 2: I want to directly back up the database in the PHP background, so I have never thought of any idea. at the beginning, I was thinking about using php to access the directory where the server installed mysql, for example, in the/usr/local/mysql/data Directory, the following files are directly backed up, but a problem occurs: 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. 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. ___FCKpd___1 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. POST) {if (! Is_writable ($ dbdir) {echo "Sorry! The specified backup directory cannot be written! Modify the permission "; exit;} // op as an invisible domain to identify backup or restore if ( The following describes two methods for backing up and recovering MySQL databases. Method 1: ___FCKpd___0 Method 2: I want to directly back up the database in the PHP background, so I have never thought of any idea. at the beginning, I was thinking about using php to access the directory where the server installed mysql, for example, in the/usr/local/mysql/data Directory, the following files are directly backed up, but a problem occurs: 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. 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. ___FCKpd___1 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. POST ["op"]) {// back up data // generate a temporary backup file for each table foreach ( The following describes two methods for backing up and recovering MySQL databases. Method 1: ___FCKpd___0 Method 2: I want to directly back up the database in the PHP background, so I have never thought of any idea. at the beginning, I was thinking about using php to access the directory where the server installed mysql, for example, in the/usr/local/mysql/data Directory, the following files are directly backed up, but a problem occurs: 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. 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. ___FCKpd___1 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. POST ["tbl_name"] as $ tbl) {$ txtname [] = $ tbl. ". txt "; $ SQL =" SELECT * FROM '$ tbl' INTO outfile '". $ dbdir. end ($ txtname ). "'"; $ db-> query ($ SQL) ;}// combine the generated temporary backup file $ outfile = date ("Y-m-d "). ". SQL "; if (file_exists ($ dbdir. $ outfile) @ unlink ($ dbdir. $ outfile); $ fpr = fopen ($ dbdir. $ outfile, "a"); foreach ($ txtname as $ txt) {if (file_exists ($ dbdir. $ txt) {// read the temporary backup file $ tdata = readfiles ($ dbdir. $ txt); // Generate a backup file $ t Bl = explode (". ", $ txt); $ str = ""'. $ tbl [0]. "'{{". $ tdata. "}}"; if (fwrite ($ fpr, $ str) {echo $ tbl [0]. "... $ outfile written successfully! \ N ";}else {echo $ tbl [0]."... failed to write $ outfile! \ N ";}@ unlink ($ dbdir. $ txt) ;}} fclose ($ fpr);} else {// restore data $ tdata = readfiles ($ dbdir. The following describes two methods for backing up and recovering MySQL databases. Method 1: ___FCKpd___0 Method 2: I want to directly back up the database in the PHP background, so I have never thought of any idea. at the beginning, I was thinking about using php to access the directory where the server installed mysql, for example, in the/usr/local/mysql/data Directory, the following files are directly backed up, but a problem occurs: 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. 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. ___FCKpd___1 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. POST ["sqlfile"]); preg_match_all ("/'(. *)'\{\{(. *) \}\}/isU ", $ tdata, $ data_ar); foreach ($ data_ar [1] as $ k => $ tt) {if (empty ($ data_ar [2] [$ k]) continue; $ tfile = $ dbdir. $ tt. ". txt "; $ fp = fopen ($ tfile," w "); if (fwrite ($ fp, $ data_ar [2] [$ k]) {// clear the TABLE $ SQL = "truncate table '$ tt"'; $ db-> query ($ SQL ); // reload DATA $ SQL = "LOAD DATA LOW_PRIORITY INFILE '". $ dbdir. $ tt. ". txt ". "'into table' $ tt" '; if ($ db-> query ($ sq L) {fclose ($ fp); echo $ tt. "The table has been restored successfully! \ N "; unlink ($ dbdir. $ tt.". txt ");} else {echo $ tt." table data recovery failed! \ N ";}}// echo $ tdata; // print_r ($ data_ar); // exit ;}} /** read file content * parameter $ file is the file name and complete path * returned file content */function readfiles ($ file) {$ tdata = ""; $ fp = fopen ($ file, "r"); if (filesize ($ file) <= 0) return; while ($ data = fread ($ fp, filesize ($ file) {$ tdata. = $ data ;}fclose ($ fp); return $ tdata ;}?>

Method 2:

I want to directly back up the database in the PHP background, so I have never thought of any idea. at the beginning, I was thinking about using php to access the directory where the server installed mysql, for example, in the/usr/local/mysql/data Directory, the following files are directly backed up, but a problem occurs:

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.

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.

___FCKpd___1

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.