6.1. Introduction to Replication
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:
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. SELECT queries 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
non-updating 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 5.9.1, “Database Backups”.
