ProxySQL setup in MySQL

hansung.dev·2021년 3월 11일
mysqlproxysql
1

MySQL 구축 및 운영

목록 보기
6/8
post-thumbnail

ProxySQL은 MySQL을 위한 오픈 소스 고성능 고가용성 데이터베이스 프로토콜 인식 프록시입니다. ProxySQL를 Docker로 구축하고 ProxySQL 콘솔로 접근하여 간단한 정보를 조회해보도록 하겠습니다.

ProxySQL 주요 기능

  • Application Layer Proxy
  • Zero-downtime Changes
  • Database Firewall
  • Advanced Query Rules
  • Data Sharding & Transformation
  • Failover Detection

Features

  • Query Cache
  • Query Logging
  • Mirroring
  • Scheduler
  • ProxySQL Cluster
  • Enable HTTP Web UI Statistic Graphs
  • REST API
  • Multiplexing

자세한 정보는 공식문서 또는 github을 참고 부탁드립니다.

Getting Started

실습 환경은 아래와 같습니다.

Docker : 20.10.2, Docker Compose : 1.27.4, build 40524192
ProxySQL : 2.1.0
Orchestrator : 3.2.4
MySQL : 8.0.23

File Directory with Docker-Compose

Docker-Compose로 MySQL와 ProxySQL, Orchestrator를 아래와 같이 구성 합니다.

mysql-orchestrator-proxysql
├── docker-compose.yml
├── post_sql.sh
├── mysql-01
│   ├── conf
│   │   └── my.cnf
│   ├── data
│   └── log
├── mysql-02
│   ├── conf
│   │   └── my.cnf
│   ├── data
│   └── log
├── mysql-03
│   ├── conf
│   │   └── my.cnf
│   ├── data
│   └── log
├── orchestrator
│   └── conf
│       └── orchestrator.conf.json
├── proxysql
│   ├── conf
│   │   └── proxysql.cnf
│   └── data
├── sql
│   ├── test_insert.sh
└   └── test_select.sh

docker-compose.yml

version: "3.7"

services:
  mysql-01:
    image:  mysql:8.0.23
    container_name: mysql-01
    hostname: mysql-01
    ports:
      - "3306:3306"
    command: mysqld --default-authentication-plugin=mysql_native_password --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    environment:
      MYSQL_ROOT_PASSWORD: 'root'
    volumes:
      - "${PWD}/mysql-01/data:/var/lib/mysql"
      - "${PWD}/mysql-01/log:/var/log/mysql"
      - "${PWD}/mysql-01/conf/my.cnf:/etc/mysql/conf.d/my.cnf"
    networks:
      - mybridge

  mysql-02:
    image: mysql:8.0.23
    container_name: mysql-02
    hostname: mysql-02
    ports:
      - "13306:3306"
    command: mysqld --default-authentication-plugin=mysql_native_password --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    environment:
      MYSQL_ROOT_PASSWORD: 'root'
    volumes:
      - "${PWD}/mysql-02/data:/var/lib/mysql"
      - "${PWD}/mysql-02/log:/var/log/mysql"
      - "${PWD}/mysql-02/conf/my.cnf:/etc/mysql/conf.d/my.cnf"
    networks:
      - mybridge

  mysql-03:
    image: mysql:8.0.23
    container_name: mysql-03
    hostname: mysql-03
    ports:
      - "23306:3306"
    command: mysqld --default-authentication-plugin=mysql_native_password --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    environment:
      MYSQL_ROOT_PASSWORD: 'root'
    volumes:
      - "${PWD}/mysql-03/data:/var/lib/mysql"
      - "${PWD}/mysql-03/log:/var/log/mysql"
      - "${PWD}/mysql-03/conf/my.cnf:/etc/mysql/conf.d/my.cnf"
    networks:
      - mybridge

  orchestrator:
    image: openarkcode/orchestrator:latest
    container_name: orchestrator
    hostname: orchestrator
    ports:
      - "3000:3000"
    volumes:
      - "${PWD}/orchestrator/conf/orchestrator.conf.json:/etc/orchestrator.conf.json"
    networks:
      - mybridge

  proxysql:
    image: proxysql/proxysql:2.1.0
    container_name: proxysql
    hostname: proxysql
    ports:
      - "6032:6032"
      - "6033:6033"
    volumes:
      - "${PWD}/proxysql/data:/var/lib/proxysql"
      - "${PWD}/proxysql/conf/proxysql.cnf:/etc/proxysql.cnf"
    networks:
      - mybridge

networks:
  mybridge:
    external: true

