Heim >Backend-Entwicklung >PHP-Tutorial >备份mysql数据的php代码

备份mysql数据的php代码

WBOY
WBOYOriginal
2016-07-25 08:45:50876Durchsuche
  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{
  81. $return .= '"'.$rowdata[$i] . "\"," ;
  82. }
  83. // Let's remove the last comma
  84. $return = substr("$return", 0, -1) ;
  85. $return .= ");" ."\n" ;
  86. }
  87. $return .= "\n\n" ;
  88. }
  89. // Close the connection
  90. $mysqli->close() ;
  91. $return .= 'SET FOREIGN_KEY_CHECKS = 1 ; ' . "\n" ;
  92. $return .= 'COMMIT ; ' . "\n" ;
  93. $return .= 'SET AUTOCOMMIT = 1 ; ' . "\n" ;
  94. //$file = file_put_contents($fileName , $return) ;
  95. $zip = new ZipArchive() ;
  96. $resOpen = $zip->open(BACKUP_DIR . '/' .$fileName.".zip" , ZIPARCHIVE::CREATE) ;
  97. if( $resOpen ){
  98. $zip->addFromString( $fileName , "$return" ) ;
  99. }
  100. $zip->close() ;
  101. $fileSize = get_file_size_unit(filesize(BACKUP_DIR . "/". $fileName . '.zip')) ;
  102. $message =

    BACKUP completed ,


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

mysql, php


Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel:php读取cookie示例代码 Nächster Artikel:简单的php抽奖代码