MHA 를 사용할 때 가장 아쉬운 점 하나가 READ ONLY VIP 관리의 어려움입니다.

MaxScale + MHA 조합하여 아키텍처 구성 시 WAS 단에서 DB VIP 가 아닌 MaxScale 서버 IP만 연결해주면 

MaxScale에서 DB R/W load balancing 을 해주기 때문에 MHA 의 단점이 해결됩니다.

 

-1. MHA DB Failover 감지

 

Sat Mar 23 04:11:26 2019 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Sat Mar 23 04:11:50 2019 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Sat Mar 23 04:11:50 2019 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/logs001/masvc01/10.3/binary --output_file=/engn001/masvc01/mha/workdir/save_binary_logs_test --manager_version=0.57 --binlog_prefix=mysql-bin
Sat Mar 23 04:11:50 2019 - [info] HealthCheck: SSH to  is reachable.
Sat Mar 23 04:11:53 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on (111))
Sat Mar 23 04:11:53 2019 - [warning] Connection failed 2 time(s)..
Sat Mar 23 04:11:56 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on  (111))
Sat Mar 23 04:11:56 2019 - [warning] Connection failed 3 time(s)..
Sat Mar 23 04:11:59 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on  (111))
Sat Mar 23 04:11:59 2019 - [warning] Connection failed 4 time(s)..
Sat Mar 23 04:11:59 2019 - [warning] Master is not reachable from health checker!
Sat Mar 23 04:11:59 2019 - [warning] Master  is not reachable!
Sat Mar 23 04:11:59 2019 - [warning] SSH is reachable. 
.
.
.

=> MHA 에서 DB Down 감지하여 Failover 진행

 

-2. MaxScale DB status 체크

MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
dbserv1            | 11.111.111.111    |  3312 |           0 | Master, Running
dbserv2            | 22.222.222.222    |  3312 |           0 | Slave, Running
dbserv3            | 33.333.333.333    |  3312 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+-------------------- 

=> Master Node Down 전

 

MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
dbserv1            | 11.111.111.111    |  3312 |           0 | Down
dbserv2            | 22.222.222.222   |  3312 |           0 | Slave, Running
dbserv3            | 33.333.333.333  |  3312 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
dbserv1            | 11.111.111.111    |  3312 |           0 | Down
dbserv2            | 22.222.222.222   |  3312 |           0 | Slave, Running
dbserv3            | 33.333.333.333  |  3312 |           0 | Master, Running
-------------------+-----------------+-------+-------------+--------------------

 

=> Master Node Failover 후. dbserv3 을 새로운 Master Node 로 인식 완료