[MySQL] ProxySQL

들어가며

ProxySQL이란?

• 서비스 운영시 MySQL 서버를 여러대로 구성해야하는 경우가 있을 수 있다. (ex. Replication, Sharding...)
• 위와 같은 경우 Application Server 수가 증가할수록 MySQL 서버와 맺게되는 커넥션 수가 급격하게 늘어난다.
• 예를 들어 MySQL 서버가 20대, Application Server가 100대, Application Server별로 커넥을 10개를 유지한다면 20 * 100 * 10 = 20000 커넥션이 발생한다.
• 이런 이슈를 피하기 위해 중간에 Middle Ware 개념으로 프록시 역할을 수행해주는 솔루션이 ProxySQL이다.
• 커넥션의 효율적인 관리, 라우팅 등 여러 기능을 수행한다.

ProxySQL 설치하기

설명

• ProxySQL 서버 1대와 MySQL 서버 여러대(Replication)를 구성하는 방법을 설명한다.
• ProxySQL는 MySQL 기반으로 동작하기 때문에 MySQL 서버 뿐만 아니라, ProxySQL 서버에도 MySQL를 설치한 후 아래 예제를 진행한다.
• 서버 운영체제는 ubuntu를 사용한다.
• 서버 구성
  • ProxySQL
    • 192.168.56.22:6033
  • MySQL
    • 192.168.56.23:3306 (mysql-master)
    • 192.168.56.24:3306 (mysql-slave-01)
    • 192.168.56.25:3306 (mysql-slave-02)
• 참고
  • MySQL 설치 : https://sg-choi.tistory.com/268
  • MySQL Replication : https://sg-choi.tistory.com/597

ProxySQL 설치

• release 참고 : https://github.com/sysown/proxysql/releases
• 설치

wget https://github.com/sysown/proxysql/releases/download/v2.2.0/proxysql_2.2.0-ubuntu20_amd64.deb \
&& sudo dpkg -i proxysql_2.2.0-ubuntu20_amd64.deb

• 설치 완료 후 버전 확인

proxysql --version

ProxySQL 실행

• 실행

sudo service proxysql start

• 종료(참고)

sudo service proxysql stop

ProxySQL Admin Interface 접속

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

MySQL 서버 모니터링을 위한 계정 설정

• 모니터링 계정 설정 (ProxySQL에서 MySQL로 원격 접속할 수 있는 계정)

UPDATE global_variables SET variable_value = 'ubuntu' WHERE variable_name = 'mysql-monitor_username';
UPDATE global_variables SET variable_value = '123456' WHERE variable_name = 'mysql-monitor_password';

• 설정 확인

SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

• 설정 적용

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

MySQL 서버 연결하기

• MySQL 서버 정보 추가

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.56.23', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.56.24', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.56.25', 3306);

• MySQL 서버 추가 확인

SELECT * FROM mysql_servers;

• MySQL 서버 추가 반영

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

MySQL 서버 연결 성공 확인

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

클라이언트에서 접속을 위한 계정 추가

• 계정 추가 (클라이언트에서 ProxySQL로 원격 접속할 수 있는 계정)

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('ubuntu', '123456', 1);

• 계정 반영

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

클라이언트에서 접속 확인

• 아래 명령어로 여러번 조회해보면 접속된 MySQL 서버 호스트명이 계속 바뀌는 것을 확인할 수 있다.

mysql -uubuntu -p123456 -h 127.0.0.1 -P6033 -e "SELECT @@hostname";

앞에서 설정한 내용들 파일로 백업(필요할 경우 진행)

• 설정 백업

SELECT CONFIG INTO OUTFILE /tmp/backup.cfg;
SAVE CONFIG TO FILE  /tmp/backup.cfg;

• /etc/proxysql.cnf 내용 조회

SELECT CONFIG FILE;

Read/Write Split

설명

• Query Rule에 따라서 SELECT 쿼리일 경우 slave에서 처리를 하고, 그 외의 쿼리일 경우 master에서 처리하도록 설정하는 방법을 설명한다.

서버 그룹 재설정

-- 기존 서버 목록 삭제
DELETE FROM mysql_servers;

