Databases Reference
In-Depth Information
Although this works acceptably for many scenarios, it's often impossible to do on a
busy server. It also has the disadvantage of changing the replica's data outside of rep-
lication. Changing a replica's data through replication (by making changes on the mas-
ter) is usually the safest technique, because it avoids nasty race conditions and other
surprises. If the table is very large or network bandwidth is limited, dumping and re-
loading is also prohibitively expensive. What if only every thousandth row in a million-
row table is different? Dumping and reloading the whole table is wasteful in this case.
pt-table-sync
is another tool from Percona Toolkit that solves some of these problems.
It can find and resolve differences between tables efficiently. It can also operate through
replication, resynchronizing the replica by executing queries on the master, so there
are no race conditions. It integrates with the checksum table created by
pt-table-check-
sum
, so it can operate only on chunks of tables that are known to differ. It doesn't work
in all scenarios, though: it requires that replication is running in order to sync a master
and replica correctly, so it won't work when there's a replication error.
pt-table-sync
is
designed to be efficient, but it still might be impractical for extremely large data sizes.
Comparing a terabyte of data on the master and the replica inevitably causes extra work
for both servers. Still, for those cases where it works, it can save you a great deal of time
and effort.
Changing Masters
Sooner or later, you'll need to point a replica at a new master. Maybe you're rotating
servers for an upgrade, maybe there was a failure and you need to promote a replica to
be the master, or maybe you're just reallocating capacity. Regardless of the reason, you
have to inform the replica about its new master.
When the process is planned, it's easy (or at least easier than it is in a crisis). You simply
need to issue the
CHANGE MASTER TO
command on the replica, using the appropriate
values. Most of the values are optional; you can specify just the ones you're changing.
The replica will discard its current configuration and relay logs and begin replicating
from the new master. It will also update the
master.info
file with the new parameters,
so the change will persist across a replica restart.
The hardest part of this process is figuring out the desired position on the new master,
so the replica begins at the same logical position at which it stopped on the old master.
Promoting a replica to a master is a little harder. There are two basic scenarios for
replacing a master with one of its replicas. The first is when it's a planned promotion;
the second is when it's unplanned.
