MySQL & Maria
[Maria] Galera Cluster to Single DB(DR)
- -
반응형
이번엔 Galera Cluster 사용하고 있는 환경에서 DR용 Single DB( Replication )를 구성하기로하였다.
초기에는 Maxscale을 이용하여 Binlog Server용으로 사용할려고 하였지만 Maxscale을 실제로 사용하기 위해서는 라이센스 구매가 필요하다는 답변을 마리아 DB 코리아에서 연락을 받았다.. 어쩔 수 없이 Binlog Server 없이 다이렉트로 Replication을 맺어주기로 하였다.
■ Galera Cluster 설치
- log_slave_updates=on : Galera 클러스터의 노드가 쓰기 세트를 비동기 슬레이브로 복제 log-slave-updates 하려면 Galera 마스터에서 활성화해야 합니다. 이것이 활성화되지 않으면 클러스터의 다른 노드에서 복제된 변경 사항이 비동기 슬레이브로 복제되지 않습니다
1. Node별 MariaDB 설치
(생략)
2. Galera Cluster 설정
[root@galera001 galera]# vi /etc/my.cnf * Galera 설정 추가
[myqsld]
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=2
log_slave_updates=on
[galera]
wsrep_on=ON
wsrep_provider=/data/maria/lib/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://192.168.100.50,192.168.100.51,192.168.100.52'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.100.50'
wsrep_node_name='node1'
wsrep_sst_method=rsync
wsrep_sst_auth=root:root
binlog_format=row
default_storage_engine=InnoDB
bind-address=0.0.0.0
3. Galera Cluster Start
[root@galera001 galera]# mysqld_safe --user=mysql --wsrep-new-cluster &
[root@galera002 ~]# mysqld_safe --user=mysql &
[root@galera003 ~]# mysqld_safe --user=mysql &
MariaDB [(none)]> show status like 'wsrep%';
+------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------------------------+
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_causal_reads | 0 |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_cert_index_size | 1 |
| wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 1b1b407d-cb4d-11ec-9096-ebc3a88500ac |
| wsrep_cluster_status | Primary |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_connected | ON |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_sent | 0 |
| wsrep_gcomm_uuid | 1b1a9f8e-cb4d-11ec-9f1c-ba06482f6217 |
| wsrep_incoming_addresses | 192.168.100.50:3306,192.168.100.51:3306,192.168.100.52:3306 |
| wsrep_last_committed | 3 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_cached_downto | 1 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_commits | 0 |
| wsrep_local_index | 0 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_local_state_uuid | 1b1b407d-cb4d-11ec-9096-ebc3a88500ac |
| wsrep_protocol_version | 7 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.22(r3764) |
| wsrep_ready | ON |
| wsrep_received | 10 |
| wsrep_received_bytes | 758 |
| wsrep_repl_data_bytes | 1133 |
| wsrep_repl_keys | 3 |
| wsrep_repl_keys_bytes | 93 |
| wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 3 |
| wsrep_replicated_bytes | 1418 |
| wsrep_thread_count | 2 |
+------------------------------+-------------------------------------------------------------+
58 rows in set (0.00 sec)
4. REPLICATION USER 생성
MariaDB [(none)]> create user 'repl'@'192.168.100.%' identified by 'repl';
MariaDB [(none)]> grant all on *.* to 'repl'@'192.168.100.%' ;
■ DR용 Single 설치
- gtid_domain_id = 99 : 슬레이브의 값 은 Galera 마스터의 값과 gtid_domain_id 다르게 설정하여야함
[root@Single ~]# vi /etc/my.cnf ##추가
#Replication slave to Galera Cluster
server-id = 02
relay-log-index = /var/log/mysql/slave-relay-bin.index
relay-log = /var/log/mysql/slave-relay-bin
gtid_domain_id = 99
log-bin = /var/log/mysql/slave-bin
log-bin-index = /var/log/mysql/slave-bin.index
binlog_format = mixed
■ Replication 설정
- 나중에 galera cluster의 다른 Node가 Master가 될 수 있기 때문에 binlog file로 replication을 맺기보단 gtid를 통해서 Replication 설정
1. Export dumpfile ( Galera )
[root@galera001 ~]# mysqldump -uroot -proot --single-transaction --routines --triggers --master-data=2 --all-databases > galera.sql
[root@galera001 ~]# scp galera.sql root@192.168.100.49:/root/
root@192.168.100.49's password:
galera.sql
2. import dumpfile ( Slave )
[root@ripple ~]# mysql -uroot -proot < galera.sql
[root@ripple ~]# head -100 galera.sql
(생략)
-- SET GLOBAL gtid_slave_pos='0-1-54';
(생략)
3. Replication ( Slave )
MariaDB [(none)]> SET GLOBAL gtid_slave_pos='0-1-54';
Query OK, 0 rows affected, 1 warning (0.01 sec)
MariaDB [(none)]> show variables like '%gtid_slave%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| gtid_slave_pos | 0-1-54 |
+----------------+--------+
MariaDB [(none)]> change master to master_host='192.168.100.50',master_user='repl',master_password='repl',master_port=3306,master_connect_retry=10,master_use_gtid=slave_pos;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.50
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql.000009
Read_Master_Log_Pos: 238631124
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 41446346
Relay_Master_Log_File: mysql.000009
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: 144274789
Relay_Log_Space: 66342525
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: 4106
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_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-54
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Write_rows_log_event::write_row(-1)
■ Galera Cluster Master DB 죽었을떄 Single DB 재연결
- Replication을 설정했던 Master DB가 죽었다면 Slave를 Stop 한 이후 Galera Cluster에서 살아있는 Node를 Master로 재설정
- gtid로 Replicaiton을 맺어놓은 상태이기 때문에 Slave를 재설정하는 시간 동안 Galera Cluster에서 발생하였던 DML도 Slave 재설정 시 적용된다.
1. Galrea Cluster 1 Node Shutdown ( 192.168.100.50 )
[root@galera001 test_db]# mysqladmin -uroot -proot shutdown
[1]+ Done mysqld_safe --user=mysql --wsrep-new-cluster (wd: ~)
(wd now: ~/test_db)
2. Slave DB 상태 확인
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 192.168.100.50
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql.000009
Read_Master_Log_Pos: 238631251
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 66342300
Relay_Master_Log_File: mysql.000009
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
(중략)
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error reconnecting to master 'repl@192.168.100.50:3306' - retry-time: 10 maximum-retries: 86400 message: Can't connect to MySQL server on '192.168.100.50' (111 "Connection refused")
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-289
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)
3. 다른 Galera node에 Slave 재연결
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> change master to master_host='192.168.100.51',master_user='repl',master_password='repl',master_port=3306,master_connect_retry=10,master_use_gtid=slave_pos;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql.000008
Read_Master_Log_Pos: 243336084
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 14209
Relay_Master_Log_File: mysql.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(생략)
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-291
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)
■ Slave 상태 체크
- Python을 통해서 간단하게 slave I/O, SQL Thread에 문제가 발생하였을 때 Email이 발송되도록 설정 ( Slave I/O, Slave SQL Thread가 No가 되었을 때 Email을 보냄 )
- Email은 각자에 맞게 수정 필요
#!/bin/python2
import requests
import json
import os
import sys
import pymysql
import smtplib
from email.mime.text import MIMEText
## TEXT
msg="Current Slave_IO Status: "
msg2="Current Slave_SQL status: "
## EMAIL
s = smtplib.SMTP('smtp.gmail.com', 587)
s.starttls()
s.login('******@gmail.com','ziobivgvgvpsstzh')
## Current Slave_IO/SQL Status Get
mysql_db = pymysql.connect(
user="root",
password="root",
host="localhost",
db='information_schema',
port=3306
)
cursor = mysql_db.cursor()
sql = "show slave status"
cursor.execute(sql)
result=cursor.fetchall()
result=result[0]
##Master_Host
C_Master_host = result[1:2]
C_Master_host="".join(C_Master_host)
##Slave_SQL
C_Slave_SQL = result[11:12]
C_Slave_SQL = "".join(C_Slave_SQL)
##Slave_IO
C_Slave_IO = result[10:11]
C_Slave_IO = "".join(C_Slave_IO)
## Slave_IO & Slave_SQL Status
Slave_IO = "No"
Slave_SQL = "No"
if Slave_IO == C_Slave_IO:
msg="Current_Master "+C_Master_host+"\n"+msg+"NO"
msg=MIMEText(msg)
msg['Subject'] = 'Slave_IO_Status_NO'
s.sendmail("******@gmail.com", "******@naver.com", msg.as_string())
print(msg)
exit
elif Slave_SQL == C_Slave_SQL:
msg2="Current_Master "+C_Master_host+"\n"+msg2+"NO"
msg2=MIMEText(msg2)
msg2['Subject'] = 'Slave_SQL_Status_NO'
s.sendmail("******@gmail.com", "******@naver.com", msg2.as_string())
else:
exit
■Single DR DB New DB Connect
- Bash Shell을 이용하여 Slave에 새로운 Master DB를 연결하도록 Script 작성
[root@slave monitor]# cat new.sh
#!/bin/bash
## NEW MASTER IP
echo "#### Enter The NEW MASTER IP ####"
cat /etc/hosts
read IP
## STOP SLAVE
connect='-uroot -proot'
mysql ${connect} -e "stop slave;"
mysql ${connect} -e "change master to master_host='${IP}',master_user='repl',master_password='repl',master_port=3306,master_connect_retry=10,master_use_gtid=slave_pos;"
mysql ${connect} -e "start slave;"
mysql -uroot -proot -e "show slave status\G" | grep "Master_Host\|Slave_IO_Running\|Slave_SQL_Running" | grep -v "Slave_SQL_Running_State"
[root@slave monitor]# sh +x new.sh # 192.168.100.50으로 Master DB 변경
#### Enter The NEW MASTER IP ####
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.100.50 galera1
192.168.100.51 galera2
192.168.100.52 galera3
192.168.100.50
Master_Host: 192.168.100.50
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
■참고
https://www.linuxbabe.com/mariadb/master-slave-replication-galera-cluster
Set Up MariaDB Master-Slave Replication with Galera Cluster on Ubuntu
You will learn how to add an asynchronous replication slave to MariaDB Galera cluster, which means the Galera cluster will be acting as a master.
www.linuxbabe.com
반응형
'MySQL & Maria' 카테고리의 다른 글
| MHA 구성 - MySQL ( Using Ansible ) (0) | 2022.09.22 |
|---|---|
| 성능테스트 SYSBENCH CUSTOM SCRIPT (0) | 2022.08.10 |
| [ Maria ] DDL시 Replication Delay 발생 (0) | 2022.04.26 |
| [ MySQL ] Online DDL - Varchar (0) | 2022.04.12 |
| [Maria] Maxscale GUI 설치 (2) | 2022.03.22 |
Contents
소중한 공감 감사합니다