proxysql 学习一 proxysql docker 运行试用

proxysql 是一个比较强大的mysql proxy 服务，支持动态mysql 实例调整，查询重写，查询cache，监控，数据镜像，读写分离
以及ha，最近已经发布了2.0 ，很值得试用下

环境准备

  • docker-compose 文件
version: "3"
services:
  mysql:
    image: mysql:5.7.16
    ports:
      - 3306:3306
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    environment:
      MYSQL_ROOT_PASSWORD: dalongrong
      MYSQL_DATABASE: test
      MYSQL_USER: test
      MYSQL_PASSWORD: test
      TZ: Asia/Shanghai
  proxysql:
    image: proxysql/proxysql:2.0.5
    volumes: 
    - "./proxysql.cnf:/etc/proxysql.cnf"
    ports:
    - "6033:6033"
    - "6032:6032"
  • proxysql.cnf 文件

    主要是关于proxysql 的一些运行是配置，同时我们也可以配置mysql server 实例,以下为了简单，直接使用配置文件的方式

datadir="/var/lib/proxysql"
​
admin_variables=
{
    admin_credentials="admin:admin;radmin:radmin"
    mysql_ifaces="0.0.0.0:6032"
}
​
mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.5.30"
    connect_timeout_server=3000
    monitor_username="root"
    monitor_password="dalongrong"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}
​
 mysql_servers =
 (
  {
    address="mysql"
    port=3306
    hostgroup=0
    max_connections=200
  }
 )
​
mysql_users:
 (
  {
    username = "root"
    password = "dalongrong"
    default_hostgroup = 0
    max_connections=1000
    default_schema="information_schema"
    active = 1
  }
 )

启动&&简单操作

  • 启动
docker-compose up -d
  • 管理连接接口
mysql -h127.0.0.1 -P6032 -uradmin -pradmin --prompt "ProxySQL Admin>"
  • 简单操作
show databases;

效果

ProxySQL Admin>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.01 sec)

查询mysql server

select * from main.mysql_serversG;

效果

select * from main.mysql_serversG;
*************************** 1. row ***************************
       hostgroup_id: 0
           hostname: mysql
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 200
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
1 row in set (0.00 sec)
​
ERROR: 
No query specified
 

查询运行时mysql server

select * from runtime_mysql_serversG;
*************************** 1. row ***************************
       hostgroup_id: 0
           hostname: mysql
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 200
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
1 row in set (0.01 sec)
  • 应用连接
    应用连接的端口为6033
    连接方式：
mysql -h127.0.0.1 -P6033 -uroot -pdalongrong

简单查询

 select * from rongdemo.usersinfo;
+----------+--------+
| username | userid |
+----------+--------+
| dalong | 1 |
+----------+--------+
1 row in set (0.00 sec)
 

说明

proxysql 功能是比较强大的，性能也很不错，对于同时提供了管理以及连接地址，暴露为标准的mysql 协议，对于配置的生效
并不是添加mysql server 实例到main.mysql_servers 就可以了，需要到runtime ,同时还需要配置进行持久化，相关命令有

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

对于proxysql，社区也提供了好多方便的监控工具，比如exporter ，方便与promethues 集成
对应官方的dockerfile 文件

 
FROM debian:stretch
MAINTAINER Nikolaos Vyzas <nick@proxysql.com>
RUN apt-get update && apt-get install -y wget lsb-release gnupg apt-transport-https ca-certificates && wget -O - 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add - && echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list && apt-get update && apt-get install proxysql=2.0.5 && rm -rf /var/lib/apt/lists/*
ENTRYPOINT ["proxysql", "-f", "-D", "/var/lib/proxysql"]

参考资料

https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL
https://github.com/sysown/proxysql
https://github.com/rongfengliang/proxysql-docker-compose
https://hub.docker.com/r/proxysql/proxysql

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/rongfengliang/p/11268007.html