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

Back Up MySQL Database

March 28, 2008 by debbie T | Content Management • Linux Corner • Web Development

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?

phpMyAdmin

phpMyAdmin also offers an easy way to backup your MySQL database. For more advanced users, it offers customized backup options, but truthfully, I backup using only their default settings.

Look for phpMyAdmin on the cpanel main page, check for the “database” category. phpMyAdmin should be found there.

find phpMyAdmin

Once signed into phpMyAdmin, choose a database from the left column. If you have more than one, all will be listed.

In the tabbed menu, now choose “Export”

mysql export backup

By default, all your tables are selected, but to be sure click the “select all” link and make sure the radio button next to “SQL” is selected. Keep all the other settings as the default. To be truthful, I really don’t know exactly what’s up with all those settings, so I keep everything as is.

in phpMyAdmin, exporting database

Next, toward the bottom, check the box next to “Save as File” – type in a name for your file – and depending on your preferences, choose either “Zipped” or “GZipped” – I choose gzipped, but Windows users might want to choose “zipped”.

export the zipped database file

Click the “go” button and browse to the folder on your hard drive where you plan to store your database backups.

If you ever do need to restore your database, choose the “Import” tab and navigate to the most recently backed up file.

Using Built-in Backup Options

Some web applications offer a database backup feature. For example, from the Admin option in Expression Engine, there is a utility to backup the database. Same with my discussion forum software Simple Machines.

Check the software you are using to see if there is a backup utility offered.

Automatic Backup Script

I found an indispensable script that automatically backs up all my database(s) every night, and then again weekly and monthly. On a regular basis, I download and save the latest backup file for each of my databases, so I can keep copies safe on my hard drive.

It’s been a couple of years since I enabled the script, but I did write an article with detailed steps to install the script and set up a cron job:
cron jobs & backing up database data

I also found another article that explains the process.

No Excuses, Just Backup!

No matter which option you choose to backup your database data, just do it, and do it often. It is especially important right before you upgrade software. If something goes wrong with the upgrade, you can always revert to the prior files and database.

Good luck! :)

Leave a Comment

(Optional)

Subscribe without commenting