close
프로필 배경
프로필 로고

JH 개인 블로그

DataBase/MySQL & MariaDB · 2023. 1. 27. fullscreen 넓게보기

[MySQL - Read/Write Split 부하분산 구성] use. ProxySQL

MySQL 3node 운영중 Master노드의 리소스 과부화 현상을 해소하고자 Read/Write Split을 하게되었습니다.

 

Proxy서버로 ProxySQL을 사용하여 최종적으로 Write트랜잭션은 Master노드로만, Read 트랜잭션은 모든 노드로 가도록 설정하습니다.

 

 

테스트 환경
Hostname
IP
VIP
DB Version
proxy
192.168.100.84
 
proxysql-2.4.4
master
192.168.100.80
192.168.100.88
MySQL 8.0
slave1
192.168.100.81
 
MySQL 8.0
slave2
192.168.100.83
 
MySQL 8.0

 

 

사전 구성

MySQL서버 3nodes MHA구성

MariaDB [(none)]> show slave hosts\G;
*************************** 1. row ***************************
Server_id: 3
     Host:
     Port: 3306
Master_id: 1
*************************** 2. row ***************************
Server_id: 2
     Host:
     Port: 3306
Master_id: 1
2 rows in set (0.000 sec)

 

 

■ Proxy SQL install 

[root@proxy1 ~]# yum -y install epel-release

[root@proxy1 ~]# wget https://github.com/sysown/proxysql/releases/download/v2.4.4/proxysql-2.4.4-1-centos7.x86_64.rpm

[root@proxy1 ~]# yum -y install proxysql-2.4.4-1-centos7.x86_64.rpm

[root@proxy1 ~]# systemctl start proxysql


[root@proxy1 ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032

mysql> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

 

 

■ ProxySQL 구성 (proxy 서버에서 진행)
  1. MySQL 서버 등록 ( mysql_servers )
  2. Query 실행할 유저 등록 ( mysql_users )
  3. Rules 등록 ( mysql_query_rules )
[root@proxy1 ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032

insert into mysql_servers (hostgroup_id, hostname, port) values (1,'192.168.100.88',3306);
INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('kim', 'kim', 1, 1);
INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '.*@.*', 1, 1);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;





## 클라이언트에서 접속을 위한 계정 추가  (클라이언트에서 ProxySQL로 원격 접속할 수 있는 계정)
MYSQL 서버에 kim 유저 생성

MariaDB [(none)]> create user 'kim'@'%' identified by 'kim';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on *.* to 'kim'@'%';
Query OK, 0 rows affected (0.00 sec)

 

 

■ monitor 유저 생성  (ProxySQL에서 MySQL로 원격 접속할 수 있는 계정)
해당 monitor 유저로 MySQL Database의 상태를 모니터링할수 있다.
[root@proxy1 ~]# cat /etc/proxysql.cnf | grep monitor
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
    monitor_username="monitor"
    monitor_password="monitor"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500



### MYSQL 서버에 monitor 유저 생성
MariaDB [(none)]> create user 'monitor'@'192.168.100.%' identified by 'monitor';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on *.* to 'monitor'@'192.168.100.%';
Query OK, 0 rows affected (0.00 sec)

 

 

■ Query 테스트 (proxy 서버)
proxy를 통해 접속
##Write
[root@proxy1 ~]# mysql -h 192.168.100.84 -P6033 -ukim -pkim -e "insert into kim.kim values (1);"


## Read
[root@proxy1 ~]# mysql -h 192.168.100.84 -P6033 -ukim -pkim -e "select * from kim.kim;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| i    |
+------+
|    1 |
+------+

 

 

■ Read/Write Split 설정
VIP: 192.168.100.88
Master : 192.168.100.80 / Slave1 : 192.168.100.81 / Slave2 : 192.168.100.83
Hostgroup1 : Master IP
Hostgroup2 : Master IP , Slave1 IP, Slave 2 IP
### Server 설정
[root@proxy1 ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032

INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('192.168.100.80',1,3306,1000,0);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('192.168.100.80',2,3306,1000,10);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('192.168.100.81',2,3306,1000,10);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('192.168.100.83',2,3306,1000,10);

INSERT INTO mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) VALUES (1,2);
LOAD MYSQL SERVERS TO RUNTIME; 
SAVE MYSQL SERVERS TO DISK;



mysql> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.100.80 | 3306 | 0         | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.100.80 | 3306 | 0         | ONLINE | 1000   | 0           | 1000            | 10                  | 0       | 0              |         |
| 2            | 192.168.100.81 | 3306 | 0         | ONLINE | 1000   | 0           | 1000            | 10                  | 0       | 0              |         |
| 2            | 192.168.100.83 | 3306 | 0         | ONLINE | 1000   | 0           | 1000            | 10                  | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)




## 서버 연결 성공 확인
mysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+----------------+------+------------------+-------------------------+---------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.100.81 | 3306 | 1668751461478938 | 1018                    | NULL          |
| 192.168.100.80 | 3306 | 1668751460876963 | 973                     | NULL          |
| 192.168.100.83 | 3306 | 1668751460275018 | 957                     | NULL          |
| 192.168.100.80 | 3306 | 1668751401382812 | 1107                    | NULL          |
| 192.168.100.81 | 3306 | 1668751400828708 | 1007                    | NULL          |
| 192.168.100.83 | 3306 | 1668751400274594 | 966                     | NULL          |
| 192.168.100.81 | 3306 | 1668751341335961 | 1023                    | NULL          |
| 192.168.100.83 | 3306 | 1668751340805075 | 1203                    | NULL          |
| 192.168.100.80 | 3306 | 1668751340274211 | 985                     | NULL          |
| 192.168.100.81 | 3306 | 1668751281387623 | 1147                    | NULL          |
+----------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)




