A common high availability (HA) configuration for MySQL is the Master/Master pair. In such a configuration, each side is both a Slave and a Master. The application can write to only one instance at a time (Active/Passive) or to both instances (Active/Active). Assuming you are familiar with MySQL replication configuration and management, you can configure Master-to-Master replication for this purpose.
This section tells you how to make a MySQL Master a Slave to the Xpand system. Prerequisites:
Assuming that Xpand is configured as a Slave to an existing MySQL Master, you can configure MySQL as a Slave to the Xpand system. To prevent undesired "write" actions on the Xpand Slave from being replicated to the Master MySQL instance, put Xpand into a read-only mode by issuing the following command. Any updates coming through the binary log bypass read-only:
slave> SET GLOBAL read_only = true;
You now have an Active/Passive replication topology, with Xpand acting as the Passive (Slave) system.
Before promoting, ensure that the Passive Slave is keeping up with the replication stream from the Active Master. Verify that the Passive Slave is not more than a couple of seconds behind the Active Master by issuing the SHOW SLAVE STATUS command. If the Slave is significantly behind, wait for it to catch up, to reduce the amount of time required for the replication network to quiesce. Verify that the MySQL Active Master is replicating from the Xpand Passive Slave instance.
To quiesce the system and get the Active and Passive systems into a known consistent state, disable writes to the MySQL Active Master by issuing the following command:
master> SET GLOBAL read_only = true;
To determine the active MySQL Master's position, issue the following command:
master> SHOW MASTER STATUS\G
When the two systems display the same position, they are in a consistent and quiescent state. Now you can safely promote either system to the role of Active Master.
To promote a Xpand Passive Slave to Active Master, disable read-only mode on Xpand by issuing the following command:
slave> SET GLOBAL read_only = false;
Next, redirect the application to use the Xpand system as its write target. The Xpand system is now configured as the Active Master. Verify that events are getting replicated to the MySQL server, which is now configured as the Passive Slave.
When Xpand is the Active Master, you can obtain information about all the slaves connected to the master by issuing the following command:
master> SHOW FULL processlist;
To easily identify the slave(s), a best practice is to use user names that end with "_slave".
To promote a Xpand Slave (Passive) to a Master (Active) replication participant, perform the following steps: