[MariaDB] Master-Slave 설정

[MariaDB] Master-Slave 설정

1. 각 DB에 사용자 추가

(기본적으로 Master서버에만 설정하면되나 Auto-Failover시 누가 Master가 될지 모르기 때문에 동일하게 추가)

 

drop user 'replication_user'@'%';

CREATE USER 'replication_user'@'%' IDENTIFIED BY '비밀번호';

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

flush privileges;

 

SELECT host, user, password FROM mysql.user WHERE user='replication_user';

 

2. Mater 서버 명령

-- LOCK 설정

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

 

select BINLOG_GTID_POS('master1-bin.000006', 360);

 

-- 슬레이브 실행 후

UNLOCK TABLES;

 

3. Slave 서버 명령

SHOW VARIABLES LIKE 'server_id';

 

STOP SLAVE;

RESET SLAVE;

 

SET GLOBAL gtid_slave_pos = '0-1-7946432';

 

CHANGE MASTER TO

  MASTER_HOST='서버 IP',

  MASTER_USER='replication_user',

  MASTER_PASSWORD='비밀번호',

  MASTER_PORT=3306,

  MASTER_LOG_FILE='master1-bin.000003',

  MASTER_LOG_POS=388577,

  MASTER_CONNECT_RETRY=10,

  MASTER_USE_GTID=slave_pos;

 

START SLAVE;

 

SHOW SLAVE STATUS;

 

 

출처 : yeti.tistory.com/132