Splash of Style...Macs, Photography, Design, and other Passions

Back Up MySQL Database

For those that utilize a MySQL database, whether for a blog, discussion forum, photo page, etc. it is so important to make regular backups of the database(s). You never know when or if the database could be corrupted for any reason.

It is very easy to backup your database, so don’t be afraid! This article will cover a few simple backup (and restore) solutions.

Using Backup Wizard from Cpanel

Does your hosting provider offer Cpanel as part of your hosting plan?

If you have access to Cpanel, then you also should have access to the “Backup Wizard” – on the Cpanel main page, check the category “Files”. You should find the Backup Wizard there.

backup wizard

This is a very simple, straight-forward way to backup your entire web site, including all databases. If you just want to backup only your databases, then click the MySQL database link and choose the specific database to backup. Save each file to the folder on your hard drive where you plan to store your database backups.

There is also an option to restore your whole site or a particular database.

The Backup Wizard is probably the easiest way to backup your web files.

If you cannot find the Backup Wizard, or your host doesn’t offer cpanel as part of your hosting package, then contact them to find out what tools are available to you to backup your database(s).

Want to learn more? Read the Rest of the Article

Permalink | Comments Off

Cron Jobs & Backing Up Database Data

June 7, 2006 by debbie T | Content Management • Linux Corner • Web Development

I have been backing up my mySQL databases manually through phpMyAdmin, and although it is an simple, straightforward way to backup, I really wanted to find a more reliable, automated backup system for my database data.

After a remind on an email list, I decided to learn a little more about Cron Jobs. First I googled “cron job backup database” and found an interesting script for backing up WordPress.

WordPress backup with Cron

From that page, I also found a more robust MySQL backup script:

AutoMySQLBackup from SourceForge.net

I read the basic instructions, then downloaded the .sh file. I thought the file was fairly easy to configure; make sure to scroll down the page for detailed instructions.

It looked like a simple enough process, but I did have a little trouble. My Notes:

* I uploaded the .sh file in a new directory above my public web files. Important! Make sure that you do not upload this file to your public_html directory, as it is possible that the file could be accessed there.
* I changed the .sh file permissions to 755.
* I created a new directory where the backed up database files will be saved. Make sure that the directory is referenced correctly in the .sh file. This is where I kept having trouble, so I finally set an absolute path to the backup directory.
BACKUPDIR="/home/username/backup"
* Set permissions to 755 for backup directory.
* I checked in my /etc directory, and did not find a daily cron folder, so I experimented with my own cron job. In my hosting account’s cpanel, at the bottom category “Advanced Tools”, choose Cron Jobs.
* Choose the “Standard” button for a wizard-like set-up.
* Type in a valid email address. If there is an error in the script, it is very helpful to receive an email notice.
* Type the path to the .sh file in the “Command to run” text box.
./directory/automysqlbackup.sh.2.5 – change directory to your specific name. Also, make sure to include the exact file name. If you are using a version of the script different than “2.5” you must reference the specific file name, or rename the file to “automysqlbackup.sh”
* To test the script, I suggest a temporary setting of every 5 or 10 minutes. It will keep running while you troubleshoot the errors. Once the script runs successfully, remember to change the setting back to “0”
cron-job
* Once you receive an email showing the completed process, open the backup directory. There should be newly created directories for daily, weekly and monthly. Inside the daily directory, look for individual database sub-folders, with compressed .sql files.
* Regularly download the database compressed files. I plan on downloading my weekly files.
* If it is ever needed, the .sh file also includes instructions for restoring your database.

This looks like a really useful script, and it will make my backups so much simpler and less time consuming!

Update: This morning, I received an email letting me know that the 3AM backup was successful; I checked the backup folder on my hosting account, and found a backup file for Thursday.

Permalink | Comments (2)