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:
Create a PHP script that connects to MySQL database and select the database we want to backup.
Save and download the database to our backup file.
Create a front-end in Bootstrap where users can backup the database by just clicking the Backup button.
To integrate and apply the source code in your projects.
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.
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.
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.
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.
Line 57-78 – this is the script for saving the sql script to our backup file and allow us to download the backup file.
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.
Backup Database in PHP
Backup Database
Video Demo
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.
How to Automatically Backup MySQL Database Using PHP
MySQL is an open-source RDBMS that can efficiently create, manage and store huge volumes of data in a structured way. To ensure the security of the data, backing up the database is an important task. MySQL supports many languages, so to create a backup of a MySQL database, the user can write the script in PHP, which is a powerful scripting language.
This article will discuss how to create a backup of a MySQL database using PHP and automate the process of backup.
Automatically Backup MySQL Database Using PHP
To write PHP code, open any code editor. For this post, “Visual Studio Code” is being used:
Create a PHP file named “db_backup.php”:
Type this code and provide your MySQL database credentials:
Define the Backup directory, where the backup files will be stored:
Set the date format for the name of backup file:
Define the “backup_file”:
To create a backup file use the mysqldump utility, and provide the database credentials:
Save the file, and run it to confirm if the backup file is created or not. Open the code editor terminal and type the command to execute the file:
List the directory to see if the backup file exists. The output displays the backup file that is created successfully:
To automate the process of backup, open the start menu, search “Task Scheduler” and click on the “Open” button:
From the “Actions” and press the “Create Task” option:
A new wizard will open. Head into the “General” tab and provide the task’s name. Select the option that ensures the backup even if the user is logged out:
Navigate to the “Triggers” tab and press the “New” button:
Select the option “On a schedule”. Select the scheduled time as “Daily” and adjust the “Start” time. In the Advanced settings check the “Enabled” option and press the “OK” button:
The status will change to “Enabled”:
Select the “Actions” tab and click on the “New” button:
Type the name for “Action” and browse for the “Program/script” PHP file you created and “Add arguments” and click on “OK”:
The action will be created successfully:
Go to the “Conditions” tab and check the checkbox “Wake the computer to rub this task”:
In the “Settings” tab and select the options as displayed in the output and adjust the time for the restart and click on the “OK” button:
The prompt will appear, type the credentials and click on the “OK” button:
Your MySQL database will automatically backup. Check the backup directory to see the backup files when needed:
You have successfully created the PHP file to backup MySQL database, then to automate the backup process you used the Task Scheduler.
Conclusion
Use the code editor to create a PHP file, provide the MySQL database credentials, and backup file name format and date format. Use the mysqldump command to create an SQL file and gzip tool to compress the SQL file. Use the Task Scheduler for the automation of the backup process. This post demonstrated how to automatically backup a MySQL database using PHP.
About the author
Abdul Mannan
I am curious about technology and writing and exploring it is my passion. I am interested in learning new skills and improving my knowledge and I hold a bachelor’s degree in computer science.
Dynamically creating database backup via program will be hand in many a situations. We can schedule the backup process periodically by using CRON jobs.
Taking the database backup using PHP programming is easy. In this tutorial, we are going to learn how to backup MySQL database with a simple core PHP code.
First, I get the database connection object to get the table schema and data to be dumped into a file. I read all the table names from the database and store into an array.
Then, I iterate the array of database tables to prepare the SQL script for the data/structure. This SQL script will be written into a file which will be downloaded to the user’s browser and removed from the target.
Getting Database Table Names
The code shows how to get the database connection and set the default character set before executing queries. Since we are dumping the database structure and the data, we need to be careful about the consistency.
By setting the default character set, it tells the database server about the character encoding.
The SHOW TABLES statement is used to fetch the table names. The table names are stored in an array which will be iterated to prepare the backup SQL script for the database structure and the data.
set_charset("utf8"); // Get All Table Names From the Database $tables = array(); $sql = "SHOW TABLES"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_row($result)) < $tables[] = $row[0]; >?>
Create SQL Script for Table Data/Structure
After fetching the list of the database table name in an array, I loop through this array to generate the SQL script. For each loop iteration, I have generated the SQL script for creating the table structure and dumping data into the table.
The SHOW CREATE TABLE statement is used to get the SQL for creating a table structure. Then, I get the column name and data to prepare the SQL for dumping data.
After preparing the SQL script for the database table and the structure, it will be written into a backup file which is created dynamically in the specified target.
Then, this file will be downloaded to the user’s browser and removed from the target location. The code for saving and downloading the database backup file is,
