MySQL Master Master Replication: 4 Easy Steps

on Data Engineering, Data Replication, Database Management Systems, Database Schema Design, MySQL, SQL, Tutorials • January 24th, 2022 • Write for Hevo

MySQL Master Master Replication

Server replication provides redundancy and allows the prevention of data loss. It also increases efficiency at the time of data access. Thus, knowing how to replicate your database is an important skill to have in your arsenal. Master-Slave replication is one of the most popular configurations of database replication. However, it is the MySQL Master Master replication that has proven to be advantageous by enabling read/write operations from multiple servers. 

In this blog post, you will get to know how to perform MySQL Master Master replication. MySQL is one of the most used relational databases and setting up replication is quite simple.

Table of Contents

Prerequisites

Setting up MySQL Master Master Replication will be a lot easier if you’ve gone through the following aspects:

  • An active MySQL account.
  • Working knowledge of MySQL and SQL Commands.
  • Working knowledge of Databases.
  • Clear idea regarding the type of data to be replicated.

What is MySQL?

MySQL Master Master Replication: MySQL Logo | Hevo Data
Image Source

MySQL is one of the most popular open-source relational database management systems.MySQL uses a simple Client-Server Model to assist users in managing Relational Databases, or data stored in rows and columns across tables. It makes use of the well-known query language Structured Query Language (SQL), which enables users to conduct all CRUD (Create, Read, Update, and Delete) actions.

MySQL was first created in 1994 by MySQL AB, a Swedish corporation. Sun Microsystems bought the company in 2008, and Sun Microsystems was eventually bought out by Oracle, a US IT giant. Oracle is now in charge of MySQL’s development and expansion. Despite the fact that MySQL is open-source and freely available to everybody, it contains some premium features that Oracle only makes available to customers who are willing to pay for them.

To know more about MySQL, visit this link.

Download the Ultimate Guide on Database Replication
Download the Ultimate Guide on Database Replication
Download the Ultimate Guide on Database Replication
Learn the 3 ways to replicate databases & which one you should prefer.

Key Features of MySQL

  • MySQL is a simple and easy-to-use database system.
  • MySQL is an open-source relational database management system that is quick, flexible, and scalable.
  • MySQL is a safe database management system since it uses an encrypted password-protected method to connect to the server.
  • MySQL is a cross-platform database management system. It swiftly works with a variety of platforms, including Linux, Solaris, Windows, macOS, Ubuntu, and others.
  • MySQL is a powerful database that makes use of stored procedures and triggers.
  • Because MySQL operates in cluster mode, data backup is simple.
  • MySQL is built on a client-server model. As a result, the client can query and alter data from anywhere using the internet to communicate with the server.

Why do you need MySQL Master Master Replication?

MySQL Master Master replication is a development of master-slave replication that addresses its major flaws. This method of replication requires two or more master nodes that can handle both read and write requests. Additionally, each of your masters can have several slave nodes. Asynchronous replication occurs between master nodes.

Replicate MySQL Data in Minutes using Hevo’s Data Pipelines

Hevo can be your go-to tool if you’re looking for Data Replication and a monopoly over all peripheral interactions of data transfer. It offers compatibility of integrations from 100+ Data Sources (internal link to integration page)(including 40+ Free Data Sources) like MySQL into Redshift, Snowflake, Databricks, and many other databases and warehouses systems. Hevo also supports native connectors for various variants of MySQL like Cloud, on-premise, etc. Hence, Hevo is an all-in-one solution.

Try Hevo if you’re looking for an all-in-one package. Hevo supports MySQL as a destination as well. To further streamline and prepare your data for analysis, you can process and enrich Raw Granular Data using Hevo’s robust & built-in Transformation Layer without writing a single line of code!

With Hevo in place, you can reduce your Data Extraction, Cleaning, Preparation, and Enrichment time & effort by many folds! In addition, Hevo’s native integration with BI & Analytics Tools will empower you to mine your replicated data to get actionable insights.

Sign up here for a 14-Day Free Trial!

Steps to Set Up MySQL Master Master Replication

MySQL Master Master Replication | Hevo Data
Image Source

Now that you have a basic grasp of MySQL, let’s try to understand the procedure to set up MySQL Master Master replication. Below are the steps you can follow to build your first MySQL Master Master replication:

Step 1: Installation and Configuration of MySQL on Server 1

