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.

Configure 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.

Configure 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.

Create a replication user

Create a specific user for replication:

brie3> mysql -u root -p
mysql> GRANT REPLICATION SLAVE on *.* to 'replication'@'%.cshl.edu' IDENTIFIED BY 'slavepass';

Get the current status of the master

brie3 mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      597 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Replicate existing data to one slave

First, we need to replicate existing data from the master onto one slave. Unfortunately, this has to be done piecemeal since we have so little disk space on brie3. From there, we will replicate the slave configuration onto other nodes.

brie3> mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;

In a separate session:
shell> mysqldump --user root --pass --databases elegans_WS190 > ~/brie3dump.db

then
mysql> UNLOCK TABLES;

Databases:

elegans_WS190
elegans_gmap_WS190
elegans_pmap_WS190
autocomplete_WS190
brenneri_4
briggsae_CB25_2007
briggsae_WS190
brugia_bma1
japonica_3
nbrowse_wormbase_WS185
remanei_WS190
remanei_nGASP
remanei_2005

Now import the data into a single primary slave (currently aceserver).

slave> mysql < elegans_gmap_WS190.db

Change the master configuration on slaves

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='brie3.cshl.edu',
    ->     MASTER_USER='replication',
    ->     MASTER_PASSWORD='nematoda',
    ->     MASTER_LOG_FILE='mysql-bin',
    ->     MASTER_LOG_POS=597;