MySQL Replication Setup

  • Replication used to replicate data from the Master node to a slave node[Replica].
  • By default Replication is asynchronous.
  • It uses binary logs for reading data from the Master node and relay log on slave[Replica].
  • Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Replication Methods:

  • File-based Replication:

The traditional method is based on replicating events from the master’s binary log, and requires the log files and positions in them to be synchronized between master and slave.

  • GTID Replication:

The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files. Replication using GTIDs guarantees consistency between master and slave as long as all transactions committed on the master have also been applied on the slave.

 

Configuration variables for replication:

MASTER Conf:

server_id=145

gtid-mode=on

enforce-gtid-consistency=1

log-slave-updates=1

log-bin=/mysql/log/binary/mysql-bin145

relay-log=/mysql/log/relay/relay-bin145

relay_log_recovery= on

master_info_repository = TABLE

relay_log_info_repository = TABLE

SLAVE conf:

server_id=146

gtid-mode=on

enforce-gtid-consistency=1

log-slave-updates=1

log-bin=/mysql/log/binary/mysql-bin146

relay-log=/mysql/log/relay/relay-bin146

relay_log_recovery= on

master_info_repository = TABLE

relay_log_info_repository = TABLE

# don’t replicate the mysql database

replicate_wild_ignore_table = mysql.%

read-only

skip-slave-start

 

Replication setup:

  1. Create replication user on MASTER with replication privileges.

CREATE USER IF NOT EXISTS ‘rpluser’@’%’ IDENTIFIED BY ‘rpluser@1234’;

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘rpluser’@’%’;

  1. On SLAVE: setup replication as follows :

File-based Replication:

CHANGE MASTER TO MASTER_HOST='<MASTER_IP’,MASTER_USER=’rpluser’,MASTER_PASSWORD=’rpluser1234′,MASTER_PORT=3306,MASTER_LOG_FILE=’mysql-bin.000002′,MASTER_LOG_POS=690;

GTID Replication:

CHANGE MASTER TO MASTER_HOST='<MASTER_IP’,MASTER_USER=’rpluser’,MASTER_PASSWORD=’rpluser1234′,MASTER_PORT=3306,MASTER_AUTO_POSITION=1;

  1. Start slave

START SLAVE;

  1. Check slave status

SHOW SLAVE STATUS;

Slave_IO_Running and Slave_SQL_Running column value should be ‘YES’

Ref: https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-howto.html

 

Advertisements