How to Back Up a Database

Posted on August 18th, 2009 by Marck

(No Ratings Yet)

Loading ...

No Comments (Post Comment)

Republish

For any person or organization running a website, database backup is a maintenance task that should be done regularly. You never know when your blog or website will experience server problems or malicious attacks, so you need to back up your server databases regularly just in case something goes wrong. Here are some ways to back up your database.

Why Back Up?

While the Internet is a relatively stable and safe place to store public data like text and pictures, you never know when your own server can crash. A server crash can affect your data, leading to serious data loss and, in many cases, loss in income. Backing up your database ensures that you always have a safe source of data to turn to just in case your website fails due to a crash or because of a malicious attack. (See Protection from Internet threats)

You don’t have to back up your database every day. A good rule of thumb to remember is to back up your database once every month, or twice a month if necessary. If you run your own website, do not let a month pass by without committing yourself and your systems to a full database backup.

Straight Commands

One of the most reliable ways to back up a database is to use a UNIX terminal to back up your database through straight SQL commands. UNIX is particularly useful, because almost all hosting services and dedicated servers run on this system. The names used in the following examples are arbitrary ones that you can change, depending on the actual filenames of your database tables and your directories.

The first step is to move to a directory where you can store your databases. Enter the commands after the prompt:

user@server:> cd files/site

To dump all your database tables to a single compressed file, follow these commands:

user@server:> ~/files/site> mysqldump –add-drop-table -h database.mysite.com -u sqlusername -p databasename -c dbbackup.sql

Once the server recognizes the command and you enter the correct password, the file “dbbackup.sql” should now be visible on the directory after the backup process is completed. Check the file for any inconsistencies or breaks.

phpMyAdmin

Another way to back up your database is to use your phpMyAdmin account, which is quite common for many blog sites and comes packaged with many hosting services. To back up your site using phpMyAdmin, follow these steps:

Log in to your phpMyAdmin account using your SQL username and password.
Click on “Databases.”
Click on the “Export” tab on the window. Now would be a good time to check or repair your databases if needed.
Check or uncheck all applicable tick-boxes depending on your site’s needs. You may select all databases, or back up select databases.
At the bottom of the window is a “Save As File” option, where you can leave your file uncompressed, or compressed to ZIP or gZIP. Name your file and select an option.
Click OK, and wait for the download to finish.

Third-party SQL services and clients may also work well in place of straight SQL commands or phpMyAdmin. With these steps and a regular commitment to backup, you don’t have to worry about attacks on your site, because you always have a handy backup ready for use at any time, or any untoward situation. If you enjoyed reading this article, you’ll surely be interested in learning how to form a web development team from scratch.