Set Up Master Slave Replication in MySQL 5.6


MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out.

Set Up Master Slave Replication in MySQL 5.6 & AboveThis tutorial will cover a very simple way of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave.

Below are the step to setup the replication in MySQL 5.6 and above.

MASTER

1. Set config values in /etc/my.cnf

Turn on the binary logs.

[mysqld]
log-bin = mysql-bin
server-id = 1
binlog_format = MIXED
Note: The server-id values need to be different for master and slaves. Also I would recommend using the Mixed binlog format, other formats are row based and statement based. Click here to see Statement-Based vs Row-Based Replication.

Enable GTIDS

If you are using MySQL 5.6 and above, Global Transaction IDs are a great way to ensure consistency during failover [1]. To enable GTIDs add the following config values in the my.cnf config file in addition to the bin log configs.
[mysqld]
enforce-gtid-consistency
gtid-mode=ON

2. Restarting mysql service

service mysql restart

3. Create a user and add replication privileges

You need to create a mysql user with replication privileges which the slave can use. For example repl-user can connect to this master from any host in repl-domain domain using the password ‘slavepass‘
mysql> CREATE USER ‘repl-user’@’%.repl-domain’ IDENTIFIED BY ‘slavepass’;

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl-user’@’%.repl-domain’;

SLAVE

1.Set config values in /etc/my.cnf

Add configs for binary and relay logs and enable GTIDS

[mysqld]
log-bin = mysql-bin
server-id = 2
relay-log = relay-log-slave
read-only = ON
gtid-mode =ON
enforce-gtid-consistency
binlog_format = MIXED
Note: The server-id here is different from the master

2. Restart mysql service
service mysql restart

3. Add query to listen to master giving host, log pos, etc

Use the replication user created in the MASTER to connect to the Master node. The MASTER_AUTO_POSITION will tell the slave to use GTID based replication.
mysql> CHANGE MASTER TO
> MASTER_HOST = ‘Master-Server-IP’,
> MASTER_PORT = port,
> MASTER_USER = ‘repl-user’,
> MASTER_PASSWORD = ‘slavepass’,
> MASTER_AUTO_POSITION = 1;

If you are not intent to use GTID based replication then you can use binlog position to setup the replication by running below query. (Note: you don’t need to use GTID tags in the my.cnf file of Master and Slave Servers.)

CHANGE MASTER TO MASTER_HOST=Master-Server-IP’, MASTER_USER=’repl-user’,MASTER_PASSWORD=slavepass’,MASTER_PORT=3306,  MASTER_LOG_FILE=’binarylogs.000001′,MASTER_LOG_POS=120,MASTER_CONNECT_RETRY=10;

4. Start slave
mysql> START SLAVE;
In order to monitor the replication you can run the following query on your slaves and look for values like Slave_IO_Running, Last_Error, Seconds_Behind_Master.
mysql> SHOW SLAVE STATUS;

In order to stop the replication on the Slave run the following query:
mysql> STOP SLAVE;

In order to start the replication on the Slave run the following query:
mysql> START SLAVE;


<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.