ProxySQL+MGR单主

ゞ浴缸里的玫瑰 2022-01-21 01:11 249阅读 0赞

### 文章目录 ###

*  ProxySQL+MGR单主
 *   *  MGR单主模式搭建
     *   *  前期规划
         *  前期准备
         *  安装MySQL
         *  启动组复制
     *  ProxySQL
     *   *  简介
         *  多级配置系统
         *  常用的表
         *   *  mysql\_servers
             *  mysql\_replication\_hostgroups
             *  mysql\_group\_replication\_hostgroups
             *  mysql\_users
             *  mysql\_query\_rules
         *  ProxySQL节点规划
         *  前期准备
         *  安装ProxySQL
         *  启动ProxySQL
         *  配置ProxySQL
         *   *  规划读写组
             *  配置ProxySQL
             *  查看相关状态
         *  测试
         *   *  测试读场景
             *  测试写场景
             *  测试插入
             *  测试MGR故障转移
     *  参考文档

# ProxySQL+MGR单主 #

ProxySQL中间件针对MySQL组复制模式实现读写分离以及主节点故障时能够自动切换到新的主节点，而应用对此过程无感知的功能。MySQL组复制(MGR)能够完成主节点故障后推选出来新的主节点，不过在应用层不可能通过修改新的主节点的IP来连接新的主节点，但是通过MGR+ProxySQL可以实际主节点故障时应用无感应自动切换到新的主节点。

思路大致如下：三个数据库节点使用单主模式的主复制，应用连接ProxySQL读写后端数据库节点，ProxySQL根据SQL的读写性质，分发到后端对应的数据库节点，如果后端MGR主节点down了，ProxySQL可以做到自动切换，将MGR选举出来的新主作为新的可写节点整个过程应用无需任何变动，整个切换过程秒级别的间隔

## MGR单主模式搭建 ##

### 前期规划 ###

<table> 
 <thead> 
  <tr> 
   <th>IP</th> 
   <th>系统版本</th> 
   <th>MySQL版本</th> 
   <th>数据库安装方式</th> 
   <th>主机名</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>192.168.240.51</td> 
   <td>CentOS7.6</td> 
   <td>MySQL5.7.26</td> 
   <td>二进制包安装</td> 
   <td>mgr_node1</td> 
  </tr> 
  <tr> 
   <td>192.168.240.52</td> 
   <td>CentOS7.6</td> 
   <td>MySQL5.7.26</td> 
   <td>二进制包安装</td> 
   <td>mgr_node2</td> 
  </tr> 
  <tr> 
   <td>192.168.240.53</td> 
   <td>CentOS7.6</td> 
   <td>MySQL5.7.26</td> 
   <td>二进制包安装</td> 
   <td>mgr_node3</td> 
  </tr> 
 </tbody> 
</table>

### 前期准备 ###

关闭防火墙与selinux

systemctl stop firewalld
    setenforce  0

各个节点按照规划设置主机名

hostnamectl  set-hostname mgr_node1

各个节点修改/etc/hosts文件

192.168.240.51 mgr_node1
    192.168.240.52 mgr_node2
    192.168.240.53 mgr_node3

### 安装MySQL ###

上传mysql-5.7.26-linux-glibc2.12-x86\_64.tar.gz到3个节点的/usr/local/src目录下,执行下面操作安装MySQL

cd /usr/local/src
    tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
    mv mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql5.7
    echo 'export PATH=/usr/local/mysql5.7/bin:$PATH' >> /etc/profile
    . /etc/profile

创建MySQL的数据目录结构

useradd -M -s /sbin/nologin  mysql
    mkdir -pv  /datadir/{ temp,log,data}
    chown -R mysql:mysql /datadir

初始化数据库

mysqld --initialize-insecure --datadir=/datadir/data --user=mysql
    chown -R mysql:mysql /datadir

获取随机uuid做为组复制的组名

[root@localhost ~]# cat /proc/sys/kernel/random/uuid
    b2f60782-1476-46fa-86d4-bd8c7bdbd1dd

修改/etc/my.cnf配置文件

[mysqld]
    basedir=/usr/local/mysql5.7
    user=mysql
    port=3306
    datadir=/datadir/data
    log-error=/datadir/log/err.log
    pid-file=/datadir/temp/mysqld.pid
    socket=/datadir/temp/mysql.sock
    symbolic-links=0
    
    server_id=51  # 各个节点的该值必须不同
    gtid_mode=ON
    enforce_gtid_consistency=ON
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    binlog_checksum=NONE
    log_slave_updates=ON
    log_bin=/datadir/log/binlog
    binlog_format=ROW
    
    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name="b2f60782-1476-46fa-86d4-bd8c7bdbd1dd" 
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address= "192.168.240.51:13306"
    loose-group_replication_group_seeds= "192.168.240.51:13306,192.168.240.52:13306,192.168.240.53.13306"
    loose-group_replication_bootstrap_group=off
    
    [client]
    socket=/datadir/temp/mysql.sock