If you do not have a MySQL server and MySQL client installed on your server, you can do that by using the following command:

sudo apt-get install mysql-server mysql-client

The default settings of MySQL only let it accept connections on localhost (127.0.0.1). You need to change these settings to enable replication to work properly. To do this,  you must edit the /etc/mysql/my.cnf file on your server. The following four lines must be changed, which are currently set to the following:

#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
bind-address            = 127.0.0.1

The server-id lets you identify your particular server uniquely, in the replication configuration. The first thing you need to do is uncomment the first line, by deleting the “#” symbol at the beginning. The second line specifies the file which will store the logs for all the changes made to any MySQL database or table.

The third line specifies all the databases that you want to replicate between your servers. You may add multiple databases to this line. In the given example, we will be using a single database named “demo”. Finally, the last line instructs the server to accept connections from the internet (by not listening on 127.0.0.1).

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = demo
# bind-address            = 127.0.0.1

For all these changes to be reflected, you must restart the MySQL service using the following command:

sudo service mysql restart

The next step is to change command-line settings within the MySQL instance. You can access your MySQL user as follows:

mysql -u root -p 

Once you have successfully logged in, you must run certain commands.

First, you must create a pseudo-user that will be used for the replication process. In the example in this post, we take the name of this user as “demouser”. “password” should be replaced with the password you wish to use for replication.

create user 'demouser'@'%' identified by 'password'; 

This user must be granted sufficient permissions to enable replication:

grant replication slave on *.* to 'demouser'@'%'; 


Finally, to finish the initial configuration of Server 1, we must extract some information about the current MySQL instance which will later be provided to Server 2.

The following command can be used to get the required information. Make note of this information, you will require it later::

show master status; 
MySQL Master Master Replication: MySQL Installer | Hevo Data
Image Source

Make sure to note the file and position which you will use in the next step.

Step 2: Installation and Configuration of MySQL on Server 2

You need to set up Server 2 similar to Server 1 by repeating the same steps as above. First, you need to install it, after successful installation edit the my.cnf file in much the same way. The server-id for this server will, however, be set to 2.

server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log
binlog_do_db           = demo
# bind-address            = 127.0.0.1

The next step is to save the file and then restart the MySQL service:

sudo service mysql restart

After the configuration is complete, you must go into the MySQL shell and set some more configuration options.

mysql -u root -p 

The first step as in the case of Server 1 is to create the pseudo-user which will be responsible for the replication. 

create user 'demouser'@'%' identified by 'password'; 

You need to ensure that this user has the required permissions:.

grant replication slave on *.* to ‘demouser’@'%'; 

Now, it is time to create the database you want to replicate.

create database demo; 

The next step uses the information that you would have noted earlier. The following command will allow replication to begin:

slave stop; 
CHANGE MASTER TO MASTER_HOST = '3.3.3.3', MASTER_USER = 'demouser', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107; 
slave start; 

Your values for MASTER_LOG_FILE and MASTER_LOG_POS may be different from what is shown above. You should use the values that “SHOW MASTER STATUS” returns on Server 1.

To enable MySQL master master replication in the other direction (from Server 2 to Server 1), you must make note of the master log file and position.

To get the necessary information, use the following command:

SHOW MASTER STATUS; 

What makes Hevo’s Data Replication Experience Best in Class?

Replicating data can be a tiresome task without the right set of tools. Hevo’s Data Replication & Integration platform empowers you with everything you need to have a smooth Data Collection, Processing, and Replication experience. Our platform has the following in store for you!

  • Exceptional Security: A Fault-tolerant Architecture that ensures Zero Data Loss.
  • Built to Scale: Exceptional Horizontal Scalability with Minimal Latency for Modern-data Needs.
  • Built-in Connectors: Support for 100+ Data Sources, including MySQL, Databases, SaaS Platforms, Files & More. Native Webhooks & REST API Connector available for Custom Sources.
  • Data Transformations: Best-in-class & Native Support for Complex Data Transformation at fingertips. Code & No-code Flexibility ~ designed for everyone.
  • Smooth Schema Mapping: Fully-managed Automated Schema Management for incoming data with the desired destination.
  • Blazing-fast Setup: Straightforward interface for new customers to work on, with minimal setup time.
Get Started with Hevo for Free

Step 3: Completing MySQL Master Master Replication on Server 1

