Code to back up MySQL data via PHP

Source: Internet
Author: User
Tags ziparchive ftp client mysql backup
  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'll 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 have
  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";
  74. Cycle through each Table-row
  75. while ($rowdata = $result->fetch_row ())
  76. {
  77. Prepare code that would 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";
  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 = <<<>
  104. BACKUP completed,


  105. The archive has the name of: $fileName and it's file-size is: $fileSize.
  106. This zip archive can ' t is accessed via a Web browser, as it's stored into a protected directory.
  107. It ' s highly recomended to transfer this backup to another filesystem, use your favorite FTP client to download the Archie Ve.
  108. Msg
  109. Echo $message;
  110. Function to append proper Unit after file-size.
  111. function Get_file_size_unit ($file _size) {
  112. Switch (TRUE) {
  113. Case ($file _size/1024 < 1):
  114. Return Intval ($file _size). " Bytes ";
  115. Break
  116. Case ($file _size/1024 >= 1 && $file _size/(1024*1024) < 1):
  117. Return Intval ($file _size/1024). " KB ";
  118. Break
  119. Default
  120. Return Intval ($file _size/(1024*1024)). " MB ";
  121. }
  122. }
Copy Code
PHP, MySQL
  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.