MySQL Replication Walkthru

dataflow, howto Add comments

This walkthru shows my steps to configure trivial replication using MySQL’s standard capability on a pair of servers.

Replication and HA are often difficult to configure and maintain in the little nuances and corner-cases. It’s so easy to make a mistake that doesn’t show until it’s time ot bring the backup online, or until a logfile slowly fills up the disk and chokes the server 212 days later. That said, parallel servers bring in a complexity of ACID/idempotent actions, split-brain issues, load-balancing, and at the core, one server remains the “master” for a very small delta between “master” and “subordinate”.

The problem with active/passive is that you can only reap 50% of your investment costs, and the service retains a limitation in overall throughput. Eventually — even through organic growth — the service will be unable to maintain the same responsiveness to queries while handing the continual updates and inserts. At some point, there needs to be a split.

As opposed to going back to parallelism, a PRM/SRM relationship can be used. A “Primary Replication Manager” (PRM, we used to call it “master”) is used to accept updates and offer serialized (ie: logical clock, ordered, not as in “Java Serialization”, “persistence”, and “hibernation”) updates to a number of “Secondary Replication Managers” (SRM). This is a process exploited in DNS most commonly, but I’ve configured 8 SRMs on a single LDAP PRM (Hi, Phorm), 4 Backup Domain Controllers in a single (hidden) Primary Domain Controllers on Windows NT-3.51, and my own NIS secondaries from a MySQL-fed NIS primary at Connected Networks, an ISP we used to own. The complexity is shifted from the maintenance to a the initial setup, which (in MySQL, LDAP, and NIS) can be done as a “smart postinstall trigger” approach to reduce the risk of human error.

The MySQL case is a bit more difficult, so here’s my walkthru, mostly to remind myself, but maybe someone else will benefit.

In my case, I wanted to evaluate the feasibility and difference in performance — in very crude numbers — when replication is configured.

My walkthru features the following players:

PRM: Sleepy
SRM: Doc
MySQL: 5.1.41-enterprise-commercial-pro
OS: WIndows 2008R2

I’ve cut this into separate articles not because I have some advertising quota to make, simply because that makes it bite-sized actions that a SysAdmin can take while fighting fires and repairing the Dell Retractible Drink Holder labeled “DVD” on some desktop systems.

  1. Enable Network Access
  2. Enable Replication on the Primary
  3. Make a Remote Backup
  4. ), we can activate the secondary server.
  5. Activate the Secondary Server

For a detailed example for the Replication Eval done in Scotts Valley on 2010-10-08, the example walkthru is online on the VI blog.

Leave a Reply

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in