MySQL Master Master Replication: 4 Easy Steps

on Data Engineering, Data Replication, Database Management Systems, Database Schema Design, MySQL, SQL, Tutorials • August 19th, 2020 • Write for Hevo

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.

Introduction to MySQL

MySQL Logo
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.

Simplify Integrations Using Hevo’s No-code Data Pipeline

Hevo Data helps you directly transfer data from MySQL, and 100+ data sources (including 30+ free sources) to Business Intelligence tools, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo takes care of all your data preprocessing needs required to set up the integration and lets you focus on key business activities and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination.

Get Started with Hevo for Free

Check out what makes Hevo amazing:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

Steps to Set Up MySQL Master Master Replication

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; 

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; 

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:

mysql master master replication
Image Source

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!

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. But, if you want to backup your data in a data warehouse, then you can try Hevo.

Visit our Website to Explore Hevo

Businesses can use automated platforms like Hevo Data to set the integration and handle the ETL process. It helps you directly transfer data from MySQL or other data sources of your choice to a Data Warehouse, Business Intelligence tools, or any other desired destination in a fully automated and secure manner without having to write any code and will provide you a hassle-free experience.

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