MySQL Database Backups

If you're storing anything in MySQL databases that you do not want to lose, chances are you should be doing weekly or even daily backups. Depending on what you're using your databases for -- be it to store forum messages, employee information, or your spending information -- you are going to need to choose a backup schedule that meets your needs.


You may or may not know that MySQL databases are just files that are stored on your web server. This fact makes the whole backup and restore process extremely simple and painless once you have figured out how to do it.

Different Ways to Get That Backup Done

There are many paths you can take to create a MySQL backup. However, no matter which application, control panel tool, or SSH script you use, all of the backups will fit into two types of backups: a dump or raw backup.

MySQL Dump

A MySQL dump is a bit slower than a raw backup because it creates all the SQL queries required to create the tables of that database, as well as all the insert queries required to place the information back into the database's tables.
If you want to perform the mysql dump manually, without the assistance of your hosts control panel, then run SSH to your web server and do the following (taken from MySql.com):

• mysqldump --tab=/path/to/some/dir --opt db_name

If you were to open up a MySQL dump file you would see a slew of SQL queries that you would probably be able to understand (if you've already read through this whole tutorial!).

MySQL Raw Backup

A MySQL Raw Backup is quicker because it does not translate the contents of the database into human readable SQL queries. However, not many control panels support this type of backup, so do not worry if your hosting provider doesn't have this option set up for you.

MySQL Backup in Control Panel cPanel

cPanel is the most widely used web host control panel at this time, so we thought it would make sense to provide a walkthrough specifically for cPanel.
From the application selection screen click "Backup". This will bring you to the backup application that allows you to generate and download complete backups for your site.
To back up a database individually, look for the title "Download a SQL Database Backup" or something similar. Below that title should be a listing of every database that you have created. Simply click on the name of the database you want to backup and save it to your computer.
That's it! Now just be sure that you have a regular backup schedule, just in case the unthinkable happens and your web host loses all your database information!

Posted in: MySQL