Databases Reference
In-Depth Information
You can also use the distribution master for other purposes, such as applying filters
and rewrite rules to the binary log events. This is much more efficient than repeating
the logging, rewriting, and filtering on each replica.
If you use Blackhole tables on the distribution master, it will be able to serve more
replicas than it could otherwise. The distribution master will execute the queries, but
the queries will be extremely cheap, because the Blackhole tables will not have any
data. The drawback of Blackhole tables is that they have bugs, such as forgetting to put
autoincrementing IDs into their binary logs in some circumstances, so be very careful
with Blackhole tables if you use them.
12
A common question is how to ensure that all tables on the distribution master use the
Blackhole storage engine. What if someone creates a new table on the master and
specifies a different storage engine? Indeed, the same issue arises whenever you want
to use a different storage engine on a replica. The usual solution is to set the server's
storage_engine
option:
storage_engine = blackhole
This will affect only
CREATE TABLE
statements that don't specify a storage engine ex-
plicitly. If you have an existing application that you can't control, this topology might
be fragile. You can disable InnoDB and make tables fall back to MyISAM with the
skip_innodb
option, but you can't disable the MyISAM or Memory engines.
The other major drawback is the difficulty of replacing the master with one of the
(ultimate) replicas. It's hard to promote one of the replicas into its place, because the
intermediate master ensures that they will almost always have different binary log co-
ordinates than the original master does.
13
Tree or Pyramid
If you're replicating a master to a very large number of replicas—whether you're dis-
tributing data geographically or just trying to build in more read capacity—it can be
more manageable to use a pyramid design, as illustrated in
Figure 10-12
.
The advantage of this design is that it eases the load on the master, just as the distri-
bution master did in the previous section. The disadvantage is that any failure in an
intermediate level will affect multiple servers, which wouldn't happen if the replicas
were each attached to the master directly. Also, the more intermediate levels you have,
the harder and more complicated it is to handle failures.
12. See MySQL bugs 35178 and 62829 for starters. In general, anytime you use a nonstandard storage engine
or feature, it can be a good idea to look for open and closed bugs affecting it.
13. You can use Percona Toolkit's
pt-heartbeat
to create a crude global transaction ID to help with this. It
makes it much easier to find binary log positions on various servers, because the heartbeat table itself has
the approximate binary log positions in it.
