备份mysql数据的php代码

2. #####################
3. //CONFIGURATIONS
4. #####################
5. // Define the name of the backup directory
6. define('BACKUP_DIR', './myBackups' ) ;
7. // Define Database Credentials
8. define('HOST', 'localhost' ) ;
9. define('USER', 'testd!b' ) ;
10. define('PASSWORD', 'k^$2y4n9@#VV' ) ;
11. define('DB_NAME', 'test123' ) ;
12. /*
13. Define the filename for the sql file
14. If you plan to upload the file to Amazon's S3 service , use only lower-case letters
15. */
16. $fileName = 'mysqlbackup--' . date('d-m-Y') . '@'.date('h.i.s').'.sql' ;
17. // Set execution time limit
18. if(function_exists('max_execution_time')) {
19. if( ini_get('max_execution_time') > 0 ) set_time_limit(0) ;
20. }
22. ###########################
24. //END OF CONFIGURATIONS
26. ###########################
28. // Check if directory is already created and has the proper permissions
29. if (!file_exists(BACKUP_DIR)) mkdir(BACKUP_DIR , 0700) ;
30. if (!is_writable(BACKUP_DIR)) chmod(BACKUP_DIR , 0700) ;
32. // Create an ".htaccess" file , it will restrict direct accss to the backup-directory .
33. $content = 'deny from all' ;
34. $file = new SplFileObject(BACKUP_DIR . '/.htaccess', "w") ;
35. $file->fwrite($content) ;
37. $mysqli = new mysqli(HOST , USER , PASSWORD , DB_NAME) ;
38. if (mysqli_connect_errno())
39. {
40. printf("Connect failed: %s", mysqli_connect_error());
41. exit();
42. }
43. // Introduction information
44. $return .= "--\n";
45. $return .= "-- A Mysql Backup System \n";
46. $return .= "--\n";
47. $return .= '-- Export created: ' . date("Y/m/d") . ' on ' . date("h:i") . "\n\n\n";
48. $return = "--\n";
49. $return .= "-- Database : " . DB_NAME . "\n";
50. $return .= "--\n";
51. $return .= "-- --------------------------------------------------\n";
52. $return .= "-- ---------------------------------------------------\n";
53. $return .= 'SET AUTOCOMMIT = 0 ;' ."\n" ;
54. $return .= 'SET FOREIGN_KEY_CHECKS=0 ;' ."\n" ;
55. $tables = array() ;
56. // Exploring what tables this database has
57. $result = $mysqli->query('SHOW TABLES' ) ;
58. // Cycle through "$result" and put content into an array
59. while ($row = $result->fetch_row())
60. {
61. $tables[] = $row[0] ;
62. }
63. // Cycle through each table
64. foreach($tables as $table)
65. {
66. // Get content of each table
67. $result = $mysqli->query('SELECT * FROM '. $table) ;
68. // Get number of fields (columns) of each table
69. $num_fields = $mysqli->field_count ;
70. // Add table information
71. $return .= "--\n" ;
72. $return .= '-- Tabel structure for table `' . $table . '`' . "\n" ;
73. $return .= "--\n" ;
74. $return.= 'DROP TABLE IF EXISTS `'.$table.'`;' . "\n" ;
75. // Get the table-shema
76. $shema = $mysqli->query('SHOW CREATE TABLE '.$table) ;
77. // Extract table shema
78. $tableshema = $shema->fetch_row() ;
79. // Append table-shema into code
80. $return.= $tableshema[1].";" . "\n\n" ;
81. // Cycle through each table-row
82. while($rowdata = $result->fetch_row())
83. {
84. // Prepare code that will insert data into table
85. $return .= 'INSERT INTO `'.$table .'` VALUES ( ' ;
86. // Extract data of each row
87. for($i=0; $i{
88. $return .= '"'.$rowdata[$i] . "\"," ;
89. }
90. // Let's remove the last comma
91. $return = substr("$return", 0, -1) ;
92. $return .= ");" ."\n" ;
93. }
94. $return .= "\n\n" ;
95. }
96. // Close the connection
97. $mysqli->close() ;
98. $return .= 'SET FOREIGN_KEY_CHECKS = 1 ; ' . "\n" ;
99. $return .= 'COMMIT ; ' . "\n" ;
100. $return .= 'SET AUTOCOMMIT = 1 ; ' . "\n" ;
101. //$file = file_put_contents($fileName , $return) ;
102. $zip = new ZipArchive() ;
103. $resOpen = $zip->open(BACKUP_DIR . '/' .$fileName.".zip" , ZIPARCHIVE::CREATE) ;
104. if( $resOpen ){
105. $zip->addFromString( $fileName , "$return" ) ;
106. }
107. $zip->close() ;
108. $fileSize = get_file_size_unit(filesize(BACKUP_DIR . "/". $fileName . '.zip')) ;
109. $message =

     BACKUP completed ,

     
     
110. the archive has the name of : $fileName and it's file-size is : $fileSize .
     
111. This zip archive can't be accessed via a web browser , as it's stored into a protected directory .
     
112. It's highly recomended to transfer this backup to another filesystem , use your favorite FTP client to download the archieve .
113. msg;
114. echo $message ;
116. // Function to append proper Unit after file-size .
117. function get_file_size_unit($file_size){
118. switch (true) {
119. case ($file_size/1024 return intval($file_size ) ." Bytes" ;
120. break;
121. case ($file_size/1024 >= 1 && $file_size/(1024*1024) return intval($file_size/1024) ." KB" ;
122. break;
123. default:
124. return intval($file_size/(1024*1024)) ." MB" ;
125. }
126. }

复制代码

mysql, php