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 설정을 위해서는 repository 를 TABLE 에 저장해야함
master_info_repository는 slave server 가 master status와 connection inforamtion 를 어디에 저장하겠냐는 파라미터로
TABLE 로 설정 시 mysql.slave_master_info 에서 조회 가능
- server id 설정
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 64 |
+---------------+-------+
1 row in set (0.19 sec)=> M/S 모든 서버들은 서로 server id 가 달라야함
- 각 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)- 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';
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
Replicate_Do_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: 716
Relay_Log_Space: 533
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_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
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master_1
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
- 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 떨굼
모니터링
- perfomrance_schema.replication_connection_status
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
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
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
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: 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
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 191103 09:58:08
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master_2
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
=> 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
Replicate_Do_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: 902
Relay_Log_Space: 888
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: 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
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master_2
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace: