Are you looking to take database backups in your PHP projects? It is always recommended to back up your database periodically. By doing so, your site data will always be safe in case of any problem occurs to your database. In this article, we show you how to backup the MySQL database periodically and store it on your server.

This article is written for projects which are built-in core PHP and not with any CMS or Framework. The CMS like WordPress has several plugins available like UpdraftPlus for backing up the database. In Laravel websites, users can use packages like laravel-backup for the same purposes.

The Spatie team has developed a package that is useful for taking database backup in core PHP projects. This package provides support for MySQL, PostgreSQL, SQLite, and MongoDB. We will discuss the MySQL database in this article. That being said, let’s take a look at how this package works in backing up your database.

MySQL Database Backup using PHP

Before proceeding further make sure you have your database credentials and Composer is installed on your system.

Next, open the terminal in your project root directory and run the command below to install the package.

composer require spatie/db-dumper

Upon package installation, create a directory db-backups(you can give any name) where you can store your SQL backup files. Also, create a file backup-database.php and add the code below to it.

<?php
require_once "vendor/autoload.php";
 
Spatie\DbDumper\Databases\MySql::create()
    ->setDbName('DB_NAME')
    ->setUserName('DB_USER_NAME')
    ->setPassword('DB_PASSWORD')
    ->dumpToFile('db-backups/'.time().'_dump.sql');

Make sure to replace placeholders with the actual values. In the above code, we appended the current time to the SQL file to keep distinct file names. It will dump your database and store it in the given SQL file.

The package gives more options while dumping the database. For instance, you can exclude specific tables from the backup by providing the exclude tables array as below.

<?php
Spatie\DbDumper\Databases\MySql::create()
    ->setDbName('DB_NAME')
    ->setUserName('DB_USER_NAME')
    ->setPassword('DB_PASSWORD')
    ->excludeTables(['table1', 'table2', 'table3'])
    ->dumpToFile('db-backups/'.time().'_dump.sql');

Automatically Backup MySQL Database

So far, you have a PHP file that takes your database backup. The next thing that needs to do is automate the backup process so you will get your backups without executing the script manually.

When you hosted a site on the server, your hosting provider gives you cPanel where you can see the basic and advanced options for managing the website. One feature they provided is ‘Cron Jobs’. Cron jobs allow you to run a specific script(PHP file) at a certain interval in the background. All you need to do is set an interval and pass a directory path of the PHP file. After this, your server runs your script in the background at the specified intervals.

To set a Cron, login to your cPanel and click on the ‘Cron Jobs’ which you may find under the ‘advanced’ section.

On the next page under the ‘Add a New Cron Job’ section choose the ‘Once Per Day’ for the ‘Common Settings’ dropdown. It means your script will run once a day. The user can set any interval depending on the requirement.

In the command section pass the PHP file’s path as follow:

php /home/username/public_html/backup-database.php

Adjust the script path as per your hosting configurations and submit it. After this, your server starts executing a script daily at midnight. And this script will automatically store your MySQL database in the specified directory(e.g. db-backups).

We hope you understand how to make MySQL database backup in PHP and automate the process using Cron. We would like to hear your thoughts or suggestions in the comment section below.

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.