Skip to main content

How to Backup MySQL Database using PHP


Getting Started

In order to beautify the presentation of this tutorial, I've used Bootstrap which is included in the downloadable of this tutorial but if you want, you can download Bootstrap using this link.

Creating our Backup Function

Next, we create the function that backup and download our MySQL database.

Create a new file, name it as function.php and paste the codes below.

  1. <?php
  2.  
  3. function backDb($host, $user, $pass, $dbname, $tables = '*'){
  4. //make db connection
  5. $conn = new mysqli($host, $user, $pass, $dbname);
  6. if ($conn->connect_error) {
  7. die("Connection failed: " . $conn->connect_error);
  8. }
  9.  
  10. //get all of the tables
  11. if($tables == '*'){
  12. $tables = array();
  13. $sql = "SHOW TABLES";
  14. $query = $conn->query($sql);
  15. while($row = $query->fetch_row()){
  16. $tables[] = $row[0];
  17. }
  18. }
  19. else{
  20. $tables = is_array($tables) ? $tables : explode(',',$tables);
  21. }
  22.  
  23. //getting table structures
  24. $outsql = '';
  25. foreach ($tables as $table) {
  26.  
  27. // Prepare SQLscript for creating table structure
  28. $sql = "SHOW CREATE TABLE $table";
  29. $query = $conn->query($sql);
  30. $row = $query->fetch_row();
  31.  
  32. $outsql .= "\n\n" . $row[1] . ";\n\n";
  33.  
  34. $sql = "SELECT * FROM $table";
  35. $query = $conn->query($sql);
  36.  
  37. $columnCount = $query->field_count;
  38.  
  39. // Prepare SQLscript for dumping data for each table
  40. for ($i = 0; $i < $columnCount; $i ++) {
  41. while ($row = $query->fetch_row()) {
  42. $outsql .= "INSERT INTO $table VALUES(";
  43. for ($j = 0; $j < $columnCount; $j ++) {
  44. $row[$j] = $row[$j];
  45.  
  46. if (isset($row[$j])) {
  47. $outsql .= '"' . $row[$j] . '"';
  48. } else {
  49. $outsql .= '""';
  50. }
  51. if ($j < ($columnCount - 1)) {
  52. $outsql .= ',';
  53. }
  54. }
  55. $outsql .= ");\n";
  56. }
  57. }
  58.  
  59. $outsql .= "\n";
  60. }
  61.  
  62. // Save the SQL script to a backup file
  63. $backup_file_name = $dbname . '_backup.sql';
  64. $fileHandler = fopen($backup_file_name, 'w+');
  65. fwrite($fileHandler, $outsql);
  66. fclose($fileHandler);
  67.  
  68. // Download the SQL backup file to the browser
  69. header('Content-Description: File Transfer');
  70. header('Content-Type: application/octet-stream');
  71. header('Content-Disposition: attachment; filename=' . basename($backup_file_name));
  72. header('Content-Transfer-Encoding: binary');
  73. header('Expires: 0');
  74. header('Cache-Control: must-revalidate');
  75. header('Pragma: public');
  76. header('Content-Length: ' . filesize($backup_file_name));
  77. flush();
  78. readfile($backup_file_name);
  79. exec('rm ' . $backup_file_name);
  80.  
  81. }
  82.  
  83. ?>

Creating our Form

Next, for the sake of presentation, we are going to create a form where you can put your database credential and database name that you want to backup.

Create a new file, name it as index.html and paste the codes below.

  1. <!DOCTYPE html>
  2. <meta charset="utf-8">
  3. <title>How to Backup MySQL Database using PHP</title>
  4. <link rel="stylesheet" type="text/css" href="bootstrap4/css/bootstrap.min.css">
  5. </head>
  6. <div class="container">
  7. <h1 class="text-center" style="margin-top:30px;">Backup MySQL Database using PHP</h1>
  8. <hr>
  9. <div class="row justify-content-center">
  10. <div class="col-sm-6">
  11. <div class="card">
  12. <div class="card-body">
  13. <h3>Database Credential and Name</h3>
  14. <br>
  15. <form method="POST" action="backup.php">
  16. <div class="form-group row">
  17. <label for="server" class="col-sm-3 col-form-label">Server</label>
  18. <div class="col-sm-9">
  19. <input type="text" class="form-control" id="server" name="server" placeholder="ex 'localhost'" required>
  20. </div>
  21. </div>
  22. <div class="form-group row">
  23. <label for="username" class="col-sm-3 col-form-label">Username</label>
  24. <div class="col-sm-9">
  25. <input type="text" class="form-control" id="username" name="username" placeholder="ex 'root'" required>
  26. </div>
  27. </div>
  28. <div class="form-group row">
  29. <label for="password" class="col-sm-3 col-form-label">Password</label>
  30. <div class="col-sm-9">
  31. <input type="text" class="form-control" id="password" name="password" placeholder="db password">
  32. </div>
  33. </div>
  34. <div class="form-group row">
  35. <label for="dbname" class="col-sm-3 col-form-label">Database</label>
  36. <div class="col-sm-9">
  37. <input type="text" class="form-control" id="dbname" name="dbname" placeholder="database name to backup" required>
  38. </div>
  39. </div>
  40. <button type="submit" class="btn btn-primary" name="backup">Backup</button>
  41. </form>
  42. </div>
  43. </div>
  44. </div>
  45. </div>
  46. </div>
  47. </body>
  48. </html>

Creating our Submit Action

Lastly, we create the code that handles submission of our created form.

Create a new file, name it as backup.php and paste the codes below.

  1. <?php
  2. //include our function
  3. include 'function.php';
  4.  
  5. if(isset($_POST['backup'])){
  6. //get credentails via post
  7. $server = $_POST['server'];
  8. $username = $_POST['username'];
  9. $password = $_POST['password'];
  10. $dbname = $_POST['dbname'];
  11.  
  12. //backup and dl using our function
  13. backDb($server, $username, $password, $dbname);
  14.  
  15. exit();
  16.  
  17. }
  18. else{
  19. echo 'Fill up database credentail and name';
  20. }
  21.  
  22. ?>

That ends this tutorial. Happy Coding :)

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.