First of all, I dislike your setup. When Server B fails, the Server C will not be in replication anymore. If you want to be safe, that the data is on every server in sync, you should try to use a cluster. A cluster is minimum 3 servers and would fit your environment.
Next question for me is, why don't you use just one master (Server A) and two slaves (B+C)?
To answer your question, you need to activate the relay log, so that all changes to the slave are stored for the other slaves:
# REPLICATION slave to other slaves #
log-slave-updates = 1
relay-log = /var/lib/mysql/relay-bin
slave-net-timeout = 60
I think you what you are describing is a replication topology called a Star Topology
I wrote about that before
In that event, the Central Server must serve as a Slave in MySQL Replication paradigm until all changes in the binary logs of the Master (outside server) are pulled and executed in the Central Server.
If you pull the plug, replication breaks. It will lose its place.
MySQL does have a setting to control this in the CHANGE MASTER TO command. By default, the retry interval for MySQL 5.6 is 86400 seconds. That's 24 hours. Before MySQL 5.6, it was 60 seconds. To change the retry interval simply run,
STOP SLAVE;
CHANGE MASTER TO MASTER_CONNECT_RETRY = 60;
START SLAVE:
CAVEAT
Even if you pull the plug, it is possible for replication to lose its place and not reconnect because of incomplete transmission of binlog events. When that happens, you must reset the replication coordinates manually.
Here is how to fix replication manually in this regard:
Step 1: STOP SLAVE;
Step 2: SHOW SLAVE STATUS\G
Suppose the display looks like this
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.48.20.253
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000255
Read_Master_Log_Pos: 858190247
Relay_Log_File: relay-bin.066069
Relay_Log_Pos: 873918
Relay_Master_Log_File: mysql-bin.000254
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 858190247
Relay_Log_Space: 873772
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
Step 4: Record Relay_Master_Log_File and Exec_Master_Log_Pos
In this case, 'mysql-bin.000254'
and 858190247
Step 5: Use Replication Coordinates from Step 4
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000254',MASTER_LOG_POS=858190247;
Step 6: Start Replication
START SLAVE;
Give it a Try !!!
Best Answer
You can use master-master replication which consists of two master-slave configurations: One master-slave where node 1 is master and node 2 is slave, and one where it's the other way around.
There are a couple of caveats with this, and it's a bit fragile: You need to set
auto_increment_increment
to 2 on both nodes and setauto_increment_offset
to 1 on the one node and 2 on the other, IIRC. This way, you avoidINSERT
collisions (assuming the tables useauto_increment
). Here's an article that might be useful: MariaDB master/master GTID based replication with keepalived VIPThere is another solution called multi-master replication, and with MariaDB that can be achieved with MariaDB Galera Cluster. This is a more robust solution, although it does have some limitations - note e.g. that it only really works with the InnoDB storage engine. You would usually want to have an odd number of nodes (typically 3) to have quorum amongst the nodes in the event of a network split, although you can simulate this with 2 normal nodes and one Galera arbitrator.