Skip to main content

Simple Database Backup System using PHP with Source Code

Language

Hi Guys! I have here a PHP Source Code that you might need for your Web Application Developments. This is a simple Database Backup System for your web applications. This Simple Web Application has a simple form where you will input all necessary information need from your database including the database you wanted to back up. The purpose of this article is to help you guys especially those beginners in developing a Web Application using PHP Language.

The code that I have created for this article has a simple user-interface that has a single form for dynamically configuring/input the database credential of your wanted database to be backed up. The backup file will be automatically downloaded as an SQL file. The Code that I have used for the backup database function is the script below.

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

The backDb above will be the function that will call when backing up a database.

Example

Assuming that the script above is written inside the "db_bup_function.php" file.

  1. <?php
  2.  
  3. // include first the function in your other file
  4. include "db_bup_function.php";
  5.  
  6. //Database Credentials Vairables
  7. $servername = "localhost";
  8. $username = "root";
  9. $password = "MyDbPassword";
  10. $db_name = "MyDB";
  11.  
  12. // Initiating the backup database function
  13. backDb($servername, $username, $password, $db_name);
  14. ?>

The above example will backup all the tables in your given database. And to backup only a selected tables you can use the scripts below.

Backup a single table only
  1. <?php
  2. backDb($servername, $username, $password, $db_name,'table1');
  3.  
  4. ?>
Backup a selected multiple tables
  1. <?php
  2. $tableNames = array('table1','table2','table3','table4');
  3. backDb($servername, $username, $password, $db_name,);
  4. ?>

To try the source code provided follow the instruction below.

How to Run

Requirements:
  • Download and Install any local web server that can run PHP script such as XAMPP/WAMP.
  • Download the provided source code zip file. (download button is located below)
Setup
  1. Open your XAMPP/WAMP's Control Panel and start the "Apache" and "MySQL".
  2. Extract the downloaded source code zip file.
  3. If you are using XAMPP, copy the extracted folder and paste it into the XAMPP's "htdocs" directory. And if you are using WAMP, paste it into the "www" directory.
  4. Browse the PHPMyAdmin in a browser. http://localhost/phpmyadmin
  5. Create a sample database and create a sample Table/s with data in it.
  6. Browse the Backup My Database Web App in your browser. i.e. http://localhost/bmd
  7. Enter your database credentials in the given form and click the "Initiate" button to Backup.
DEMO

I hope this simple source code will help you with what you are looking for and also for your future PHP Projects. Feel free to modify the source code in the way you wanted.

Enjoy :)

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.

Comments

Submitted bypupa (not verified)on Fri, 03/01/2019 - 11:33

great job and keep the good work

Add new comment