In the following section, we provide answers to questions that are most frequently asked about MySQL Replication.
- A.14.1. Must the slave be connected to the master all the time?
- A.14.2. Must I enable networking on my master and slave to enable replication?
- A.14.3. How do I know how late a slave is compared to the master? In other words, how do I know the date of the last statement replicated by the slave?
- A.14.4. How do I force the master to block updates until the slave catches up?
- A.14.5. What issues should I be aware of when setting up two-way replication?
- A.14.6. How can I use replication to improve performance of my system?
- A.14.7. What should I do to prepare client code in my own applications to use performance-enhancing replication?
- A.14.8. When and how much can MySQL replication improve the performance of my system?
- A.14.9. How can I use replication to provide redundancy or high availability?
- A.14.10. How do I tell whether a master server is using statement-based or row-based binary logging format?
- A.14.11. How do I tell a slave to use row-based replication?
- A.14.12. How do I prevent GRANT and REVOKE statements from replicating to slave machines?
- A.14.13. Does replication work on mixed operating systems (for example, the master runs on Linux while slaves run on OS X and Windows)?
- A.14.14. Does replication work on mixed hardware architectures (for example, the master runs on a 64-bit machine while slaves run on 32-bit machines)?
Must the slave be connected to the master all the time?
No, it does not. The slave can go down or stay disconnected for hours or even days, and then reconnect and catch up on updates. For example, you can set up a master/slave relationship over a dial-up link where the link is up only sporadically and for short periods of time. The implication of this is that, at any given time, the slave is not guaranteed to be in synchrony with the master unless you take some special measures.
To ensure that catchup can occur for a slave that has been disconnected, you must not remove binary log files from the master that contain information that has not yet been replicated to the slaves. Asynchronous replication can work only if the slave is able to continue reading the binary log from the point where it last read events.
Must I enable networking on my master and slave to enable replication?
Yes, networking must be enabled on the master and slave. If
networking is not enabled, the slave cannot connect to the
master and transfer the binary log. Verify that the
How do I know how late a slave is compared to the master? In other words, how do I know the date of the last statement replicated by the slave?
When the slave SQL thread executes an event read from the
master, it modifies its own time to the event timestamp. (This
How do I force the master to block updates until the slave catches up?
Use the following procedure:
What issues should I be aware of when setting up two-way replication?
MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. In other words, it is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that makes the update of client A work differently than it did on co-master 1. Thus, when the update of client A makes it to co-master 2, it produces tables that are different from what you have on co-master 1, even after all the updates from co-master 2 have also propagated. This means that you should not chain two servers together in a two-way replication relationship unless you are sure that your updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.
You should also realize that two-way replication actually does not improve performance very much (if at all) as far as updates are concerned. Each server must do the same number of updates, just as you would have a single server do. The only difference is that there is a little less lock contention because the updates originating on another server are serialized in one slave thread. Even this benefit might be offset by network delays.
How can I use replication to improve performance of my system?
Set up one server as the master and direct all writes to it.
Then configure as many slaves as you have the budget and
rackspace for, and distribute the reads among the master and the
slaves. You can also start the slaves with the
What should I do to prepare client code in my own applications to use performance-enhancing replication?
See the guide to using replication as a scale-out solution, Section 17.4.5, “Using Replication for Scale-Out”.
When and how much can MySQL replication improve the performance of my system?
MySQL replication is most beneficial for a system that processes frequent reads and infrequent writes. In theory, by using a single-master/multiple-slave setup, you can scale the system by adding more slaves until you either run out of network bandwidth, or your update load grows to the point that the master cannot handle it.
To determine how many slaves you can use before the added
benefits begin to level out, and how much you can improve
performance of your site, you must know your query patterns, and
determine empirically by benchmarking the relationship between
the throughput for reads and writes on a typical master and a
typical slave. The example here shows a rather simplified
calculation of what you can get with replication for a
hypothetical system. Let
Let's say that system load consists of 10% writes and 90% reads,
and we have determined by benchmarking that
The last equation indicates the maximum number of writes for
This analysis yields the following conclusions:
These computations assume infinite network bandwidth and neglect
several other factors that could be significant on your system.
In many cases, you may not be able to perform a computation
similar to the one just shown that accurately predicts what will
happen on your system if you add
How can I use replication to provide redundancy or high availability?
How you implement redundancy is entirely dependent on your application and circumstances. High-availability solutions (with automatic failover) require active monitoring and either custom scripts or third party tools to provide the failover support from the original MySQL server to the slave.
To handle the process manually, you should be able to switch from a failed master to a pre-configured slave by altering your application to talk to the new server or by adjusting the DNS for the MySQL server from the failed server to the new server.
For more information and some example solutions, see Section 17.4.8, “Switching Masters During Failover”.
How do I tell whether a master server is using statement-based or row-based binary logging format?
Check the value of the
The value shown will be one of
How do I tell a slave to use row-based replication?
Slaves automatically know which format to use.
Start the server with the
Does replication work on mixed operating systems (for example, the master runs on Linux while slaves run on OS X and Windows)?
Does replication work on mixed hardware architectures (for example, the master runs on a 64-bit machine while slaves run on 32-bit machines)?