Monday, January 7, 2008

MySQL live backup using replication

Assume that you have a website using MySQL as its database. You want to make a live backup of your database on another machine, so that if your host machine encounters a hardware failure, you can recover your web service ASAP. MySQL replication provides a simple way to do that.

First, you should prepare your backup machine:
  • it is 'closed' to your host machine, in the meaning that data transfer speed is fast between them.
  • it is installed MySQL, the major version should be the same as MySQL on the host machine, ie, both are MySQL 5.x.x, or both are MySQL 4.x.x.
Assume that your host machine's IP is 192.168.1.111, your backup machine's IP is 192.168.2.222.