搭建MySQL读写分离、高可用

发表于 更新于 分类于

前言

在做基础脚手架开发的过程中涉及到代码要不要支持读写分离，支持的话的需要一定的工作量，虽然代码不多，也比较容易实现，但毕竟需要维护。为了避免重新写轮子，先查询。还有真有不少解决方案，第一种就是基于应用的，自己在应用程序中编码实现；第二种就是基于中间件，有MySQL Proxy、MaxScale和HAProxy等，MySQL Proxy已经不维护了；HAProxy本身不支持读写分离，还需要搭配其他才能实现；MaxScale 文档比较全，并且是MariaDB 开发的，所以先用起来看看效果。

环境

  • Linux rocky 8
  • MySQL 5.7
  • MariaDB MaxScale 6.4
  • ProxySQL 2.4.8

环境规划

序号应用hostnameIP端口
1MySQL Master192.168.56.1003306
2MySQL Slave192.168.56.1013306
3MaxScale192.168.56.1024006/8989
4ProxySQL192.168.56.1036033

MySQL 主从搭建

Master 安装

  1. 下载安装包

    1
    2
    wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar
    tar -xvf mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar

  2. 检查是否之前是否有安装，有删除

    1
    2
    yum list installed | grep -i maria
    yum list installed | grep -i mysql

  3. 安装

    1
    yum install mysql-community-{server,client,common,libs}-* --exclude='*minimal*'

  4. 修改默认数据目录，

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    # 1. 创建目录
    mkdir /home/mysql_data
    # 2. 拷贝目录数据目录
    cp -a /var/lib/mysql /home/mysql_data/
    # 3. 修改MySQL配置文件
    vi /etc/my.cnf
    # 4. 需要修改的内容
    datadir=/home/mysql_data/mysql
    socket=/home/mysql_data/mysql/mysql.sock
    [client]
    socket=/home/mysql_data/mysql/mysql.sock

  5. 修改SELinux对MySQL默认数据目录的保护

    1
    2
    3
    4
    # 1. 打开/etc/selinux/targeted/contexts/files/file_contexts.local
    vi /etc/selinux/targeted/contexts/files/file_contexts.local
    # 2. 添加以下内容
    /home/mysql_data(/.*)?   system_u:object_r:mysqld_db_t:s0

  6. 启动MySQL

    1
    2
    3
    4
    # 启动
    systemctl start mysqld
    # 开启自启动
    systemctl enable mysqld

  7. 找到root的默认密码

    1
    grep 'A temporary password' /var/log/mysqld.log |tail -1

  8. 修改默认密码

    1
    2
    3
    4
    # 登陆到MySQL Server
    mysql -uroot -p
    # 设置密码
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
Slave 安装

同master

