Backup Your Database With An Automated Cron Job

With every update for WordPress everyone who actually bothers to read the readme or howto sees the warning/instruction

Step 1: Backup Database Tables and Files including .htaccess

Use phpMyAdmin or other appropriate tools provided by your webhost, to backup the database used by WordPress.

Luckily the WordPress codex points at phpMyAdmin for the database backup, because the WordPress export feature is officially useless as a backup tool.
There are several backup plugins for WP, but most don’t serve the optimal purpose.

As blogger you should not only backup your database when you upgrade but you should regularly back your data up. But honestly, who visits regularly the options to backup the database.
I known many website owners, or bloggers, who’d love to know how to regularly backup the database in order to avoid losing all their posts every time they screw up their Cpanel config.
Luckily the mysql backup process can easily be automated with a cron job.

Since many bloggers have cPanel, here is a short howto automate your mysql database backup with a cron job in cPanel(1).

Select in your cPanel the Cron Job icon and let’s immediately jump to the advanced (Unix Style) experience level. It is just as easy to explain the advanced (Unix Style) option than stick to the standard one. The only difference is the interface, but the command and the needed attention level are the same.

The Command Line

The structure of the command line for the cronjob is following

mysqldump -udbusername -pdbpassword db_name | gzip > /home/cpanelusername/yourfolder/mysql_backup.gz

The different switches explained

  • mysqldump
    This is the command to backup the database.
  • -udbusername
    This is a double switch The first part -u identifies the user to the database. Obviously you have to add your database username(2)
  • -pdbpassword
    Again a double switch, with -p being the trigger to add the database password(3).
  • db_name
    Here we add the name of the database we want to backup(4)
  • | gzip
    Triggers the compression for the backup. This is not needed. If you don’t want to compress the backup, we just skip this switch.
  • /home/cpanelusername/yourfolder/mysql_backup.gz
    This is where we will store the backup and how it will be named. If you have opted not the compress the mysql backup, change the extension gz into sql. Of course you can name your backup and folder(s) differently.
    Feel free to be creative. I am boring conservative and save my backups in the folder /home/mycpanelusername/etc/dbase(5).
    It could be that your server is setup differently and doesn’t follow the /home/cpanelusername/ structure. Normally you find the path to your root folder in the cPanel sidebar. If not, best is to contact your hoster and ask.
    cpanelusername is the nick you use to login into your cPanel.

When do we run the script

Last thing to define is when to run the script. Cron Jobs follow an easy structure for this.

  • Minute
    What minute of the time period. Values from 0 to 59
  • Hour
    What hour. Values from 0 to 23
  • Day
    What day. Values from 0 to 31 (remember the shorter months)
  • Month
    Which month, 0 to 12, Gregorian Calendar.
  • Weekday
    Guess… 0 to 7, 0 and 7 being Sunday.

Some examples for dates.
1 * * * * : Every minute after the hour, daily. * works as wild card, EVERY unit.
12 4 * * 3 : 04.12AM every Tuesday.
6 22 15 2 * : February, 15th at 10.06PM.
1 0 * * 1 : Every Monday at 0.01AM.
As can be seen in last example (every Monday), it is easy to create another job for every day (remember to give your file another name for every day as well). There also is an option to date stamp the backups but this might not work on every cPanel install and also is the best way to clutter your web space because probably you’ll forget to delete the old files anyway. This will overwrite your backup every time.

Update: I have expanded this entry with How to backup files/folders on BloggingPro.

  1. The syntax is the same for Plesk and other back ends()
  2. If you have used Fantastico and don’t know those data, retrieve them from your wp.config file()
  3. If you have used Fantastico and don’t know those data, retrieve them from your wp.config file()
  4. If you have used Fantastico and don’t know those data, retrieve them from your wp.config file()
  5. IMHO it is better to save your backups in a non-public folder, that is one level higher than your public_html/www/ folder. Use the cPanel File Manager to create this folder, or finally get a decent and free FTP-client()
  • Pingback: Automate The WordPress Database and Files Backup()

  • Hey Franky,

    You are an expert of WordPress. Just came here to check for any update or more info about my problem. But I forgot; it is your old article. Ha Ha Ha :)

    • I sadly can not replicate the issue so I’m pretty much left guessing.
      At least you have a working routine with the regular date, albeit not your preferred one (yet).

  • Aaliyah Wood

    It is also easy to backup and transfer all your websites from one server to another server if you have cPanel installed~”‘