Periodically backing up database is crucial to data safety for DBAs or webmasters. But manually doing this is often boring and easily forgotten.
Below script works on Linux system to backup mysql database. With an easy-to-use webcron, you can setup periodical backup jobs which will happen automatically.
Steps to take:
1. Create an empty PHP file named with whatever you like [e.g. example.php].
2. Copy the following code into the PHP file.
3. Change the parameters [DB_HOST, DB_NAME, DB_USER, DB_PASSWORD, BACKUP_SAVE_TO] in the script to reflect the settings of your environment. You would like to keep the database backups from accessing by web visitor, so you may need to set a private path to BACKUP_SAVE_TO or put a .htaccess file with following content
Order deny,allow Deny from all
into the backup storage folder.
4. Put the PHP file on your server [If you want to use a webcron to trigger the auto backup, you need to make sure the file is accessible via HTTP]. If your backup storage folder is not yet existent, please create it. Your folder should be made writable by PHP.
5. You can both use Linux's Cron or a webcron to trigger to script. If you need a webcron, just go to EasyCron.com and register an account to proceed.
Advantages:
- Great performance. Because it calls MySQL's native command to perform the backup.
- Saves your disk space as backups are gzipped.
- Uses a way to keep both last 30 days' backups and each 1st day's backup of past months. It balances the data importance [through earlier to later time] and disk space cost.
Requirements:
- Linux [Though the script can be amend to work on other platforms].
- PHP + MySQL
by Vincy. Last modified on July 8th, 2022.
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.
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.
Save and Download Database Backup File
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,
Download
↑ Back to Top