ProxySQL+MGR单主

ゞ 浴缸里的玫瑰 2022-01-21 01:11 287阅读 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单主模式搭建

前期规划


































IP 系统版本 MySQL版本 数据库安装方式 主机名
192.168.240.51 CentOS7.6 MySQL5.7.26 二进制包安装 mgr_node1
192.168.240.52 CentOS7.6 MySQL5.7.26 二进制包安装 mgr_node2
192.168.240.53 CentOS7.6 MySQL5.7.26 二进制包安装 mgr_node3

前期准备

关闭防火墙与selinux

  1. systemctl stop firewalld
  2. setenforce  0

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

  1. hostnamectl  set-hostname mgr_node1

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

  1. 192.168.240.51 mgr_node1
  2. 192.168.240.52 mgr_node2
  3. 192.168.240.53 mgr_node3

安装MySQL

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

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

创建MySQL的数据目录结构

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

初始化数据库

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

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

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

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

  1. [mysqld]
  2. basedir=/usr/local/mysql5.7
  3. user=mysql
  4. port=3306
  5. datadir=/datadir/data
  6. log-error=/datadir/log/err.log
  7. pid-file=/datadir/temp/mysqld.pid
  8. socket=/datadir/temp/mysql.sock
  9. symbolic-links=0
  11. server_id=51  # 各个节点的该值必须不同
  12. gtid_mode=ON
  13. enforce_gtid_consistency=ON
  14. master_info_repository=TABLE
  15. relay_log_info_repository=TABLE
  16. binlog_checksum=NONE
  17. log_slave_updates=ON
  18. log_bin=/datadir/log/binlog
  19. binlog_format=ROW
  21. transaction_write_set_extraction=XXHASH64
  22. loose-group_replication_group_name="b2f60782-1476-46fa-86d4-bd8c7bdbd1dd" 
  23. loose-group_replication_start_on_boot=off
  24. loose-group_replication_local_address= "192.168.240.51:13306"
  25. loose-group_replication_group_seeds= "192.168.240.51:13306,192.168.240.52:13306,192.168.240.53.13306"
  26. loose-group_replication_bootstrap_group=off
  28. [client]
  29. 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

所有节点启动数据库

  1. mysqld --daemonize

启动组复制

在mgr_node1节点，进入数据库执行

  1. # 创建MGR内部消息互通的用户
  2. 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';
  3. # 检查是否成功安装
  4. SHOW PLUGINS ;
  6. # 使用当前数据库节点引导组,一个组内只有一个节点能引导
  7. # 不允许组内多次引导，这将导致产生两个相同名称的不同组
  8. SET GLOBAL group_replication_bootstrap_group=ON;
  9. START GROUP_REPLICATION;
  10. SET GLOBAL group_replication_bootstrap_group=OFF;

查看是否成功

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

MEMBER_STATE显示为ONLINE表示成功

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

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

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

  1. mysql> SELECT * FROM performance_schema.replication_group_members;
  2. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  3. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
  4. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  5. | group_replication_applier | 76879176-88c6-11e9-af20-000c29d49988 | mgr_node3   |        3306 | ONLINE       |
  6. | group_replication_applier | 77ddf134-88c6-11e9-b034-000c2913609d | mgr_node2   |        3306 | ONLINE       |
  7. | group_replication_applier | 90266496-8829-11e9-9d8d-000c29dd514d | mgr_node1   |        3306 | ONLINE       |
  8. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  9. 3 rows in set (0.00 sec)

状态都为ONLINE表示成功；

查看当前主节点

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

ProxySQL

简介

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

多级配置系统

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

  1. +-------------------------+
  2. |         RUNTIME         |
  3. +-------------------------+
  4.        /|\          |
  5.         |           |
  6.     [1] |       [2] |
  7.         |          \|/
  8. +-------------------------+
  9. |         MEMORY          |
  10. +-------------------------+ _
  11.        /|\          |      |\
  12.         |           |        \
  13.     [3] |       [4] |         \ [5]
  14.         |          \|/         \
  15. +-------------------------+  +-------------------------+
  16. |          DISK           |  |       CONFIG FILE       |
  17. +-------------------------+  +-------------------------+

