How to Backup Database in PHP and MySQL

How to Backup Database in PHP and MySQL Free Source code and Tutorial

Introduction

Backups are important for several reasons. First, if you accidentally delete a file or make changes that you later regret, you can always restore a backup to get your original file back. Second, if your computer crashes or becomes infected with malware, you can use a backup to restore your files and get your computer running again. Third, if you need to share files with someone else, you can use a backup to ensure that the other person gets the same file as you did. Finally, backups are a way to protect yourself from data loss if your computer crashes or something happens to your hard drive. Always make sure that you have at least one backup of all your important files.

This article will guide you through the development of backup script in PHP and MySQL. Let’s get started with the development.

Objectives

By the end of this tutorial, you will be able to:

1. Create a PHP script that connects to MySQL database and select the database we want to backup.
2. Save and download the database to our backup file.
3. Create a front-end in Bootstrap where users can backup the database by just clicking the Backup button.
4. To integrate and apply the source code in your projects.

Relevant Source code

You need the following tools for creating and following this tutorial:

• XAMPP
• Text editor (VS Code, Sublime, Brackets), download and install a text editor of your choice

1. The image below displays the folder and file components of the project.

• assets – this includes the bootstrap files such as bootstrap.css, js files and others.
• php – this is the PHP file that contains the connection to our database and creating a backup script.
• sql – this is the sample backup file.
• php – this is the file where the front-end code in bootstrap for this tutorial is written.

For this tutorial the database we want to backup is the city database which was previously used in the tutorial on Export database record into csv file in PHP and MySQL.

2. Let us continue by create a file and name it as backup.php.

Code of backup

// Database configuration
$host = "localhost";
$username = "root";
$password = "";
$database_name = "city";

Line 4-7 – this is the database configuration. Please take note that to replace database name into the database you want to create a backup. As mentioned, we will use the city database for this tutorial.

$tables = array();
$sql = "SHOW TABLES";
$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_row($result)) {
$tables[] = $row[0];
}

Line 14-20 – After we have established the connection, we will now get all of the tables in our database. SHOW TABLES sql command is used to display all of the tables in a database.

$sqlScript = "";
foreach ($tables as $table) { 
// Prepare SQLscript for creating table structure
$query = "SHOW CREATE TABLE $table";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_row($result);

$sqlScript .= "\n\n" . $row[1] . ";\n\n";

$query = "SELECT * FROM $table";
$result = mysqli_query($conn, $query);

$columnCount = mysqli_num_fields($result); 
// Prepare SQLscript for dumping data for each table
for ($i = 0; $i < $columnCount; $i ++) {
while ($row = mysqli_fetch_row($result)) {
$sqlScript .= "INSERT INTO $table VALUES(";
for ($j = 0; $j < $columnCount; $j ++) {
$row[$j] = $row[$j];

if (isset($row[$j])) {
$sqlScript .= '"' . $row[$j] . '"';
} else {
$sqlScript .= '""';
}
if ($j < ($columnCount - 1)) {
$sqlScript .= ',';
}
}
$sqlScript .= ");\n";
}
}
$sqlScript .= "\n"; 
}

Line 22-55 – this is the scope of the source code where we will create the table structure and dump the data into it. SHOW CREATE TABLE statement shows/displays the statement used to create the specified table. This displays the create statements along with the clauses.

if(!empty($sqlScript))
{
// Save the SQL script to a backup file
$backup_file_name = $database_name . '_backup_' . time() . '.sql';
$fileHandler = fopen($backup_file_name, 'w+');
$number_of_lines = fwrite($fileHandler, $sqlScript);
fclose($fileHandler);

// Download the SQL backup file to the browser
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); 
}

Line 57-78 – this is the script for saving the sql script to our backup file and allow us to download the backup file.

Front-end

It is time now to create our front-end in Bootstrap. Create a file and name it as index.php. This will allow us to execute the script we have created in the backup.php by just clicking the Backup Database button.

<!DOCTYPE html>
<html>

<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, shrink-to-fit=no">
<title>Backup Database in PHP</title>
<link rel="stylesheet" href="assets/bootstrap/css/bootstrap.min.css">
</head>

<body>
<div class="row">
<div class="col-md-8 offset-md-2">
<div class="card">
<div class="card-header bg">
<h1>Backup Database in PHP</h1>
</div>
<div class="card-body">
<a href="backup.php" class="btn btn-success">Backup Database</a>
</div>
</div>
</div>
</div>
<script src="assets/js/jquery.min.js"></script>
<script src="assets/bootstrap/js/bootstrap.min.js"></script>
</body>
</html>

Video Demo

FREE DOWNLOAD SOURCE CODE

FREE DOWNLOAD PDF TUTORIAL

Summary

It is important to have a database backup feature in your system for a number of reasons. First, if something goes wrong with your system, you will be able to restore your data from a backup. Second, if you make changes to your data that you later regret, you will be able to revert to a previous version of your database. Finally, a database backup will help you measure the performance of your system over time. Having a history of your system’s performance will help you make better decisions about how to improve it. Overall, having a database backup feature is an essential part of any system.

In this article, you can download the source code and as well as the database used in creating the backup database tutorial.

We hope you found this tutorial to be helpful! Wishing you the best of luck with your projects! Happy Coding!

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive  free capstone projects resources and computer programming tutorials.

Hire our team to do the project.