Databases Reference
In-Depth Information
Because replication offers such important and complex functionality, the server itself
doesn't offer every bell and whistle that you'll need to configure, monitor, administer,
and optimize it. Third-party tools can be a tremendous help. We're biased, but we think
the most notable tools for improving your life with replication are bound to be Percona
Toolkit and Percona XtraBackup. Before you use any other tools, we advise you to
inspect their test suites. If they don't have formal, automated test suites, think hard
before trusting them with your data.
When it comes to replication, your motto should be K.I.S.S.
27
Don't do anything fancy,
such as using replication rings, Blackhole tables, or replication filters, unless you really
need to. Use replication simply to mirror an entire copy of your data, including all
privileges. Keeping your replicas identical to the master in every way will help you avoid
many problems.
Speaking of keeping replicas identical to the master, here's a short list of important
things to do when you use replication:
• Use Percona Toolkit's
pt-table-checksum
to verify that replicas are true copies of
the master.
• Monitor replication to ensure that it's running and isn't lagging behind the master.
• Understand the asynchronous nature of replication, and design your application
to avoid or tolerate reading stale data from replicas.
• Don't write to more than one server in a replication topology. Configure replicas
as read-only, and lock down privileges to prevent changes to data.
• Enable sanity and safety settings as described in this chapter.
As we'll discuss in
Chapter 12
, replication failure is one of the most common reasons
for MySQL downtime. To avoid problems with replication, read that chapter, and try
to put its suggestions into practice. You should also read the replication section of the
MySQL manual thoroughly, and learn how replication works and how to administer
it. If you like reading, the book
MySQL High Availability
by Charles Bell et al. (O'Reilly)
also has useful information about replication internals. But you still need to read the
manual!
27. Keep It Simple, Schwartz! Some of us think that's what K.I.S.S. means, anyway.
