MySQL 리플리케이션(Replication) 설정 방법

MySQL Replication을 이용하여 DBMS 단방향 이중화 설정

 

Replication은 복제를 뜻하며 2대 이상의 DBMS를 나눠서 데이터를 저장하는 방식으로,

사용하기 위해 Master / Slave 구성을 하여야 합니다

 

Master DBMS 역할 : 

웹서버로 부터 데이터 등록/수정/삭제 요청시 바이너리로그(Binarylog)를 생성하여 Slave 서버로 전달

(웹서버로 부터 요청한 데이터 등록/수정/삭제 기능을 하는 DBMS로 많이 사용)

 

Slave DBMS 역할 : 

Master DBMS로 부터 전달받은 바이너리로그(Binarylog)를 데이터로 반영

(웹서버로 부터 요청을 통해 데이터를 불러오는 DBMS로 많이 사용)

Replication의 사용 목적

 

Replication은 사용목적은 실시간 Data 백업과 여러대의 DB서버의 부하를 분산을 위해 사용

1. 데이터 백업

2. DBMS 부하 분산

 

Replication 설정 시 주의 사항

 

Mysql 버전을 동일하게 맞추는 것을 추천

둘이 버전이 다를 경우 Slave 서버에 상위 버전을 사용하여야 함

실행 시 Master 서버 > Slave 서버 순으로 실행

 

Replication 설정

 

환경 ubuntu18.04 mariadb 10.1.48

 

Master 서버 설정

 

1. DB 생성

 

mysql > create database test_db default character set utf8mb4 COLLATE utf8mb4_general_ci;

 

2. 계정 생성

 

mysql > create user test@'%' identified by 'qwer1234';

 

3. 권한 주기

 

grant all privileges on test_db.* to test@'%' identified by 'qwer1234';

 

MySQL sql 파일 덤프 후 slave 서버에서 복원

 

리플리케이션 계정 생성

 

mysql > grant replication slave on *.* to 'repl_user'@'%' identified by 'test456';

 

마스터 서버에 연결하기 위한 리플리케이션 계정을 생성하고

DB권한은 test계정에게 주었기 때문에 DB 수정은 test계정으로 진행

 

 

MySQL 설정

 

# vi /etc/mysql/mariadb.conf.d/50-server.cnf < 현재 경로는 ubuntu 마리아 db 경로

mysql은 vi /etc/my.cnf 에서 설정을 해주어야 함 또는 인클루드 되어있는 파일을 찾아서 수정

 

아래 내용 추가

------------------------------------------------------

[mysqld]

log-bin=mysql-bin

server-id=1

------------------------------------------------------

 

MySQL 재시작

 

# systemctl restart mysql

 

Master 정보 확인

 

mysql > show master status;

master 서버 정보 < 자신의 서버마다 다름

 

File : Mysql log 파일

Position : log 파일 내 읽을 위치

Binlog_Do_DB : 바이너리 로그 파일

Binlog_ignore_DB : 복제 제외 되는 정보

 

Slave 서버 설정

 

1. DB 생성

 

mysql > create database test_db default character set utf8mb4 COLLATE utf8mb4_general_ci;

 

2. 계정 생성

 

mysql > create user test@'%' identified by 'qwer1234';

 

3. 권한 주기

 

grant all privileges on test_db.* to test@'%' identified by 'qwer1234';

 

리플리케이션 계정 생성

 

mysql> grant replication slave on *.* to 'repl_user1'@'%' identified by 'test456';

 

MySQL 설정

 

master 서버와 마찬가지로 설정

 

아래 내용 추가

------------------------------------------------------

[mysqld]

server-id=2

replicate-do-db='db명'

------------------------------------------------------

 

server-id : Master 서버와 겹치지 않게 Master 서버 id는 제외하고 설정

 

Master 서버에서 덤프 해놓은 DB를 Slave 서버에 복원

 

 

Master 서버로 연결 설정

 

mysql> change master to

master_host='마스터 서버 IP',

master_user='리플리케이션 ID',

master_password='비밀번호',

master_log_file='마스터 스테이터스 로그 파일명',

master_log_pos=마스터 스테이터스 포지션 값;

 

MySQL 재시작

 

# systemctl restart mysql

 

MySQL 리플리케이션 상태 확인

 

마스터 서버 상태

 

1) 쓰레드 상태 확인

 

mysql > show processlist\G

 

 

ID : 35 Slave 서버 IP 연결되어있는 것 확인

 

2) 쓰레드 주요인자 상태보기

 

 

Slave_IO_State : Master서버의 연결을 시도하고 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_Allowed : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 

 

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

 

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

 

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

 

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

 

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

 

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