What is MySQL replication?
Replication is a feature of MySQL that allows one database server to mirror the data on another database server by executing the same queries. There are more complicated ways of setting it up but basic replication involves one master server and one slave server.
The master server is typically being used to execute reads and writes of the data while the slave (which started with an exact copy of the master’s data) duplicates the writes and thus maintains data that is identical to the master. Replication can be setup on several levels: all databases on a server, selected databases or even selected tables.
Usage Scenarios
Replication can be a very powerful tool in various scenarios:
- When distributing load, you can use multiple slave servers to read data from while only writing to the one master server.
- A slave server can be used as a hot standby in case of master failure.
- Slave servers can be used to perform operations that would typically result in degraded performance on the master, such as executing backups or running large queries for reports.
Advantages and Feature
A major feature of replication is the easy with which it can be implemented in a working environment. Replication is also fairly hands-free when it comes to maintenance (a few typical problems not withstanding, see below).
The simple operation allows for a changing and evolving setup so your replicated database solution can grow with you. Need more power? Add more read-only slaves. This will scale quite well for most solutions and depending on your particular usage. Naturally the more read-heavy your problem, the better use you can make of replicated read-only slaves to scale. Fortunately most database problems are read-heavy.
Another feature of replication is that it is asynchronous, i.e. the master and slave do not need to be constantly connected. When a connection is established, the slave server will “catch up” to the master. This helps make any solution implemented with replication pretty robust as long as one keeps in mind that the slave servers can and do lag behind the master on occasion – as we see in the next paragraph.
Potential Issues
However, replication is not without its share of problems.
For example, in implementing some of the above uses of replication one has to be weary of lag between the master and the slave. Either due to the slave’s “slow” connection speeds, the slower speed of the slave server or high load on the slave server it can start lagging behind the master. This is the result of the asynchronous nature of replication, while a definite and necessary feature, requires that we keep an eye on this potential problem. In normal operation with similarly matches master and slave servers, replication can be considered instantaneous (particularly on fast local networks).
A second drawback of replication can occur when the data on the slave becomes corrupted. A scenario like this can occur when servers are powered off unexpectedly or the server’s hard drives develop problems. While not a common occurrence, if the data is corrupted beyond salvation, the replication process needs to be started from scratch. This requires “pausing” the master server while a snapshot of its data is taken. After this the data is copied over to the slave and replication is restarted from the master’s current position.
Most issues due to interrupted replication are solved fairly quickly by restarting the replication and possibly skipping over the first duplicate record in the binary log. More detail about that can be found in this post.
Conclusion
Replication is a powerful feature of MySQL that is being used daily to provide high scalability and performance while keeping the entry cost low in terms of administration and setup time.