You still need to finish configuring MySQL Master Master replication back on Server 1. Running the following command will replicate all your data from Server 2:

slave stop; 
CHANGE MASTER TO MASTER_HOST = '4.4.4.4', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 107; 
slave start; 

Please be cautious of replacing the parameters with values that you get for your servers (which may differ from the ones used above), including the value of MASTER_PASSWORD. Please use the password you created when setting up the replication user.

The last thing you need to do is just test whether MySQL Master Master replication is working on both servers.

Step 4: Testing MySQL Master Master Replication

Now that you have your configuration in place, all you have to do is test it. For this, you will create a table in your ‘demo’ database on Server 1 and see if it shows up on Server 2. To check its bidirectional functionality, you will then delete this database from Server 2 and ensure that it’s no longer showing up on Server 1.

For the testing process, start by creating the table in your database using the following commands:

use database demo; 
create table dummy (`id` varchar(10)); 

Now, check Server 2 to see if this table shows up.

show tables in demo; 

You should see output similar to the following:

+-------------------+
| Tables_in_demo |
+-------------------+
| dummy             |
+-------------------+
1 row in set (0.00 sec)

Now, delete this dummy table from Server 2 and check Server 1. You can do this by using the following commands:

On Server 2:

DROP TABLE dummy; 

On Server 1:

Empty set (0.00 sec)

If you get the above output, then your MySQL Master Master replication is working just fine! Here you have it. Congratulations!

MySQL Master Master Replication Pros & Cons

Pros

  • You can scale write requests by adding additional master nodes as well as expanding the computational capabilities of a single master node.
  • Because you have many master nodes, failover is semi-automatic. The chances of all master nodes failing at the same time are extremely slim. There is at least one more master node that can handle the requests if one of the master nodes fails.

Cons

  • If one of the master nodes fails, you may lose certain transactions due to asynchronous replication amongst all master nodes.
  • You can’t be guaranteed that backups created on each master node have the same data because of asynchronous replication.
  • In the event that you need to promote a Slave node to a Master node, failover is still not entirely automated.

MySQL Master Master Replication Alternatives

MySQL MGR replication

A MySQL Server plugin implements the MySQL Group Replication feature, which enables replication in a fundamentally new approach based on a distributed state machine architecture.

Group MySQL Replication enables the creation of fault-tolerant systems with redundancy, ensuring that the cluster will remain online even if some of the servers fail (as long as it is not a majority). MGR replication is distinguished by the fact that it has built-in automated recovery and dispute resolution.

MySQL BinLog Replication

Hevo supports MySQL BinLog Replication as a viable alternative for MySQL Master Master Replication. Depending on the database updates being recorded, the information in the binary log is stored in different logging formats. The binary log from the source is read by replicas, and the events in the binary log are executed on the replica’s local database.

Each duplicate obtains a copy of the binary log’s entire contents. The replica is responsible for determining which statements in the binary log should be executed. Unless you specify differently, the replica executes all events in the source’s binary log. You can configure the replica to process only events that pertain to specific databases or tables if necessary.

Each replica keeps track of the binary log coordinates it reads and processes from the source: the file name and position within the file. This means that many replicas can be connected to the source and run various parts of the same binary log at the same time. Individual replicas can be attached and disconnected from the server without disrupting the source’s operation because the replicas control the process. Furthermore, because each replica keeps track of its present place in the binary log, replicas can be detached, rejoin, and restart processing.

You can refer to this for detailed information on MySQL BinLog Replication.

Conclusion

In this post, you have learned how to perform MySQL Master Master replication. It also gave you a brief overview of MySQL. MySQL Master Master Replication provides a backup of your data in case of data loss. However, when it comes to working with replication, you need to be an expert at MySQL to set up servers from scratch and manually configure several details. Moreover, most of the time, the data is not available in the right format and you will need data engineering and MySQL administration skills to transform the data.

Visit our Website to Explore Hevo

Hevo Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. Hevo caters to 100+ data sources (including 40+ free sources) and can seamlessly perform MySQL Replication in real-time. Furthermore, Hevo’s fault-tolerant architecture ensures a consistent and secure replication of your MySQL data. Using Hevo will make your life easier and make Data Replication hassle-free.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share in the comments below your thoughts on MySQL Master Master Replication.

No-Code Data Pipeline for MySQL