Tutorial Thursday: Backing Up your mySQL Database

cPanel (most control panels actually) provides you with phpMyAdmin which is what we will use to backup our mySQL database.

Though you can backup your complete database at one go, I recommend making two backup files, one containing the Structure and the other containing the Data.

Why? Simply because your structure will usually remains same (unless you create / delete tables), while your data keeps changing.

By making a seperate file for Structure, you ensure that in the future only the Data needs to be backed up.

Here’s how you go about it:

1. Login to cPanel by going to yourdomain:2082
2. Click mySQL Databases
3. Click on Click here to access phpMyAdmin at the bottom of the page. A new window will open and load phpMyAdmin.
4. If you have more than one database select the one you want to backup from the drop-down box in the left frame, or click on the name of the database if you have just one. A new page listing the tables will load in the right frame.
5. Before we backup the database we shall optimize it. Click Check All and then from the drop-down box select Optimize Table.
6. After the new page loads click on Export in the horizontal menu at the top.
7. Now uncheck Data. Check Save as file. In File Name Template enter __DB___Structure. Select "gzipped" and then hit the Go button. You will be prompted to save a file. Save it. This file is your mySQL backup in gz format containing the Table Structure only
8. Now check Data and uncheck Structure and change File Name Template to __DB___Data, while retaining all the other settings of step
9. Click Go and save the file. This file is your mySQL backup in gz format containing the Table Data only.

In the future you can skip step 8 and only backup your Table Data.