MySQL 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 16, 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.

Multiple-master replication is possible, but raises issues not present in single-master replication. See Section 6.15, “Auto-Increment in Multiple-Master Replication”.

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. Keep in mind as well that updates on the slave side might be affected differently depending on whether you are using statement-based or row-based replication. Consider the following scenario, where a row is inserted on the slave, followed by a statement on the master side that should empty the table:

slave> INSERT INTO tbl VALUES (1);
master> DELETE FROM tbl;

The master doesn't know about the INSERT operation on the slave server. With statement-based replication, tbl will be empty on both master and slave as soon as the slave catches up with the master, because the master sends its DELETE statement to the slave. As a result, tbl has the same contents on both servers. With row-based replication, the effect of the DELETE on the slave is different. The master writes to its binary log each row to be deleted from the table. The slave deletes only those rows, and not the row that was inserted on the slave side. As a result, the table has different contents on the master and server, which may cause replication problems.

For information about row-based replication, see Section 6.3, “Row-Based Replication”.

Replication offers benefits for robustness, speed, and system administration: