A.13.13:
Does replication work on mixed operating systems (for example,
the master runs on Linux while slaves run on OS X and Windows)?

A.13.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)?

Questions and Answers

A.13.1:
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.

A.13.2:
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. Check that the
skip-networking option has not
been enabled in the configuration file for either server.

A.13.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?

When the slave SQL thread executes an event read from the
master, it modifies its own time to the event timestamp. (This
is why TIMESTAMP is well
replicated.) In the Time column in the output
of SHOW PROCESSLIST, the number
of seconds displayed for the slave SQL thread is the number of
seconds between the timestamp of the last replicated event and
the real time of the slave machine. You can use this to
determine the date of the last replicated event. Note that if
your slave has been disconnected from the master for one hour,
and then reconnects, you may immediately see large
Time values such as 3600 for the slave SQL
thread in SHOW PROCESSLIST. This
is because the slave is executing statements that are one hour
old. See Section 17.2.1, “Replication Implementation Details”.

A.13.4:
How do I force the master to block updates until the slave
catches up?

Use the following procedure:

On the master, execute these statements:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

Record the replication coordinates (the current binary log
file name and position) from the output of the
SHOW statement.

On the slave, issue the following statement, where the
arguments to the
MASTER_POS_WAIT() function
are the replication coordinate values obtained in the
previous step:

mysql> SELECT MASTER_POS_WAIT('log_name', log_pos);

The SELECT statement blocks
until the slave reaches the specified log file and position.
At that point, the slave is in synchrony with the master and
the statement returns.

On the master, issue the following statement to enable the
master to begin processing updates again:

mysql> UNLOCK TABLES;

A.13.5:
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.

A.13.6:
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
--skip-innodb,
--low-priority-updates, and
--delay-key-write=ALL options to
get speed improvements on the slave end. In this case, the slave
uses nontransactional MyISAM tables instead
of InnoDB tables to get more speed by
eliminating transactional overhead.

A.13.7:
What should I do to prepare client code in my own applications
to use performance-enhancing replication?

A.13.8:
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 reads and
writes denote the number of reads and writes
per second, respectively.

Let's say that system load consists of 10% writes and 90% reads,
and we have determined by benchmarking that
reads is 1200 - 2 *
writes. In other words, the system can do
1,200 reads per second with no writes, the average write is
twice as slow as the average read, and the relationship is
linear. Suppose that the master and each slave have the same
capacity, and that we have one master and
N slaves. Then we have for each
server (master or slave):

The last equation indicates the maximum number of writes for
N slaves, given a maximum possible
read rate of 1,200 per second and a ratio of nine reads per
write.

This analysis yields the following conclusions:

If N = 0 (which means we have no
replication), our system can handle about 1200/11 = 109
writes per second.

If N = 1, we get up to 184 writes
per second.

If N = 8, we get up to 400 writes
per second.

If N = 17, we get up to 480
writes per second.

Eventually, as N approaches
infinity (and our budget negative infinity), we can get very
close to 600 writes per second, increasing system throughput
about 5.5 times. However, with only eight servers, we
increase it nearly four times.

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 N
replication slaves. However, answering the following questions
should help you decide whether and by how much replication will
improve the performance of your system:

What is the read/write ratio on your system?

How much more write load can one server handle if you reduce
the reads?

For how many slaves do you have bandwidth available on your
network?

A.13.9:
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.

The value shown will be one of STATEMENT,
ROW, or MIXED. For
MIXED mode, statement-based logging is used
by default but replication switches automatically to row-based
logging under certain conditions, such as unsafe statements. For
information about when this may occur, see
Section 5.2.4.3, “Mixed Binary Logging Format”.

A.13.11:
How do I tell a slave to use row-based replication?

Slaves automatically know which format to use.

A.13.12:
How do I prevent GRANT and
REVOKE statements from
replicating to slave machines?