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('DB_HOST', 'your_mysql_host');

define('DB_USER', 'your_mysql_username');

define('DB_PASS', 'your_mysql_password');

define('DB_NAME', 'your_database_name');

Define the Backup directory, where the backup files will be stored:

define('BACKUP_DIR', '/path/to/your/backup/directory');

Set the date format for the name of backup file:

$date = date('Y-m-d_H-i-s');

Define the “backup_file”:

$backup_file = BACKUP_DIR . '/' . DB_NAME . '-' . $date . '.sql';

To create a backup file use the mysqldump utility, and provide the database credentials:

$command = "mysqldump --user=".DB_USER." --password=".DB_PASS." ".DB_NAME." > ".$backup_file;

system($command);

Compress the backup file using the “gzip” tool:

$gzip_command = "gzip ".$backup_file;

system($gzip_command);

Type this piece of code to remove the old backup files, for this post the files that are “7” days old will be deleted:

$find_command = "find ".BACKUP_DIR." -type f -name '*.gz' -mtime +7 -delete";

system($find_command);

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:

php .\db_backup.php

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.

How to Automatically Backup MySQL Database Using PHP

Automatically Backup MySQL Database Using PHP

Conclusion

About the author

Abdul Mannan