上面标准文件中修改的点如下

*  `server_id`:不能与其他MySQL实例重复
 *  `loose-group_replication_group_name`：填写上一步生成的随机uuid
 *  `loose-group_replication_local_address`:当前节点用于内部通讯的ip:port
 *  `loose-group_replication_group_seeds`:组内成员通讯的ip:port

所有节点启动数据库

mysqld --daemonize

### 启动组复制 ###

在mgr\_node1节点，进入数据库执行

# 创建MGR内部消息互通的用户
    CREATE USER rpl_user@'%' IDENTIFIED BY 'Hal@123'; GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; FLUSH PRIVILEGES; # 安装MGR插件 INSTALL PLUGIN group_replication SONAME 'group_replication.so';
    # 检查是否成功安装
    SHOW PLUGINS ;
    
    # 使用当前数据库节点引导组,一个组内只有一个节点能引导
    # 不允许组内多次引导，这将导致产生两个相同名称的不同组
    SET GLOBAL group_replication_bootstrap_group=ON;
    START GROUP_REPLICATION;
    SET GLOBAL group_replication_bootstrap_group=OFF;

查看是否成功

mysql> SELECT * FROM performance_schema.replication_group_members\G
    *************************** 1. row ***************************
    CHANNEL_NAME: group_replication_applier
       MEMBER_ID: 90266496-8829-11e9-9d8d-000c29dd514d
     MEMBER_HOST: mgr_node1
     MEMBER_PORT: 3306
    MEMBER_STATE: ONLINE
    1 row in set (0.00 sec)

MEMBER\_STATE显示为`ONLINE`表示成功

其余节点加入组中,其他节点在数据库中执行下面命令

# 安装MGR插件
    INSTALL PLUGIN group_replication SONAME 'group_replication.so';
    
    # 加入组复制
    CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Hal@123' FOR CHANNEL 'group_replication_recovery';
    START GROUP_REPLICATION;

在mgr\_node1节点，数据库内执行命令，查看是否成功

状态都为ONLINE表示成功；

查看当前主节点

mysql> show status like 'group_replication_primary_member';
    +----------------------------------+--------------------------------------+
    | Variable_name                    | Value                                |
    +----------------------------------+--------------------------------------+
    | group_replication_primary_member | 90266496-8829-11e9-9d8d-000c29dd514d |
    +----------------------------------+--------------------------------------+
    1 row in set (0.03 sec)

## ProxySQL ##

### 简介 ###

ProxySQL是具有GPL许可证的高性能MySQL代理。作为监视进程监视的守护进程运行。该进程监视守护进程并在发生崩溃时重新启动它以最大限度地减少停机时间。 守护进程接受来自MySQL客户端的传入流量，并将其转发到后端MySQL服务器。 代理设计为连续运行而无需重新启动。大多数配置可以在运行时使用类似于SQL语句的查询来完成。其中包括运行时参数，服务器分组和与流量相关的设置。

### 多级配置系统 ###

通过一个多级配置系统，在MEMORY中配置好参数，加载到RUNTIME（运行环境）中生效，并在有需求时持久化到磁盘上，从而实现能够简单地动态更新配置文件，兼容MySQL的管理接口可以实现这个目标，能够动态修改尽可能多的配置项，无需重启ProxySQL。能够轻松地回滚无效的配置项

+-------------------------+
    |         RUNTIME         |
    +-------------------------+
           /|\          |
            |           |
        [1] |       [2] |
            |          \|/
    +-------------------------+
    |         MEMORY          |
    +-------------------------+ _
           /|\          |      |\
            |           |        \
        [3] |       [4] |         \ [5]
            |          \|/         \
    +-------------------------+  +-------------------------+
    |          DISK           |  |       CONFIG FILE       |
    +-------------------------+  +-------------------------+

### 常用的表 ###

#### mysql\_servers ####

用于定义后端主机组