常用的表

mysql_servers

用于定义后端主机组

  1. proxysql> show create table mysql_servers\G
  2. *************************** 1. row ***************************
  3.        table: mysql_servers
  4. Create Table: CREATE TABLE mysql_servers (
  5.     hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
  6.     hostname VARCHAR NOT NULL,
  7.     port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
  8.     gtid_port INT CHECK (gtid_port <> port AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
  9.     status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
  10.     weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
  11.     compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
  12.     max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
  13.     max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
  14.     use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
  15.     max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
  16.     comment VARCHAR NOT NULL DEFAULT '',
  17.     PRIMARY KEY (hostgroup_id, hostname, port) )
  18. 1 row in set (0.00 sec)

























































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

mysql_replication_hostgroups

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

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

























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

mysql_group_replication_hostgroups

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

  1. proxysql> show create table mysql_group_replication_hostgroups\G
  2. *************************** 1. row ***************************
  3.        table: mysql_group_replication_hostgroups
  4. Create Table: CREATE TABLE mysql_group_replication_hostgroups (
  5.     writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
  6.     backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,
  7.     reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),
  8.     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),
  9.     active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
  10.     max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,
  11.     writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0,
  12.     max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,
  13.     comment VARCHAR,
  14.     UNIQUE (reader_hostgroup),
  15.     UNIQUE (offline_hostgroup),
  16.     UNIQUE (backup_writer_hostgroup))
  17. 1 row in set (0.00 sec)













































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

mysql_users

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

  1. proxysql> SHOW CREATE TABLE mysql_users\G
  2. *************************** 1. row ***************************
  3.        table: mysql_users
  4. Create Table: CREATE TABLE mysql_users (
  5.     username VARCHAR NOT NULL,
  6.     password VARCHAR,
  7.     active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
  8.     use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
  9.     default_hostgroup INT NOT NULL DEFAULT 0,
  10.     default_schema VARCHAR,
  11.     schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
  12.     transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
  13.     fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
  14.     backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
  15.     frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
  16.     max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
  17.     comment VARCHAR NOT NULL DEFAULT '',
  18.     PRIMARY KEY (username, backend),
  19.     UNIQUE (username, frontend))
  20. 1 row in set (0.00 sec)





























































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

mysql_query_rules

用于定义路由规则和属性

  1. proxysql> SHOW CREATE TABLE mysql_query_rules\G
  2. *************************** 1. row ***************************
  3.        table: mysql_query_rules
  4. Create Table: CREATE TABLE mysql_query_rules (
  5.     rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  6.     active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
  7.     username VARCHAR,
  8.     schemaname VARCHAR,
  9.     flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
  10.     client_addr VARCHAR,
  11.     proxy_addr VARCHAR,
  12.     proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535), digest VARCHAR,
  13.     match_digest VARCHAR,
  14.     match_pattern VARCHAR,
  15.     negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
  16.     re_modifiers VARCHAR DEFAULT 'CASELESS',
  17.     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),
  18.     destination_hostgroup INT DEFAULT NULL,
  19.     cache_ttl INT CHECK(cache_ttl > 0),
  20.     cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
  21.     cache_timeout INT CHECK(cache_timeout >= 0),
  22.     reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
  23.     timeout INT UNSIGNED CHECK (timeout >= 0),
  24.     retries INT CHECK (retries>=0 AND retries <=1000),
  25.     delay INT UNSIGNED CHECK (delay >=0),
  26.     next_query_flagIN INT UNSIGNED,
  27.     mirror_flagOUT INT UNSIGNED,
  28.     mirror_hostgroup INT UNSIGNED,
  29.     error_msg VARCHAR,
  30.     OK_msg VARCHAR,
  31.     sticky_conn INT CHECK (sticky_conn IN (0,1)),
  32.     multiplex INT CHECK (multiplex IN (0,1,2)),
  33.     gtid_from_hostgroup INT UNSIGNED,
  34.     log INT CHECK (log IN (0,1)),
  35.     apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
  36.     comment VARCHAR)
  37. 1 row in set (0.00 sec)









































































































































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