## 클라이언트에서 접속을 위한 계정 추가  (클라이언트에서 ProxySQL로 원격 접속할 수 있는 계정)
MYSQL DB쪽에도 생성되어있어야한다.
INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('kim', 'kim', 1, 1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| kim      | kim      | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.00 sec)




### Query Split을 위한 규칙 생성
insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest) values('kim',1,1,3,'^SELECT.*FOR UPDATE');
insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest) values('kim',2,1,3,'^SELECT');

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;


mysql> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest        | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | attributes | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| 1       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                | .*@.*         | 0                    | CASELESS     | NULL    | NULL            | 1                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     |            | NULL    |
| 2       | 1      | kim      | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT.*FOR UPDATE | NULL          | 0                    | CASELESS     | NULL    | NULL            | 1                     | NULL      | NULL               | NULL          | NULL      | NULL    | 3       | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
| 3       | 1      | kim      | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT             | NULL          | 0                    | CASELESS     | NULL    | NULL            | 2                     | NULL      | NULL               | NULL          | NULL      | NULL    | 3       | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
3 rows in set (0.00 sec)

 

 

■ Mysql server 작업 (Master)
db server로부터 데이터를 가져올 monitor 유저 생성
MariaDB [kim]> create user 'monitor'@'%' identified by 'monitor';
Query OK, 0 rows affected (0.001 sec)

MariaDB [kim]> grant all on *.* to 'monitor'@'%';
Query OK, 0 rows affected (0.000 sec)

 

 

 

■ Write Test 

## proxy를 통해 3번 데이터 write
[root@proxy1 proxysql]# mysql -h192.168.100.84 -P6033 -ukim -pkim -e "insert into kim.host values(@@hostname);"
[root@proxy1 proxysql]# mysql -h192.168.100.84 -P6033 -ukim -pkim -e "insert into kim.host values(@@hostname);"
[root@proxy1 proxysql]# mysql -h192.168.100.84 -P6033 -ukim -pkim -e "insert into kim.host values(@@hostname);"



## Master서버로 데이터 들어오는것 확인
MariaDB [(none)]> select * from kim.host;
+--------+
| i      |
+--------+
| master |
| master |
| master |
+--------+
3 rows in set (0.000 sec)



[root@proxy1 proxysql]# while true; do sleep 1; mysql -h192.168.100.84 -P6033 -ukim -pkim -e "insert into kim.host values(@@hostname)"; done

MariaDB [(none)]> select * from kim.host;
+--------+
| i      |
+--------+
| master |
| master |
| master |
| master |
| master |
| master |
| master |
| master |
| master |
| master |
| master |
| master |
+--------+
12 rows in set (0.000 sec)

 

 

■ Read Test 

## 조회할때 hostname이 바뀌는 것을 확인

[root@proxy1 proxysql]# while true; do mysql -h192.168.100.84 -P6033 -ukim -pkim -e "select @@hostname;"; sleep 1; done
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| master     |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| slave1     |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| slave2     |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| slave2     |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| slave1     |
+------------+

 

 

 

Failover Test

■ Master kill & Failover 

[root@master ~]# ps -ef | grep mysql
root     11703 24230  0 13:41 pts/0    00:00:00 grep --color=auto mysql
mysql    18746     1  0 Nov17 ?        00:01:01 ./mysqld --user=mysql


[root@master ~]# kill -9 18746


Selected 192.168.100.81(192.168.100.81:3306) as a new master.
192.168.100.81(192.168.100.81:3306): OK: Applying all logs succeeded.
192.168.100.81(192.168.100.81:3306): OK: Activated master IP address.
192.168.100.83(192.168.100.83:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.100.83(192.168.100.83:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.100.81(192.168.100.81:3306)
192.168.100.81(192.168.100.81:3306): Resetting slave info succeeded.
Master failover to 192.168.100.81(192.168.100.81:3306) completed successfully.

 

 

■ Read Test
기존 master를 제외한 나머지 노드로만 read 세션 분산
mysql -h192.168.100.84 -P6033 -ukim -pkim -e "select @@hostname;";

mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| slave1     |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| slave2     |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| slave2     |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| slave1     |
+------------+

 

 

■ Write Test 

MariaDB [(none)]> select * from kim.host;
+--------+
| i      |
+--------+
| slave1 |
| slave1 |
| slave1 |
| slave1 |
| slave1 |
| slave1 |
| slave1 |
+--------+
7 rows in set (0.00 sec)

 

 

테스트에서는 VIP로 Write Hostgroup에 추가했지만,  ProxySQL에서 자체적으로 Failover를 감지하고 Hostgroup을 바꿔주기때문에 그냥 Master IP로 설정해도 무방합니다.

 

 

 

참고

 

https://proxysql.com/documentation/global-variables/mysql-variables/

 

MySQL Variables - ProxySQL

MySQL Variables List of MySQL Variables NOTE: You can click on the variable name to jump […]

proxysql.com

저작자표시

'DataBase > MySQL & MariaDB' 카테고리의 다른 글

[MySQL - Orchestrator Takeover & Failover 테스트] part 2  (0) 2023.02.05
[MySQL - Orchestrator 구축] part 1  (2) 2023.02.05
[MySQL - Tunner 설치]  (0) 2022.11.05
[MySQL - mysql-proxy] part 2. Read/Write Split & 부하테스트  (0) 2022.09.27
[MySQL - mysql-proxy 설치] part 1.  (0) 2022.09.27
DataBase/MySQL & MariaDB 관련 글
더 보기

티스토리툴바