proxysql 설치

패키지 관리자를 통한 설치

## ProxySQL 설치  
$ sudo dnf -y install epel-release
$ sudo dnf -y install proxysql
$ proxysql --version
ProxySQL version 2.4.8, codename Truls

## mysql 클라이언트 설치(Workbench를 사용해도됨)
$ sudo dnf -y install mysql

## ProxySQL 시작
$ sudo systemctl start proxysql
$ sudo systemctl enable proxysql
$ sudo systemctl status proxysql

proxysql 설치 후 시작

계정 생성

모니터링용 계정 생성

• 모니터링용 계정은 REPLICATION CLIENT 권한이 필요합니다.
• primary-db에서 생성합니다.

primary-db

mysql> CREATE USER 'proxysql_monitor'@'%' IDENTIFIED WITH 'mysql_native_password' BY '2345';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT REPLICATION CLIENT ON *.* TO 'proxysql_monitor'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW GRANTS FOR 'proxysql_monitor'@'%';
+-----------------------------------------------------------+
| Grants for proxysql_monitor@%                             |
+-----------------------------------------------------------+
| GRANT REPLICATION CLIENT ON *.* TO `proxysql_monitor`@`%` |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

ProxySQL이 MySQL 서버의 상태를 모니터링할 수 있도록 하기 위해 MySQL 서버에 이 계정을 생성하고 적절한 권한을 부여해야 합니다.

proxysql

$ mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '
-> 우선 mysql로 proxysql에 접속합니다.
-> ProxySQL을 운영하기 위해 필요한 데이터베이스 확인

ProxySQL에서 생성한 모니터링용 계정에 대한 정보를 갱신

## 정보 업데이트
ProxySQLAdmin> UPDATE global_variables SET variable_value = 'proxysql_monitor' WHERE variable_name = '
mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> UPDATE global_variables SET variable_value = '2345' WHERE variable_name = 'mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

## 업데이트 내용 확인
ProxySQLAdmin> select * from global_variables WHERE variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+------------------+
| variable_name          | variable_value   |
+------------------------+------------------+
| mysql-monitor_password | 2345             |
| mysql-monitor_username | proxysql_monitor |
+------------------------+------------------+
2 rows in set (0.00 sec)

## 변경 사항 저장
ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 154 rows affected (0.01 sec)

• ProxySQL에서 MySQL서버를 모니터링 할 수 있는 계정을 설정한다.
• 이 설정은 ProxySQL이 MySQL로 접속할 수 있는 계정이어야 한다.
• 사용자 환경에 따라 username과 passwrd를 변경하여 사용한다.

DB 계정 정보 등록

ProxySQL에서 DB 계정 정보 설정의 필요성

• 쿼리 라우팅
  • ProxySQL은 데이터베이스 클러스터에 대한 쿼리를 중계하는 역할을 합니다. 이 때, 쿼리의 종류에 따라 적절한 MySQL 서버로 라우팅해야 합니다. 예를 들어, 읽기 쿼리는 읽기 전용 서버로, 쓰기 쿼리는 쓰기 전용 서버로 라우팅합니다.
  • 이를 위해 ProxySQL은 애플리케이션이 사용하는 데이터베이스 계정 정보와 권한을 알고 있어야 합니다. 이 정보는 mysql_users 테이블에 설정됩니다.
• 접속 관리
  • ProxySQL은 여러 MySQL 서버에 대한 클러스터를 관리할 수 있습니다. 각 서버의 접근 권한과 사용자 정보를 알고 있어야 중계와 쿼리 라우팅을 적절히 처리할 수 있습니다.
  • 이 정보를 mysql_users 테이블에 저장하여, ProxySQL이 MySQL 서버에 접속할 때 사용합니다.
• 보안 및 인증
  ProxySQL이 MySQL 서버에 연결할 때 필요한 인증 정보를 관리합니다. 이는 ProxySQL이 올바른 사용자 계정으로 인증을 받고, 적절한 권한을 부여받아 서버에 접근할 수 있도록 보장합니다.

