How to Schedule Database Backup Using Cron Job

For backing up of your WordPress database, there are plenty of plugins around that can get the job done. Some of them backup only your database while others backup the whole WP directory. If you have used WordPress long enough, you will realize that these plugins are not reliable. They will work one day and fail another day, for thousand and one reasons. It could be that they are not compatible with the new WordPress version that you have upgraded to, or that they are conflicting with a newly activated plugin. As in my case, it could also because your database is getting too big for them to handle.

Whatever the reasons, rather than relying on plugins, a better alternative is to use a cron job in your server and schedule it to backup your database everyday.

Note: The following instruction can be used to backup any database. The only requirement is that you are using a Linux server to host your files.

Here is how you do it:

1. Login to your web host control panel. If you are using cPanel, look for a Cron Job icon.

2. You can set the time and frequency to backup the database. Under the command field, copy and paste the following command:

mysqldump --opt -Q -u dbusername --password=dbpassword dbname | gzip > /path-to-store-the-backup-file/db_backup.sql.gz

• Replace dbusername with the database user
• Replace dbpassword with the database user password
• Replace dbname with the database that you are backing up
• Replace the path-to-store-the-backup-file to the file path in your server where you want to save the backup

Note: In some server, you might need to put a pair of single quote around the dbpassword for it to work. For example:

mysqldump --opt -Q -u dbusername --password='dbpassword' dbname | gzip > /path-to-store-the-backup-file/db_backup.sql.gz

Email yourself the database

Storing the database on your server could take up plenty of storage space. One way you can do is to email the backup file to yourself.

1. Download this php script.

2. Extract the zip file and open the backup.php with a text editor.

3. Change the database detail:

$dbhost = "localhost"; // usually localhost
$dbuser = "dbuser"; //enter your database username here
$dbpass = "dbpass"; //enter your database password here
$dbname = "dbname"; // enter your database name here
$sendto = "Send To <sendto@email.com>"; //the email address to send the database to
$sendfrom = "Send From <sendfrom@email.com>";
$sendsubject = "Daily Database Backup"; //the subject of the email
$bodyofemail = "Here is the daily backup of my database.";

4. Save the file. Upload the whole folder to your server. Note: The folder includes a .htaccess file to prevent any access from the browser. If you want to access from the browser, remove the .htaccess file.

5. In the cron job section of the cPanel, enter the following command:

php -q /path-to-the-php-script-folder/backup.php

That’s it.