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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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. MySQL – How to Set Maximum Rates for Connections and Queries
  2. How to take Logical Backups on a MySQL Replication Slave using shell script
  3. System Tuning for MySQL Server
  4. Beginners Guide to Management of MySQL Cluster Log Files
  5. What is the meaning of the TRX_TABLES_LOCKED column in the information_schema.INNODB_TRX MySQL table
  6. Configuring mysqld to log slow queries
  7. MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
  8. Understanding MySQL Pluggable Authentication
  9. How to backup and restore MySQL database
  10. How to List and Set SELinux Context for MySQL Server

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright