MySQL Replication

🔨 Replication 이란?

• MySQL 데이터베이스 서버의 데이터를 하나 이상의 다른 MySQL 데이터베이스 서버로 복사

• 기본적으로 비동기식 복제 방식

• 모든 데이터베이스, 특정 데이터베이스 또는 데이터베이스 내에서 특정 테이블 복제 가능

  📌 복제 방법

  • Binary Log Position Based
  • GTID (Global Transaction Identifier) Based

  📌 동기화 유형

  • Asynchronous
  • Semisynchronous

  📌 복제 형식

  • Statement based
    -> SQL 형태로 기록
  • Row based
    -> 변경된 데이터 기반으로 기록
  • Mixed (Statement + Row)
    -> 기본적으로 Statement based, 필요에 따라 Row based로 기록

🔨 아키텍처

- Master DB -
(1) 데이터 변경 발생 (Event)
(2) Binary Log에 기록
(3) Binary Log의 내용을 Slave DB로 전달 (Binlog dump thread)

- Slave DB -
(4) Relay Log에 기록 (IO-Thread)
(5) Relay Log를 읽고 SQL 실행

🔨 구성

Master DB와 Slave DB의 데이터가 동일한 상태에서 진행

=> 데이터가 동일하지 않다면 backup tool을 이용하여 Slave DB 구성 후 진행

MySQL 8.0은 Binary Log 는 기본 활성화

🔧 Binary Log Position Based

• https://dev.mysql.com/doc/refman/8.0/en/replication-howto.html

(1) Master DB 서버 ID 설정

my.cnf 파일에 server_id 옵션 추가 후 재기동 또는 SET 명령어로 변경

[mysqld]
server_id = 1

mysql> SET GLOBAL server_id = 1;

(2) Slave DB 서버 ID 설정

my.cnf 파일에 server_id, read_only 옵션 추가 후 재기동 또는 SET 명령어로 변경

[mysqld]
server_id = 2
read_only = 1

mysql> SET GLOBAL server_id = 2;
mysql> SET GLOBAL read_only = 1;

(3) Replication 계정 생성

mysql> CREATE USER 'repl'@'slave_ip' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip';

(4) Binary Log Position 확인

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      509 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

(5) Replication 연결

mysql> CHANGE MASTER TO \
	MASTER_HOST='source_host_name', \
	MASTER_PORT=port, \
	MASTER_USER='replication_user', \
	MASTER_PASSWORD='replication_password', \
	MASTER_LOG_FILE='binary_log_file_name', \
	MASTER_LOG_POS=binary_log_position;

mysql> START SLAVE;

- MySQL 8.0.23 이후 -
mysql> CHANGE REPLICATION SOURCE TO \
	SOURCE_HOST='source_host_name', \
	SOURCE_PORT=port, \
	SOURCE_USER='replication_user', \
	SOURCE_PASSWORD='replication_password', \
	SOURCE_LOG_FILE='binary_log_file_name', \
	SOURCE_LOG_POS=binary_log_position;

mysql> START REPLICA;

Replication 계정의 패스워드 플러그인이 caching_sha2_password 일 경우 MASTER_SSL = 1 or SOURCE_SSL=1 추가

(6) Replication 상태 확인

mysql> SHOW SLAVE STATUS\G
	SLAVE_IO_Running: Yes
	SLAVE_SQL_Running: Yes

- MySQL 8.0.23 이후 -
mysql> SHOW REPLICA STATUS\G
	Replica_IO_Running: Yes
	Replica_SQL_Running: Yes

🔧 GTID Based

• https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-howto.html

📌 GTID = 커밋된 각 트랜잭션에 대해 생성 및 연결된 고유 식별자

📌 Binary Log Position Based 의 (1) ~ (3) 단계 동일

(1) GTID 활성화

my.cnf 파일에 gtid_mode, enforce-gtid-consistency 옵션 추가 후 재기동

[mysqld]
gtid_mode=ON
enforce-gtid-consistency=ON

(2) Replication 연결

mysql> CHANGE MASTER TO \
	MASTER_HOST='source_host_name', \
	MASTER_PORT=port, \
	MASTER_USER='replication_user', \
	MASTER_PASSWORD='replication_password', \
	MASTER_AUTO_POSITION = 1;

mysql> START SLAVE;

- MySQL 8.0.23 이후 -
mysql> CHANGE REPLICATION SOURCE TO \
	SOURCE_HOST='source_host_name', \
	SOURCE_PORT=port, \
	SOURCE_USER='replication_user', \
	SOURCE_PASSWORD='replication_password', \
	SOURCE_AUTO_POSITION = 1;

mysql> START REPLICA;

(3) Replication 상태 확인

mysql> SHOW SLAVE STATUS\G
	SLAVE_IO_Running: Yes
	SLAVE_SQL_Running: Yes
	Auto_Position: 1

- MySQL 8.0.23 이후 -
mysql> SHOW REPLICA STATUS\G
	Replica_IO_Running: Yes
	Replica_SQL_Running: Yes
	Auto_Position: 1

📌 GTID 값 수동 설정 방법

mysql> STOP REPLICA;

mysql> SET GLOBAL GTID_PURGED="server_uuid:transaction_id";

mysql> START REPLICA;