Configure Master-Master MySQL Database Replication

What is MySQL Master-Master Replication?

MySQL Master-Master replication adds speed and redundancy for active websites. With replication, two separate MySQL servers act as a cluster. Database clustering is particularly useful for high availability website configurations. Use two separate Linodes to configure database replication, each with private IPv4 addresses.

mysql --version

Install MySQL

1. Use the following commands to install MySQL on each of the Linodes:

   sudo apt-get update
   sudo apt-get upgrade -y
   sudo apt-get install mysql-server mysql-client

2. Run the MySQL secure installation command. You are prompted to create a root password. It is recommended you select yes to all of the questions:

   mysql_secure_installation

Edit MySQL’s Configuration

1. Edit the /etc/mysql/my.cnf file on Server 1, and Server 2 Linodes. Add or modify the following values:

   Server 1:

   File: /etc/mysql/my.cnf

   1 2 3 4 5 6 7 8 9 10 11

   [mysqld] server_id = 1 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_replica_updates = 1 auto-increment-increment = 2 auto-increment-offset = 1

   Server 2:

   File: /etc/mysql/my.cnf

   1 2 3 4 5 6 7 8 9 10 11

   [mysqld] server_id = 2 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_replica_updates = 1 auto-increment-increment = 2 auto-increment-offset = 2

   Note

   If using MySQL 8.0.25 or earlier, replace log_replica_updates with log_slave_updates (within both Servers 1 and 2). See MySQL documentation for details.

2. Edit the bind-address configuration in order to use the private IP addresses, for Server 1 and Server 2:

   Server 1:

   File: /etc/mysql/my.cnf

   1	bind-address = 192.0.2.1

   Server 2:

   File: /etc/mysql/my.cnf

   1	bind-address = 192.0.2.2

3. Once completed, restart the MySQL application on Server 1, and Server 2:

   sudo systemctl restart mysql

Create Replication Users

1. Log in to MySQL on Server 1, and Server 2 Linodes:

   mysql -u root -p

2. Configure the replication users on each Linode. Replace 192.0.2.1 and 192.0.2.2 with the private IP address of the Server 1 and Server 2 Linodes and replace password with a strong password.

   Server 1

   CREATE USER 'replication'@'x.x.x.x' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.0.2.1';

   Server 2

   CREATE USER 'replication'@'x.x.x.x' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.0.2.2';

   Server 1

   GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.0.2.1' IDENTIFIED BY 'password';

   Server 2

   GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.0.2.2' IDENTIFIED BY 'password';

3. Run the following command to test the configuration. Use the private IP address of the respective Linodes:

   Server 1

   mysql -u replication -p -h 192.0.2.1 -P 3306

   Server 2

   mysql -u replication -p -h 192.0.2.2 -P 3306

   This command should connect you to the remote server’s MySQL instance.

Configure Database Replication

1. Log into MySQL on Server 1 as the root user and query the master status:

   SHOW MASTER STATUS;

   Note the file and position values that are displayed:

   mysql> SHOW MASTER STATUS;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      277 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

2. On Server 2 at the MySQL prompt, set up the replica functionality for that database. Replace192.0.2.1 with the private IP of Server 1. Also replace the value for source_log_file with the file value from the previous step, and the value for source_log_pos with the position value.

   STOP REPLICA;
   CHANGE REPLICATION SOURCE TO
       source_host='192.0.2.1',
       source_port=3306,
       source_user='replication',
       source_password='password',
       source_log_file='mysql-bin.000001',
       source_log_pos=277;
   START REPLICA;

   STOP SLAVE;
   CHANGE MASTER TO
       master_host='192.0.2.1',
       master_port=3306,
       master_user='replication',
       master_password='password',
       master_log_file='mysql-bin.000001',
       master_log_pos=277;
   START SLAVE;

3. On Server 2, query the master status. Again note the file and position values.

   SHOW MASTER STATUS;

4. Set the replica database status on Server 1, utilizing similar commands as in step 2. When entering the commands, use the IP address of Server 2 and the file and position values you just collected in the previous step for Server 2.

5. Test by creating a database and inserting a row:

   Server 1:

   create database test;
   create table test.flowers (`id` varchar(10));

   Server 2:

   show tables in test;

When queried, you should see the tables from Server 1 replicated on Server 2. Congratulations, you now have a MySQL Master-Master cluster!

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

• MySQL Reference Manuals