proxysql> show create table mysql_servers\G
    *************************** 1. row ***************************
           table: mysql_servers
    Create Table: CREATE TABLE mysql_servers (
        hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
        hostname VARCHAR NOT NULL,
        port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
        gtid_port INT CHECK (gtid_port <> port AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
        status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
        weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
        compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
        max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
        max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
        use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
        max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
        comment VARCHAR NOT NULL DEFAULT '',
        PRIMARY KEY (hostgroup_id, hostname, port) )
    1 row in set (0.00 sec)

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>hostgroup_id</td> 
   <td>后端MySQL实例所属的主机组</td> 
  </tr> 
  <tr> 
   <td>hostname</td> 
   <td>后端MySQL实例监听的地址</td> 
  </tr> 
  <tr> 
   <td>port</td> 
   <td>后端MySQL实例监听的端口</td> 
  </tr> 
  <tr> 
   <td>gtid_port</td> 
   <td>the backend server port where ProxySQL Binlog Reader listens on for GTID tracking</td> 
  </tr> 
  <tr> 
   <td>status</td> 
   <td>ONLINE：表示在线 SHUNNED：该后端节点暂时倍ProxySQL自动避开，原因可能是发生了大量的连接错误，也可能是slave和master数据延迟太大</td> 
  </tr> 
  <tr> 
   <td>weight</td> 
   <td>权重，值越高，ProxySQL会发送更多请求给它们</td> 
  </tr> 
  <tr> 
   <td>compression</td> 
   <td>如果该字段值大于0，ProxySQL和该后端新建理解中，会先压缩数据在传输</td> 
  </tr> 
  <tr> 
   <td>max_connections</td> 
   <td>和该后端允许建立的最大连接数。当达到最大数量时，即使该后端的权重很大，也不会和它新建连接。请确保该后端的<code>max_connections</code>值是合理的，以避免MySQL超负荷时ProxySQL继续向其发送请求。</td> 
  </tr> 
  <tr> 
   <td>max_replication_lag</td> 
   <td>如果值大于0，ProxySQL的Monitor模块将会定期检查该<strong>slave</strong>的复制是否延后于master，如果延迟的值大于该字段的值，ProxySQL将会暂时避开该节点，直到该slave赶上master。</td> 
  </tr> 
  <tr> 
   <td>use_ssl</td> 
   <td>如果设置为1，则和该后端MySQL建立SSL连接。</td> 
  </tr> 
  <tr> 
   <td>max_latency_ms</td> 
   <td>Monitory模块定期向该后端发起ping检查，如果该节点的ping时间大于该字段的值，则将其排除在连接池之外(尽管该节点仍处于<em>ONLINE</em>状态)。</td> 
  </tr> 
  <tr> 
   <td>comment</td> 
   <td>注释</td> 
  </tr> 
 </tbody> 
</table>

#### mysql\_replication\_hostgroups ####

用于定义传统的异步、半同步主从复制主机组。如果是MySQL组复制(group replication)或者InnoDB Cluster，则使用`mysql_group_replication_hostgroups`表。如果是Galera或者PXC(Percona XtraDB Cluster)，则使用`mysql_galera_hostgroups`表。

proxysql> SHOW CREATE TABLE mysql_replication_hostgroups\G
    *************************** 1. row ***************************
           table: mysql_replication_hostgroups
    Create Table: CREATE TABLE mysql_replication_hostgroups (
        writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
        reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
        check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT 'read_only',
        comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
    1 row in set (0.00 sec)

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>writer_hostgroup</td> 
   <td>默认的写组。后端<code>read_only=0</code>的节点会分配到这个组中。</td> 
  </tr> 
  <tr> 
   <td>reader_hostgroup</td> 
   <td>负责读的组。查询规则或者只具有只读权限的用户的读请求都会路由到该主机组中的节点。后端<code>read_only=1</code>的节点会分配到这个组中。</td> 
  </tr> 
  <tr> 
   <td>check_type</td> 
   <td>默认检查后端MySQL实例变量<code>read_only</code>也可替换为<code>super_read_only</code></td> 
  </tr> 
  <tr> 
   <td>comment</td> 
   <td>注释</td> 
  </tr> 
 </tbody> 
</table>

#### mysql\_group\_replication\_hostgroups ####

定义后端使用组复制或者InnoDB Cluster的主机组。

proxysql> show create table mysql_group_replication_hostgroups\G
    *************************** 1. row ***************************
           table: mysql_group_replication_hostgroups
    Create Table: CREATE TABLE mysql_group_replication_hostgroups (
        writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
        backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,
        reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),
        offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),
        active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
        max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,
        writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0,
        max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,
        comment VARCHAR,
        UNIQUE (reader_hostgroup),
        UNIQUE (offline_hostgroup),
        UNIQUE (backup_writer_hostgroup))
    1 row in set (0.00 sec)

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>writer_hostgroup</td> 
   <td>默认的写组。后端<code>read_only=0</code>的节点会分配到这个组中</td> 
  </tr> 
  <tr> 
   <td>backup_writer_hostgroup</td> 
   <td>如果后端MySQL集群有多个节点可写并设置了<code>max_writes</code>字段的值，ProxySQL将会把其余的所有节点(超出<code>max_writes</code>)都放进备写组<code>backup_writer_hostgroup</code>中作为备份节点。</td> 
  </tr> 
  <tr> 
   <td>reader_hostgroup</td> 
   <td>负责读的组。查询规则或者只具有只读权限的用户的读请求都会路由到该主机组中的节点。后端<code>read_only=1</code>的节点会分配到这个组中。</td> 
  </tr> 
  <tr> 
   <td>offline_hostgroup</td> 
   <td>当ProxySQL监控并决定了某节点为<code>OFFLINE</code>后，会将其放进组<code>offline_hostgroup</code>中</td> 
  </tr> 
  <tr> 
   <td>active</td> 
   <td>是否启用，启用后，ProxySQL会监控该主机组，并在不同组之间合理地移动节点。</td> 
  </tr> 
  <tr> 
   <td>max_writers</td> 
   <td>该字段的值决定写组<code>writer_hostgroup</code>中最大允许的节点数量，超出该数量的但允许写的节点都会放进备份组<code>backup_writer_hostgroup</code>中。</td> 
  </tr> 
  <tr> 
   <td>writer_is_also_reader</td> 
   <td>决定一个节点升级为写节点(放进<code>writer_hostgroup</code>)后是否仍然保留在<code>reader_hostgroup</code>组中提供读服务。如果是MGR多主的场景这个值必须为1</td> 
  </tr> 
  <tr> 
   <td>max_transactions_behind</td> 
   <td>当某节点延后于写节点时，为了防止读取到过期数据，ProxySQL可能会自动避开该节点。该字段决定最多延后写节点多少个事务(具体延后的事务数量可以从MySQL的<code>sys.gr_member_routing_candidate_status</code>表中的<code>transaction_behind</code>字段获取)，延后的事务数量超出该值时，ProxySQL就会自动避开这个节点。</td> 
  </tr> 
  <tr> 
   <td>comment</td> 
   <td>注释</td> 
  </tr> 
 </tbody> 
