Replication - High Performance MySQL - page 472

For example, running an ALTER TABLE statement locks the entire table, blocking reads
and writes to it. This can take a long time and disrupt service. However, the master-
master configuration lets you stop the replication threads on the active server (so it
doesn't process any updates from the passive server), alter the table on the passive
server, switch the roles, and restart replication on the formerly active server. 10 That
server then reads its relay log and executes the same ALTER TABLE statement. Again, this
might take a long time, but it doesn't matter because the server isn't serving any live
queries.
The active-passive master-master topology lets you sidestep many other problems and
limitations in MySQL. There are some toolsets to help with this type of operational
task, too.
Let's see how to configure a master-master pair. Perform these steps on both servers,
so they end up with symmetrical configurations:
1. Ensure that the servers have exactly the same data.
2. Enable binary logging, choose unique server IDs, and add replication accounts.
3. Enable logging replica updates. This is crucial for failover and failback, as we'll see
later.
4. Optionally configure the passive server to be read-only to prevent changes that
might conflict with changes on the active server.
5. Start each server's MySQL instance.
6. Configure each server as a replica of the other, beginning with the newly created
binary log.
Now let's trace what happens when there's a change to the active server. The change
gets written to its binary log and flows through replication to the passive server's relay
log. The passive server executes the query and writes the event to its own binary log,
because you enabled log_slave_updates . The active server then ignores the event,
because the server ID in the event matches its own. See the section “Changing Mas-
ters” on page 489 to learn how to switch roles.
Setting up an active-passive master-master topology is a little like creating a hot spare
in some ways, except that you can use the “spare” to boost performance. You can use
it for read queries, backups, “offline” maintenance, upgrades, and so on—things you
can't do with a true hot spare. However, you cannot use it to gain better write perfor-
mance than you can get with a single server (more about that later).
As we discuss more scenarios and uses for replication, we'll come back to this config-
uration. It is a very important and common replication topology.
10. You can also disable binary logging temporarily with SET SQL_LOG_BIN=0 , instead of stopping replication.
Some commands, such as OPTIMIZE TABLE , also support a LOCAL or NO_WRITE_TO_BINLOG option that prevents
logging. This can allow you to choose your timing more precisely, rather than just letting the ALTER happen
when it occurs in the replication stream.