[MARIA] Maxscale Replication

기존 MariaDB를 Replication을 걸어두고 윗단에 Maxscale을 두어서 Auto Failover / VIP 접근 /  READ & WRITE 분산을 테스트 해보았다.  아직까지 Replication에서 쓰고있는 고객들은 보지 못하였지만 나중을 위해 테스트해본 것을 기록하였다.

 

0.TEST 환경

Master	172.40.40.189
Slave	172.40.40.188
Maxscale	172.40.40.187

 

1.MAXSCALE 설치

** 1.필요 library 설치 **
[root@maxscale ~]# yum -y install libcurl libaio openssl gnutls libatomic


** 2.Maxscale 유저 그룹 생성 **
[root@maxscale ~]# groupadd maxscale
[root@maxscale ~]# useradd -g maxscale maxscale


** 3.압축 해제 & MV **
[root@maxscale ~]# tar -zxvf maxscale-2.5.6.rhel.7.tar.gz
[root@maxscale ~]# mv maxscale-2.5.6.rhel.7 /maxscale


** 4.maxscale plugin Directory 생성 **
[root@maxscale ~]# mkdir -p /maxscale/var/mysql/plugin
-생성 하지 않을 시 alert  : Can't access '/maxscale/var//mysql/plugin’. 에러가 난다.


** 5.cnf File 수정 **
[root@maxscale etc]# cp maxscale.cnf.template maxscale.cnf
[root@maxscale etc]# vi maxscale.cnf


[server1]
type=server
address=172.40.40.189
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=172.40.40.188
port=3306
protocol=MariaDBBackend


[Maria-Monitor]
type=monitor
module=mariadbmon
servers=server1, server2
user=maxscale
password=1
monitor_interval=2000ms
auto_failover=true
auto_rejoin=true


