MySQL(MariaDB) 3개서버를 양방향 이중화하기 (replication 및 semi sync)

MySQL데이터양이 기가단위로 증가하게 됨에 따라 발생되는 쿼리양이 증가하게 되어

기존 1대의 MySQL서버와 3대의 Web서버로 운영하는 방식을

3대 모두 MySQL과 Web을 운영하게끔 변경하게 되었다.

(이를 통해 Web의 적은 부하량을 MySQL에 추가적으로 할당함으로써 더 많은 자원을 효율적으로 사용할 수 있다.)

서버의 아이피정보는 아래와 같다. (예시)

- 11.11.11.101

- 11.11.11.102

- 10.10.10.10

 

각각의 서버에서 Slave 접근을 위한 계정을 생성

grant replication slave on *.* to 'repl'@'%' identified by '패스워드'; 

각각의 서버에서 아래의 my.ini혹은 my.cnf 값을 추가한다.

[mysqld] 
log-bin=mysql-bin 
server-id=2 #서버마다 다른 숫자값을 입력한다. 
binlog_format = 'MIXED' 
slave-skip-errors=all 
skip-name-resolve 
read_only=0 

이전에 사용하던 혹은 메인이 되는 서버의 모든데이터베이스를 mysqldump를 통해 
백업을 받은뒤 다른 서버에 모두 insert해준다.

mysqldump -uroot -p패스워드 -all-databases > 경로.sql #전체백업 
mysql -uroot -p패스워드 < 경로.sql #전체복원 

서버를 재시작해준다.
윈도우의 경우 서비스 - MySQL - 재시작
리눅스의 경우 service mysqld restart


각각의 서버에서 master status명령어를 입력하여
mysql-bin 파일과 포지션을 확인한다.

11.11.11.101서버에서

show master status; 
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000009 |  130573 |              |                  | 
+------------------+----------+--------------+------------------+ 

11.11.11.102서버에서

show master status; 
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000027 |      342 |              |                  | 
+------------------+----------+--------------+------------------+ 

10.10.10.10서버에서

show master status; 
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000032 |   62625  |              |                  | 
+------------------+----------+--------------+------------------+ 

mysql-bin과 position 값을 아래과 같이 SQL 입력하여 MASTER-SLAVE를 구성한다.

11.11.11.101서버에서 

change master to 
master_host='11.11.11.102', 
master_user='repl', 
master_password='패스워드', 
master_log_file='mysql-bin.000027', 
master_log_pos=342; 

11.11.11.102서버에서

change master to 
master_host='10.10.10.10', 
master_user='repl', 
master_password='패스워드', 
master_log_file='mysql-bin.000029', 
master_log_pos=63778; 

10.10.10.10서버에서

change master to 
master_host='11.11.11.101', 
master_user='repl', 
master_password='패스워드', 
master_log_file='mysql-bin.000009', 
master_log_pos=130573; 

위와 같은 셋팅은 11.11.11.101 → 11.11.11.102 → 10.10.10.10 → 11.11.11.101 와 같은 형태로
양방향 Replication 동작이 수행된다.

모든 서버의 mysql 서비스를 재시작해준다.
윈도우의 경우 서비스 - MySQL - 재시작
리눅스의 경우 service mysqld restart
재시작이후 show slave status;를 입력하면 정상적으로 replication이 구성된것을 확인할 수 있다.
show slave status; 명령어 뿐만 아니라 phpmyadmin - 상태를 통해서도 확인이 가능하다.

Slave_IO_Status 와 Slave_SQL_Running 2개 의 값이 YES로 되어 있다면 정상적으로 리플리케이션이 설정된것이다.


총 3개의 서버를 통해 양방향 리플리케이션을 실행하다보니,
11.11.11.101서버에 적용된 데이터가 10.10.10.10으로 도달하기까지 시간지연이 소요됨으로 semi_sync를 설장한다.

3개 이상의 Replication 적용시 11.11.11.101에서 insert되는 데이터가 11.11.11.102로 동기화되고 10.10.10.10으로 보내지기 까지 시간이 상당히 걸려 웹서비스에서 좀 당황스러운 사태가 발생할 수 있다.

각각의 서버의 my.ini 혹은 my.cnf 파일에 다음의 내용을 추가한다.
위의 replication 설정이 온전하게 되지 않을 경우 semi_sync는 작동하지 않으니 필히 확인후 진행해야 한다.

rpl_semi_sync_master_enabled=1 
rpl_semi_sync_master_timeout =1000 
rpl_semi_sync_slave_enabled=1 

모든 서버의 mysql 서비스를 재시작해준다.
윈도우의 경우 서비스 - MySQL - 재시작
리눅스의 경우 service mysqld restart