[MySQL - 4node MHA & select 부하분산] use. NCP Loadbalancer

현재의 Master+Slave1,2,3 MHA 구성에서 Slave 2,3을 MHA에서 제외시킨 뒤 LoadBalancer를 통해 Slave 2,3을 select 용 부하분산을 할 예정입니다.

여기서 가장 중요한 점은 was단에서 read와 write를 각 IP별로 나눠줄 수 있어야합니다. 먼저 확인 후 진행하시길 바랍니다.

최종적으로 MHA manager + Master + Slave1 이 write작업 및 고가용성을 지원하며, Slave2 + Slave3은 read 부하분산용으로 사용 예정입니다. 따라서 write용 IP는 MHA의 VIP가 서비스 IP가 되며, read용 IP는 Loadbalancer의 도메인이 됩니다.

 

 

■ Slave2,3 MHA에서 제외 (mgr)

[root@jh-mgr ~]# vi /etc/mha.cnf


#ping interval, 3 times trial
ping_interval=5


#custom scripts for master ip failover
master_ip_failover_script=/var/log/masterha/app1/custom_scripts/master_ip_failover


#custom scripts for shutdown using fencing network custom script
#shutdown_script=/var/log/masterha/app1/custom_scripts/power_manager


#custom scripts for manual master switch
master_ip_online_change_script=/var/log/masterha/app1/custom_scripts/master_ip_online_change


[server1]
hostname=192.168.100.36
port=3306
ignore_fail=1


[server2]
hostname=192.168.100.37
port=3306
ignore_fail=1


------Slave 3, 4번 목록 삭제---------
[server3]
hostname=192.168.100.38
port=3306
ignore_fail=1


[server4]
hostname=192.168.100.39
port=3306
ignore_fail=1




[root@jh-mgr ~]# sshcheck
Wed Jul 27 09:26:16 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Jul 27 09:26:16 2022 - [info] Reading application default configuration from /etc/mha.cnf..
Wed Jul 27 09:26:16 2022 - [info] Reading server configuration from /etc/mha.cnf..
Wed Jul 27 09:26:16 2022 - [info] Starting SSH connection tests..
Wed Jul 27 09:26:17 2022 - [debug]
Wed Jul 27 09:26:16 2022 - [debug]  Connecting via SSH from root@192.168.100.36(192.168.100.36:22) to root@192.168.100.37(192.168.100.37:22)..
Wed Jul 27 09:26:16 2022 - [debug]   ok.
Wed Jul 27 09:26:17 2022 - [debug]
Wed Jul 27 09:26:17 2022 - [debug]  Connecting via SSH from root@192.168.100.37(192.168.100.37:22) to root@192.168.100.36(192.168.100.36:22)..
Wed Jul 27 09:26:17 2022 - [debug]   ok.
Wed Jul 27 09:26:17 2022 - [info] All SSH connection tests passed successfully.
Use of uninitialized value in exit at /usr/local/bin/masterha_check_ssh line 44.



[root@jh-mgr ~]# replcheck
Wed Jul 27 09:26:37 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Jul 27 09:26:37 2022 - [info] Reading application default configuration from /etc/mha.cnf..
Wed Jul 27 09:26:37 2022 - [info] Reading server configuration from /etc/mha.cnf..
Wed Jul 27 09:26:37 2022 - [info] MHA::MasterMonitor version 0.57.
Wed Jul 27 09:26:38 2022 - [info] GTID failover mode = 0
Wed Jul 27 09:26:38 2022 - [info] Dead Servers:
Wed Jul 27 09:26:38 2022 - [info] Alive Servers:
Wed Jul 27 09:26:38 2022 - [info]   192.168.100.36(192.168.100.36:3306)
Wed Jul 27 09:26:38 2022 - [info]   192.168.100.37(192.168.100.37:3306)
Wed Jul 27 09:26:38 2022 - [info] Alive Slaves:
Wed Jul 27 09:26:38 2022 - [info]   192.168.100.37(192.168.100.37:3306)  Version=8.0.28 (oldest major version between slaves) log-bin:enabled
Wed Jul 27 09:26:38 2022 - [info]     Replicating from 192.168.100.36(192.168.100.36:3306)
Wed Jul 27 09:26:38 2022 - [info] Current Alive Master: 192.168.100.36(192.168.100.36:3306)
Wed Jul 27 09:26:38 2022 - [info] Checking slave configurations..
Wed Jul 27 09:26:38 2022 - [info]  read_only=1 is not set on slave 192.168.100.37(192.168.100.37:3306).
Wed Jul 27 09:26:38 2022 - [info] Checking replication filtering settings..
Wed Jul 27 09:26:38 2022 - [info]  binlog_do_db= , binlog_ignore_db=
Wed Jul 27 09:26:38 2022 - [info]  Replication filtering check ok.
Wed Jul 27 09:26:38 2022 - [info] GTID (with auto-pos) is not supported
Wed Jul 27 09:26:38 2022 - [info] Starting SSH connection tests..
Wed Jul 27 09:26:39 2022 - [info] All SSH connection tests passed successfully.
Wed Jul 27 09:26:39 2022 - [info] Checking MHA Node version..
Wed Jul 27 09:26:39 2022 - [info]  Version check ok.
Wed Jul 27 09:26:39 2022 - [info] Checking SSH publickey authentication settings on the current master..
Wed Jul 27 09:26:39 2022 - [info] HealthCheck: SSH to 192.168.100.36 is reachable.
Wed Jul 27 09:26:39 2022 - [info] Master MHA Node version is 0.57.
Wed Jul 27 09:26:39 2022 - [info] Checking recovery script configurations on 192.168.100.36(192.168.100.36:3306)..
Wed Jul 27 09:26:39 2022 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data --output_file=/var/log/masterha/app5/save_binary_logs_test --manager_version=0.57 --start_file=mariadb-bin.000015
Wed Jul 27 09:26:39 2022 - [info]   Connecting to root@192.168.100.36(192.168.100.36:22)..
  Creating /var/log/masterha/app5 if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data, up to mariadb-bin.000015
