— 3 min read

Contents

Quick introduction

My employers presented me with a challenge this week. The task was not difficult in the end but to me it was an untried concept involving MySQL.

I have never been a fan of MySQL and generally turn my nose at the thought of using it, let alone replicating it etc.

The task in question? Master -> Master -> Slave -> Slave replication.

From this point forward I will expect you to have MySQL installed and set-up as normal.

  • Master 1 will be known as Master 1 and Slave 2 with IP 10.1.1.1
  • Master 2 will be known as Master 2 and Slave 1 with IP 10.1.1.2
  • Slave 1 will be known as Slave 3 with IP 10.1.1.3
  • and Slave 2 will be known as Slave 4 with IP 10.1.1.4

Master 1

Modify your MySQL config file, usually named my.cnf or mysql.cnf

Add the following lines to [mysqld]

[mysqld]
server-id=1
auto_increment_offset=1
auto_increment_increment=2

log-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
log-slave-updates

Save and close.

You should note that I have included auto_increment_offset*and *auto_increment_increment. auto_increment_offset is the same as server-id in my case, it does as the name suggests - offsets the auto increment value. auto_increment_increment should be set to the number of servers you have as masters, in this example we have 2.

Open up a MySQL prompt and run the following query

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.1.1.2' IDENTIFIED BY 'password';

Now restart MySQL.

Master 2 (Slave 1)

Modify your MySQL config file.

Add the following in [mysqld]

[mysqld]
server-id=2
auto_increment_offset=2
auto_increment_increment=2

log-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
log-slave-updates

master-host = 10.1.1.1
master-user = replication
master-password = password
master-port = 3306

Save and restart MySQL.

Now open a MySQL prompt and run the following queries

START SLAVE;
SHOW SLAVE STATUS\G;

Slave_IO_Running and Slave_SQL_Running must be set to Yes.

Master 1 (Slave 2)

Open a MySQL prompt and run the following query

SHOW MASTER STATUS;

You should see a master record has been created.

Now we need to configure Master 1 to run as Slave 2.

Modify MySQL config and add the following lines to [mysqld]

[mysqld]
master-host = 10.1.1.2
master-user = replication
master-password = password
master-port = 3306

Save and restart MySQL.

Master 2

Open a MySQL prompt and run the following query

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.1.1.1' IDENTIFIED BY 'password';

Master 1

Open a MySQL prompt and run the following queries

START SLAVE;
SHOW SLAVE STATUS\G;

Slave_IO_Running and Slave_SQL_Running must be set to Yes.

Slave 3 and Slave 4

Now that you have Master - Master replication set up it’s time to attach the slaves.

I am going to do the following

  • make Slave 3 slave of Master 1
  • and Slave 4 a slave of Master 2.

Master 1

Open a MySQL prompt and run the following query

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.1.1.3' IDENTIFIED BY 'password';

Master 2

Open a MySQL prompt and run the following query

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.1.1.4' IDENTIFIED BY 'password';

Slave 3

Open your MySQL config file, under [mysqld] put the following

[mysqld]
server-id=3

master-host = 10.1.1.1
master-user = replication
master-password = password
master-port = 3306

Save and restart MySQL.

Open a MySQL prompt and run the following queries

START SLAVE;
SHOW SLAVE STATUS\G;

Slave_IO_Running and Slave_SQL_Running must be set to Yes.

Slave 4

Open your MySQL config file, under [mysqld] put the following

[mysql]
server-id=4

master-host = 10.1.1.2
master-user = replication
master-password = password
master-port = 3306

Save and restart MySQL.

Open a MySQL prompt and run the following queries

START SLAVE;
SHOW SLAVE STATUS\G;

Slave_IO_Running and Slave_SQL_Running must be set to Yes.

Kura

Anarchist. Pessimist. Bipolar. Hacker. Hyperpolyglot. Musician. Ex-(semi-)pro gamer. They/Them.

Kura
View Source