</table>

#### mysql\_users ####

定义MySQL用户，用于连接到后端。

proxysql> SHOW CREATE TABLE mysql_users\G
    *************************** 1. row ***************************
           table: mysql_users
    Create Table: CREATE TABLE mysql_users (
        username VARCHAR NOT NULL,
        password VARCHAR,
        active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
        use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
        default_hostgroup INT NOT NULL DEFAULT 0,
        default_schema VARCHAR,
        schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
        transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
        fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
        backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
        frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
        max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
        comment VARCHAR NOT NULL DEFAULT '',
        PRIMARY KEY (username, backend),
        UNIQUE (username, frontend))
    1 row in set (0.00 sec)

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>username</td> 
   <td>前端连接到ProxySQL以及ProxySQL连接到后端的用户名</td> 
  </tr> 
  <tr> 
   <td>password</td> 
   <td>用户名对应的密码</td> 
  </tr> 
  <tr> 
   <td>active</td> 
   <td><code>active = 0</code>的用户会保留在库中，但不会加载到runtime数据结构中。</td> 
  </tr> 
  <tr> 
   <td>use_ssl</td> 
   <td>官方文档未说明</td> 
  </tr> 
  <tr> 
   <td>default_hostgroup</td> 
   <td>如果该用户发送的查询语句无法匹配任何规则，则该查询会路由到该字段指定的默认组中。</td> 
  </tr> 
  <tr> 
   <td>default_schema</td> 
   <td>建立连接时默认将切换到该schema。</td> 
  </tr> 
  <tr> 
   <td>schema_locked</td> 
   <td>目前还不支持该功能。</td> 
  </tr> 
  <tr> 
   <td>transaction_persistent</td> 
   <td>如果正在和ProxySQL建立连接的客户端使用的用户设置了该字段，那么当该用户开启一个事务后，该客户端后续的请求都将路由到同一主机组中(无视查询规则)，使得事务在这个组中是持久的，避免同一事务分散到其它组，直到事务结束才可能会路由到其它主机组。</td> 
  </tr> 
  <tr> 
   <td>fast_forward</td> 
   <td>如果设置了该字段，ProxySQL将绕过查询处理层(重写语句、缓存)，直接将原请求语句转发给后端节点。</td> 
  </tr> 
  <tr> 
   <td>backend</td> 
   <td>如果设置为1，前端将可以使用该行所代表的用户(username,password)连接到ProxySQL。</td> 
  </tr> 
  <tr> 
   <td>frontend</td> 
   <td>如果设置为1，ProxySQL将可以使用该行所代表的用户(username,password)连接到后端节点。</td> 
  </tr> 
  <tr> 
   <td>max_connections</td> 
   <td>默认最大连接数</td> 
  </tr> 
  <tr> 
   <td>comment</td> 
   <td>注释</td> 
  </tr> 
 </tbody> 
</table>

#### mysql\_query\_rules ####