[Splitter-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscale
password=1
###
###readwritesplit 라우터는 일관성을 유지하면서 클러스터의 읽기 전용 처리 능력을 높이기 위해 설계되었습니다. 이는 쿼리로드를 읽기 및 쓰기 쿼리로 분할하여 수행됩니다. 데이터를 수정하지 않는 읽기 쿼리는 여러 노드에 분산되고 모든 쓰기 쿼리는 단일 노드로 전송됩니다.

[Splitter-Listener]
type=listener
service=Splitter-Service
protocol=MariaDBClient
port=3306

** 6.Maxscale 시작 **
/maxscale/bin/maxscale --basedir=/maxscale --user=maxscale

 

2.READ WRITE 분산확인

** WRITE TEST **
mysql -umaxscale -p -P3306 -h 172.40.40.187 -e "use test; insert into host values (@@hostname);"

MariaDB [test]> select * from host;
+--------+
| name  |
+--------+
| master |  --> Insert DML시 master에서 실행된거 볼수 있다.
+--------+
1 row in set (0.00 sec)

** READ TEST **
mysql -umaxscale -p -P3306 -h 172.40.40.187 -e "select @@hostanem;"
+------------+
| @@hostname |
+------------+
| slave      |  --> Select는 slave에서 하고잇음
+------------+

 

3. Auto Failover TEST

** 1.기존 상태 **
[root@localhost ~]# while true; do maxctrl list servers; sleep 2; done
┌─────────┬───────────────┬──────┬─────────────┬─────────────────┬──────────────┐
│ Server  │ Address       │ Port │ Connections │ State           │ GTID         │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server1 │ 172.40.40.189 │ 3306 │ 0           │ Master, Running │ 0-1-16196795 │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server2 │ 172.40.40.188 │ 3306 │ 0           │ Slave, Running  │ 0-1-16196795 │
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴──────────────┘


** 2.Master Server Down **
[root@master ~]# mysqladmin -uroot -proot shutdown


[root@localhost ~]# while true; do maxctrl list servers; sleep 2; done
┌─────────┬───────────────┬──────┬─────────────┬────────────────┬──────────────┐
│ Server  │ Address       │ Port │ Connections │ State          │ GTID         │
├─────────┼───────────────┼──────┼─────────────┼────────────────┼──────────────┤
│ server1 │ 172.40.40.189 │ 3306 │ 0           │ Down           │ 0-1-16196795 │
├─────────┼───────────────┼──────┼─────────────┼────────────────┼──────────────┤
│ server2 │ 172.40.40.188 │ 3306 │ 0           │ Slave, Running │ 0-1-16196795 │
└─────────┴───────────────┴──────┴─────────────┴────────────────┴──────────────┘
┌─────────┬───────────────┬──────┬─────────────┬────────────────┬──────────────┐
│ Server  │ Address       │ Port │ Connections │ State          │ GTID         │
├─────────┼───────────────┼──────┼─────────────┼────────────────┼──────────────┤
│ server1 │ 172.40.40.189 │ 3306 │ 0           │ Down           │ 0-1-16196795 │
├─────────┼───────────────┼──────┼─────────────┼────────────────┼──────────────┤
│ server2 │ 172.40.40.188 │ 3306 │ 0           │ Slave, Running │ 0-1-16196795 │
└─────────┴───────────────┴──────┴─────────────┴────────────────┴──────────────┘
(중략)
┌─────────┬───────────────┬──────┬─────────────┬────────────────┬──────────────┐
│ Server  │ Address       │ Port │ Connections │ State          │ GTID         │
├─────────┼───────────────┼──────┼─────────────┼────────────────┼──────────────┤
│ server1 │ 172.40.40.189 │ 3306 │ 0           │ Down           │ 0-1-16196795 │
├─────────┼───────────────┼──────┼─────────────┼────────────────┼──────────────┤
│ server2 │ 172.40.40.188 │ 3306 │ 0           │ Slave, Running │ 0-1-16196795 │
└─────────┴───────────────┴──────┴─────────────┴────────────────┴──────────────┘
┌─────────┬───────────────┬──────┬─────────────┬─────────────────┬──────────────┐
│ Server  │ Address       │ Port │ Connections │ State           │ GTID         │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server1 │ 172.40.40.189 │ 3306 │ 0           │ Down            │ 0-1-16196795 │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server2 │ 172.40.40.188 │ 3306 │ 0           │ Master, Running │ 0-1-16196795 │  --> Master로 변한것을 확인할 수 있다.
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴──────────────┘


** 3.Log 조회 **
#Slave Server log
2021-02-26  8:11:04 140345104697088 [Note] Slave: received end packet from server, apparent master shutdown:
2021-02-26  8:11:04 140345104697088 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql.000012' at position 377; GTID position '0-1-16196795'
2021-02-26  8:11:04 140345104697088 [ERROR] Slave I/O: error reconnecting to master 'maxscale@172.40.40.189:3306' - retry-time: 60  maximum-retries: 86400  message: Can't connect to MySQL server on '172.40.40.189' (111 "Connection refused"), Internal MariaDB error code: 2003
2021-02-26  8:11:13 140345104389888 [Note] Error reading relay log event: slave SQL thread was killed
2021-02-26  8:11:13 140345104389888 [Note] Slave SQL thread exiting, replication stopped in log 'mysql.000012' at position 377; GTID position '0-1-16196795'
2021-02-26  8:11:13 140345104697088 [Note] Slave I/O thread killed during or after a reconnect done to recover from failed read
2021-02-26  8:11:13 140345104697088 [Note] Slave I/O thread exiting, read up to log 'mysql.000012', position 377; GTID position 0-1-16196795
2021-02-26  8:11:13 140345239811840 [Note] Deleted Master_info file '/data/master.info'.
2021-02-26  8:11:13 140345239811840 [Note] Deleted Master_info file '/data/relay-log.info'.

#Maxscale log
2021-02-26 10:45:00   error  : Monitor was unable to connect to server server1[172.40.40.189:3306] : ''
2021-02-26 10:45:00   notice : Server changed state: server1[172.40.40.189:3306]: master_down. [Master, Running] -> [Down]
2021-02-26 10:45:00   warning: [mariadbmon] Master has failed. If master does not return in 4 monitor tick(s), failover begins.
2021-02-26 10:45:08   notice : [mariadbmon] Selecting a server to promote and replace 'server1'. Candidates are: 'server2'.
2021-02-26 10:45:08   warning: [mariadbmon] Slave 'server2' has gtid_strict_mode disabled. Enabling this setting is recommended. For more information, see https://mariadb.com/kb/en/library/gtid/#gtid_strict_mode
2021-02-26 10:45:08   notice : [mariadbmon] Selected 'server2'.
2021-02-26 10:45:08   notice : [mariadbmon] Performing automatic failover to replace failed master 'server1'.
2021-02-26 10:45:08   notice : [mariadbmon] Failover 'server1' -> 'server2' performed.
2021-02-26 10:45:08   notice : Server changed state: server2[172.40.40.188:3306]: new_master. [Slave, Running] -> [Master, Running]


** 4.Auto_rejoin **

**Auto rejoin하기전에 Server2(Master)에 Data Insert
MariaDB [testdb]> insert into test values(2);
MariaDB [testdb]> insert into test values(3);
MariaDB [testdb]> insert into test values(4);

[root@master ~]# mysqld --user=mysql &

[root@localhost ~]# while true; do maxctrl list servers; sleep 2; done
┌─────────┬───────────────┬──────┬─────────────┬─────────────────┬──────────────┐
│ Server  │ Address       │ Port │ Connections │ State           │ GTID         │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server1 │ 172.40.40.189 │ 3306 │ 0           │ Down            │ 0-1-16196795 │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server2 │ 172.40.40.188 │ 3306 │ 0           │ Master, Running │ 0-2-16196798 │
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴──────────────┘
┌─────────┬───────────────┬──────┬─────────────┬─────────────────┬──────────────┐
│ Server  │ Address       │ Port │ Connections │ State           │ GTID         │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server1 │ 172.40.40.189 │ 3306 │ 0           │ Slave, Running  │ 0-2-16196798 │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server2 │ 172.40.40.188 │ 3306 │ 0           │ Master, Running │ 0-2-16196798 │
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴──────────────┘
┌─────────┬───────────────┬──────┬─────────────┬─────────────────┬──────────────┐
│ Server  │ Address       │ Port │ Connections │ State           │ GTID         │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server1 │ 172.40.40.189 │ 3306 │ 0           │ Slave, Running  │ 0-2-16196798 │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server2 │ 172.40.40.188 │ 3306 │ 0           │ Master, Running │ 0-2-16196798 │
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴──────────────┘
┌─────────┬───────────────┬──────┬─────────────┬─────────────────┬──────────────┐
│ Server  │ Address       │ Port │ Connections │ State           │ GTID         │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server1 │ 172.40.40.189 │ 3306 │ 0           │ Slave, Running  │ 0-2-16196798 │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server2 │ 172.40.40.188 │ 3306 │ 0           │ Master, Running │ 0-2-16196798 │
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴──────────────┘

[root@master ~]# mysql -uroot -proot
MariaDB [(none)]> select * from testdb.test;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
--> Data도 잘 복사해 간것을 볼 수 있다.
4 rows in set (0.00 sec)




** 5.log 조회 **

#Maxscale Log
2021-02-26 10:50:35   notice : Server changed state: server1[172.40.40.189:3306]: server_up. [Down] -> [Running]
2021-02-26 10:50:35   notice : [mariadbmon] Directing standalone server 'server1' to replicate from 'server2'.
2021-02-26 10:50:35   notice : [mariadbmon] Slave connection from server1 to [172.40.40.188]:3306 created and started.
2021-02-26 10:50:35   notice : [mariadbmon] 1 server(s) redirected or rejoined the cluster.
2021-02-26 10:50:35   notice : Server changed state: server1[172.40.40.189:3306]: new_slave. [Running] -> [Slave, Running]



** 6.마지막 Switchover로 server1 Master 재변경 **
[root@localhost ~]# maxctrl list monitors
┌───────────────┬─────────┬──────────────────┐
│ Monitor       │ State   │ Servers          │
├───────────────┼─────────┼──────────────────┤
│ Maria-Monitor │ Running │ server1, server2 │
└───────────────┴─────────┴──────────────────┘
[root@localhost ~]# maxctrl call command mariadbmon switchover Maria-Monitor server1 server2


[root@localhost ~]# maxctrl list servers
┌─────────┬───────────────┬──────┬─────────────┬─────────────────┬──────────────┐
│ Server  │ Address       │ Port │ Connections │ State           │ GTID         │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server1 │ 172.40.40.189 │ 3306 │ 0           │ Master, Running │ 0-2-16196798 │   --> Server1이 Master로 변경됨
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────────────┤
│ server2 │ 172.40.40.188 │ 3306 │ 0           │ Slave, Running  │ 0-2-16196798 │
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴──────────────┘

#Maxscale Log
2021-02-26 10:59:26   warning: [mariadbmon] Killed connection id 12 to 'server2' from super-user 'root' to prevent writes.
2021-02-26 10:59:26   notice : [mariadbmon] Slave connection from server2 to [172.40.40.189]:3306 created and started.
2021-02-26 10:59:26   notice : [mariadbmon] All redirected slaves successfully started replication from 'server1'.
2021-02-26 10:59:26   notice : [mariadbmon] Switchover 'server2' -> 'server1' performed.
2021-02-26 10:59:26   notice : Server changed state: server1[172.40.40.189:3306]: new_master. [Slave, Running] -> [Master, Running]
2021-02-26 10:59:26   notice : Server changed state: server2[172.40.40.188:3306]: new_slave. [Running] -> [Slave, Running]