By: Benedict Chiu

February 8th, 2001

Reader Rating: 7

The Internet is currently seeing a transition from static sites with little interactivity to dynamic sites being generated from databases, with tools such as forums, free e-mail and other web-based applications being used to lure visitors back. So it is vitally important that your database is backed up every so often in order to avoid mishaps in the event that your host crashes, along with your site and your database. It's a good idea to periodically back up your site anyway.

In this short article, we'll look at how to back up some common databases: MySQL, PostGreSQL, Microsoft SQL Server and Microsoft Access.

Who Needs to do Back Ups

Before you begin, you need to take stock of your situation to decide how often you will back up your database. For example, if you run a personal 'diary/blog' type site that is updated every week or so, and have a guest book with comments inserted every so often, chances are you only need to do updates every week or two, depending on how much 'important' information you have. On the other hand, if you run a high-traffic forum on various topics or you run a site the magnitude of SitePoint (over 300 articles, a vBulletin forum with hundreds of posts every day, templates galore and much more) then you will need to look at backing up your site and database every few days depending on the importance of the information you supply.

In general, the more important your information is or the more traffic you get, the more you should think about backing up your database to different locations. That means not only backing it up to another directory on your server, but also to your local machine, to tape backups, to another server on a different network, possibly even to a free space provider (like Driveway or iDrive). It always pays to be careful.

How Often Should Back Ups be Done

As an example, SitePoint backs up its site every day at a certain time to a local machine in Australia as well as on the server itself (but in a slightly different location). Hardware, such as RAID-5 drives, tape backups and mirroring devices, also collaborates to help with backing up.

So once you've considered how often you should backup your database, follow the links below (depending on the type of database you have) and follow the steps!

Note: Whenever I say 'backup area' in any of the guides, that refers to the place where your backups will be stored - whether it be your own hard drive, tape backups on your server or another server elsewhere on the Net.

MySQL

MySQL is a popular database for use on Unix and Linux machines; the Windows version has also recently become open-source (with MySQL going the GPL way). When combined with PHP, the two form a powerful platform for you to write interactive web-based applications.

There are three main ways to back up a MySQL database on Unix/Linux (Windows users should find the commands are reasonably similar):

1. Use the mysqldump command.
   If you have Telnet/SSH access to your MySQL server, log in and issue the following command for each database you want to back up:

   shell> mysqldump -u user -ppassword --opt -full database_name > backupfile.sql

   Then move the resulting file(s) to your preferred backup areas. If you require more information on the mysqldump command, then simply check out this URL:www.mysql.com/documentation/mysql [1]

2. Copy all the relevant table files.
   If the server isn't updating anything (or you've deliberately killed mysqld for this purpose) then you can copy all the files with the following extensions in your MySQL data directory:

   *.frm
   *.myd
   *.myi

   Make sure you restart the MySQL daemon once you finish copying and downloading the files to your preferred backup areas.

   TIP: once you've completed the backup, restart MySQL with the --log-update switch. This will allow you to keep track of all modifications done in the MySQL tables since your last 'dump'.

   To restore your dumps, you should either restore to an existing database or create a new database using

   shell> mysqladmin create database_name

   then issue the following command :

   shell> mysql -u user -ppassword database_name < backup-file.sql

3. If you don't have access to Telnet/SSH and you're unable to do backups using the methods described above, you should ask your host if it is possible for them to do a backup for you and put the backups in a separate directory so that you can easily FTP your backups to your selected backup areas. Otherwise, if you have access to phpMyAdmin, you can use the following procedure:
   • Access phpMyAdmin, and select the database you wish to 'dump' (backup).
   • Scroll down and you will see a bulleted point saying: "View dump (schema) of database" along with some radio and check boxes. Choose 'Structure and data', then click on 'Add Drop Table' and 'Send' and click 'Go'. This will save the 'dump' to your hard drive.

   To restore a dump using phpMyAdmin, simply insert the file in the correct place once you have chosen the correct database by doing the following:

   • Choose the database you will insert your data into, or create a new database.
   • Insert the appropriate SQL queries you already have, or just paste the name of the .sql file you have on your hard drive into the text box under 'Location of the textfile', and fire away!

   Tip : use a crontab job to schedule backups periodically.

PostgreSQL

Although not as widely known, PostgreSQL is a powerful 'object-relational' database system, supporting many powerful features that MySQL does not have. Again, PHP makes a very good combination with PostgreSQL.

Backing up a PostgreSQL database is also quite easy. In Telnet/SSH, issue the following pg_dump command:

shell> pg_dump dbname > dbname.pgdump

You can restore your database from the dump with the following command:

shell> cat dbname.pgdump | psql dbname

Of course, make sure you replace 'dbname' in the above commands with the appropriate names.

If you have a large PostgreSQL database, you'll need to use gzip or split to reduce the resulting file into a more manageable file size.
gzip: Issue the following command :

shell> pg_dump dbname | gzip > filename.dump.gz

and to reload into a new database…

shell> createdb dbname
shell> gunzip -c filename.dump.gz | psql dbname
or
shell> cat filename.dump.gz | gunzip | psql dbname

split: Issue the following command :

shell> pg_dump dbname | split -b 1m - filename.dump.

and to reload into a new database…

shell> createdb dbname
shell> cat filename.dump.* | pgsql dbname

Tip : use a crontab job to schedule backups periodically.

Access

Access is widely used by many companies, although its original purpose was not as a Web database. Access has the capacity to handle 30 simultaneous users on the Net, and interacts reasonably well with ASP.

That said, backing up your Access database is absurdly easy. Simply FTP the Access database on your site to your backup area, and upload again when you need it.

SQL Server

Microsoft's database solution for medium-sized businesses (though it can suit both small and big business), SQL server is a powerful application designed with the power user in mind. You can use a point-and-click interface to manage your database through Enterprise Manager, or get down and dirty with transactions and SQL sub-selects. ASP and SQL Server offer an effective combination, particularly for web-based applications and sites requiring e-commerce capabilities.

To back up your database you should log in as an Administrator or a user with SQL Server permissions and perform backups through the interface provided. If you don't have access to your server then you should ask your system administrator. There are a few ways to do backups on SQL Server; unfortunately, there isn't room in this article to discuss them all, so I'll let you read it direct from the horse's mouth; check out this article from Microsoft's TechNet [2]

Conclusion

We've just examined how to back up your databases with some common database systems and provided some handy tips on various related issues. No matter what type of database-driven site you run -- be it your personal diary, a Slashdot-style news portal or a 24/7 e-commerce application -- you should implement some sort of backup mechanism. Should disaster befall your site in some shape or form, you'll appreciate the safety net.

[1] http://www.mysql.com/documentation/mysql/commented/manual.php?section=mysqldump
[2] http://www.microsoft.com/TechNet/sql/manuals/admincmp/75517c03.asp