用于定义路由规则和属性

proxysql> SHOW CREATE TABLE mysql_query_rules\G
    *************************** 1. row ***************************
           table: mysql_query_rules
    Create Table: CREATE TABLE mysql_query_rules (
        rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
        username VARCHAR,
        schemaname VARCHAR,
        flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
        client_addr VARCHAR,
        proxy_addr VARCHAR,
        proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535), digest VARCHAR,
        match_digest VARCHAR,
        match_pattern VARCHAR,
        negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
        re_modifiers VARCHAR DEFAULT 'CASELESS',
        flagOUT INT CHECK (flagOUT >= 0), replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
        destination_hostgroup INT DEFAULT NULL,
        cache_ttl INT CHECK(cache_ttl > 0),
        cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
        cache_timeout INT CHECK(cache_timeout >= 0),
        reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
        timeout INT UNSIGNED CHECK (timeout >= 0),
        retries INT CHECK (retries>=0 AND retries <=1000),
        delay INT UNSIGNED CHECK (delay >=0),
        next_query_flagIN INT UNSIGNED,
        mirror_flagOUT INT UNSIGNED,
        mirror_hostgroup INT UNSIGNED,
        error_msg VARCHAR,
        OK_msg VARCHAR,
        sticky_conn INT CHECK (sticky_conn IN (0,1)),
        multiplex INT CHECK (multiplex IN (0,1,2)),
        gtid_from_hostgroup INT UNSIGNED,
        log INT CHECK (log IN (0,1)),
        apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
        comment VARCHAR)
    1 row in set (0.00 sec)

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>rule_id</td> 
   <td>规则的id。规则是按照rule_id的顺序进行处理的</td> 
  </tr> 
  <tr> 
   <td>active</td> 
   <td>只有该字段值为1的规则才会加载到runtime数据结构，所以只有这些规则才会被查询处理模块处理。</td> 
  </tr> 
  <tr> 
   <td>username</td> 
   <td>用户名筛选，当设置为非NULL值时，只有匹配的用户建立的连接发出的查询才会被匹配。</td> 
  </tr> 
  <tr> 
   <td>schemaname</td> 
   <td>schema筛选，当设置为非NULL值时，只有当连接使用<code>schemaname</code>作为默认schema时，该连接发出的查询才会被匹配。(在MariaDB/MySQL中，schemaname等价于databasename)。</td> 
  </tr> 
  <tr> 
   <td><code>flagIN</code></td> 
   <td>允许我们创建"链式规则"(chains of rules)，一个规则接一个规则。</td> 
  </tr> 
  <tr> 
   <td>client_addr</td> 
   <td>通过源地址进行匹配</td> 
  </tr> 
  <tr> 
   <td>proxy_addr</td> 
   <td>当流入的查询是在本地某地址上时，将匹配。</td> 
  </tr> 
  <tr> 
   <td>proxy_port</td> 
   <td>当流入的查询是在本地某端口上时，将匹配。</td> 
  </tr> 
  <tr> 
   <td>match_digest</td> 
   <td>通过正则表达式匹配digest</td> 
  </tr> 
  <tr> 
   <td>match_pattern</td> 
   <td>通过正则表达式匹配查询语句的文本内容</td> 
  </tr> 
  <tr> 
   <td>negate_match_pattern</td> 
   <td>设置为1时，表示未被<code>match_digest</code>或<code>match_pattern</code>匹配的才算被成功匹配。也就是说，相当于在这两个匹配动作前加了NOT操作符进行取反。</td> 
  </tr> 
  <tr> 
   <td>re_modifiers</td> 
   <td>RE正则引擎的修饰符列表，多个修饰符使用逗号分隔。指定了<code>CASELESS</code>后，将忽略大小写。指定了<code>GLOBAL</code>后，将替换全局(而不是第一个被匹配到的内容)。为了向后兼容，默认只启用了<code>CASELESS</code>修饰符</td> 
  </tr> 
  <tr> 
   <td>flagOUT</td> 
   <td>允许我们创建"链式规则"(chains of rules)，一个规则接一个规则。</td> 
  </tr> 
  <tr> 
   <td>destination_hostgroup</td> 
   <td>将匹配到的查询路由到该主机组。但注意，如果用户的<code>transaction_persistent=1</code>，且该用户建立的连接开启了一个事务，则这个事务内的所有语句都将路由到同一主机组，无视匹配规则。</td> 
  </tr> 
  <tr> 
   <td>cache_ttl</td> 
   <td>查询结果缓存的时间长度(单位毫秒)。</td> 
  </tr> 
  <tr> 
   <td>cache_empty_result</td> 
   <td>控制是否缓存不带行的结果集</td> 
  </tr> 
  <tr> 
   <td>cache_timeout</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>reconnect</td> 
   <td>特性暂不支持</td> 
  </tr> 
  <tr> 
   <td>timeout</td> 
   <td>被匹配或被重写的查询执行的最大超时时长(单位毫秒)。如果一个查询执行的时间太久(超过了这个值)，该查询将自动被杀掉。如果未设置该值，将使用全局变量<code>mysql-default_query_timeout</code>的值。</td> 
  </tr> 
  <tr> 
   <td>retries</td> 
   <td>当在执行查询时探测到故障后，重新执行查询的最大次数。如果未指定，则使用全局变量<code>mysql-query_retries_on_failure</code>的值。</td> 
  </tr> 
  <tr> 
   <td>delay</td> 
   <td>延迟执行该查询的毫秒数。本质上是一个限流机制和QoS，使得可以将优先级让位于其它查询。这个值会写入到<code>mysql-default_query_delay</code>全局变量中，所以它会应用于所有的查询。将来的版本中将会提供一个更高级的限流机制。</td> 
  </tr> 
  <tr> 
   <td>next_query_flagIN</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>mirror_flagOUT</td> 
   <td>mirroring相关的设置</td> 
  </tr> 
  <tr> 
   <td>mirror_hostgroup</td> 
   <td>mirroring相关的设置</td> 
  </tr> 
  <tr> 
   <td>error_msg</td> 
   <td>查询将被阻塞，然后向客户端返回<code>error_msg</code>指定的信息。</td> 
  </tr> 
  <tr> 
   <td>OK_msg</td> 
   <td>对于使用已定义规则的查询，将返回指定的消息。</td> 
  </tr> 
  <tr> 
   <td>sticky_conn</td> 
   <td>尚未实现</td> 
  </tr> 
  <tr> 
   <td>multiplex</td> 
   <td>如果设置为0，将禁用multiplexing。如果设置为1，则启用或重新启用multiplexing，除非有其它条件(如用户变量或事务)阻止启用。如果设置为2，则只对当前查询不禁用multiplexing。默认值为<code>NULL</code>，表示不会修改multiplexing的策略。详细内容见<a href="https://github.com/malongshuai/proxysql/wiki/Multiplexing" rel="nofollow">wiki</a>。 <code>log</code> - 查询将记录日志。</td> 
  </tr> 
  <tr> 
   <td>gtid_from_hostgroup</td> 
   <td>定义应将哪个hostgroup用作gtid一致性读取的前导（通常是复制hostgroup对中定义的writer hostgroup）</td> 
  </tr> 
  <tr> 
   <td>log</td> 
   <td>查询将记录日志。</td> 
  </tr> 
  <tr> 
   <td>apply</td> 
   <td>当设置为1后，当匹配到该规则后，将立即应用该规则，不会再评估其它的规则(注意：应用之后，将不会评估<code>mysql_query_rules_fast_routing</code>中的规则)。</td> 
  </tr> 
  <tr> 
   <td>comment</td> 
   <td>注释</td> 
  </tr> 
 </tbody> 
