ProxySQL 테스트

ProxySQL 적용하기 우선 Mac 에서 Docker 설치 후 진행하였습니다.

docker --version

proxysql docker image 를 pull 받습니다.

docker pull proxysql/proxysql

ProxySQL 컨테이너를 실행합니다.

docker run -d --name proxysql -p 6032:6032 -p 6033:6033 proxysql/proxysql

컨테이너 내부에 접근합니다.

docker exec -it proxysql /bin/bash

ProxySQL 관리 CLI로 접속합니다. (기본 비밀번호는 admin 입니다.)

mysql -u admin -p -h 127.0.0.1 -P6032

접속 후, 필요한 설정을 진행합니다.

백엔드 MySQL 설정

create database myDb;
use myDb;
create table member
(
    id   bigint auto_increment primary key,
    name varchar(30)
) comment = '회원';

select *
from member;

select *
from mysql.user;
create user 'monitor'@'%' identified by 'monitor';
flush privileges;
show grants for 'monitor'@'%';

create user 'appuser'@'%' identified by 'appuser';
grant select, insert, update, delete on *.* to 'appuser'@'%';
flush privileges;
show grants for 'appuser'@'%';

ProxySQL 설정 단계

1. 백엔드 MySQL 서버 추가

1. MySQL RDS 또는 MySQL 서버를 ProxySQL에 백엔드로 추가합니다:

insert into mysql_servers(hostgroup_id, hostname, port) values (1, 'pado.c7sa6wwmcopt.ap-northeast-2.rds.amazonaws.com', 3306);

• hostgroup_id: 백엔드 서버 그룹을 식별하는 ID. (예: 1은 기본 읽기/쓰기 그룹)

• hostname: MySQL 서버의 주소 (AWS RDS 엔드포인트 사용).

• port: MySQL 서버 포트(기본: 3306).

2. 설정 적용:

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

3. 연결 확인

SELECT hostgroup_id, hostname, port, status FROM runtime_mysql_servers;

ONLINE 이면 연결 되어있는것

2. 사용자 계정 추가

• ProxySQL을 통해 클라이언트가 MySQL 서버에 연결할 수 있도록 사용자 계정을 추가합니다:

insert into mysql_users(username, password, default_hostgroup) values ('appuser', 'appuser', 1);

• username: 클라이언트가 사용할 사용자 이름.

• password: 클라이언트가 사용할 비밀번호.

• default_hostgroup: 사용자가 기본적으로 연결할 hostgroup ID.

• 설정 적용:

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

3. 상태 확인

현재 ProxySQL에 설정된 서버와 사용자 정보를 확인합니다.

• mysql_servers 테이블 확인

SELECT * FROM mysql_servers;

• mysql_users 테이블 확인

SELECT * FROM mysql_users;

4. 클라이언트 연결 테스트

• 클라이언트에서 ProxySQL의 클라이언트 포트(6033)로 연결합니다:

mysql -u your_username -p -h <EC2-Public-IP> -P 6033

• ProxySQL을 통해 MySQL RDS 또는 백엔드 MySQL 서버와 통신이 이루어지는지 확인합니다.

5. 핸드쉐이크 과정에서 실패한 경우

• proxysql 에서 실행한 쿼리 로그를 확인합니다.

SELECT * FROM stats.stats_mysql_query_digest ORDER BY last_seen DESC LIMIT 10;

• 핸드쉐이크 과정에서 mysql 5버전과 8버전의 차이로 인해 발생하는 변수들에 대해 mysql_query_rules 에 변환 규칙을 반영해줍니다.

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, apply) VALUES (1, 1, '@@query_cache_size', 'NULL', 0);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, apply) VALUES (2, 1, '@@query_cache_type', 'NULL', 0);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, apply) VALUES (3, 1, '@@tx_isolation', '@@transaction_isolation', 0);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
select * from mysql_query_rules;

5.1.27 버전에서는 show collations 때문에 Caused by: java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long 발생했는데, 아래 룰 추가하면 해결됨.

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, apply) VALUES(4,1,'^SHOW COLLATION$','SELECT "utf8mb4_0900_ai_ci" AS Collation, "utf8mb4" AS Charset, 255 AS Id, "YES" AS IsDefault, "YES" AS Compiled, 1 AS SortLen',0);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
select * from mysql_query_rules;

가장 최저 버전까지 확인한 것은 5.1.10 버전이었음

6. 테스트

dependencies {
//    implementation 'mysql:mysql-connector-java:5.1.49'
//    implementation 'mysql:mysql-connector-java:5.1.47' // 성공 : proxysql 연결해서
//    implementation 'mysql:mysql-connector-java:5.1.37' // 성공 : query_rules 추가
//    implementation 'mysql:mysql-connector-java:5.1.27' // 성공 : show collation rule 추가
//    implementation 'mysql:mysql-connector-java:5.1.17' // 성공
    implementation 'mysql:mysql-connector-java:5.1.10' // 성공 : 최저버전

    testImplementation platform('org.junit:junit-bom:5.10.0')
    testImplementation 'org.junit.jupiter:junit-jupiter'
}

아래 jdbc url 파라미터로 proxysql 없이 5.1.17 버전에서 연결이 되었었는데 그 상위 버전 5.1.27 에서는 또 연결이 안되었음

"?useSSL=false" +
"&useUnicode=true" +
"&characterEncoding=UTF-8" +
"&autoReconnect=true" +
"&useOldAliasMetadataBehavior=true" +
"&serverTimezone=Asia/Seoul";