MySQL Multi Source Replication
Multi Source Replication ?
하나의 slave db서버가 여러개의 master와 연결하여 replication 을 구성하는 기능으로 mysql 5.7.x 버전대 부터 사용가능함
백업 용도, 로그 통합 DB, 배치작업 등을 위해 데이터를 한곳에 모을 필요가 있을 때 매우 유용한 기능
- multi master 와 multi source 는 다른 개념
- slave는 여러 개의 master 를 channel name 으로 구분함
구성방법
- replication repository 설정
mysql> show variables like '%repository%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
2 rows in set (0.25 sec)
=> MSR 설정을 위해서는 replication 관련 정보 repository 를 TABLE 에 저장해야함
master_info_repository=TABLE 의미는 Slave가 복제하는 MASTER DB의 status와 connection inforamtion 정보를
mysql.slave_master_info 에 저장하겠다는 의미
- 각 master 에서 server_id (다르게) 설정 및 replication user 생성
mysql> set global server_id=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'replUser'@'172.17.0.5' IDENTIFIED BY '!dlatl00';
Query OK, 0 rows affected (0.03 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replUser'@'172.17.0.5';
Query OK, 0 rows affected (0.02 sec)
=> M/S 모든 서버들은 서로 server id 가 달라야 DB 내에서 구분이 가능함
server id 는 재기동 되어도 유지될 수 있도록 my.cnf에 반영해놓을것
- replication 설정
mysql> CHANGE MASTER TO
-> MASTER_HOST='172.17.0.6',
-> MASTER_USER='replUser',
-> MASTER_PASSWORD='!dlatl00',
-> MASTER_LOG_FILE='mysql-bin.000010',
-> MASTER_LOG_POS=716
-> FOR CHANNEL 'master_1';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='172.17.0.7',
-> MASTER_USER='replUser',
-> MASTER_PASSWORD='!dlatl00',
-> MASTER_LOG_FILE='mysql-bin.000010',
-> MASTER_LOG_POS=714
-> FOR CHANNEL 'master_2';
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql>
mysql> CHANGE MASTER TO
-> MASTER_HOST='172.17.0.8',
-> MASTER_USER='replUser',
-> MASTER_PASSWORD='!dlatl00',
-> MASTER_LOG_FILE='mysql-bin.000011',
-> MASTER_LOG_POS=388
-> FOR CHANNEL 'master_3';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
START SLAVE FOR CHANNEL 'master_1';
START SLAVE FOR CHANNEL 'master_2';
START SLAVE FOR CHANNEL 'master_3';
=> 복제 대상 Master 서버 마다 replication channel 할당
- replication status
mysql> show slave status for channel 'master_1'\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.6
Master_User: replUser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 716
Relay_Log_File: relay-bin-master_1.000003
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 716
Relay_Log_Space: 533
Master_Server_Id: 1
Master_UUID: ba315763-e7e8-11e9-9c29-0242ac110002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Channel_Name: master_1
.
.
.
=> replication channel 별로 status 조회 가능
- binary log 는?
[root@da25e57166b5 data]# ls -ltr relay-bin*
-rw-r----- 1 root root 155 Nov 3 09:52 relay-bin-master_3.000002
-rw-r----- 1 root root 211 Nov 3 09:52 relay-bin-master_1.000002
-rw-r----- 1 root root 56 Nov 3 09:52 relay-bin-master_1.index
-rw-r----- 1 root root 211 Nov 3 09:52 relay-bin-master_2.000002
-rw-r----- 1 root root 28 Nov 3 09:52 relay-bin-master_3.index
-rw-r----- 1 root root 56 Nov 3 09:52 relay-bin-master_2.index
-rw-r----- 1 root root 510 Nov 3 09:58 relay-bin-master_1.000003
-rw-r----- 1 root root 510 Nov 3 09:58 relay-bin-master_2.000003
=> master 마다 각각 다른 relay-log file 떨굼
- replication 모니터링
mysql> select * From performance_schema.replication_connection_status\G:
*************************** 1. row ***************************
CHANNEL_NAME: master_1
GROUP_NAME:
SOURCE_UUID: ba315763-e7e8-11e9-9c29-0242ac110002
THREAD_ID: 37
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 17
LAST_HEARTBEAT_TIMESTAMP: 2019-11-03 10:01:00.967195
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION: ANONYMOUS
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-11-03 09:58:00.954012
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-11-03 09:58:00.954012
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2019-11-03 09:58:00.955845
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2019-11-03 09:58:00.955890
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
CHANNEL_NAME: master_2
GROUP_NAME:
SOURCE_UUID: ba315763-e7e8-11e9-9c29-0242ac110002
THREAD_ID: 39
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 17
LAST_HEARTBEAT_TIMESTAMP: 2019-11-03 10:00:38.758086
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION: ANONYMOUS
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-11-03 09:58:08.748663
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-11-03 09:58:08.748663
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2019-11-03 09:58:08.750592
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2019-11-03 09:58:08.750634
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
- replication error 발생했을 때
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.7
Master_User: replUser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 902
Relay_Log_File: relay-bin-master_2.000003
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1007
Last_Error: Error 'Can't create database 'test1'; database exists' on query.Default database: 'test1'. Query: 'create database test1'
Skip_Counter: 0
Exec_Master_Log_Pos: 714
Relay_Log_Space: 721
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test1'; database exists' on query.Default database: 'test1'. Query: 'create database test1'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: ba315763-e7e8-11e9-9c29-0242ac110002
Master_Info_File: mysql.slave_master_info
Last_SQL_Error_Timestamp: 191103 09:58:08
Replicate_Rewrite_DB:
Channel_Name: master_2
=> master_2 replication 에러 발생
mysql> stop slave for channel 'master_2';
Query OK, 0 rows affected (0.01 sec)
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.01 sec)
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.7
Master_User: replUser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 902
Relay_Log_File: relay-bin-master_2.000004
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 902
Relay_Log_Space: 888
Until_Condition: None
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: 2
Master_UUID: ba315763-e7e8-11e9-9c29-0242ac110002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Channel_Name: master_2
=> replication error 발생한 channel stop 후 개별 조치