ProxySQL节点规划


















IP 系统 ProxySQL版本 安装方式
192.168.240.54 CentOS7.6 proxysql-2.0.4 rpm包安装

前期准备

关闭防火墙和selinux

  1. systemctl  stop firewalld
  2. setenforce  0

安装ProxySQL

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

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

启动ProxySQL

  1. systemctl start proxysql

配置ProxySQL

规划读写组


















默认的写组 后备的写组 读组 离线组
10 20 30 40

配置ProxySQL

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

  1. USE sys;
  3. DELIMITER $$
  5. CREATE FUNCTION IFZERO(a INT, b INT)
  6. RETURNS INT
  7. DETERMINISTIC
  8. RETURN IF(a = 0, b, a)$$
  10. CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
  11. RETURNS INT
  12. DETERMINISTIC
  13. RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$
  15. CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
  16. RETURNS TEXT(10000)
  17. DETERMINISTIC
  18. RETURN GTID_SUBTRACT(g, '')$$
  20. CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
  21. RETURNS INT
  22. DETERMINISTIC
  23. BEGIN
  24.   DECLARE result BIGINT DEFAULT 0;
  25.   DECLARE colon_pos INT;
  26.   DECLARE next_dash_pos INT;
  27.   DECLARE next_colon_pos INT;
  28.   DECLARE next_comma_pos INT;
  29.   SET gtid_set = GTID_NORMALIZE(gtid_set);
  30.   SET colon_pos = LOCATE2(':', gtid_set, 1);
  31.   WHILE colon_pos != LENGTH(gtid_set) + 1 DO
  32.      SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
  33.      SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
  34.      SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
  35.      IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
  36.        SET result = result +
  37.          SUBSTR(gtid_set, next_dash_pos + 1,
  38.                 LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
  39.          SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
  40.      ELSE
  41.        SET result = result + 1;
  42.      END IF;
  43.      SET colon_pos = next_colon_pos;
  44.   END WHILE;
  45.   RETURN result;
  46. END$$
  48. CREATE FUNCTION gr_applier_queue_length()
  49. RETURNS INT
  50. DETERMINISTIC
  51. BEGIN
  52.   RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
  53. Received_transaction_set FROM performance_schema.replication_connection_status
  54. WHERE Channel_name = 'group_replication_applier' ), (SELECT
  55. @@global.GTID_EXECUTED) )));
  56. END$$
  58. CREATE FUNCTION gr_member_in_primary_partition()
  59. RETURNS VARCHAR(3)
  60. DETERMINISTIC
  61. BEGIN
  62.   RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
  63. performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
  64. ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
  65. 'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
  66. performance_schema.replication_group_member_stats USING(member_id));
  67. END$$
  69. CREATE VIEW gr_member_routing_candidate_status AS SELECT
  70. sys.gr_member_in_primary_partition() as viable_candidate,
  71. IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
  72. performance_schema.global_variables WHERE variable_name IN ('read_only',
  73. 'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
  74. sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$
  76. DELIMITER ;

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

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

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

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

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

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

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

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

在mysql_servers添加后端节点

  1. 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');
  2. 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');
  3. 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');
  4. # 将mysql_servers表加载到runtime
  5. proxysql> LOAD mysql users TO RUNTIME;
  6. # 将mysql_servers表保存到磁盘
  7. proxysql> SAVE mysql servers TO DISK;

设置监控用户账户密码

  1. proxysql> set mysql-monitor_username='monitor';
  2. proxysql> set mysql-monitor_password='monitor';
  3. # 加载到runtime
  4. proxysql> LOAD mysql variables TO RUNTIME;
  5. # 保存到磁盘中
  6. proxysql> SAVE mysql variables TO DISK;

设置提供访问的用户

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

配置mysql_group_replication_hostgroups表

  1. 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);
  2. # 加载到runtime
  3. proxysql> load mysql servers to runtime;
  4. # 保存到磁盘中
  5. proxysql> save mysql servers to disk;

