search

Jayant Kumar @jayantkumar314

Published On - 21 January 2020 Last Updated - 4 February 2020

How to backup your sql database automatically using Cron Jobs?

Website Hacked? Database Hacked? Now every data is lost !!! Now all registered users' data is compromised. What if the hacker destroys the database? Gone...Everything is gone... NOPE!! Wait... There is a solution. Why not schedule to backup the database automatically on a certain interval? Now, if our database is attacked and destroyed,  then there nothing to worry because we have our database safe in our backup. So, in this blog, I will show you how you can back up your whole database data in certain interval using "Cron Jobs", so that you can be on safer side in case of any attack intended to destroy your database and hence your effort. Aren't you excited? So, let's get going !!!

Some more reason why you should backup your database regularly:

Working as a freelancer I came accross several project where  the clients' has lost all their users because their database were destroyed by some sort of attact like:-

  1. SQL Injection ( Very common one) : Most PHP websites are vulnerable to this attack if proper care is not taken. Even a single form present on your website, where users are allowed to fill the form, can leave your website vulnerable for SQL Injection, if not sanitized and validated properly.

Websites coding doesn't get affected much. But all we loose is our database where various users registers daily with a hope of staying connected with you. If they subscribed to your email notification whenever you post something new... then what? Your good will compell some users to register and subscribe again..but various will not do that again... Why are we even discussing this if we have simple solution for that. Thanks to cronjob. ????

So, depending on how much traffic your website gets daily you can schedule a cronjob for every minutes, hours, months, etc. (We can run many cronjob parallely. So don't worry about it) .

Method 1: Write cron job directly in command in cpanel

If you just want to run backup command then can use this method. But its considered unsecure way of writing because you are revealing the password in the command you wrote. So I prefer the second method. 

 

 

Method 2: Write cron job in a file (here php) and then execute that script using CURL in the command in cpanel

 

 

 

 

 


/usr/bin/mysqldump -u USERNAMEOFDB-p'PASSWORDOFDB' DBNAME> /home/usernameofcpanel/public_html/db_backup_`date +\%Y\%m\%d\%H\%M`.sql