</table>

### ProxySQL节点规划 ###

<table> 
 <thead> 
  <tr> 
   <th>IP</th> 
   <th>系统</th> 
   <th>ProxySQL版本</th> 
   <th>安装方式</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>192.168.240.54</td> 
   <td>CentOS7.6</td> 
   <td>proxysql-2.0.4</td> 
   <td>rpm包安装</td> 
  </tr> 
 </tbody> 
</table>

### 前期准备 ###

关闭防火墙和selinux

systemctl  stop firewalld
    setenforce  0

### 安装ProxySQL ###

上传安装包到/usr/local/src目录下

cd /usr/local/src
    yum install proxysql-2.0.4-1-centos7.x86_64.rpm  -y

### 启动ProxySQL ###

systemctl start proxysql

### 配置ProxySQL ###

#### 规划读写组 ####

<table> 
 <thead> 
  <tr> 
   <th>默认的写组</th> 
   <th>后备的写组</th> 
   <th>读组</th> 
   <th>离线组</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>10</td> 
   <td>20</td> 
   <td>30</td> 
   <td>40</td> 
  </tr> 
 </tbody> 
</table>

#### 配置ProxySQL ####

在MGR主节点执行下面SQL语句，创建视图提供给ProxySQL判断该节点状态

USE sys;
    
    DELIMITER $$
    
    CREATE FUNCTION IFZERO(a INT, b INT)
    RETURNS INT
    DETERMINISTIC
    RETURN IF(a = 0, b, a)$$
    
    CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
    RETURNS INT
    DETERMINISTIC
    RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$
    
    CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
    RETURNS TEXT(10000)
    DETERMINISTIC
    RETURN GTID_SUBTRACT(g, '')$$
    
    CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
    RETURNS INT
    DETERMINISTIC
    BEGIN
      DECLARE result BIGINT DEFAULT 0;
      DECLARE colon_pos INT;
      DECLARE next_dash_pos INT;
      DECLARE next_colon_pos INT;
      DECLARE next_comma_pos INT;
      SET gtid_set = GTID_NORMALIZE(gtid_set);
      SET colon_pos = LOCATE2(':', gtid_set, 1);
      WHILE colon_pos != LENGTH(gtid_set) + 1 DO
         SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
         SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
         SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
         IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
           SET result = result +
             SUBSTR(gtid_set, next_dash_pos + 1,
                    LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
             SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
         ELSE
           SET result = result + 1;
         END IF;
         SET colon_pos = next_colon_pos;
      END WHILE;
      RETURN result;
    END$$
    
    CREATE FUNCTION gr_applier_queue_length()
    RETURNS INT
    DETERMINISTIC
    BEGIN
      RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
    Received_transaction_set FROM performance_schema.replication_connection_status
    WHERE Channel_name = 'group_replication_applier' ), (SELECT
    @@global.GTID_EXECUTED) )));
    END$$
    
    CREATE FUNCTION gr_member_in_primary_partition()
    RETURNS VARCHAR(3)
    DETERMINISTIC
    BEGIN
      RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
    performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
    ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
    'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
    performance_schema.replication_group_member_stats USING(member_id));
    END$$
    
    CREATE VIEW gr_member_routing_candidate_status AS SELECT
    sys.gr_member_in_primary_partition() as viable_candidate,
    IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
    performance_schema.global_variables WHERE variable_name IN ('read_only',
    'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
    sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$
    
    DELIMITER ;

到各个节点检查视图是否创建成功

mysql> SELECT * FROM sys.gr_member_routing_candidate_status;
    +------------------+-----------+---------------------+----------------------+
    | viable_candidate | read_only | transactions_behind | transactions_to_cert |
    +------------------+-----------+---------------------+----------------------+
    | YES              | NO        |                   0 |                    0 |
    +------------------+-----------+---------------------+----------------------+
    1 row in set (0.00 sec)

在MGR主节点执行下面SQL语句,创建监控用户用于ProxySQL监控数据库状态

create user 'monitor'@'%' identified by 'monitor'; grant select on sys.* to 'monitor'@'%';

在MGR主节点创建用户，用于ProxySQL访问

create user 'proxysql'@'%' identified by 'proxysql';
    grant all on *.* to 'proxysql'@'%';

在ProxySQL节点，连接ProxySQL的Admin管理接口

mysql -uadmin -padmin --prompt='proxysql> ' -P6032 -h127.0.0.1

在mysql\_servers添加后端节点

proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'192.168.240.51',3306,1,3000,10,'mgr_node1');
    proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'192.168.240.52',3306,1,3000,10,'mgr_node2');
    proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'192.168.240.53',3306,1,3000,10,'mgr_node3');
    # 将mysql_servers表加载到runtime
    proxysql> LOAD mysql users TO RUNTIME;
    # 将mysql_servers表保存到磁盘
    proxysql> SAVE mysql servers TO DISK;

