In this post you will learn how to use cron to schedule the backing up of MySQL databases monthly, weekly, and/or daily.

Prerequisites

Here are some requirements in order to get started:

• Your server has a control panel for you to control the server (cPanel or others).
• You are familiar with command line (if you want to customize the script below).

Steps

1. Login to the control panel of your server.
2. Locate and enter the “Cron jobs” or similar section in the control panel (use the search feature if yours has one).

   

3. Under “Add New Cron Job”, select the interval you want to have the backup to be executed (hourly, daily, weekly, monthly, etc.) from the drop down menu in the Common Settings field.

   

4. Enter the commands below in the Command field:
   [bash wraplines="1"]rm public_html/backup.sql*;mysqldump -u [username] -p’[password]‘ –all-databases > public_html/backup.sql;zip public_html/backup.sql.zip public_html/backup.sql;echo "Remember to download the <a href="http://[domain]/backup.sql.zip">database backup file</a> and copy to external drive for backup." | mail -s "$(echo -e "Backup completed\nContent-Type: text/html")" [Email][/bash]

   Be sure to replace the text along with the square brackets with your own. Here is the explanation for each command:

   1. rm public_html/backup.sql*: Remove the files backup.sql and backup.sql.zip if you backed up before. Note that if you have either of these files for different purpose, make sure you move them to different location before you add the above commands. Or you could change the backup location across the above commands.
   2. mysqldump -u [username] -p’[password]‘ –all-databases > public_html/backup.sql: Dump out all databases on your server into a file named backup.sql. Make sure to replace [username] with the username of your MySQL server, and [password] with your password.
   3. zip public_html/backup.sql.zip public_html/backup.sql: Create a zip archive from the sql file you just dumped out. It’s for easier downloading to your local machine for backup. The browser would simply output the file rather than downloading it if you tried to download the sql file directly.
   4. echo “Remember to download the <a href=’http://[domain]/backup.sql.zip’>database backup file</a> and copy to external drive for backup.” | mail -s “$(echo -e “Backup completed\nContent-Type: text/html”)” [Email]: This command is a little trickier to explain. First it outputs the message to allow you to download the zip file for backup. Then it sends this message to your email address as a HTML document with “Backup completed” subject. Make sure to replace [domain] with your domain name, and [Email] with your email address.
5. Hit the add button.

You are done with the setup. Now it would execute the script whenever the specified time arrives. If you want to test out the script you wrote, you can edit this job to every minute so you could see its result within next minute. But don’t forget to change it back to your original time interval, or else it would use up your server resources, unless you intended to backup the databases every minute.

If you have any question about setting up the cron job, leave a comment below.