Performancing Metrics

Back It Up Or Lose It!

If your weblog uses a mySQL database to store all your data then chances are at some point in time it’ll get corrupted or damaged beyond repair. Imagine having a year or two’s worth of entries, links, and comments disappear in an instant.

Thankfully, backing up your database is easy and can be automated so you don’t have to worry about it anymore. I’ll show you the quick and easy way to create an automated daily backup of your mySQL database.

First open a text editor and add the following:

#!/bin/bash

mysqldump –user=”Database Username” –password=”Database Password” –all “Database Name” | gzip > “Full Path Where You Want Backup Saved”-`date +%Y%m%d`.gz

You can add multiple lines each with different database names if you want to dump multiple databases on your server.

The mysqldump command is used to dump or copy a database. By not indicating which table to copy we will default to dumping all the tables in the database. Be sure to fill-in your database username, password and the name of the database. I used quotes only to point out where you need to make changes. Do NOT use the quotes. The gzip command says we should zip up the database for economy purposes. Finally you need to add the full path to the directory where you want the dumps kept. The file name will be in the format of databasename-yymmdd.gz.

Save this text file as mysql_dump.bsh and upload it in ASCII mode to the root of your server. Now you need to create a cron job to tell the server to execute the above file at a designated time. If you have a cron tab manager in your webpanel, this will be easy. I have mine set to

0 6 * * * /path/to/your/mysql_dump.bsh

This will back up my database every day 6 AM.

If you do not have cron built into your webpanel then you will need to set it using your telnet client. First open another file with your text editor and add the following on the TOP line:

0 6 * * * /path/to/your/mysql_dump.bsh

Hit return to create an empty line below. Now save the file as cron.txt. Upload in ASCII mode to the root of your server. Log into your server via telnet. At the command prompt type

crontab cron.txt

crontab -l (where -l is a lowercase L)

Your finished. Be sure to create the directory off your root that you’re storing your back-ups in. FTP into your server the next day to verify that a backup copy was created. Your risk for data loss is now no more than 24 hours. If you don’t post much to your blog then maybe dumping your database once a week is appropriate. This will depend on your circumstances.

I hope this has been helpful in detailing one way of automating data backups on a mySQL database. Thanks to Webmaster-Central for the reminder on the crontab commands.

Categories: Blogging Sense

This post was written by . You can visit the for a short bio, more posts, and other information about the author.


Comments Closed

Comments