Replication

Official documentation

Quick setup on 5.7

Replication configuration

Each replica must have unique server id differs from source or other replica

SET GLOBAL server_id = 21;

File configuration

[mysqld]
server-id=21
log-bin=mysql-bin

You can’t enabled log-bin on a running server

Create dedicated user for replication

CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

Lock table with read statement

FLUSH TABLES WITH READ LOCK;

Get the current binary log position

SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 73
     Binlog_Do_DB: test
 Binlog_Ignore_DB: manual, mysql
Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5
1 row in set (0.00 sec)

Dump database

mysqldump --all-databases --master-data > dbdump.db

Unlock DB

UNLOCK TABLES;

Restore DB on target replicat

mysql -h source < fulldb.dump

Setting source configuration on replica server

CHANGE MASTER TO MASTER_HOST='source_host_name', MASTER_USER='replication_user_name', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='recorded_log_file_name', MASTER_LOG_POS=recorded_log_position;

Get replication status

SHOW SLAVE STATUS\G

Start & Stop slave

STOP SLAVE;
START SLAVE;

Check replication

CREATE DATABASE test;
CREATE TABLE replication (ID INT NOT NULL);
INSERT INTO test.replication (ID) VALUES (1);
select * from test.replication; #On master host
select * from test.replication; #On slave host