proxysql的安装和使用(docker)

待我称王封你为后i 2022-03-18 10:22 1022阅读 0赞

**环境准备：**

具备docker+docker-compose环境的服务器4台

192.168.100.231

192.168.100.232

192.168.100.241

192.168.100.242

**实现目标:  **

(1)192.168.100.241 和 192.168.100.242上分别安装mysql-master和myql-slave，并且mysql-master负责写，myql-slave负责读，实现读写分离

(2) 192.168.100.231 和 192.168.100.232各安装一个proxysql(使用proxysql-cluster组成集群)

**参考链接：**

**    [ubuntu安装docker和docker-compose][ubuntu_docker_docker-compose]**

[https://github.com/bergerx/docker-mysql-replication][https_github.com_bergerx_docker-mysql-replication]

[https://github.com/sysown/proxysql/wiki][https_github.com_sysown_proxysql_wiki]

[https://hub.docker.com/r/severalnines/proxysql/][https_hub.docker.com_r_severalnines_proxysql]

**一、安装mysql-replication实现主从同步**

(1) 在 192.168.100.241上安装mysql-master, docker-compose.yml如下

version: '2.1'
    services:
      master:
        image: bergerx/mysql-replication:5.6
        network_mode: "host"
        restart: always
        environment:
          MYSQL_ALLOW_EMPTY_PASSWORD: "1"
          MYSQL_DATABASE: "test_db"
        volumes:
          - "/etc/timezone:/etc/timezone:ro"
          - "/etc/localtime:/etc/localtime:ro"
        command: ["--character-set-server=utf8mb4", "--collation-server=utf8mb4_unicode_ci"]

使用docker-compose up -d启动 （对docker-compose不熟悉的，这边稍微提示一下，docker-compose命令需要在docker-compose.yml同级目录下执行,因为它默认使用当前目录下的docker-compose.yml）

(2) 在 192.168.100.242上安装mysql-slave, docker-compose.yml如下

version: '2'
    services:
      slave:
        image: bergerx/mysql-replication:5.6
        restart: always
        environment:
          MYSQL_ALLOW_EMPTY_PASSWORD: "1"
          MASTER_HOST: "192.168.100.241"
          MASTER_PORT: 3306
        volumes:
          - "/etc/timezone:/etc/timezone:ro"
          - "/etc/localtime:/etc/localtime:ro"
        network_mode: host
        command: ["--character-set-server=utf8mb4", "--collation-server=utf8mb4_unicode_ci"]

使用docker-compose up -d启动

（3）验证主从同步效果

从上面的配置我们可以看到，master默认创建了test\_db数据库,这时只需要检查slave上是否也有test\_db数据库即可,具体方式如下： 在192.168.100.242的docker-compose.yml同级目录下执行如下命令

$: docker-compose exec slave mysql -e "show databases"

此时如果test\_db存在,则证明mysql-replication安装成功

（4）创建用户供proxysql使用，这里主要创建两个用户monitor和msandbox，前者用于proxysql监控mysql，后者用于外部程序通过proxysql访问mysql,由于已经实现了主从同步,因此只在192.168.100.241上操作即可

$: docker-compose exec master mysql

进入mysql控制台，创建用户

mysql> insert into mysql.user(Host,User,Password) values("%","monitor",password("monitor"));

mysql> grant all privileges on \*.\* to monitor@"%" identified by "monitor";

mysql> flush privileges;

mysql> insert into mysql.user(Host,User,Password) values("%","msandbox",password("msandbox"));

mysql> grant all privileges on test\_db.\* to msandbox@"%" identified by 'msandbox';

mysql> flush privileges;

**二、安装proxysql**

在192.168.100.231和192.168.100.232上的操作是一样的

(1) 首先创建一个目录 /home/ubuntu/proxysql,然后创建docker-compose.yml

version: '2.1'
    services:
      proxysql:
        image: severalnines/proxysql
        restart: always
        network_mode: host
        environment:
          MONITOR_CONFIG_CHANGE: "true"
        volumes:
          - "/etc/timezone:/etc/timezone:ro"
          - "/etc/localtime:/etc/localtime:ro"
          - "./proxysql.cnf:/etc/proxysql.cnf:ro"

(2) 上面的步骤中挂载了本地的一个proxysql.cnf文件,这个主要用于自定义proxysql配置，如下

#file proxysql.cfg
    
    datadir="/var/lib/proxysql"
    
    admin_variables=
    {
            // cluster1:secret1pass主要用于cluster访问
            admin_credentials="admin:admin;cluster1:secret1pass"
            mysql_ifaces="0.0.0.0:6032"
            // 以下配置只有需要实现proxysql-cluster时才需要
            cluster_username="cluster1"
            cluster_password="secret1pass"
            cluster_check_interval_ms=200
            cluster_check_status_frequency=100
            cluster_mysql_query_rules_save_to_disk=true
            cluster_mysql_servers_save_to_disk=true
            cluster_mysql_users_save_to_disk=true
            cluster_proxysql_servers_save_to_disk=true
            cluster_mysql_query_rules_diffs_before_sync=3
            cluster_mysql_servers_diffs_before_sync=3
            cluster_mysql_users_diffs_before_sync=3
            cluster_proxysql_servers_diffs_before_sync=3
    }
    
    // 若要实现proxysql-cluster,则proxysql_servers必须在配置文件中进行配置
    proxysql_servers =
    (
        {
            hostname="192.168.100.231"
            port=6032
            comment="proxysql_231"
        },
        {
            hostname="192.168.100.232"
            port=6032
            comment="proxysql_232"
        }
    )
    
    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="monitor"
      monitor_password="monitor"
      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
    }
    
    
    # defines all the MySQL servers
    mysql_servers =
    (
      { address="192.168.100.241" , port=3306 , hostgroup=1 },
      { address="192.168.100.242" , port=3306 , hostgroup=2 }
    )
    
    
    # defines all the MySQL users
    mysql_users:
    (
      {
        username = "msandbox" 
        password = "msandbox"
        default_hostgroup = 1
        active = 1 
      }
    )
    
    
    #defines MySQL Query Rules
    mysql_query_rules:
    (
     {
       rule_id=100
       active=1
       schemaname=test_db
       username=msandbox
       match_digest="(?i)^SELECT.*$"
       destination_hostgroup=2
       apply=1
     },
     {
       rule_id=200
       active=1
       schemaname=test_db
       username=msandbox
       match_digest="(?i)^(?!SELECT).*$"
       destination_hostgroup=1
       apply=1
     }
    )
    
    scheduler=
    (
    #  {
    #    id=1
    #    active=0
    #    interval_ms=10000
    #    filename="/var/lib/proxysql/proxysql_galera_checker.sh"
    #    arg1="0"
    #    arg2="0"
    #    arg3="0"
    #    arg4="1"
    #    arg5="/var/lib/proxysql/proxysql_galera_checker.log"
    #  }
    )
    
    
    mysql_replication_hostgroups=
    (
    #        {
    #                writer_hostgroup=30
    #                reader_hostgroup=40
    #                comment="test repl 1"
    #       },
    #       {
    #                writer_hostgroup=50
    #                reader_hostgroup=60
    #                comment="test repl 2"
    #        }
    )

以上配置文件内容比较多,其实默认情况下proxysql有一个默认的proxysql.cnf，即使我们不挂载自己的proxysql.cnf，proxysql也可以正常启动，像 mysql\_variables、mysql\_users、mysql\_servers、mysql\_query\_rules 是可以动态配置的，除了少数的配置必须在启动时配置,比如proxysql\_servers这些关于proxysql-cluster的配置,必须在初次启动时配置好,不支持动态修改。

(3)启动proxysql,在 /home/ubuntu/proxysql目录下执行如下命令即可

$: docker-compose up -d

**三、动态配置proxysql**

在192.168.100.231或192.168.100.232 的/home/ubuntu/proxysql目录下执行命令

$: docker-compose exec proxysql mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

命令执行后界面如下

![20190220171703862.png][] (1) 添加mysql后端实例

Admin> INSERT INTO mysql\_servers(hostgroup\_id,hostname,port) VALUES (1,'192.168.100.241',3306);  
Admin> INSERT INTO mysql\_servers(hostgroup\_id,hostname,port) VALUES (2,'192.168.100.242',3306);

\-- 让配置在运行环境中生效  
Admin> LOAD MYSQL SERVERS TO RUNTIME;

\-- 让配置持久化到硬盘  
Admin> SAVE  MYSQL SERVERS TO DISK;

（2）添加监控用户（mysql中创建的monitor用户）

Admin> UPDATE global\_variables SET variable\_value='monitor' WHERE variable\_name='mysql-monitor\_username';  
Admin> UPDATE global\_variables SET variable\_value='monitor' WHERE variable\_name='mysql-monitor\_password';

\-- 让配置在运行环境中生效  
Admin> LOAD MYSQL VARIABLES TO RUNTIME;  
\-- 让配置持久化到硬盘  
Admin> SAVE MYSQL VARIABLES TO DISK;

-- 检查与后端服务的连接情况

Admin> select \* from mysql\_server\_ping\_log  order by time\_start\_us desc limit 5\\G

若出现下面的信息,则表示与mysql连接正常

![image2019-2-19_17-39-19.png_version_1_modificationDate_1550569161000_api_v2][]

（3）添加mysql用户(mysql中创建的msandbox用户）

Admin> INSERT INTO mysql\_users(username,password,default\_hostgroup) VALUES ('msandbox','msandbox',1);  
\-- 使配置在运行环境中生效  
Admin> LOAD MYSQL USERS TO RUNTIME;  
\-- 将配置持久化到硬盘   
Admin> SAVE MYSQL USERS TO DISK;

(4)添加路由规则实现读写分离

\-- hostgroup为2的负责读,根据步骤(1)的配置,即是让192.168.100.242执行读操作

Admin> INSERT INTO mysql\_query\_rules (active,schemaname,username,match\_digest,destination\_hostgroup,apply) VALUES (1,'test\_db','msandbox','(?i)^SELECT',2,1);

\--  hostgroup为1的负责写，根据步骤(1)的配置,即是让192.168.100.241执行写操作  
Admin> INSERT INTO mysql\_query\_rules (active,schemaname,username,match\_digest,destination\_hostgroup,apply) VALUES (1,'test\_db','msandbox','(?i)^(?!select).\*$',1,1);

\-- 使配置在运行环境中生效  
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;  
\-- 将配置持久化到硬盘   
Admin> SAVE MYSQL QUERY RULES TO DISK;

有几个需要注意的地方：

a、 LOAD XX XX XX TO RUNTIME  表示让修改的配置在运行环境中生效

b、SAVE XX XX XX TO DISK 表示将配置持久化到硬盘,必须proxysql重启后丢失数据

c、本文已实现了proxysql-cluster，因此只需要在其中一个proxysql实例中进行动态配置即可让另一个proxysql实例的配置同步修改，触发cluster进行配置同步的命令是 LOAD XX XX XX TO RUNTIME

# **四、 使用proxysql** #

(1)使用mysql-client访问proxysql后端的mysql

$: mysql -umsandbox -pmsandbox -h192.168.100.231 -P6033

由于实现了proxysql-cluster，也可以访问192.168.100.232

$: mysql -umsandbox -pmsandbox -h192.168.100.232 -P6033

(2) 在spring-boot中访问proxysql

spring:  
    datasource:  
        driver-class-name: com.mysql.jdbc.Driver  
        url: "jdbc:mysql://192.168.100.231:6033/test\_db?useUnicode=true&characterEncoding=utf8"  
        username: msandbox  
        password: msandbox

[ubuntu_docker_docker-compose]: https://blog.csdn.net/song_java/article/details/88061162
[https_github.com_bergerx_docker-mysql-replication]: https://github.com/bergerx/docker-mysql-replication
[https_github.com_sysown_proxysql_wiki]: https://github.com/sysown/proxysql/wiki
[https_hub.docker.com_r_severalnines_proxysql]: https://hub.docker.com/r/severalnines/proxysql/
[20190220171703862.png]: /images/20220318/ef43d50f331d4f7eab0159dd0f27d3a7.png
[image2019-2-19_17-39-19.png_version_1_modificationDate_1550569161000_api_v2]: http://192.168.100.212:8090/download/attachments/21790721/image2019-2-19_17-39-19.png?version=1&modificationDate=1550569161000&api=v2