设置读写分离规则

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

查看相关状态

查看后端节点健康状态

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

查看MGR配置

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

查看MGR相关的监控指标

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

测试

测试读场景

  1. [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
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. server_id       53
  4. mysql: [Warning] Using a password on the command line interface can be insecure.
  5. server_id       53
  6. mysql: [Warning] Using a password on the command line interface can be insecure.
  7. server_id       52
  8. mysql: [Warning] Using a password on the command line interface can be insecure.
  9. server_id       53
  10. mysql: [Warning] Using a password on the command line interface can be insecure.
  11. server_id       52
  12. mysql: [Warning] Using a password on the command line interface can be insecure.
  13. server_id       52
  14. mysql: [Warning] Using a password on the command line interface can be insecure.
  15. server_id       52
  16. mysql: [Warning] Using a password on the command line interface can be insecure.
  17. server_id       52
  18. mysql: [Warning] Using a password on the command line interface can be insecure.
  19. server_id       53
  20. mysql: [Warning] Using a password on the command line interface can be insecure.
  21. server_id       52

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

测试写场景

  1. [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
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. server_id       51
  4. mysql: [Warning] Using a password on the command line interface can be insecure.
  5. server_id       51
  6. mysql: [Warning] Using a password on the command line interface can be insecure.
  7. server_id       51
  8. mysql: [Warning] Using a password on the command line interface can be insecure.
  9. server_id       51
  10. mysql: [Warning] Using a password on the command line interface can be insecure.
  11. server_id       51
  12. mysql: [Warning] Using a password on the command line interface can be insecure.
  13. server_id       51
  14. mysql: [Warning] Using a password on the command line interface can be insecure.
  15. server_id       51
  16. mysql: [Warning] Using a password on the command line interface can be insecure.
  17. server_id       51
  18. mysql: [Warning] Using a password on the command line interface can be insecure.
  19. server_id       51
  20. mysql: [Warning] Using a password on the command line interface can be insecure.
  21. server_id       51

for update请求都到了51节点上

测试插入

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

  1. [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
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. mysql: [Warning] Using a password on the command line interface can be insecure.
  4. mysql: [Warning] Using a password on the command line interface can be insecure.
  5. mysql: [Warning] Using a password on the command line interface can be insecure.
  6. mysql: [Warning] Using a password on the command line interface can be insecure.
  7. mysql: [Warning] Using a password on the command line interface can be insecure.
  8. mysql: [Warning] Using a password on the command line interface can be insecure.
  9. mysql: [Warning] Using a password on the command line interface can be insecure.
  10. mysql: [Warning] Using a password on the command line interface can be insecure.
  11. mysql: [Warning] Using a password on the command line interface can be insecure.

进入数据库从节点查看表

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

测试MGR故障转移

down掉主节点

  1. mysqladmin  shutdown

执行写入操作

  1. [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
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. mysql: [Warning] Using a password on the command line interface can be insecure.
  4. mysql: [Warning] Using a password on the command line interface can be insecure.
  5. mysql: [Warning] Using a password on the command line interface can be insecure.
  6. mysql: [Warning] Using a password on the command line interface can be insecure.
  7. mysql: [Warning] Using a password on the command line interface can be insecure.
  8. mysql: [Warning] Using a password on the command line interface can be insecure.
  9. mysql: [Warning] Using a password on the command line interface can be insecure.
  10. mysql: [Warning] Using a password on the command line interface can be insecure.
  11. mysql: [Warning] Using a password on the command line interface can be insecure.

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

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

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

现在启动51节点的MySQL

  1. mysqld --daemonize

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

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

再次查看组成员状态

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

可以看到已经自动上线了

参考文档

  • https://www.cnblogs.com/f-ck-need-u/p/9383126.html#配置proxysql
  • https://github.com/sysown/proxysql/wiki
  • https://github.com/malongshuai/proxysql/wiki
  • https://blog.51cto.com/l0vesql/2090721

发表评论

表情：
评论列表 （有 0 条评论，287人围观）

还没有评论，来说两句吧...

相关阅读