This post applies both to Server and Cluster environments, as cluster replication uses the MySQL Server / API node to facilitate the replication. There are minor config differences between the two databases, however, the core replication configuration is similar.
The main requirement is that an existing M/S (Master/Slave) replication channel exists and is functioning nominally in the environment, which will then be extended to M/M (Master/Master). Configuring the existing replication SLAVE (now called the Secondary node) to act as a master for 2-way replication requires an understanding of the following;
1. It is much simpler to configure 2-way replication if the Secondary is 100% read-only ( and has been since its inception ) and is identical to the Master/Primary.
2. Binary logging needs to have been enabled prior to any instance-local (i.e. tables which exist only on the Secondary) DML made on the Secondary, and those binary logs still exist for reading.
If neither of these conditions applies, then any changes made to the Secondary will not be reflected on the Primary ( unless a full backup and restore is enacted or replication filters are used ) prior to enabling 2way replication.
3. If filtered replication is in effect or filtered 2-way replication is required, then proceed with caution. It is still possible to have local-only Schemas/tables on either the Secondary/Primary which aren’t intended to be replicated, however, this will require a more complex replication filter environment to be configured. It is possible but adds risk and complexity if not performed correctly.
Example
1-way Replication READ ONLY Slave
SLAVE is and has been read-only. It has had NO writes directly to it from clients.
MySQL Server
1. Ensure the SECONDARY meets the requirements for replication
2. Optionally issue a RESET MASTER on the SECONDARY node. This is mostly for clarity as it will reset the binary logs to 000001 so that they are cleaner to view and follow.
3. Issue a SHOW MASTER on the SECONDARY
4. On the PRIMARY, issue a CHANGE MASTER command using the values for the variables from 3
No GTID
mysql> change master to master_host='SECONDARY', master_port=PORT ,master_file_name='$master_file_name', master_file_pos=$master_file_pos; mysql> start slave user='repl' password='$password';
With GTID
mysql> change master to master_host='SECONDARY', master_port=PORT , master_auto_position=1; mysql> start slave user='repl' password='$password';
5. execute Start SLAVE;
MySQL Cluster
1. Identify the API node on the SECONDARY cluster from which replication to the PRIMARY cluster will connect. Ensure it meets the requirements for replication. Restart the API node if necessary to enact the changes.
2. Issue a RESET MASTER on this SECONDARY API node. This is mostly for clarity as it will reset the binary logs to 000001 so that they are cleaner to view and follow.
3. Issue a SHOW MASTER on this node.
4. On the PRIMARY cluster, on the MySQL Server / API node which will act as the SLAVE of the SECONDARY cluster, issue a change master to with the master_file_name, master_file_pos coordinates from 3
mysql> change master to master_host='SECONDARY', master_port=PORT ,master_file_name='$master_file_name', master_file_pos=$master_file_pos; mysql> start slave user='repl' password='$password';
5. Execute start slave.
Variation
Whereby schemas/tables exist only on the SLAVE and are to be included in the 2-way replication.
If the current SLAVE is replicating a set of schemas/tables and only this set is required to be included in the Multi-master replication set, configuration changes are required to filter these schemas/tables, but replication can be configured as per previous steps. The recommendation in configuring 2-way replication from an existing 1-way environment is almost always to migrate the SLAVE only tables to the MASTER and to take a clean full copy of the Master to create the Slave. 2-way replication can then proceed via the steps previously outlined in this post above.