MySQL Master Master Replication: 4 Easy Steps

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. 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 MySQL Master Master replication is quite simple.

What is MySQL Master Master Replication? 

MySQL Master-Master replication, also known as mirror or active-active replication, is a method where data is copied and synchronized between two servers in real time. This setup allows both servers to act as masters, meaning they can both handle read and write operations, providing redundancy and improving the performance of transactional commits. It’s particularly useful for high availability solutions in web applications and SaaS products. A key advantage of MySQL replication master-master is the ability to perform writes on both servers, which can be beneficial for load balancing.

Configuring MySQL multi master replication requires careful planning to prevent conflicts and ensure data consistency across all master nodes.

Steps to Set Up MySQL Master Master Replication

Now that you have a basic grasp of MySQL master master replication, let’s try to understand the procedure for its set up. 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
• Step 2: Installation and Configuration of MySQL on Server 2
• Step 3: Completing MySQL Master Master Replication on Server 1
• Step 4: Testing 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; 

Make sure to note the file and position which you will use in the next Master Master Replication MySQL 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 Master Master Replication 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; 

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 MySQL Replication Master Master 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.

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. The first step in how to setup master slave replication in MySQL involves configuring the master server to log changes that the slave later replicates.

Note: A common challenge with mysql database replication master slave is handling failover and ensuring the slave database can take over without data loss. Also, MySQL master master replication existing data requires a thorough review of the current database schema to ensure compatibility with the replication setup.

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.

Learn more on MySQL BinLog Replication.

Before wrapping up, let’s look at the basics of MySQL.

What is MySQL?

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 about Oracle to MySQL migration, visit this link.

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.

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.

MySQL master master replication setup is a complex process that involves careful planning and execution to ensure data consistency across servers. 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 150+ 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.