- 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:
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’@’%’;
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;
Start slave
START SLAVE;
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