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 로 인식 완료