post_sql.sh

## For ProxySQL
${EXEC_MASTER} "CREATE DATABASE testdb DEFAULT CHARACTER SET=utf8" 2>&1 | grep -v "Using a password"
${EXEC_MASTER} "CREATE USER appuser@'%' IDENTIFIED BY 'apppass'" 2>&1 | grep -v "Using a password"
${EXEC_MASTER} "GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO appuser@'%'" 2>&1 | grep -v "Using a password"
${EXEC_MASTER} "CREATE USER monitor@'%' IDENTIFIED BY 'monitor'" 2>&1 | grep -v "Using a password"
${EXEC_MASTER} "GRANT REPLICATION CLIENT ON *.* TO monitor@'%'" 2>&1 | grep -v "Using a password"

EXEC_PROXY="mysql -h127.0.0.1 -P6032 -uradmin -pradmin -e "

${EXEC_PROXY} "INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, 'mysql-01', 3306)" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, 'mysql-01', 3306)" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, 'mysql-02', 3306)" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, 'mysql-03', 3306)" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "INSERT INTO mysql_replication_hostgroups VALUES (10,20,'read_only','')" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "LOAD MYSQL SERVERS TO RUNTIME" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "SAVE MYSQL SERVERS TO DISK" 2>&1 | grep -v "Using a password"

${EXEC_PROXY} "INSERT INTO mysql_users(username,password,default_hostgroup,transaction_persistent)
               VALUES ('appuser','apppass',10,0)" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "LOAD MYSQL USERS TO RUNTIME" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "SAVE MYSQL USERS TO DISK" 2>&1 | grep -v "Using a password"

${EXEC_PROXY} "INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup)
               VALUES (1,1,'^SELECT.*FOR UPDATE$',10)" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup)
               VALUES (2,1,'^SELECT',20)" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "LOAD MYSQL QUERY RULES TO RUNTIME" 2>&1 | grep -v "Using a password"
${EXEC_PROXY} "SAVE MYSQL QUERY RULES TO DISK" 2>&1 | grep -v "Using a password"

Setting up and running

Docker-Compos Run, Stop

docker-compose를 실행하여 ProxySQL 및 MySQL, Orchestrator 서비스를 시작합니다.

#run
docker-compose up -d

#stop
# docker-compose stop

MySQL Replication 구성 및 Orchestrator 설정을 위한 아래 스크립트를 실행합니다.

sh post_sql.sh

Orchestrator setup

Orchestrator의 Topology는 아래와 같이 구성되었습니다.

Orchestrator setup은 "Orchestrator, MySQL High availability 구축하기" 를 참고 합니다.

Connecting ProxySQL through the admin interface

기본 자격 증명을 사용하여 관리 인터페이스에 로그인하려면 mysql 클라이언트를 사용하고 포트(6032)에서 로컬로 다음 관리 자격 증명을 사용하여 연결합니다.

mysql -h127.0.0.1 -P6032 -uradmin -pradmin --prompt "Admin>"

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

Admin>

ProxySQL 버전을 확인 합니다.

Admin>select version();
+---------------------+
| version()           |
+---------------------+
| 2.1.0-544-g17a4b4a7 |
+---------------------+
1 row in set (0.00 sec)

Admin>

ProxySQL에 등록된 서버 정보를 확인 합니다. hostgroup_id가 10은 쓰기와 읽기, 20은 읽기 전용으로 설정 합니다.

Admin>SELECT * FROM mysql_servers;
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | mysql-01 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-01 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-02 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-03 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Admin>
Admin>SELECT * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  |         |
+------------------+------------------+------------+---------+
1 row in set (0.01 sec)
Admin>select rule_id,active,match_pattern,destination_hostgroup from mysql_query_rules;
+---------+--------+----------------------+-----------------------+
| rule_id | active | match_pattern        | destination_hostgroup |
+---------+--------+----------------------+-----------------------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 10                    |
| 2       | 1      | ^SELECT              | 20                    |
+---------+--------+----------------------+-----------------------+
2 rows in set (0.00 sec)

등록된 서비스 계정은 appuser 입니다. 기본 hostgroup_id는 10으로 설정되어있습니다.

Admin>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 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| appuser  | apppass  | 1      | 0       | 10                | NULL           | 0             | 0                      | 0            | 1       | 1        | 10000           |            |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.00 sec)

코드는 Github에서 확인 가능 합니다.

profile
hansung.dev
Data Engineer
이전 포스트

MySQL Automated recovery with Orchestrator

다음 포스트

ProxySQL Features

0개의 댓글