搭建配置

  1. 修改master的my.cnf配置文件

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    # server id 唯一
    server_id=112
    # GTID 模式
    gtid-mode = ON
    # 强制GTID一致性
    enforce-gtid-consistency = ON
    # 是否记录从服务器同步数据动作
    log-slave-updates=ON
    # 开启及设置二进制日志文件名称
    log_bin=mysql-bin
    # 设置binlog文件格式
    binlog_format=MIXED

    # 记录IO线程读取已经读取到的master binlog位置，用于slave宕机后IO线程根据文件中的POS点重新拉取binlog日志
    master-info-repository = TABLE
    # 记录SQL线程读取Master binlog的位置，用于Slave 宕机后根据文件中记录的pos点恢复Sql线程
    relay-log-info-repository = TABLE   
    # 启用确保无信息丢失；任何一个事务提交后, 将二进制日志的文件名及事件位置记录到文件中
    sync-master-info = 1
    # 设定从服务器的复制线程数；0表示关闭多线程复制功能
    slave-parallel-workers = 2
    # 设置binlog校验算法（循环冗余校验码）
    binlog-checksum = CRC32
    # 设置主服务器是否校验
    master-verify-checksum = 1
    # 设置从服务器是否校验
    slave-sql-verify-checksum = 1
    # 用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度
    binlog-rows-query-log_events = 1
    # 保证master crash safe，该参数必须设置为1
    sync_binlog = 1
    # 保证master crash safe，该参数必须设置为1
    innodb_flush_log_at_trx_commit = 1

  2. 重启MySQL Server

  3. 添加同步用户

    1
    2
    GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO sync@'192.168.56.%' IDENTIFIED BY 'password';
    FLUSH PRIVILEGES;

  4. 查看master 状态并记录下位置信息

    1
    2
    3
    4
    5
    6
    7
    mysql> show master status;
    +------------------+----------+--------------+-------------------------------------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
    +------------------+----------+--------------+-------------------------------------------------+-------------------+
    | mysql-bin.000008 |      154 |              | sys,mysql,information_schema,performance_schema |                   |
    +------------------+----------+--------------+-------------------------------------------------+-------------------+
    1 row in set (0.00 sec)

  5. 修改slave的my.cnf 配置文件

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    # server id 唯一
    server-id=113
    # GTID 模式
    gtid-mode = ON
    # 强制GTID一致性
    enforce-gtid-consistency = ON
    # 是否记录从服务器同步数据动作
    log-slave-updates=ON
    # 开启及设置二进制日志文件名称
    log_bin=mysql-bin
    # 设置binlog文件格式
    binlog_format=MIXED
    # 设置一般用户为只读模式
    read-only=1

    master-info-repository = TABLE
    relay-log-info-repository = TABLE
    sync-master-info = 1
    slave-parallel-workers = 4
    binlog-checksum = CRC32
    master-verify-checksum = 1
    slave-sql-verify-checksum = 1
    binlog-rows-query-log_events = 1
    # crash safe slave
    relay_log_recovery = 1

  6. 重启MySQL Server

  7. 打开master 防火墙的3306 端口

    1
    2
    firewall-cmd --zone=public --add-port=3306/tcp --permanent
    firewall-cmd --reload

  8. 配置从主库同步数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    # 1. 登陆MySQL slave Server
    mysql -uroot -p
    # 2. 配置
    # MASTER_LOG_FILE 和 MASTER_LOG_POS 的值就是从 主库 show master status 命令中获取的信息
    mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.115',
           MASTER_USER='sync',
           MASTER_PASSWORD='password',
           MASTER_AUTO_POSITION=1;
    # 3. 开启同步
    mysql> start slave;

  9. 查看同步情况

1
2
3
4
5
mysql> show slave status\G
# 检查以下参数都是 Yes 代表正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

  1. 测试，通过创建数据库、表，添加数据来看同步是否正常

MaxScale 搭建