##  DB 계정 정보 등록
mysql> CREATE USER testapi@'%' IDENTIFIED WITH 'mysql_native_password' BY '3456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO testapi@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW GRANTS FOR 'testapi'@'%';
+-------------------------------------------------------------------+
| Grants for testapi@%                                              |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testapi`@`%`                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO `testapi`@`%` |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

ProxySQL을 사용하여 중계하여 MySQL을 사용한다면 ProxySQL에도 접속하려는 DB계정 정보가 입력되어야 합니다.

ProxySQLAdmin> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('testapi', '3456', 1);
Query OK, 1 row affected (0.01 sec)

ProxySQLAdmin> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| testapi  | 3456     | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.00 sec)

ProxySQLAdmin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.11 sec)

ProxySQLAdmin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.02 sec)

사용할 MySQL DB계정에 대한 정보를 다음과 같이 ProxySQL에 등록합니다.

서버 정보 등록

## primary-db 정보 등록
ProxySQLAdmin> INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.110.110', 3306);
Query OK, 1 row affected (0.00 sec)

## readreplica-db 정보 등록
ProxySQLAdmin> INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '192.168.110.111', 3306);
Query OK, 1 row affected (0.01 sec)

ProxySQLAdmin> INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '192.168.110.112', 3306);
Query OK, 1 row affected (0.01 sec)

## 서버 변경 사항 반영
ProxySQLAdmin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

ProxySQLAdmin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.05 sec)

• 서버 등록을 이와 같이 해도 실제 접속 처리하는 부분은 ProxySQL의 Runtime 서버 정보를 별도로 사용하기 때문에 1개의 hostgroup_id로 등록해도 되긴 합니다.
  • 하나의 호스트 그룹에 모든 서버를 등록하더라도, 쿼리 라우팅 규칙을 통해 서버 그룹을 분리하고, 읽기와 쓰기 작업을 올바르게 분산할 수 있습니다.
  • 라우팅 규칙만 잘 설정하면, 호스트 그룹을 분산하지 않고도 쿼리를 적절히 처리할 수 있습니다.
  • 호스트 그룹은 서버의 역할을 구분하거나 물리적으로 서버를 구분하는 데 사용됩니다.
  • 쿼리 라우팅 규칙은 쿼리의 종류에 따라 서버를 선택하고 처리하는 역할을 합니다.

## hostgroup_id를 기본 1로 지정
ProxySQLAdmin> UPDATE mysql_users SET default_hostgroup = 1;
Query OK, 1 row affected (0.00 sec)

## 변경사항 반영
ProxySQLAdmin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

ProxySQLAdmin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.04 sec)

## 변경 사항 반영 확인
ProxySQLAdmin> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| testapi  | 3456     | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.00 sec)

hostgroup_id = 1을 사용자 기본 호스트 그룹을 지정합니다.

ProxySQLAdmin> select from mysql_servers;
ProxySQLAdmin> select from runtime_mysql_servers;

Query Rule 설정

• ProxySQL에서는 정규표현식으로 Query Rule을 설정할 수 있습니다.
• 정규표현식에 맞는(매칭된) 쿼리는 지정된 호스트그룹으로 전달됩니다.
• Rule이 지정되지 않은 쿼리는 사용자별 default hostgroup 으로 전달됩니다.
  • 설정에 따라 다를 수 있으나 보통의 default hostgroup은 Writer 호스트그룹인 1로(또는 첫번째 0) 지정하여 사용합니다.
  • 쿼리 룰에 지정되지 않은 다른 쿼리 종류인 DML이나 DDL 등과 같은 쿼리는 1번 호스트그룹(default hostgroup )으로 전달됩니다

## SELECT로 시작하는 쿼리는 hostgroup=2(readreplica)로 라우팅
ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id, active, apply, match_digest, destination_hostgroup) VALUES (1, 1, 1, '^SELECT', 2);
Query OK, 1 row affected (0.00 sec)

## 변경사항 반영 확인
ProxySQLAdmin> 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   | ^SELECT      | NULL          | 0
 | CASELESS     | NULL    | NULL            | 2                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     |
  | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
1 row in set (0.00 sec)

## 변경사항 반영
ProxySQLAdmin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

ProxySQLAdmin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.03 sec)

• 쿼리 타입에 따라 각 그룹으로 분기할 룰을 지정합니다.
  • SELECT쿼리는 hostgroup_id = 2의 서버로 호출하고, 나머지 쿼리는 hostgroup_id = 1 서버로 호출합니다.
  • 이때 동일 호스트 그룹에 2대 이상의 서버가 있으면 ProxySQL에서 라운드로빈으로 로드밸런싱을 합니다.

조회 쿼리 실행

6033 포트는 클라이언트(애플리케이션) 접속을 위한 ProxySQL의 애플리케이션 포트 번호이며, 기본설정은 6033 포트입니다.

SELECT

위에서 test 데이터베이스을 생성하지 않아서 DML 테스트가 불가능
-> test 테이블 생성 후 권한을 가지고 있는 사용자를 조회(testapi 사용자)

mysql -utestapi -p3456 -h 127.0.0.1 -P6033 -e "SELECT @@hostname" test
-> test 데이터베이스에 대해서 SELECT는 readreplica-db만 가능 -> 라운드 로빈은 삼중화후 테스트

트랜잭션 활성화 및 DML 수행(SELECT 제외)

mysql -utestapi -p3456 -h 127.0.0.1 -P6033 -e "start transaction; SELECT @@hostname;" test

• 트랜잭션을 시작한 서버는 START TRANSACTION 명령어가 실행된 서버입니다.
• 트랜잭션 관련 쿼리는 트랜잭션을 시작한 서버에서만 처리됩니다.
• 트랜잭션 내에서 SELECT @@hostname을 실행하여 결과를 확인함으로써, 트랜잭션을 시작한 서버가 PRIMARY 서버인지 확인할 수 있습니다.

testapi는 create권한은 없기에 mysql에 직접접속해 tb_test 테이블 생성

INSERT

• mysql -utestapi -p3456 -h 127.0.0.1 -P6033 -e "INSERT INTO test.tb_test (col1, col2) SELECT @@hostname, @@read_only;" test
  • test 데이터베이스의 tb_test 테이블에 데이터를 삽입합니다.
  • MySQL 시스템 변수 @@hostname (현재 MySQL 서버의 호스트 이름)과 @@read_only (서버의 읽기 전용 상태)를 선택합니다.
  • 이 명령어를 실행하면, 현재 MySQL 서버의 호스트 이름과 읽기 전용 상태가 tb_test 테이블의 col1과 col2 열에 삽입됩니다.
• mysql -utestapi -p3456 -h 127.0.0.1 -P6033 -e "select * from tb_test;" test
  • 테이블의 모든 열과 모든 행을 선택하여 조회합니다.
  • 이 명령어를 실행하면, tb_test 테이블에 있는 모든 데이터가 출력됩니다. 데이터는 INSERT 명령어로 추가된 데이터를 포함하고 있습니다.

primary - readreplica 적용도 확인

proxysql에서 insert한 값이 primary에 적용

primary에 적용된 값이 readreplica에 적용

결론

ProxySQL의 쿼리 라우팅 설정을 통해 SELECT 쿼리는 읽기 전용 서버(Replica)에서 실행되고, INSERT, UPDATE, DELETE와 같은 쓰기 작업은 주 서버(Primary)에서 실행되도록 설정할 수 있습니다!!