MYSQL 5.6 Master-Slave replication setup

About Replication:

It is also know as coordinate replication.MySQL replication is the method that allows you to create copy of your database automation from master to slave. These replicated instances could be used for backup, DR, HA or simply scale-out.

Here below is quick history for MYSQL replication:

MySQL 3.23 - Generally Available, January 2001
o MySQL Replication came to be (3.23.15 – May 2000).
o Replication filters
MySQL 4.0 - Generally Available, March 2003
o Two Replication Threads instead of just one.
o Slave Relay logs.
MySQL 4.1 - Generally Available, October 2004
o Replication over SSL.
o Disk synchronization options for binary log.
MySQL 5.0 - Generally Available, October 2005
o Replication of Stored Routines and Triggers.
o Slave retries transactions on transient errors.
MySQL 5.1 - Generally Available, November 2008
o Row-based Replication (RBR).
MySQL 5.5 - Generally Available, December 2010
o Semi-sync replication.
o Replication Heartbeats.
o RBR type conversion.
MySQL 5.6 - Generally Available, February 2013
o Crash-safe Slaves.
o Global Transaction Ids.
o Replication Event Checksums.
o Binary Log Group Commit.
o Multi-threaded Slaves.
o RBR enhanced.
o MySQL Utilities 1.3, GA on August 2013
MySQL 5.7.2 DMR, September 2013
o Multi-Threaded Inter-Transactional Replication
o Lossless Semi-Synchronous Replication
o MySQL Utilities 1.4
Here we will take very simple example of mysql replication—one master will send information to a single slave. We have created 2 MYSQL nodes in previous Blogs NODE1 & NODE2

This tutorial will use the following Nodes:

NODE1(192.168.56.110) : Master Database
NODE2(192.168.56.121) : Slave Database

 1: Configure the Master Database (NODE 1)

Edit your configuration file “/etc/my.cnf” to add/edit following parameter on your master server (NODE 1)

a. Look for bind-address and either comment it or put IP address of NODE1

Example:

# bind-address = 127.0.0.1

Or 

bind-address = 192.168.56.110 

b. Next change is server-id you may need to uncomment this parameter and unique number to be provide among nodes

Example:

server-id = 1 

c. Move on to the log_bin line. This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log. For this step we simply need to uncomment the line that refers to log_bin and provide with location. If location not provided by default it will create in datadir directory.

log_bin = /var/log/mysql/mysql-bin

Restart mysql server “/etc/init.d/mysql restart” and check the position of binarylog

Create user for replication and give it necessary grants

Now login to mysql and goto database and apply read lock on all tables. So that data would remain consistent with binlog position in backup. Take the backup > note down the mater binary location> unlock tables.
Information Gathered (this position will be required while configuring slave)

Binary log file  : mysql-bin.000003
Position           : 120

Transfer the backup to slave machine(NODE2) 

2. Configure the slave server(Node2)

Connect to slave server and create database with same name as master which is required to replicate

Import the database that you have exported from master database(NODE 1) 

Edit your configuration file “/etc/my.cnf” to add/edit following parameter on your slave server (NODE 2)

relay-log=/var/lib/mysql_logs/relay-log/Node2-relay-bin

server-id=2

Restart your mysql instance (NODE2)

Connect you MYSQL prompt again and run below command .
It has information which we captured while setting up master previously.

CHANGE MASTER TO MASTER_HOST='192.168.56.110',MASTER_USER='repl', MASTER_PASSWORD='google@123', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS= 120;

Now start slave with command “start slave” and then check slave status with command “show slave status \G”

Great !! ....You have just built master-slave replication. You can test it out by creating objects in master node1 under database “color” and see data replication on slave node2.