PHP Ubuntu

Automate Database Backup with Email Notifications using PHP CRON Job

Automate Database Backup with Email Notifications using PHP CRON Job
Written by Yogesh Koli

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 Database Backup Script Using Cron Job

PHP Database Backup Script Using Cron Job

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:

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

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.

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:

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:

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.

You should see following screen on you end:

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

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:

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!

About the author

Yogesh Koli

Yogesh Koli is a software engineer & a Blogger lives in India. He's driven by an addiction to learning and a love for adventure. he has 5+ years of experience working with the front-end, back-end, web application development, and system design.

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
varun.s Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
newest oldest most voted
Notify of
varun.s
Guest
varun.s

dear yogesh, i do steps as you mentioned .i have doubt that when did database backup is taken and also where i have to paste the following 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);… Read more »