MySQL is no doubt one of the most used database management system in this world. The first rank goes to Oracle, but unlike Oracle, MySQL is open source. It means that individual developers can help build and maintain MySQL so that everyone can simply use it for free. One of the best features of MySQL is replication. In this guide, I will demonstrate How to setup master-master replication in MySQL.
MySQL powers millions of databases around the world. Even the most popular Content Management System uses MySQL to manage data, Yes, WordPress! It is because MySQL allows you to scale your database quickly. In this world, full of data, the databases grow very quickly and it’s the job of database management system to keep up with ever growing data.
One method to keep your database up and running is to divide the load between two or more servers containing the same data. The method is called replication.
So, What is special about master-master replication? And why not MySQL master-slave replication? It is because, In case of master-master replication, we can run write queries on both the servers. While in case of master-slave replication, we can only run write queries on Master server. It means that in Master-master replication, both the servers will keep each other up to date, while in case of master-slave replication, we can write data on master server only.
That’s the basic difference. To create a master-slave replication, you can follow our guide linked above. In this guide, we will set up master-master replication. Let’s see the hostnames and IP addresses we will use to identify two servers to make it easier to understand.
- Server 1
Hostname: master1
IP Address: 1.2.3.4 - Server 2
Hostname: master2
IP Address: 2.3.4.5
So, Let’s start with the first step.
Installing MySQL on Master2
Considering we have data on master1, we will start fresh with the master2. Our goal is to copy all the data from master1 to master2 and then enable the replication on both the servers so that both the servers can sync data from each other.
Installing MySQL is not so hard. You just need a root access or sudo privileges to install MySQL. Execute the following bunch of commands to install MySQL on master2. If you have already installed MySQL on master2, you can simply skip this step.
sudouser@master2:~$ sudo apt-get update sudouser@master2:~$ sudo apt-get install mysql-server mysql-client -y
Once done, execute the following command to secure the MySQL installation.
sudouser@master2:~$ sudo mysql_secure_installation
It will ask you a few questions. Answer it according to your requirements. But we recommend you to turn on the Validate password plugin to keep a minimum standard on strong passwords. And then answer all the questions with “Y” to secure your server by removing anonymous users, reloading privileges, deactivating remote access.
Once the master2 is configured, we can start the process for replication.
Configure the Master1 Server
In this step, we will update the main MySQL configuration file of the master1 and create a backup of the database we want to replicate. It is easy enough. We will first update the main configuration file.
UPDATE THE CONFIGURATION FILE
In Ubuntu operating system, the MySQL configuration file is located at /etc/mysql/mysql.conf.d/mysqld.cnf. Execute the following command to open the configuration file in edit mode.
sudouser@master1:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Now, Find a line containing bind-address and replace the value 127.0.0.1 with the actual IP address of the server. The updated line should look like the following.
bind-address = 1.2.3.4
Then, find a line containing server-id and uncomment the line. By default, the server id will be 1. We do not have to update the ID of this server, so just uncomment the line and move on to the next line that starts with log_bin. Uncomment this line too.
After two lines, the third line will start with binlog_do_db that you have to uncomment and enter the name of the database as value. The whole block of configuration should look like the following.
server-id = 1 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = test1
Do not forget to replace the name of the database in binlog_do_db directive. It should contain the name of the database you want to replicate. Once done, press CTRL+X followed by Y followed by Enter key to save the file.
Now, restart the MySQL server to apply the changes.
sudouser@master1:~$ sudo service mysql restart
Once done, we can create a replication user on our MySQL server.
CREATE A REPLICATION USER AND A BACKUP
We have to create a user that can access our master1 server from our master2 server to replicate the data. Login to the MySQL server using the following command first.
sudouser@master1:~$ mysql -uroot -p;
Enter the correct password to login. After you are logged in, execute the following command in MySQL command line interface to create a user for replication.
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl_user’@’2.3.4.5‘ IDENTIFIED BY ‘password‘;
Do not forget to replace the IP address and password. Note that the IP address used as a host for user is the IP address of the master2 server. Now, select a database you want to replicate, lock the tables to avoid any changes and then see the current master status. Execute the following bunch of commands to perform these tasks.
mysql> use test1; mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS;
You will see a table just like this one.
+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 455 | test1 | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
Note down the position. It is important. Now, run the exit command to log out of the MySQL CLI and then run the following command to export the database.
sudouser@master1:~$ mysqldump -uroot -p test1 > database.sql
Now, Log in to MySQL CLI again and unlock the tables to allow writing data. Execute the following commands on MySQL CLI.
mysql> UNLOCK TABLES;
Then, run the exit command to exit the MySQL server. Now, Let’s set up the master2 server.
Configure master2 server
The configuration is very similar. We have to perform the same steps but there are few variations. Let’s see how to configure our master2 server.
UPDATE THE CONFIGURATION FILE
In a new terminal window, connect to the master2 server as a root user or a user having sudo privileges. Once you are connected, we have to update the configuration file first. Execute the following command to open MySQL configuration file in edit mode.
sudouser@master2:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Similar to the master1, update the line containing bind-address directive and enter the public IP address of master2 as value. The updated line should look like this.
bind-address = 2.3.4.5
Now, Similarly, update the rest of the lines just like master1. The only difference should be the ID of the server. The updated directives should look like the following.
server-id = 2 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = test1
Now, execute the following command to restart the MySQL service.
sudouser@master2:~$ sudo service mysql restart
Now, Let’s pull the backup we created in master1 to master2 server.
IMPORT THE DATABASE
Execute the following command to pull the backup from Master1 server to Master2 server.
sudouser@master2:~$ scp sudouser@master1:~/database.sql ./
Now, Let’s create and import the database. Login to MySQL server and execute the following command to create a database.
mysql> CREATE DATABASE test1;
Exit from MySQL command line interface and execute the following command to import data into the database.
sudouser@master2:~$ mysql -uroot -p test1 < database.sql
Once the data is imported to the server, we can move on to create a replication user for Master1 and mark down the log position.
CREATE REPLICATION USER AND MARK LOG POSITION
Log in to MySQL server and execute the following command to create a replication user on Master2 server.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'1.2.3.4' IDENTIFIED BY 'password';
Note that the IP address used as a host for this replication user is the IP address of the master1 server. Now, Execute the following command just like we did on the master1 server and mark the log position.
mysql> SHOW MASTER STATUS;
The output will be the same as it was on the master1 server. However, the log position might differ. Please mark the log position as we will use it in the next step.
Start Replication
The only thing left to do is to start the replication. As we now have the same databases on both the servers and both the servers has users with replication privileges, we can start replication. We will first start replication on master2 as we might already have new data to replicate on master1 server.
START REPLICATION ON MASTER2
To start replication on master2, we first have to stop the save, change the master and start the slave again. So, Execute the following commands in MySQL server of master2.
mysql> STOP SLAVE; mysql> CHANGE MASTER TO master_host='1.2.3.4', master_port=3306, master_user='repl_user', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=455; mysql> START SLAVE;
Note that the IP address we used in master_host is the IP address of the master1 server. And enter the correct master_log_file and master_log_position we recorded in Master1 server.
Then, the third command will start the slave and it will start replicating new data from master1 server. Now, Let’s do the same for Master1 server.
START REPLICATION ON MASTER1
Now we are going to start replication on Master1 server. It’s the same as we did on master2 server. Execute the following commands in MySQL server of master1.
mysql> STOP SLAVE; mysql> CHANGE MASTER TO master_host='2.3.4.5', master_port=3306, master_user='repl_user', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=1426; mysql> START SLAVE;
That’s all! You have successfully set up master-master replication in MySQL.