Published February 23, 2018 by

Configure Master to Master MySQL Database Replication

MySQL replication is the process of MySQL database live-copied to another MySQL server. master-master replication allows data to be copied from either server to the other one.This configuration adds redundancy and increases efficiency when dealing with accessing the data.

In this example, I take two servers, named Server 1 and Server 2. and Ubuntu environment.

 Server 1:  192.168.0.11
Server 2:  192.168.0.22

Install Mysql

Install MySQL on both servers using the following command

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

Edit MySQL’s Configuration File

Edit the /etc/mysql/mysql.conf.d/mysqld.cnf file on both servers.

# vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add or modify the following data:

Server 1:

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_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 1
bind-address = 192.168.0.11

Server 2:

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_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 2
bind-address = 192.168.0.22

Restart MySQL on both Servers

# sudo service mysql restart

Create Replication Users on Both Servers

1. Log in to MySQL on servers 1

# mysql -u root -p

2. Configure the replication users on server 1

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

3. Log in to MySQL on servers 2

# mysql -u root -p

4. Configure the replication users on server 2

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

Configure Database Replication

1. Log into MySQL on Server 1, query the master status:

# SHOW MASTER STATUS;

2. Log into MySQL on Server 2, and insert below command

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

3. Query for the master status:

# SHOW MASTER STATUS;

4. Log into MySQL on Server 1, and insert below command









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

 Now master-master replication configuration done. it's time to test.

Test Replication Configuration

Server 1:

create database Subhash;
create table Subhash.serverkaka (`id` varchar(10));

Server 2:

show tables in Subhash;

When queried, we should see the tables from Server 1 replicated on Server 2.

,