• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Configure 2-way replication in an existing Master, Slave MySQL Replication environment

By admin

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.

Filed Under: mysql, MySQL Cluster

Some more articles you might also be interested in …

  1. “Another MySQL daemon already running with the same unix socket” – error while starting MYSQL
  2. What is the purpose of “mysql.sys@localhost” user
  3. MySQL Shell: Using External Python Modules
  4. MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE
  5. MySQL : How To Find the Slowest Queries
  6. Understanding mysqlcheck and myisamchk utilities
  7. How to Add New Nodes To an Existing MySQL Cluster Setup
  8. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  9. Understanding MySQL Privileges
  10. How to Create a MySQL Docker Container for Testing

You May Also Like

Primary Sidebar

Recent Posts

  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • How to Capture More Logs in /var/log/dmesg for CentOS/RHEL
  • Unable to Start RDMA Services on CentOS/RHEL 7
  • How to rename a KVM VM with virsh
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary