1

I have two mysql/galera clusters of three nodes each (two + garb node).

The clusters are in separate DC's (dc1 and dc2)

The cross cluster replication happens between node1-dc1 and node1-dc2

I want to know how to automatically fail-over in case node1-dc1 goes down to node2-dc1.

I guess another question is: Is automatic fail-over even a good idea if I can script it? Any other recommendations and best practices are also welcome.

This is how replication is set up between node1-dc1 and node1-dc2

1 - On each node add following lines to /etc/my.cnf file and restart mysql service

# vi /etc/my.cnf
server-id=101       # 102 for the remote galera node
log-slave-updates=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=1

2 - Add grant to 'replicant' user on both nodes

> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replicant@'%' IDENTIFIED BY 'password';

3 - Start replication on both nodes, set MASTER_HOST to IP address of the other node

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST = '172.1.1.55', MASTER_PORT = 3306, MASTER_USER = 'replicant', MASTER_PASSWORD = 'password', MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;
John Test
  • 89
  • 1
  • 3
  • 14
  • Letting your application write to both clusters is not a solid recipe for data consistency. Unlike Galera, asynchronous replication has no mechanism for handling concurrent writes that conflict with each other. Tying two clusters together asynchronously like this instead of doing a 2/1 or 3/2 physical split of a single cluster seems trouble-prone. – Michael - sqlbot Jul 29 '15 at 00:28
  • @Michael-sqlbot it doesn't write to both clusters. Only one cluster the other one is a failover cluster. – John Test Jul 29 '15 at 02:30

0 Answers0