Two methods of backup/recovery in MySQL database _ MySQL

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.