[2020-01-28] MySQL Replication - DBMS 단방향 이중화

※ MHA와는 관련 없는 순수 Replication 구성임

 

1. Replication 설명

- Master와 Slave로 나뉜다.

- Master에서 DB변경사항이 발생하면 바이너리 로그(Binarylog)를 생성하여 Slave 서버로 전달한다.

- Master에서 가져온 바이너리로그를 반영한다.

- 보통 Master를 등록/변경/삭제로 사용하고 Slave를 조회용으로 사용한다.

---

2. Master 작업

- 이중화로 사용될 DB를 생성

mysql> create database [DB명] default character set utf8;

- 해당 DB의 계정생성

mysql> create user [아이디]@'%' identified by '[비밀번호]';

- 권한 처리

mysql> grant all privileges on [DB명].* to [아이디]@'%' identified by '[비밀번호]';

- Replication 용도로 사용될 계정 생성

mysql> grant replication slave on *.* to [아이디]@'%' identified by '[비밀번호]';

- my.cnf 설정

#vi /etc/my.cnf

= 아래 내용 추가

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

- 서비스 재시작

#service mysqld restart

- Master 상태 확인

show master status;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1280 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

= File과 Position을 확인해야 함 이 정보가 Slave설정 시 사용됨.

= File은 mysql 로그파일

= Position은 로그파일에서 읽을 위치

= Binlog_Do_DB는 변경된 이벤트 정보가 기록되는 바이너리(Binary) 로그 파일임

= Binlog_Ignore_DB는 복제 제외 정보임

---

3. Slave 작업

- Master와 동일한 DB 생성

mysql> create database [db명] default character set utf8;

- 해당 DB의 계정 생성

mysql> create user [아이디]@'%' identified by '[비밀번호]';

- 권한 처리

mysql> grant all privileges on [DB명].* to [아이디]@'%' identified by '[비밀번호]';

- my.cnf 설정

#vi /etc/my.cnf

= 아래 내용 추가

[mysqld]
server-id=2
replicate-do-db='[DB명]'
= server-id는 Master id와 다른값을 입력
= replicate-do-db는 복제대상 DB명시 (추가할 경우 그만큼 replicate-do-db를 생성)

- Master 연결 설정

mysql> change master to master_host='[Master 서버 IP]', master_user='[Replication 계정 아이디]', master_password='[Replication 계정 비밀번호]', master_log_file='[Master 에서 확인된 Log파일명]', master_log_pos=[Master 에서 확인된 Position번호];

설정 시 오류(문제)가 발생하면

mysql> stop slave;
mysql> reset slave;
mysql> 설정구문
mysql> start slave;

- 서비스 재시작

# service mysqld restart

---

4. 상태 확인

- Master에서

mysql> show processlist\G;

*************************** 9. row ***************************
Id: 18
User: [Replication 계정 아이디]
Host: [Slave 서버 아이피]:55542
db: NULL
Command: Binlog Dump
Time: 1854
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Progress: 0.000

= 위 정보가 Replication 계정을 통해 Slave 쪽으로 연결 중임을 확인

 

- Slave에서

mysql> show processlist\G;

*************************** 7. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Slave_SQL
Time: 1841
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
Progress: 0.000

= 위 정보가 Master와 통신하기 위한 Process를 의미

 

- Slave에서

mysql> show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: [Master 서버 주소]
Master_User: [Master Replication 계정 아이디]
Master_Port: [Master 접속 포트]
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1280
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 915
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: [Replication DB]
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1280
Relay_Log_Space: 1228
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 2

=

Slave_IO_State : Master서버와의 연결 상태

Master_Host : Master서버 호스트

Master_User : Master서버 연결계정

Master_Port : Master서버 연결 포트

Connect_Retry : --master-connect-retry 옵션의 현재 값

Master_Log_File : I/O 스레드에서 현재 읽고 있는 바이너리 로그파일

Read_Master_Log_Pos : I/O 쓰레드에서 현재 Master 서버의 바이너리 로그에서 읽은 위치

Relay_Log_File : SQL 쓰레드에서 현재 relay 로그파일

Relay_Log_Pos : SQL 쓰레드에 의해 Relay 로그에서 읽고 실행한 곳까지의 위치

Relay_Master_Log_File : SQL 스레드에 의해 실행된 최근 Master서버의 바이너리 로그 파일의 이름

Slave_IO_Running : I/O 스레드가 시작되어 Master서버의 성공적으로 연결되어있는지 여부

Slave_SQL_Running : SQL 쓰레드가 시작되었는지의 여부

Replicate_Do_DB : Master서버에서 업데이트된 데이터를 반영될 DB

Last_Errno : 가장 최근에 사용된 쿼리의 에러 메시지의 번호

Last_Error : 가장 최근에 사용된 쿼리의 에러메시지의 번호

Exec_Master_Log_Pos : Master서버의 바이너리 로그의 Relay_Master_Log_File로부터 SQL스레드의 의해 마지막 이벤트의 위치

Relay_Log_Space : 존재하는 모든 Relay 로그 사이즈

Master_SSL_* : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터

Seconds_Behind_Master : Master서버에서 실행된 이벤트의 타임스탬프 이후 경과된 시간(초 단위)의 수