-- master 추가
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.56.23', 3306);

-- slave 추가
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '192.168.56.24', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '192.168.56.25', 3306);

-- 변경사항 반영
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

사용자 기본 호스트 그룹 지정

-- hostgroup_id를 기본 1로 지정
UPDATE mysql_users SET default_hostgroup = 1;

-- 변경사항 반영
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Query Rule 지정

-- 기존 쿼리 룰 삭제
DELETE FROM mysql_query_rules;

-- SELECT로 시작하는 쿼리는 hostgroup=2(slave)로 라우팅
INSERT INTO mysql_query_rules (rule_id, active, apply, match_digest, destination_hostgroup) VALUES (1, 1, 1, '^SELECT', 2);

-- 변경사항 반영
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

테스트

• master, slave 모두 mysql.general_log 테이블을 통해 쿼리 실행 이력을 확인할 수 있도록 설정
  • 참고 : https://sg-choi.tistory.com/389#쿼리-실행-이력-확인
• ProxySQL 클라이언트로 접속 후 INSERT, SELECT 쿼리 실행

  mysql -uubuntu -p123456 -h 192.168.56.22 -P6033;
  

• mysql-master 이력 확인 (INSERT 쿼리 존재)

  mysql> SELECT * FROM mysql.general_log WHERE argument LIKE 'select%' or argument LIKE 'insert%' ORDER BY event_time DESC LIMIT 2;
  +----------------------------+-----------------------------------+-----------+-----------+--------------+----------------------------------------+
  | event_time                 | user_host                         | thread_id | server_id | command_type | argument                               |
  +----------------------------+-----------------------------------+-----------+-----------+--------------+----------------------------------------+
  | 2021-11-16 06:11:00.760550 | ubuntu[ubuntu] @  [192.168.56.22] |        37 |         1 | Query        | insert into User values ('11', 'jane') |
  | 2021-11-16 06:10:56.100960 | ubuntu[ubuntu] @  [192.168.56.22] |        37 |         1 | Query        | insert into User values ('10', 'john') |
  +----------------------------+-----------------------------------+-----------+-----------+--------------+----------------------------------------+
  

• mysql-slave-01 이력 확인 (SELECT 쿼리만 존재)

  mysql> SELECT * FROM mysql.general_log WHERE argument LIKE 'select%' or argument LIKE 'insert%' ORDER BY event_time DESC LIMIT 2;
  +----------------------------+-----------------------------------+-----------+-----------+--------------+--------------------+
  | event_time                 | user_host                         | thread_id | server_id | command_type | argument           |
  +----------------------------+-----------------------------------+-----------+-----------+--------------+--------------------+
  | 2021-11-16 06:11:13.778676 | ubuntu[ubuntu] @  [192.168.56.22] |        67 |         2 | Query        | select * from User |
  | 2021-11-16 06:10:04.831489 | ubuntu[ubuntu] @  [192.168.56.22] |        67 |         2 | Query        | select * from User |
  +----------------------------+-----------------------------------+-----------+-----------+--------------+--------------------+
  

• mysql-slave-02 이력 확인 (SELECT 쿼리만 존재)

  mysql> SELECT * FROM mysql.general_log WHERE argument LIKE 'select%' or argument LIKE 'insert%' ORDER BY event_time DESC LIMIT 2;
  +----------------------------+-----------------------------------+-----------+-----------+--------------+--------------------+
  | event_time                 | user_host                         | thread_id | server_id | command_type | argument           |
  +----------------------------+-----------------------------------+-----------+-----------+--------------+--------------------+
  | 2021-11-16 06:11:08.095697 | ubuntu[ubuntu] @  [192.168.56.22] |        40 |         3 | Query        | select * from User |
  | 2021-11-16 06:09:20.268808 | ubuntu[ubuntu] @  [192.168.56.22] |        40 |         3 | Query        | select * from User |
  +----------------------------+-----------------------------------+-----------+-----------+--------------+--------------------+
  

참고

• https://hakurei.tistory.com/306
• https://proxysql.com/documentation/installing-proxysql/
• https://proxysql.com/documentation/getting-started/
• https://teamsmiley.github.io/2020/11/09/proxysql/