- Documentation Library
- Table of Contents
- MySQL 5.5 Manual
- MySQL 5.4 Manual
- MySQL 5.1 Manual
- MySQL 5.0 Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 14 Replication
- 14.1 Introduction to Replication
- 14.2 Replication Implementation Overview
- 14.3 Replication Implementation Details
- 14.4 How to Set Up Replication
- 14.5 Replication Compatibility Between MySQL Versions
- 14.6 Upgrading a Replication Setup
- 14.7 Replication Features and Known Problems
- 14.8 Replication and Binary Logging Options and Variables
- 14.9 How Servers Evaluate Replication Filtering Rules
- 14.10 Replication FAQ
- 14.11 Troubleshooting Replication
- 14.12 How to Report Replication Bugs or Problems
MySQL 3.23.15 and up features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster (see Chapter 15, MySQL Cluster).
In single-master replication, the master server writes updates to its binary log files and maintains an index of those files to keep track of log rotation. The binary log files serve as a record of updates to be sent to any slave servers. When a slave connects to its master, it informs the master of the position up to which the slave read the logs at its last successful update. The slave receives any updates that have taken place since that time, and then blocks and waits for the master to notify it of new updates.
A slave server can itself serve as a master if you want to set up chained replication servers.
When you are using replication, all updates to the tables that are replicated should be performed on the master server. Otherwise, you must always be careful to avoid conflicts between updates that users make to tables on the master and updates that they make to tables on the slave.
Replication offers benefits for robustness, speed, and system administration:
Robustness is increased with a master/slave setup. In the event of problems with the master, you can switch to the slave as a backup.
Better response time for clients can be achieved by splitting the load for processing client queries between the master and slave servers.
SELECTqueries may be sent to the slave to reduce the query processing load of the master. Statements that modify data should still be sent to the master so that the master and slave do not get out of synchrony. This load-balancing strategy is effective if nonupdating queries dominate, but that is the normal case.Another benefit of using replication is that you can perform database backups using a slave server without disturbing the master. The master continues to process updates while the backup is being made. See Section 6.2, “Database Backup Methods”.
User Comments
As said above, "A slave server can also serve as a master if you want to set up chained replication servers." If you want to do that, it is not sufficient to just switch on binary logging in the slave server, you additionally have to use the "log-slave-updates" option. This is described in more detail here: http://dev.mysql.com/doc/mysql/en/Replication_Options.html
With mysql 5.0 and 5.1 there is a way to make two-way replication, where you can send updates to any node (all of them acts as masters). This is described in the article Advanced MySQL Replication Techniques: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
Add your own comment.