设置监控用户账户密码

proxysql> set mysql-monitor_username='monitor';
    proxysql> set mysql-monitor_password='monitor';
    # 加载到runtime
    proxysql> LOAD mysql variables TO RUNTIME;
    # 保存到磁盘中
    proxysql> SAVE mysql variables TO DISK;

设置提供访问的用户

proxysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)values('proxysql','proxysql',1,10,1);
    # 加载到runtime
    proxysql>  load mysql users to runtime;
    # 保存到磁盘中
    proxysql> save mysql users to disk;

配置mysql\_group\_replication\_hostgroups表

proxysql> insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind)  values(10,20,30,40,1,1,0,0);
    # 加载到runtime
    proxysql> load mysql servers to runtime;
    # 保存到磁盘中
    proxysql> save mysql servers to disk;

设置读写分离规则

proxysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);
    proxysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',30,1);
    
    proxysql> load mysql query rules to runtime;
    proxysql> save mysql query rules to disk;

#### 查看相关状态 ####

查看后端节点健康状态

proxysql>  SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10 ;
    proxysql>  SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

查看MGR配置

proxysql> select * from mysql_group_replication_hostgroups\G
    *************************** 1. row ***************************
           writer_hostgroup: 10  # 写组
    backup_writer_hostgroup: 20  # 后备写组
           reader_hostgroup: 30  # 读组
          offline_hostgroup: 40  # 下线组
                     active: 1   # 是否启用
                max_writers: 1   # 最多的写节点个数
      writer_is_also_reader: 0   # 决定一个节点升级为写节点(放进writer_hostgroup)后是否仍然保留在reader_hostgroup组中提供读服务。如果mgr多主模式需要设置为1
    max_transactions_behind: 0   # 该字段决定最多延后写节点多少个事务
                    comment: NULL # 注释
    1 row in set (0.00 sec)