Master 安装配置

  1. 通过官网下载安装包

    1
    wget https://dlm.mariadb.com/2717592/MaxScale/6.4.5/yum/centos/8/x86_64/maxscale-6.4.5-1.rhel.8.x86_64.rpm

  2. 安装

    1
    yum install maxscale-6.4.5-1.rhel.8.x86_64.rpm

  3. 在MySQL Master 上添加监控和路由用户

    1
    2
    3
    4
    5
    6
    7
    8
    # 1. 连接MySQL Server
    mysql -uroot -p
    # 2. 添加监控用户
    grant replication slave,replication client on *.* to 'maxmon'@'192.168.56.%' identified by 'password';
    # 3. 添加路由用户
    grant select on mysql.* to 'maxrou'@'192.168.56.%' identified by 'password';
    # 4. 刷新权限信息
    flush privileges;

  4. 读写分离配置，具体可阅读官方配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    # 1. 打开配置文件
    vim /etc/maxscale.cnf
    # 2. 配置
    # 基础配置
    [maxscale]
    threads=auto
    admin_host=0.0.0.0
    admin_secure_gui=false

    # MySQL Server 配置
    [server1]
    type=server
    address=192.168.56.112
    port=3306
    protocol=MySQLBackend

    [server2]
    type=server
    address=192.168.56.113
    port=3306
    protocol=MySQLBackend
    # 监控配置
    [MariaDB-Monitor]
    type=monitor
    module=mariadbmon
    servers=server1,server2
    user=maxmon
    password=password
    monitor_interval=2000
    # 过滤器配置
    # 非必须配置，是我使用的客户端工具默认会执行解释计划，但默认explain plan 会被路由到从库
    [Named-Server-Filter]
    type=filter
    module=namedserverfilter
    match01=^explain.*
    target01=server1

    # 读写分离服务配置
    [Read-Write-Service]
    type=service
    router=readwritesplit
    servers=server1,server2
    user=maxrou
    password=password
    filters=Named-Server-Filter

    # 读写分离监听配置
    [Read-Write-Listener]
    type=listener
    service=Read-Write-Service
    protocol=MySQLClient
    port=4006

  5. 启动MaxScale

    1
    2
    3
    4
    # 启动
    systemctl start maxscale
    # 加入开机自启动
    systemctl enable maxscale

  6. 测试

    用MySQL客户端或者命令连接上MaxScale，对数据库的表进行添加和查询数据，观察read/write值的变化

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    # 1. 通过命令监控 read/write 的数量变化
    maxctrl show services
    # 结果
    │ Router Diagnostics  │ {                                                           │
    │                     │     "avg_sescmd_history_length": 0,                         │
    │                     │     "max_sescmd_history_length": 32,                        │
    │                     │     "queries": 117,                                         │
    │                     │     "replayed_transactions": 0,                             │
    │                     │     "ro_transactions": 0,                                   │
    │                     │     "route_all": 33,                                        │
    │                     │     "route_master": 9,                                      │
    │                     │     "route_slave": 75,                                      │
    │                     │     "rw_transactions": 0,                                   │
    │                     │     "server_query_statistics": [                            │
    │                     │         {                                                   │
    │                     │             "avg_selects_per_session": 0,                   │
    │                     │             "avg_sess_duration": "0ns",                     │
    │                     │             "id": "server1",                                │
    │                     │             "read": 33,                                     │
    │                     │             "total": 42,                                    │
    │                     │             "write": 9                                      │
    │                     │         },                                                  │
    │                     │         {                                                   │
    │                     │             "avg_selects_per_session": 0,                   │
    │                     │             "avg_sess_duration": "0ns",                     │
    │                     │             "id": "server2",                                │
    │                     │             "read": 108,                                    │
    │                     │             "total": 108,                                   │
    │                     │             "write": 0                                      │
    │                     │         }                                                   │
    │                     │     ]                                                       │
    │                     │ } 

    # 2. 通过MaxGUI监控 http://127.0.0.1:8989

  7. 注意

    MaxScale 不支持MySQL的故障自动转移

ProxySQL 搭建

Node 1 搭建

  1. 通过github下载安装包

    1
    wget https://github.com/sysown/proxysql/releases/download/v2.4.8/proxysql-2.4.8-1-centos8.x86_64.rpm

  2. 安装

    1
    yum install proxysql-2.4.8-1-centos8.x86_64.rpm

  3. 在MySQL Master 上添加监控用户和测试用户

    1
    2
    3
    4
    5
    6
    7
    8
    # 1. 连接MySQL Server
    mysql -uroot -p
    # 2. 添加监控用户
    grant replication slave,replication client on *.* to 'maxmon'@'192.168.56.%' identified by 'password';
    # 3. 测试用户
    grant all on ivy_base.* to 'ivy_base'@'%' identified by 'password';
    # 3. 刷新权限信息
    flush privileges;

  4. 读写分离配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    # 1. 打开配置文件
    vi /etc/proxysql.cnf
    # 2. 配置
    # 数据目录和日志文件
    datadir="/var/lib/proxysql"
    errorlog="/var/lib/proxysql/proxysql.log"

    # 设置管理变量，包括监听的端口、管理账号等。
    admin_variables=
    {
    	admin_credentials="admin:admin"
    	mysql_ifaces="0.0.0.0:6032"
    	debug=true
        web_enabled=true
    }
    # 设置MySQL 数据库 变量，监听账号、密码等。
    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="maxmon"
    	monitor_password="1234@abCD"
    	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.56.115" , port=3306 , hostgroup=0 },
    	{ address="192.168.56.113" , port=3306 , hostgroup=1 },
    )

    # defines all the MySQL users
    mysql_users:
    (
       { username = "ivy_base" , password = "1234@abCD" , default_hostgroup = 0 , active = 1 }
    )

    #定义查询路由规则
    mysql_query_rules:
    (
    	{
    		rule_id=1
    		active=1
    		match_pattern="^SELECT .* FOR UPDATE$"
    		destination_hostgroup=0
    		apply=1
    	},
    	{
    		rule_id=2
    		active=1
    		match_pattern="^SELECT"
    		destination_hostgroup=1
    		apply=1
    	},
        {
            rule_id=3
            active=1
            match_pattern="^explain"
            destination_hostgroup=0
            apply=1
        }
    )
    #调度器是一个类似于cron的实现，具有毫秒的粒度。
    scheduler=
    (
    )
    # 定义 hostgroup 的主从关系
    mysql_replication_hostgroups=
    (
    )

  5. 启动proxysql

