Automate Database Backup with Email Notifications using PHP CRON Job

How to automate database backup in php?

In this tutorial I am going to provide quick guid on how to automate database backup using php script and send email notification on daily basis using cron job

If you have a server where your having a website along with the database and you want to take periodic database backup to make your server secure from preventing data loss, it is always best idea to set auto database backup and we can make it more better by adding email notification attached to it, so that when cron job going to execute our script to take database backup in to sql file we can write a script send email notification about database backup, follow below step to setup your cron job to execute the given php script

PHP Packages Used:

• spatie/db-dumper
• swiftmailer

Let’s get started by installing this php packages using composer, create new folder on your server for this tutorial and execute following command.

Install DB Dumper:

next cd into the newly created folder and execute following command to install db-dumper:

$ composer require spatie/db-dumper

DB-Dumper is php package created by spatie and it can be easily use to run the database backup and the advantages of using this package is it supports multiple database such as MySQL, PostgreSQL, SQLite and MongoDB, so it becomes easy to use and feasible for multiple database types, to learn more about this package you can simply visit Github repository: https://github.com/spatie/db-dumper

./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 0 installs, 1 update, 0 removals
  - Updating spatie/db-dumper (2.7.0 => 1.5.1): Downloading (100%)         
Writing lock file
Generating autoload files

If you see output similar to the above lines to instructions then you php package is installed successfully and now you will see composer.json , composer.lock files and `vendor` folder created.

Install swiftmailer:

Let’s install swiftmailer to send email notifications from the application.

$ composer require "swiftmailer/swiftmailer:^6.0"

./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 3 installs, 0 updates, 0 removals
  - Installing doctrine/lexer (v1.0.1): Loading from cache
  - Installing egulias/email-validator (2.1.2): Loading from cache
  - Installing swiftmailer/swiftmailer (v6.0.1): Loading from cache
Writing lock file
Generating autoload files

Read – PHP Email Sending with SwiftMailer

Composer.json File:

Finally after installing above packages if you open up composer.json file into the editor it should look like this:

{
    "require": {
        "spatie/db-dumper": "^1.5",
        "swiftmailer/swiftmailer": "^6.0"
    }
}

Database database and email Notification:

Create new file called index.php and also create new folder called `database_backups` within same directory.

okay so now to use above packages and execute script to take the database backup into sql file, so go ahead and use following script and replace given variables value:

<?php

require __DIR__ . '/vendor/autoload.php';

use Spatie\DbDumper\Databases\MySql;


class DatabaseBackup
{

    protected $host, $username, $password, $database, $email;

    function __construct($host, $username, $password, $database, $email)
    {
        $this->host = $host;
        $this->username = $username;
        $this->password = $password;
        $this->database = $database;
        $this->email = $email;

        $this->initMySQLDBBackup();
        $this->sendEmail();
    }

    public function initMySQLDBBackup()
    {
        $file_name = 'database_backups/'. $this->database . '_' . date('Y_m_d', time()) . '.sql';

        MySql::create()
            ->setDbName($this->database)
            ->setUserName($this->username)
            ->setPassword($this->password)
            ->dumpToFile($file_name);
    }

    public function sendEmail()
    {
        $transport = (new Swift_SmtpTransport('smtp.example.org', 25))
            ->setUsername('your username')
            ->setPassword('your password');

        $mailer = new Swift_Mailer($transport);

        $message = (new Swift_Message())
            ->setSubject('Database Backup Notification')
            ->setFrom(['support@yourdomain.com' => 'Support'])
            ->setTo([$this->email])
            ->setBody('Database Backup executed successfully!');;


        return $mailer->send($message);
    }
}

$host = 'localhost';
$username = 'USERNAME';
$password = 'PASSWORD';
$database = 'DATABASE_NAME';
$email = 'YOUR_EMAIL_ADDRESS';

(new DatabaseBackup($host, $username, $password, $database, $email));

Note: make sure to update required database settings variable and smtp settings for swiftmailer.

In the above script we are limited to only MySQL, you can switch from MySQL to other database anytime you just simply needs to use given class according the to DB Dumper library.

Also, we are setting `#filename` variable to the same `database_backups` folder location you can replace it to anywhere you want for example user directory or /var/www/ directory it’s all depend on you.

So now we are good to go to execute this php script using cron job on daily basis.

Cron Job Setup to Execute PHP Script and Backup the Database:

Next login/ssh to your server and use following command to open cron job setting in edit mode.

$ crontab -e

You should see following screen on you end:

# Edit this file to introduce tasks to be run by cron.
#
# Each task to run has to be defined through a single line
# indicating with different fields when the task will be run
# and what command to run for the task
#
# To define the time you can provide concrete values for
# minute (m), hour (h), day of month (dom), month (mon),
# and day of week (dow) or use '*' in these fields (for 'any').#
# Notice that tasks will be started based on the cron's system
# daemon's notion of time and timezones.
#
# Output of the crontab jobs (including errors) is sent through
# email to the user the crontab file belongs to (unless redirected).
#
# For example, you can run a backup of all your user accounts
# at 5 a.m every week with:
# 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/
#
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command

Now go the last line on this file and add following line to execute php script daily at midnight:

* * * * * /usr/bin/php7.0 /path/to/your-newly-created-folder-name/index.php

so here we are saying that execute my php script every day at midnight, so finally adding this line you file should look like this:

# Edit this file to introduce tasks to be run by cron.
#
# Each task to run has to be defined through a single line
# indicating with different fields when the task will be run
# and what command to run for the task
#
# To define the time you can provide concrete values for
# minute (m), hour (h), day of month (dom), month (mon),
# and day of week (dow) or use '*' in these fields (for 'any').#
# Notice that tasks will be started based on the cron's system
# daemon's notion of time and timezones.
#
# Output of the crontab jobs (including errors) is sent through
# email to the user the crontab file belongs to (unless redirected).
#
# For example, you can run a backup of all your user accounts
# at 5 a.m every week with:
# 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/
#
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command
* * * * * /usr/bin/php7.0 /path/to/your-newly-created-folder-name/index.php

Replace `path/to/your-newly-created-folder-name` path to your folder path, you can easily find out path using `pwd` command.

Save and close this file.

Now we are all set cron job is going to do the job, our database backup is automated, you will get email notification from the php script at 11:00 PM every night, keep in mind this time is totally depend on your server date time setup.

Download Source Code of this tutorial- https://github.com/itechempires/php-automate-database-with-cron-job

Let me know if you have any issues related to this tutorial using comment box below.

Happy Coding!