MySQL database replication

Description

We use mysql replication to mirror mysql databases from a single master mysqld server to a series of slave servers on back end nodes. This process is asynchronous and asymmetrical - back end nodes are read-only.

Configuring the Replication Master server

Two options must be set to enable replication. First, binary logging must be enabled. Second, every mysqld server in the pool needs a unique ID.

These parameters should be set in the my.cnf file (/etc/my.cnf on WormBase servers).

[mysqld]
log-bin=mysql-bin
server-id=1

brie3 (the primary WormBase development machine) acts as the master server, with server-id = 1.

Configuring the Replication Slave servers

The only required configuration for replication slaves is the server-id:

[mysqld]
server-id=2

Binary logging is not required for slaves but can be useful for data recovery and more complicated architectures. I have it off on our read-only slaves for performance.

See WormBase_Infrastructure and Hardware catalog for server IDs.