Database Replication in MySQL
May 18, 2004
An introduction to replication
Recently, while having the knots pounded out of my body during a particularly painful shiatsu lesson, I reflected on what put them there in the first place. Yes, 'the database' was once more to blame. A busy database I work with saw one of its tables jump from 3GB to 7GB overnight, as we imported archive data. As expected, this had some performance impact. Unfortunately, I had not expected quite the knock, and it turned out that this single database server could no longer handle the load. No matter how much more I tried to optimize the queries, tweak the variables or bump up the query cache, it was not enough. The machine could not take any more memory, and a hardware upgrade would do little good (at least with the kind of budget I have to play with). However, MySQL does not claim to be enterprise-ready for nothing, and Yahoo and other high-volume users of MySQL certainly do not run on one database server. There are a number of techniques to handle high volumes, one of which I will introduce this month - MySQL replication (I will look at others in future articles).
Replication allows you to take one database, make an exact copy of it on another server, and set one of them (the slave) to take all its updates from the other (the master). The slave reads the master's binary logs, which store all statements that change a database, and repeats these on its database, keeping the two in exact sync. Since a replicating database simply repeats statements, the databases are not necessarily exactly in sync, and advanced users can take advantage of this. That is a topic for another article however, and we will look at simple replication this month - getting one database to be an exact copy of another one.
What replication is not
How to start replicating - the master server