How to Back Up Your Web Hosted Database
Sites that use web hosted databases must make database backups a priority. The site design can be easily re-created in the event of corruption or loss, but the data in databases is much harder to rebuild — unless you have a good backup.
Your host may already back up your database as part of its normal backup routines, but it is still wise to ensure that you have a recent copy held locally just in case.
Your web script may have the facility to back up your database automatically or you may be able to use a plug-in to create a backup. Back up your database either to another server (Dropbox or a similar service is a good place to back up to), or you can have a backup e-mailed to you if it isn’t too large.
Do not make the backup of the database on the same server as the master copy. If the server goes down, you will not be able to access either the master or the backup. Not good!
If you cannot back your site up automatically — or even if you can and you’re a little paranoid about your precious data — you can also back up the data manually.
How to back up web hosted databases using phpMyAdmin
Use the following steps to make a backup using phpMyAdmin:
Log in to phpMyAdmin.
Click the database you want to back up.
Click on the Export tab.
Click Go to download a complete database backup.
Alternatively, you can select only the options you require and then click Go.
The database downloads to your default download folder. Store it in a safe location — in a different folder on your hard drive, on a USB memory stick, or on a DVD.
How to back up web hosted databases using MySQL Workbench
You can also make backups using MySQL Workbench by following these steps:
Open MySQL Workbench and open a connection to your server in the Server Administration section.
In the column on the left of the Admin area, click Data Export.
In the right column, select the database(s) you want to back up.
Select whether to Export to a Dump Project Folder or a Self-Contained File.
Select the folder you want to save the backup in.
Click Start Export.
The Export Progress screen appears and notifies you when the export is complete.
The difference between exporting to a project dump folder and exporting to a self-contained file is that the first option creates a folder with separate files for each table in your database. This enables you to modify and restore any individual table without touching the others. The second option, however, creates a single file that holds all the tables. Which you choose is a matter of personal preference.