Wed Jul 27 09:26:39 2022 - [info] Binlog setting check done.
Wed Jul 27 09:26:39 2022 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed Jul 27 09:26:39 2022 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='repl' --slave_host=192.168.100.37 --slave_ip=192.168.100.37 --slave_port=3306 --workdir=/var/log/masterha/app5 --target_version=8.0.28 --manager_version=0.57 --relay_dir=/data --current_relay_log=relay-log.000002  --slave_pass=xxx
Wed Jul 27 09:26:39 2022 - [info]   Connecting to root@192.168.100.37(192.168.100.37:22)..
  Checking slave recovery environment settings..
    Relay log found at /data, up to relay-log.000002
    Temporary relay log file is /data/relay-log.000002
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Jul 27 09:26:40 2022 - [info] Slaves settings check done.
Wed Jul 27 09:26:40 2022 - [info]
192.168.100.36(192.168.100.36:3306) (current master)
+--192.168.100.37(192.168.100.37:3306)

Wed Jul 27 09:26:40 2022 - [info] Checking replication health on 192.168.100.37..
Wed Jul 27 09:26:40 2022 - [info]  ok.
Wed Jul 27 09:26:40 2022 - [info] Checking master_ip_failover_script status:
Wed Jul 27 09:26:40 2022 - [info]   /var/log/masterha/app1/custom_scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.100.36 --orig_master_ip=192.168.100.36 --orig_master_port=3306
Wed Jul 27 09:26:40 2022 - [info]  OK.
Wed Jul 27 09:26:40 2022 - [warning] shutdown_script is not defined.
Wed Jul 27 09:26:40 2022 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

 

 

■ Slave 2, 3 상태 -> read_only

[root@jh-slave2 ~]# vi /etc/my.cnf
read_only=1


mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

 

 

■ 콘솔에서 Load balancer 생성

 

 

 

※ DB서버 ACG port 3306 open되어야 합니다. 

 

 

■ 같은 대역에 있는 Client 서버에서 LB로 접속 

## Client서버에서 접속할 유저 생성

<Master>
mysql> create user test@'10.62.8.243' identified by 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to test@'10.62.8.243';
Query OK, 0 rows affected (0.00 sec)




## LB를 통해 DB접속 (exit이후 재접속시 LB알고리즘에 따라 교차 접속확인)
[root@myclient ~]# mysql -h 10.62.8.243 -utest -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| jh-slave2  |
+------------+
1 row in set (0.001 sec)


MySQL [(none)]> exit
Bye

[root@myclient ~]# mysql -h 10.62.8.243 -utest -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| jh-slave3  |
+------------+
1 row in set (0.001 sec)

 

 

■ 대량 데이터 삽입(10만건)

<Master>
mysql> create database jh;
Query OK, 1 row affected (0.00 sec)

mysql> use jh;
Database changed

mysql> CREATE TABLE Nonprimenumber(num INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE myFunction()
    -> BEGIN
    ->     DECLARE i INT DEFAULT 1;
    ->     WHILE (i <= 100000) DO
    ->         INSERT INTO `Nonprimenumber` VALUE (i);
    ->         SET i = i + 1;
    ->     END WHILE;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call myFunction();



## Slave2의 replication을 늦게 맺워 줬더니 insert 속도 차이가 난다.
[root@myclient ~]# mysql -h 10.62.8.243 -utest -ptest -e'select count(*) from jh.Nonprimenumber;'
+----------+
| count(*) |
+----------+
|    22405 |
+----------+

[root@myclient ~]# mysql -h 10.62.8.243 -utest -ptest -e'select count(*) from jh.Nonprimenumber;'
+----------+
| count(*) |
+----------+
|    91205 |
+----------+




## 테이블 조회시 slave2에 붙을땐 10만건이 조회되지 않음 -> 비동기 방식이라 LB를 통해 부하분산을 하더라도 붙는 세션별로 insert된 데이터가 다를 경우가 있어 조회시 데이터가 없을 수 있을 거 같다. 하지만 크게 문제될 정도의 속도 차이는 아니며 대량의 insert 작업을 배치작업으로 조금씩 쪼개어 작업한다면 문제 없을 거 같습니다.

[root@myclient ~]# mysql -h 10.62.8.243 -utest -ptest -e'select * from jh.Nonprimenumber;'
...
|  99989 |
|  99990 |
|  99991 |
|  99992 |
|  99993 |
|  99994 |
|  99995 |
|  99996 |
|  99997 |
|  99998 |
|  99999 |
| 100000 |
+--------+

 

 

사진 출처

 

https://hoing.io/archives/9175

MySQL MHA(Master High Availability) 1 - MHA 기능 설명 및 아키텍처 설명