MySQL Database Backup System using PHP

March 9, 2021
PHP
mysql database backup system

Project: MySQL Database Backup System using PHP with Source Code

About MySQL Database Backup System using PHP Project Free Download

This is an easy Database Backup System using PHP and MySQL for your web applications. The system features a simple form where you’ll input all necessary information need from your database including the database you wanted to copy . The aim of this system is to assist you especially those beginners in developing an internet Application using PHP Language.

The code that I even have created for this system features a simple user-interface that features a single form for dynamically configuring/input the database credential of your wanted database to be protected . The computer file are going to be automatically downloaded as an SQL file. The Code that I even have used for the backup database function is that the script below.

<?php
error_reporting(0);
function backDb($host, $user, $pass, $dbname, $tables = '*'){
 
	$conn = new mysqli($host, $user, $pass, $dbname);
	if ($conn->connect_error) {
	    die("Connection failed: " . $conn->connect_error);
	}
 
 
	if($tables == '*'){
		$tables = array();
		$sql = "SHOW TABLES";
		$query = $conn->query($sql);
		while($row = $query->fetch_row()){
			$tables[] = $row[0];
		}
	}
	else{
		$tables = is_array($tables) ? $tables : explode(',',$tables);
	}
 
 
	$outsql = '';
	foreach ($tables as $table) {
 
 
	    $sql = "SHOW CREATE TABLE $table";
	    $query = $conn->query($sql);
	    $row = $query->fetch_row();
 
	    $outsql .= "\n\n" . $row[1] . ";\n\n";
 
	    $sql = "SELECT * FROM $table";
	    $query = $conn->query($sql);
 
	    $columnCount = $query->field_count;
 
 
	    for ($i = 0; $i < $columnCount; $i ++) {
	        while ($row = $query->fetch_row()) {
	            $outsql .= "INSERT INTO $table VALUES(";
	            for ($j = 0; $j < $columnCount; $j ++) {
	                $row[$j] = $row[$j];
 
	                if (isset($row[$j])) {
	                    $outsql .= '"' . $row[$j] . '"';
	                } else {
	                    $outsql .= '""';
	                }
	                if ($j < ($columnCount - 1)) {
	                    $outsql .= ',';
	                }
	            }
	            $outsql .= ");\n";
	        }
	    }
 
	    $outsql .= "\n"; 
	}
 
 
    $backup_file_name = (time()).'_'.$dbname . '_database.sql';
    $fileHandler = fopen($backup_file_name, 'w+');
    fwrite($fileHandler, $outsql);
    fclose($fileHandler);
 
 
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename=' . basename($backup_file_name));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($backup_file_name));
    ob_clean();
    flush();
    readfile($backup_file_name);
    exec('rm ' . $backup_file_name);
 
}
 
?>

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.

<?php 
 
	// include first the function in your other file
	include "db_bup_function.php";
 
	//Database Credentials Vairables
	$servername = "localhost";
	$username = "root";
	$password = "MyDbPassword";
	$db_name = "MyDB";
 
	// Initiating the backup database function
	backDb($servername, $username, $password, $db_name);
?>

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

<?php
	backDb($servername, $username, $password, $db_name,'table1');
 
?>

Backup a selected multiple tables

<?php
	$tableNames = array('table1','table2','table3','table4');
	backDb($servername, $username, $password, $db_name,);
?>

Screenshot

database backup system

database backup system

How To Run??

Above all, to run this project you must have installed a virtual server i.e XAMPP on your PC. Database Backup System Project in PHP and MySQL with source code is free to download, Use for educational purposes only!

Follow the following steps after Starting Apache and MySQL in XAMPP:

  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


Related Projects/Tutorials: How to Update Image in PHP with Demo in MySQL Database, How to Create XML file from MySQL Database using PHP/MySQLi, Import Excel or CSV file to MySQL Database using PHP, Export MySQL Database into CSV File using PHP/MySQLi, Import SQL File to MySQL Database in PHP MySQL, Insert Data into MySQL Database using Vuejs in PHP, Fetch Data from MySQL Database using Vuejs in PHP

Download Here

Leave a Reply

Your email address will not be published. Required fields are marked *