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

通过php备份mysql数据的代码

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

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

    BACKUP completed ,


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

php, mysql

人气教程排行