MySQL & Maria
[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]
반응형
'MySQL & Maria' 카테고리의 다른 글
[Maria] Thread 모니터링 (0) | 2021.10.07 |
---|---|
[MariaDB] MSSQL DBLINK ( Using Connection Engine ) (0) | 2021.09.17 |
[MySQL] Connection 파라미터 설정 (0) | 2021.09.08 |
MySQL 백업 & 복구 ( mysqldump ) (0) | 2021.08.25 |
[MySQL] MySQL User 생성 & 권한 부여 (0) | 2021.08.25 |
Contents
소중한 공감 감사합니다