Database Backup

The correct way to backup a database is to use a "mirror" or "replica" of the database. That's a server that's running a duplicate of the database, and, perhaps also acting as a load-balancing server.

These two servers are connected by a network, and as requests come in to the main server, they are either performed on the main server, or passed on to the mirror. Update and delete operations are carried out on the main server, then executed on the mirror.

A typical scenario is to reserve a single IP address for hosting the service (on LAN). The main database has this address. It also has a second IP address for inter-database communications. The second datbase has only the inter-db communications IP address. If the main database fails, the second machine "takes over" the IP address.

This failover is combined with database replication. The free MySQL server has this feature, and is described in replication.

This provides maximum fault-tolerance.

A simpler backup that doesn't have the advantages of a mirror, is to dump the contents of a database to text files, and back those up. This is obviously a lot cheaper than purchasing a second database server.

If you opt for the latter method, make sure that you can build a database server and load the data quickly.

For archival purposes, you may want to make a database dump regularly, compress it, and have it backed up with the rest of the files.

If you're backing up a database on a website, see the article
How to Backup MySQL on a Website

database.gif13.02 KB