Thursday, August 16, 2007

Two-way MySQL replication

MySQL database server has a master-slave replication feature, which makes it possible to have a complete copy of a master database on a slave server. When the master server should fail, you have a copy available immediately, so you don't loose data and you can switch your applications to the slave-server while bringing back up the master server, so the downtime is small too.

A drawback of this master-slave replication is that it is a one-way backup. The slave server is a complete copy of the master server : all changes that are done on the master server are applied to the slave server, keeping them identical.
But because it is one-way replication, changes to the slave database server aren't applied to the master server.

I just came along an article explaining how to set up two-way replication : both servers are master and slave at the same time. So it doesn't matter on which server the database is changed, the change will be applied to the other server, which always results in two identical databases.

I'm thinking of using this setup to synchronise the database of my server at home with the database running on my laptop. This way I will have a remote backup of my database server on my laptop, in case something happens to my database server at home.
But also, all changes that I make to the database on my laptop while not at home, will be synchronised with my database server, when I get home.

This good HOWTO on setting up (one-way) master-slave replication, also provides a simple, but working, script to make a daily backup of a MySQL database server.

1 comment:

Peter Dedecker said...

Interesting stuff!