1
2
3
4
# 启动
systemctl start proxysql
# 加入开机自启动
systemctl enable proxysql

  1. 在ProxySQL 服务器上安装MySQL client

    同MySQL Master 搭建相同，只是不安装server端

    1
    yum install mysql-community-{client,common,libs}-* --exclude='*minimal*'

  2. 打开防火墙端口6033

  3. 测试

    用MySQL客户端或者命令连接上proxysql，对数据库的表进行添加和查询数据，观察read/write值的变化

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    # 1. 在主或从MySQL数据库服务器上连接proxysql
    mysql -h 192.168.56.112 -P 6033 -uivy_base -p
    # 2. 执行创建表、添加数据、查询数据等操作
    CREATE TABLE `test` (
      `id` int(11) NOT NULL,
      `name` varchar(200) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ;
    INSERT INTO test (id, NAME) VALUES ('2', '1');
    select * from test;
    # 3. 在proxysql观察情况
    mysql -uadmin -p -h 127.0.0.1 -P 6032 --prompt='admin>'
    #执行语句监控
    select hostgroup,username,digest_text,count_star from stats_mysql_query_digest;
    # 结果，通过缓存hostgroup和语句我们发现我们是配置成功的
    +-----------+-------------------------------------------------------------------------------------------------+------------+
    | hostgroup | digest_text                                                                                     | count_star |
    +-----------+-------------------------------------------------------------------------------------------------+------------+
    | 0         | INSERT INTO test (id,NAME) VALUES (?,?)                                                         | 1          |
    | 0         | CREATE TABLE `test` ( `id` int(?) NOT NULL, `name` varchar(?) DEFAULT NULL, PRIMARY KEY (`id`)) | 1          |
    | 1         | select * from test                                                                              | 3          |
    | 0         | CREATE TABLE `test` ( `id` int(?) NOT NULL, `name` varchar(?) DEFAULT NULL, PRIMARY KEY (`id`)) | 1          |
    | 1         | SELECT * FROM `douban_book` WHERE ?=?                                                           | 1          |
    | 0         | select @@version_comment limit ?                                                                | 1          |
    | 1         | SELECT DATABASE()                                                                               | 1          |
    | 0         | show databases                                                                                  | 1          |
    | 0         | show tables                                                                                     | 1          |
    +-----------+-------------------------------------------------------------------------------------------------+------------+


ProxySQL 配置说明

ProxySQL 配置有两种方式一种是通过/etc/proxysql.cnf配置文件,另一种是通过admin interface方式（即在proxysql本机使用mysql客户端连接管理端口），但第二种方式依赖于第一种方式，因为一些基础的配置必须通过配置文件来配置。

ProxySQL有配置文件/etc/proxysql.cnf和配置数据库文件/var/lib/proxysql/proxysql.db。

特别注意：

ProxySQL服务只有在第一次启动时才会去读取proxysql.cnf文件并解析；后面启动会就不会读取proxysql.cnf文件了！

如果想要让proxysql.cnf文件里的配置在重启proxysql服务后生效(即想要让proxysql重启时读取并解析proxysql.cnf配置文件)，则需要先删除/var/lib/proxysql/proxysql.db数据库文件，然后再重启proxysql服务。这样就相当于初始化启动proxysql服务了，会再次生产一个纯净的proxysql.db数据库文件(如果通过admin interface方式配置的数据，则就会被抹掉)。

官方推荐用admin interface方式！

  1. ProxySQL 库介绍

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    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 |
    +-----+---------------+-------------------------------------+

    **main:**配置数据库，表里存放后端DB实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。

    **disk:**是持久化到硬盘的配置，sqlite数据文件。默认位置为 $（DATADIR）/proxysql.db

    **stats:**proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等

    **monitor:**存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。

    **stats_history:**统计信息历史库

  2. main 库

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    admin> show tables from main;
    +----------------------------------------------------+
    | tables                                             |
    +----------------------------------------------------+
    | global_variables                                   |
    | mysql_aws_aurora_hostgroups                        |
    | mysql_collations                                   |
    | mysql_firewall_whitelist_rules                     |
    | mysql_firewall_whitelist_sqli_fingerprints         |
    | mysql_firewall_whitelist_users                     |
    | mysql_galera_hostgroups                            |
    | mysql_group_replication_hostgroups                 |
    | mysql_query_rules                                  |
    | mysql_query_rules_fast_routing                     |
    | mysql_replication_hostgroups                       |
    | mysql_servers                                      |
    | mysql_users                                        |
    | proxysql_servers                                   |
    | restapi_routes                                     |
    | runtime_checksums_values                           |
    | runtime_global_variables                           |
    | runtime_mysql_aws_aurora_hostgroups                |
    | runtime_mysql_firewall_whitelist_rules             |
    | runtime_mysql_firewall_whitelist_sqli_fingerprints |
    | runtime_mysql_firewall_whitelist_users             |
    | runtime_mysql_galera_hostgroups                    |
    | runtime_mysql_group_replication_hostgroups         |
    | runtime_mysql_query_rules                          |
    | runtime_mysql_query_rules_fast_routing             |
    | runtime_mysql_replication_hostgroups               |
    | runtime_mysql_servers                              |
    | runtime_mysql_users                                |
    | runtime_proxysql_servers                           |
    | runtime_restapi_routes                             |
    | runtime_scheduler                                  |
    | scheduler                                          |
    +----------------------------------------------------+

    常用表介绍：

    global_variables：用于定义全局变量。这是一个非常简单的表，本质上是一个key/value存储系统。
    mysql_collations：相关字符集和校验规则。
    mysql_query_rules：定义查询路由规则。
    mysql_servers：设置后端MySQL的表
    mysql_users：配置后端数据库的程序账号和监控账号。
    scheduler：调度器是一个类似于cron的实现，集成在ProxySQL中，具有毫秒的粒度。通过脚本检测来设置ProxySQL。

    mysql_replication_hostgroups：监视指定主机组中所有服务器的read_only值，并且根据read_only的值将服务器分配给写入器或读取器主机组。ProxySQL monitor模块会监控hostgroups后端所有servers 的read_only 变量，如果发现从库的read_only变为0、主库变为1，则认为角色互换了，自动改写mysql_servers表里面 hostgroup关系，达到自动 Failover 效果。

    *runtime_:**运行时的配置，这些是不能修改的

  3. disk 库

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    admin> show tables from disk;
    +--------------------------------------------+
    | tables                                     |
    +--------------------------------------------+
    | global_settings                            |
    | global_variables                           |
    | mysql_aws_aurora_hostgroups                |
    | mysql_collations                           |
    | mysql_firewall_whitelist_rules             |
    | mysql_firewall_whitelist_sqli_fingerprints |
    | mysql_firewall_whitelist_users             |
    | mysql_galera_hostgroups                    |
    | mysql_group_replication_hostgroups         |
    | mysql_query_rules                          |
    | mysql_query_rules_fast_routing             |
    | mysql_replication_hostgroups               |
    | mysql_servers                              |
    | mysql_users                                |
    | proxysql_servers                           |
    | restapi_routes                             |
    | scheduler                                  |
    +--------------------------------------------+

    用于将配置持久化到磁盘上。配置持久化后，下次重启ProxySQL时就会读取这些已被持久化的配置。这些表完全对应于内存数据库中的表。

  4. stats 库

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    admin> show tables from stats;
    +---------------------------------------+
    | tables                                |
    +---------------------------------------+
    | global_variables                      |
    | stats_memory_metrics                  |
    | stats_mysql_client_host_cache         |
    | stats_mysql_client_host_cache_reset   |
    | stats_mysql_commands_counters         |
    | stats_mysql_connection_pool           |
    | stats_mysql_connection_pool_reset     |
    | stats_mysql_errors                    |
    | stats_mysql_errors_reset              |
    | stats_mysql_free_connections          |
    | stats_mysql_global                    |
    | stats_mysql_gtid_executed             |
    | stats_mysql_prepared_statements_info  |
    | stats_mysql_processlist               |
    | stats_mysql_query_digest              |
    | stats_mysql_query_digest_reset        |
    | stats_mysql_query_rules               |
    | stats_mysql_users                     |
    | stats_proxysql_message_metrics        |
    | stats_proxysql_message_metrics_reset  |
    | stats_proxysql_servers_checksums      |
    | stats_proxysql_servers_clients_status |
    | stats_proxysql_servers_metrics        |
    | stats_proxysql_servers_status         |
    +---------------------------------------+

    常用表：

    stats_mysql_commands_counters：统计各种SQL类型的执行次数和时间，通过参数mysql-commands_stats控制开关，默认是ture。
    stats_mysql_connection_pool：连接后端MySQL的连接信息。
    stats_mysql_processlist：类似MySQL的show processlist的命令，查看各线程的状态。
    stats_mysql_query_digest：表示SQL的执行次数、时间消耗等。通过变量mysql-query_digests控制开关，默认是开。
    stats_mysql_query_rules：路由命中次数统计。

  5. monitor 库

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    admin> show tables from monitor;
    +--------------------------------------+
    | tables                               |
    +--------------------------------------+
    | mysql_server_aws_aurora_check_status |
    | mysql_server_aws_aurora_failovers    |
    | mysql_server_aws_aurora_log          |
    | mysql_server_connect_log             |
    | mysql_server_galera_log              |
    | mysql_server_group_replication_log   |
    | mysql_server_ping_log                |
    | mysql_server_read_only_log           |
    | mysql_server_replication_lag_log     |
    +--------------------------------------+

    常用表：

    mysql_server_connect_log：连接到所有MySQL服务器以检查它们是否可用，该表用来存放检测连接的日志。
    mysql_server_ping_log：使用mysql_ping API ping后端MySQL服务器，检查它们是否可用，该表用来存放ping的日志。
    mysql_server_replication_lag_log：后端MySQL服务主从延迟的检测。

  6. stats_history库

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    admin> show tables from stats_history;
    +---------------------------------------+
    | tables                                |
    +---------------------------------------+
    | history_mysql_query_digest            |
    | history_mysql_status_variables        |
    | history_mysql_status_variables_lookup |
    | history_stats_mysql_connection_pool   |
    | myhgm_connections                     |
    | myhgm_connections_day                 |
    | myhgm_connections_hour                |
    | mysql_connections                     |
    | mysql_connections_day                 |
    | mysql_connections_hour                |
    | mysql_query_cache                     |
    | mysql_query_cache_day                 |
    | mysql_query_cache_hour                |
    | system_cpu                            |
    | system_cpu_day                        |
    | system_cpu_hour                       |
    | system_memory                         |
    | system_memory_day                     |
    | system_memory_hour                    |
    +---------------------------------------+

  7. 配置主要表介绍

    • mysql_servers
    列说明
    hostgroup_idProxySQL通过 hostgroup (下称HG) 的形式组织后端db实例。一个 HG 代表同属于一个角色
    该表的主键是 (hostgroup_id, hostname, port)，可以看到一个 hostname:port 可以在多个hostgroup里面，这样可以避免 HG 1000 的从库全都不可用时，依然可以把读请求发到主库上。
    一个 HG 可以有多个实例，即多个从库，可以通过 weight 分配权重
    hostgroup_id 0 是一个特殊的HG，路由查询的时候，没有匹配到规则则默认选择 HG 0
    hostname后端MySQL监听的地址
    port后端MySQL监听的端口
    gtid_portProxySQL Binlog Reader为追踪GTID而监听的后端服务器端口。
    statusONLINE: 当前后端实例状态正常
    SHUNNED: 临时被剔除，可能因为后端 too many connections error，或者超过了可容忍延迟阀值 max_replication_lag
    OFFLINE_SOFT: “软离线”状态，不再接受新的连接，但已建立的连接会等待活跃事务完成。
    OFFLINE_HARD: “硬离线”状态，不再接受新的连接，已建立的连接或被强制中断。当后端实例宕机或网络不可达，会出现。
    weight节点在组中的权重值越高，ProxySQL会发送越多请求给它们。
    compression如果该字段的值设置为大于0，ProxySQL和该后端新建的连接中，ProxySQL将会先压缩数据再传输。
    max_connections允许连接到该后端mysql实例的最大连接数。不要大于MySQL设置的 max_connections，如果后端实例 hostname:port 在多个 hostgroup 里，以较大者为准，而不是各自独立允许的最大连接数。
    max_replication_lag允许的最大延迟，主库不受这个影响，默认0。如果 > 0， monitor 模块监控主从延迟大于阀值时，会临时把它变为 SHUNNED
    use_ssl如果设置为1，则和该后端MySQL建立SSL连接。
    max_latency_msmysql_ping 响应时间，大于这个阀值会把它从连接池剔除（即使是ONLINE）
    comment备注，不建议留空。可以通过它的内容如json格式的数据，配合自己写的check脚本，完成一些自动化的工作。

    • mysql_query_rules

      ProxySQL非常核心一个表，定义查询路由规则

    列说明
    rule_id表主键，自增。规则处理是以 rule_id 的顺序进行。
    active只有 active=1 的规则才会参与匹配。
    username如果非 NULL，只有连接用户是 username 的值才会匹配。
    schemaname如果非 NULL，只有查询连接使用的db是 schemaname 的值才会匹配。注意如果是 NULL，不代表连接没有使用schema，而是不伦任何schema都进一步匹配。
    flagINflagIN, flagOUT, apply: 用来定义路由链 chains of rules。
    - 首先会检查 flagIN=0 的规则，以rule_id的顺序；如果都没匹配上，则走这个用户的 default_hostgroup。
    - 当匹配一条规则后，会检查 flagOUT。
    - 如果不为NULL，并且 flagIN != flagOUT ，则进入以flagIN为上一个flagOUT值的新规则链。
    - 如果不为NULL，并且 flagIN = flagOUT，则应用这条规则。
    - 如果为NULL，或者 apply=1，则结束，应用这条规则。
    - 如果最终没有匹配到，则找到这个用户的 default_hostgroup。
    client_addr匹配客户端来源IP
    proxy_addr当流入的查询是在本地某地址上时，将匹配。
    proxy_port当流入的查询是在本地某端口上时，将匹配。
    digest精确的匹配一类查询。
    match_digest正则匹配一类查询。query digest 是指对查询去掉具体值后进行“模糊化”后的查询，类似 pt-fingerprint / pt-query-digest 的效果。
    match_pattern正则匹配查询。推荐用 match_digest 。关于每条查询都会计算digest对性能的影响，计算query digest确实会有性能损失，但是这却是proxysql里面非常重要的特性，主要是两点：
    - proxysql无法知道连接复用(multipexing)是否必须被自动禁用，比如连接里面有variables/tmp tables/lock table等特殊命令，是不能复用的。
    - 完整的查询去匹配正则的效率，一般没有参数化后的查询匹配效率高，因为有很长的字符串内容需要处理。再者，SELECT * FROM randomtable WHERE comment LIKE ‘%INTO sbtest1 % FROM sbtest2 %’字符串里有类似这样的语句，很难排除误匹配。
    negate_match_pattern反向匹配，相当于对 match_digest/match_pattern 的匹配取反。
    re_modifiers修改正则匹配的参数，比如默认的：忽略大小写CASELESS、禁用GLOBAL.
    flagOUT上面都是匹配规则，下面是匹配后的行为
    replace_pattern查询重写，默认为空，不rewrite。
    destination_hostgroup路由查询到这个 hostgroup。当然如果用户显式 start transaction 且 transaction_persistent=1，那么即使匹配到了，也依然按照事务里第一条sql的路由规则去走。
    cache_ttl查询结果缓存的毫秒数。
    cache_empty_result控制结果集为空是否会被缓存。
    cache_timeout没有实现
    reconnect未使用的功能
    timeout这一类查询执行的最大时间（毫秒），超时则自动kill。
    这是对后端DB的保护机制，相当于阿里云RDS loose_max_statement_time 变量的功能，但是注意不同的是，阿里云这个变量的时间时不包括DML操作出现InnoDB行锁等待的时间，而ProxySQL的这个 timeout 是计算从发送sql到等待响应的时间。默认mysql-default_query_timeout给的是 10h .
    retries语句在执行时失败时，重试次数。默认由 mysql-query_retries_on_failure变量指定，为1 。
    个人建议把它设成0，即不重试。因为执行失败，对select而言很少见，主要是dml，但自己重试对数据不放心。
    delay查询延迟执行，这是ProxySQL提供的限流机制，会让其它的查询优先执行。
    默认值 mysql-default_query_delay，为0。我们一般不用，其实还是要配合应用端使用，比如这边延迟执行，但上层等待你返回，那前端不就堵住了，没准出现雪崩效应。
    next_query_flagIN
    mirror_flagOUT
    mirror_hostgroup
    error_msg默认为NULL，如果指定了则这个查询直接被 block 掉，马上返回这个错误信息。
    这个功能也很实用，比如线上突然冒出一个 “坏查询”，应用端不方便马上发版解决，我们就可以在这配置一个规则，把查询屏蔽掉，想正常的mysql报错那样抛异常。
    OK_msg对于使用定义的规则的查询，将返回指定的信息。
    sticky_conn没实现
    multiplex连接是否复用。
    gtid_from_hostgroup定义哪个主机组应该被用作GTID一致读取的领导者（通常是复制主机组对中定义的WRITER主机组）。
    log是否记录查询日志。可以看到log是否记录的对象是根据规则。
    要开启日志记录，需要设置变量 mysql-eventslog_filename 来指定文件名，然后这个 log 标记为1。但是目前proxysql记录的日志是二进制格式，需要特定的工具才能读取： eventslog_reader_sample 。这个工具在源码目录 tools下面。
    apply当设置为1后，当匹配到该规则后，将立即应用该规则，不会再评估其它的规则(注意：应用之后，将不会评估mysql_query_rules_fast_routing中的规则)。
    attributes没实现
    comment注释说明字段，例如描述规则的意义
    • mysql_users
    列说明
    username连接后端db的用户密码。
    password这个密码你可以插入明文，也可以插入hash加密后的密文，proxysql会检查你插入的时候密码是否以 * 开头来判断，而且密文要在其它地方使用 PASSWORD()生成。但到 runtime_mysql_users 里，都统一变成了密文，所以可以明文插入，再 SAVE MYSQL USERS TO MEM，此时看到的也是HASH密文。
    active是否生效该用户。默认1
    use_ssl是否开启ssl 默认1
    default_hostgroup这个用户的请求没有匹配到规则时，默认发到这个 hostgroup，默认0
    default_schema这个用户连接时没有指定 database name 时，默认使用的schema
    注意表面上看默认为NULL，但实际上受到变量 mysql-default_schema 的影响，默认为 information_schema。
    schema_locked还不支持
    transaction_persistent如果设置为1，连接上ProxySQL的会话后，如果在一个hostgroup上开启了事务，那么后续的sql都继续维持在这个hostgroup上，不伦是否会匹配上其它路由规则，直到事务结束。默认1
    fast_forward忽略查询重写/缓存层，直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能，一般不用，路由规则 .* 就行了。
    backend目前版本这两个都需要使用默认的1，将来有可能会把 Client -> ProxySQL (frontend) 与 ProxySQL -> BackendDB (backend)的认证分开。从 runtime_mysql_users 表内容看到，记录数比 mysql_users 多了一倍，就是把前端认证与后端认证独立出来的结果
    frontend
    max_connections定义了一个特定用户的最大允许的前端连接数。
    attributes没实现
    comment文本字段，可用于用户定义的任何目的。可以是对集群存储内容的描述，也可以是对主机组添加或禁用时间的提醒，或者是一些检查器脚本所处理的JSON。
    • mysql_replication_hostgroups
    列说明
    writer_hostgroup默认的写组。后端read_only=0的节点会分配到这个组中。
    reader_hostgroup负责读的组。查询规则或者只具有只读权限的用户的读请求都会路由到该主机组中的节点。后端read_only=1的节点会分配到这个组中。
    check_type默认read_only
    comment该字段用于说明、注释。

结语

只支持读写分离，MySQL 主节点出问题，还是会有问题，后续可以把MySQL搭建为双主

参考资料

  1. MySQL permission denied error even after setting security context for SELinux on VM
  2. MySQL 5.7 installation in CentOS 7
  3. ProxySQL 配置详解及读写分离(+GTID)等功能说明 (完整篇)
  4. 中文文档