In most content management and ecommerce systems the majority of the sites data is stored in a database. MySQL is the most commonly used database platform for websites and is the subject for this tutorial. To complete this tutorial you will need two things:

1. A copy of SQLYog community edition
2. The following information on your MySQL server
   1. Database IP or host address
   2. Database username
   3. Database password

If you do not have your database connection details you can get them from your hosting providers technical support.

Connecting to the MySQL Server

If you do not already have a copy of SQLYog you can get it from the address below for free:

http://code.google.com/p/sqlyog/downloads/list

Once you have installed SQLYog run the program from the desktop shortcut. This is the first window you will see:

1. Click the button on the top left labeled new. A dialogue will appear asking for the name of this connection. A find the name of the site will usually suffice. Provide the connection name and click OK to continue.
2. Provide the IP address of your database in the field labelled MySQL Host Address.
3. Provide your database username and password in the appropriate fields.
4. Leave the fields Port and Database Names blank.
5. Click Save, then click Connect.

Troubleshooting

If you are having trouble with this tutorial it may be because you're database is not configured to allow remote connection. Below are instructions on how to correct this problem using your cPanel hosting administration area. You should have been provided details on how to access your cPanel by email when you first setup your hosting. Find this email and follow the instruction in it. If you cannot find it contact your hosting provider and ask for the email again. Once you have logged in to your cPanel follow the instructions below:

1. Find the icon labeled MySQL Databases and click it.
2. Scroll to the bottom of the page, find the text field labelled host as shown below:
   
3. Type "%" without the speech marks in here and click Add Host.

Creating a Database Backup

Now that you have connected to the database we can begin taking a backup. You should now see a screen that has a one area on the left of the window listing out the available databases. If there is more than one database listed here find the name of the database you wish to backup and ight click on it. This will cause a menu to appear. Find the menu option labelled 'Backup Database as SQL dump ...' and click it. This will cause a window like the one below to appear:

1. Thw two big white boxes at the bottom are table lists. The box on the left is tables that won't be backed up and the box on the right is tables that will be backed up. By default all the tables will be in the box on the right configured to be backed up and in 99% of cases this is correct. If you want to exclude some tables from being backed up just select them in the right hand list and click the single right arrow (3rd down) button of the four button separating the two table lists.
2. Under 'Export' ypu have the option of backing up the database structure, data or both. Make sure both is selected.
3. There are 16 checkboxes in this window, most users will require them all to be checked or unchecked like in the image above.
4. Click the '....' button next to the text field labelled 'Export to File:'. this will bring up a file save dialogue. Find an appropriate folder to store your database backup then give it a filename. I use a standard naming format for my database backup files using the current date and the database name that allows them to easily sorted. The format is as follow:
   

   DATABASENAME_YYYY-MM-DD.sql

5. Now that everthing is configured click 'Export' to start the backup process. Depending on the size of your database this could take a couple of minutes or a couple of hours. The program will inform you when it is completed.

It is important to take regular backups of the databases that support your business sites. If they are lost all of the content you have written for your site, your order histories and customer information are lost in them.

Restoring a Database from the Backup

To restore your database first connect to your MySQL server using the instructions from the first part of this guide. When you have connected go to the DB menu near the center top of the window then click on 'Restore from SQL dump ...' and a window like the one below will appear:

Click the '...' button and an open file dialogue will appear. Find the database backup we created in the previous stage and open it. If you have several backups of the same database find the one with the latest date.

Once you have opened the correct database file click 'Execute'. This will recreate your database in the exact state it was in when you backed it up.

Only attempt a database restore if your current database is completely unrecoverable. This is a potentially damaging procedure and if you are at all unsure seek professional help.

Pixeko AutoDB Backup Service

If you prefer an easier route to the one laid out in the article above we offer a fully automated database backup service called AutoDB. All you have to do is sign up to our service at the link below and provide us your database connection details. We will configure our servers to take a complete backup of your mysql database every 24 hours. If you ever have a database failure in the future all you have to do is call our hotline any time 24 hours a day 7 days a week and we will immediately start restoring your database. Typically this means your site can be back online in under an hour. 

Find out more ....