Home >Backend Development >PHP Tutorial >PHP code to backup mysql data

PHP code to backup mysql data

WBOY
WBOYOriginal
2016-07-25 08:45:50876browse
  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") . "nnn";
  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].";" . "nn" ;
  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 .= "nn" ;
  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. }
复制代码

mysql, php


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn