O'Reilly logo

Linux Server Hacks by Rob Flickenger

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #82. Setting Up Replication in MySQL

Keep live copies of your database running to increase performance and provide redundancy

As of Version 3.23.33, database replication is implemented in MySQL. It is accomplished by maintaining a binary log of each database action on one machine (the master ), and keeping it in sync with each of the replicated copies on other machines (the slaves ). As of MySQL 3.23, replication is only one-way (that is, you may make changes to the database on master that get propagated to each slave, but changes to the slaves are not sent back to the master). If you need bidirectional replication, take a look at the bleeding edge code in MySQL 4.

The most common reason for setting up replication is to distribute the load of handling database requests across multiple machines. This will not only help with performance, but will add a bit of redundancy should one of your database servers fail. Your application must be smart enough to know that write requests must be handed off to the master or else your replicated copies will get out of sync with the master. This is nobody's idea of a fun time, so make sure that every place in your code that needs to write to the database only does so to the master copy.

Here are nine easy steps to getting replication running in MySQL 3.23.33 (or later). Before starting, make sure that all of your machines are running the same version of MySQL (preferably, the latest stable version).

  1. Decide which database server will be the ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required