MySQL Dual Master Replication 설정 방법은 무엇인가요?
💡 요약 정리
- Dual Master Replication은 두 서버가 서로 마스터/슬레이브가 되어 양방향 데이터 동기화를 수행합니다.
- 먼저 Single Master를 구성한 후 Dual Master로 확장합니다.
- replication 설정할 DB, DB user, 권한은 양쪽 서버에 동일하게 미리 생성해야 합니다.
- my.cnf 설정, binlog 관리, slave status 확인이 핵심입니다.
Dual master Replication은 두대의 서버에서 서로 마스터, 슬레이브가 되어서 각각의 서버에 업데이트되는 데이터를 반대편 서버에서도 업데이트 한다.
- master server : 192.168.56.101
- slave server : 192.168.56.102
- replication 설정할 db와, db user, db user 권한설정은 master, slave server에 각각 동일하게 미리 생성해 놓고 설정한다.
1. Single Master 설정
master 서버에서 작업
① master server 설정
/etc/my.cnf 파일에 아래 내용 추가
log-bin=mysql-bin
server-id = 1
binlog_do_db = db_name
replicate_do_db = db_name
② slave 서버에서 master 서버로 접속할수 있는 replication user를 생성
mysql> grant replication slave on *.* to 'db_user'@'%' identified by 'db_pass';
mysql> grant replication slave on *.* to 'db_user'@'192.168.56.102' identified by 'db_pass';
user table에서 File_priv, Repl_slave_priv, Repl_client_priv 권한 Y로 설정 확인
③ mysql 중지, replication 구성할 db slave로 복사후 mysql start
# mysql stop
# scp 또는 rsync등을 이용해서 db를 slave로 복사
# mysql start
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 1024 | db_name | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
slave 서버에서 작업
④ slave server 설정
/etc/my.cnf 설정 추가
#log-bin=mysql-bin ---> 생략가능
server-id = 2
relay-log=mysqld-relay-bin
⑤ mysql restart 후 master server 정보 입력
# mysql restart
mysql> stop slave;
master 서버의 status 에서 확인한 log_file, log_pos값 확인 후 정보 입력
mysql> change master to master_host='192.168.56.101', master_user='db_user', master_password='db_pass', master_log_file='mysql-bin.000003', master_log_pos=1024;
mysql> show slave status;
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Waiting for master to send event | 192.168.56.101 | db_user | 3306 | 60 | mysql-bin.000003 | 1024 | cent5_2-relay-bin.000009 | 375 | mysql-bin.000003 | Yes | Yes | | | | | | | 0 | | 0 | 1024 | 533 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 |
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)
mysql>
⑥ master 와 slave 의 replication test
master 서버에서 업데이트한 데이터가 slave 서버에 정상적으로 업데이트 되는지 테스트한다.
2. Dual Master 설정
slave 서버에서 작업
① slave 서버를 master로 설정추가
- slave 서버에 bin-log 파일이 생성되어 있으면 로그파일을 삭제 한다.
- my.cnf 파일에 log-bin=mysql-bin 이 설정되어 있지 않으면, 설정을 추가 한다.
# mysql restart
② master서버에서 slave 서버로 접속할수 있는 replication user를 생성
mysql> grant replication slave on *.* to 'db_user'@'192.168.56.101' identified by 'db_pass';
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 712 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
master 서버에서 작업
③ master에서 slave 서버를 master로 설정 추가
mysql> change master to master_host='192.168.56.102', master_user='db_user', master_password='db_pass', master_log_file='mysql-bin.000001', master_log_pos=712;
mysql> show slave status;
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Waiting for master to send event | 192.168.56.102 | db_user | 3306 | 60 | mysql-bin.000001 | 712 | cent5_1-relay-bin.000004 | 369 | mysql-bin.000001 | Yes | Yes | db_name | | | | | | 0 | | 0 | 712 | 527 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 2 |
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)
mysql>
④ slave 서버와 master 서버간의 replication test
slave 서버에서 업데이트한 데이터가 master 서버에 정상적으로 업데이트 되는지 테스트한다.