当前位置：Gxlcms > PHP教程 > 通过php备份mysql数据的代码

通过php备份mysql数据的代码

时间：2021-07-01 10:21:17 帮助过：34人阅读

  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<$num_fields; $i++)
  88. {
  89. $return .= '"'.$rowdata[$i] . "\"," ;
  90. }
  91. // Let's remove the last comma
  92. $return = substr("$return", 0, -1) ;
  93. $return .= ");" ."\n" ;
  94. }
  95. $return .= "\n\n" ;
  96. }
  97. // Close the connection
  98. $mysqli->close() ;
  99. $return .= 'SET FOREIGN_KEY_CHECKS = 1 ; ' . "\n" ;
  100. $return .= 'COMMIT ; ' . "\n" ;
  101. $return .= 'SET AUTOCOMMIT = 1 ; ' . "\n" ;
  102. //$file = file_put_contents($fileName , $return) ;
  103. $zip = new ZipArchive() ;
  104. $resOpen = $zip->open(BACKUP_DIR . '/' .$fileName.".zip" , ZIPARCHIVE::CREATE) ;
  105. if( $resOpen ){
  106. $zip->addFromString( $fileName , "$return" ) ;
  107. }
  108. $zip->close() ;
  109. $fileSize = get_file_size_unit(filesize(BACKUP_DIR . "/". $fileName . '.zip')) ;
  110. $message = <<

    BACKUP completed ,


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

php, mysql

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！