Mysql - Master to Master Replication

Prerequisite Steps for Master to Master Replication in Mysql:

MySQL Master-Master replication adds speed and redundancy for active websites. With these replication it will be particularly useful for high availability website configurations.

In order to setup a Mysql Master to Master replication we need two different Instances also each one responding to a specific IP address but they don’t need to be identical, as long as they can both properly run the same version of  MySQL version

• Step 1. Installing MySQL Server on both instances
• Step 2. Create the Replicator User’s
• Step 3. Perform a full Sync
• Step 4  Configure replication one Server to another Server
• Step 5  Test the Replication

Step by Step for Configuring Replication:

Install Mysql on both the Instances:

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

Edit Mysql's Configuration:

Master Server 1:

server-id=1
max_allowed_packet=512M
max_connections=500
log_bin = mysql-bin
expire_logs_days    = 10
max_binlog_size     = 500M

innodb_buffer_pool_size = 1G

Master Server 2:

server-id=2
max_allowed_packet=512M
max_connections=500
log_bin = mysql-bin
expire_logs_days    = 10
max_binlog_size     = 500M

innodb_buffer_pool_size = 1G

Create Replication Users:

Login Mysql and create the user's as mentioned below:

• create user 'replicator'@'%' identified by 'replica123';
• grant replication slave on *.* to 'replicator'@'%' identified by 'replica123';
• flush privileges;

Perform a full sync on both the servers:

mysql -u root -p 'databasename' < dump.sql


Configure the Database Replication:

Login server1 at the Mysql prompt

mysql>show master status;

On server2 at the Mysql prompt configure the slave function for the database like below
mysql> stop slave;
mysql > CHANGE MASTER TO master_host='10.120.0.7', master_port=3306, master_user='replicator_user', master_password='replica123', master_log_file='mysql-bin.000334', master_log_pos=787242205;
mysql> start slave;

Login server2 at the Mysql prompt
mysql>show master status;


On server2 at the Mysql prompt configure the slave function for the database like below
mysql> stop slave;
mysql > CHANGE MASTER TO master_host='10.120.0.8', master_port=3306, master_user='replicator_user', master_password='replica123', master_log_file='mysql-bin.000334', master_log_pos=787242205;

mysql> start slave;

Test by creating a database and inserting a row

Server1:

mysql> create database testing;

mysql> create table testing.table1 (`id` varchar(10));

Server2:
mysql> show tables;  ====> The tables should replicate in server2

Post Migration Steps and Failovers

Failover 1 (in case for few minutes):

The server may down due to some human errors or the instance went for keep alive state, in this case we have one more master server which will help us on the site impact.

If it happens for few minutes the replication will resume automatically.so we can check the replica status for both servers using the below command.The I/O and SLAVE_SQL state should be “Yes”

mysql> show slave status\G;

Failover 2 (in case for more hours):

If suppose one instance is failure more than an hour, In this situation we may face a huge amount

of data’s mismatching between the servers,

So we need to configure the replication again and  follow the below steps:

• Taking a fresh backup and restore in on the other server.
• Verifying the replication user and privileges.
• Enabling the replication(sync the database) between both the servers.
• Test the replication status with mysql> show slave status\G;