查看MGR相关的监控指标

MySQL [(none)]> select * from mysql_server_group_replication_log desc limit 10;

### 测试 ###

#### 测试读场景 ####

[root@localhost ~]# for i in `seq 1 10`; do mysql -uproxysql -pproxysql -h192.168.240.54 -P6033 -e 'select * from performance_schema.global_variables where variable_name="server_id";' ; done | grep server
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       53
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       53
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       52
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       53
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       52
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       52
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       52
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       52
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       53
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       52

可以看到读组默认都到了52和53上

#### 测试写场景 ####

[root@localhost ~]# for i in `seq 1 10`; do mysql -uproxysql -pproxysql -h192.168.240.54 -P6033 -e 'select * from performance_schema.global_variables where variable_name="server_id" for update;' ; done  | grep server
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       51
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       51
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       51
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       51
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       51
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       51
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       51
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       51
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       51
    mysql: [Warning] Using a password on the command line interface can be insecure.
    server_id       51

for update请求都到了51节点上

#### 测试插入 ####

因为只有主可写，所以只要正常插入没问题，则表示插入操作自动路由到写节点

[root@localhost ~]# for i in `seq 1 10`; do mysql -uproxysql -pproxysql -h192.168.240.54 -P6033 -e "insert into mgr_test.t values($i)" ; done
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.

进入数据库从节点查看表

mysql> select * from mgr_test.t;
    +----+
    | id |
    +----+
    |  1 |
    |  2 |
    |  3 |
    |  4 |
    |  5 |
    |  6 |
    |  7 |
    |  8 |
    |  9 |
    | 10 |
    +----+
    10 rows in set (0.00 sec)

#### 测试MGR故障转移 ####

down掉主节点

mysqladmin  shutdown

执行写入操作

[root@localhost ~]# for i in `seq 11 20`; do mysql -uproxysql -pproxysql -h192.168.240.54 -P6033 -e "insert into mgr_test.t values($i)" ; done
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql: [Warning] Using a password on the command line interface can be insecure.

能正常继续写入,进入ProxySQL管理节点查看节点状态

[root@localhost ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 -e "select hostgroup_id, hostname, port,status from runtime_mysql_servers;"
    +--------------+----------------+------+---------+
    | hostgroup_id | hostname       | port | status  |
    +--------------+----------------+------+---------+
    | 10           | 192.168.240.53 | 3306 | ONLINE  |
    | 30           | 192.168.240.52 | 3306 | ONLINE  |
    | 20           | 192.168.240.51 | 3306 | SHUNNED |
    +--------------+----------------+------+---------+

可以看到51节点已经状态为SHUNNED，被避开了

现在启动51节点的MySQL

mysqld --daemonize

进入51节点数据库中启动组复制

stop group_replication;
    CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Hal@123' FOR CHANNEL 'group_replication_recovery';
    start group_replication;

再次查看组成员状态

[root@localhost ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 -e "select hostgroup_id, hostname, port,status from runtime_mysql_servers;"
    +--------------+----------------+------+--------+
    | hostgroup_id | hostname       | port | status |
    +--------------+----------------+------+--------+
    | 10           | 192.168.240.53 | 3306 | ONLINE |
    | 30           | 192.168.240.51 | 3306 | ONLINE |
    | 30           | 192.168.240.52 | 3306 | ONLINE |
    +--------------+----------------+------+--------+

可以看到已经自动上线了

## 参考文档 ##

*  [https://www.cnblogs.com/f-ck-need-u/p/9383126.html\#配置proxysql][https_www.cnblogs.com_f-ck-need-u_p_9383126.html_proxysql]
 *  [https://github.com/sysown/proxysql/wiki][https_github.com_sysown_proxysql_wiki]
 *  [https://github.com/malongshuai/proxysql/wiki][https_github.com_malongshuai_proxysql_wiki]
 *  [https://blog.51cto.com/l0vesql/2090721][https_blog.51cto.com_l0vesql_2090721]

[https_www.cnblogs.com_f-ck-need-u_p_9383126.html_proxysql]: https://www.cnblogs.com/f-ck-need-u/p/9383126.html#%E9%85%8D%E7%BD%AEproxysql
[https_github.com_sysown_proxysql_wiki]: https://github.com/sysown/proxysql/wiki
[https_github.com_malongshuai_proxysql_wiki]: https://github.com/malongshuai/proxysql/wiki
[https_blog.51cto.com_l0vesql_2090721]: https://blog.51cto.com/l0vesql/2090721