-
MySQL中间件之ProxySQL(15)：ProxySQL代理MySQL组复制
1.ProxySQL+组复制前言 在以前的ProxySQL版本中，要支持MySQL组复制(MGR,MySQL Group Replication)需要借助第三方脚本对组复制做健康检查并自动调整配置，但是从ProxySQL v1.4.0开始，已原生支持MySQL组复制的代理，在main库中也已提供mysql_group_replication_hostgroups表来控制组复制集群中的读、写组。 Admin> show tables ; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | ... | runtime_mysql_group_replication_hostgroups | ... | scheduler | +--------------------------------------------+ admin> show tables from monitor; +------------------------------------+ | tables | +------------------------------------+ | mysql_server_connect_log | | mysql_server_group_replication_log | | mysql_server_ping_log | | mysql_server_read_only_log | | mysql_server_replication_lag_log | +------------------------------------+ 尽管已原生支持MGR，但仍然需要在MGR节点中创建一张额外的系统视图sys.gr_member_routing_candidate_status为ProxySQL提供监控指标。创建该视图的脚本addition_to_sys.zip我已上传。在后文需要创建该系统视图的地方，我会将这个脚本的内容贴出来。 本文先解释mysql_group_replication_hostgroups表中各字段的意义，然后按……
SE_You
2024-04-02
20 0 0 -
MySQL中间件之ProxySQL(14)：ProxySQL+PXC
1.ProxySQL+PXC 本文演示ProxySQL代理PXC(Percona XtraDB Cluster)的方法，不涉及原理，纯配置过程，所以如有不懂之处，请先掌握相关理论。 ProxySQL要代理PXC，需要使用ProxySQL额外提供的脚本/usr/share/proxysql/tools/proxysql_galera_checker.sh做健康检查并动态调整，通过Scheduler来调用实现。ProxySQL v2.0版本将原生支持Galera并增加相关表mysql_galera_hostgroups，到时就无需借用第三方脚本。 实验环境： Node HostName IP_address Node1 proxysql 192.168.100.21 Node2 pxc1 192.168.100.32 Node3 pxc2 192.168.100.33 Node4 pxc3 192.168.100.34 1.1 配置PXC 1.安装percona xtradb cluster 3个节点上(pxc1、pxc2、pxc3)都操作： 提供yum源： [percona] name=percona_repo baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch enabled = 1 gpgcheck = 0 ##安装 yum install Percona-XtraDB-Cluster-client-57 2.提供配置文件 第一个节点(pxc1)配置文件/etc/my.cnf： [mysqld] server-id=100 # 各节点不同 datadir=/data socket=/data/mysql.sock log-error=/data/error.log pid-file=/data/mysqld.pid log-bin=/data/master-bin log_slave_updates wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_name=pxc-cluster wsrep_cluster_address=gcomm://192.168.100.32,192.168.100.33,192.168.100.34 wsrep_node_name=pxc1 # 各节点不同 wsrep_node_address=192.168.100.32 # 各节点不同 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:P@ssword1! pxc_strict_mode=ENFORCING binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_loc……
SE_You
2024-04-01
50 0 0 -
MySQL中间件之ProxySQL(13)：ProxySQL集群
ProxySQL有原生的集群功能，但是这个原生的集群功能还正在试验阶段。本文会详细介绍这个原生集群的实现细节。 1.ProxySQL部署在哪 在拓扑结构中，ProxySQL部署在应用程序和MySQL集群的中间位置。应用程序向ProxySQL发起SQL语句，ProxySQL分析收到的SQL语句，进行匹配、重写等操作，然后路由给后端MySQL集群中的某实例。 如图： 上图描述的是多个application共用一个ProxySQL实例，但需求总是多变的。例如有些app比较繁忙，我们想要将这些繁忙的app使用的ProxySQL分离出来，让不同的application独立使用一个ProxySQL甚至一个ProxySQL集群，让那些不太繁忙的app共用一个ProxySQL。这种情形如下图： 还可以为每个app都配置一个ProxySQL，如下图。 这种配置的好处是明显的，没有单点故障，不需要额外的负载均衡，app+proxysql的节点可以轻松扩展。但是，也有缺点，各ProxySQL之间无法共享查询缓存。但无论如何，这是一种良好的配置方式。 此外，还可以使用多层结构，对ProxySQL群进行负载均衡。如下图： 上图几个注意点： 负载软件层，也可以使用ProxySQL对ProxySQL集群进行负载均衡，因为ProxySQL自身就是一个代理，而且是专门负责MySQL协议的代理。 在负载均衡层，要保证事务持久，不能让一个事务内的不同语句路由到不同的目标上，也就是说负载的目标是"有状态"的。因此在使用lvs/haproxy时，必须想办法保证事务持久性。通用性方法是，从多个ProxySQL节点中取出一个作为逻辑写节点(可以有backup)，haproxy/lvs通过端口(也可以用户名，但通常采用端口)代理的方式，强制将所有写语句路由到这个固定的ProxySQL节点上。 而通过ProxySQL来代理ProxySQL集群，因为ProxySQL内部支持事务持久(在mysql_users表中字段transaction_persistent控制持久性)。 综上分析，通过lvs/haproxy负载ProxySQL或者负载MySQL、Galera……
SE_You
2024-03-29
84 0 0 -
MySQL中间件之ProxySQL(12)：禁止多路路由
1.multiplexing multiplexing，作用是将语句分多路路由。开启了multiplexing开关，读/写分离、按规则路由才能进行。但有时候，有些语句要求路由到同一个主机组，甚至是同一个主机组中的同一个节点上。这时会自动禁用multiplexing。禁用multiplexing后，语句会根据同一个连接进行路由。 也就是说，在禁用multiplexing情况下，会将语句路由到同一个主机组中的同一个节点上。 什么情况下会禁用多路路由(disable multiplexing)？ 激活事务时 当某连接中激活了一个事务，则在该事务提交或回滚之前，multiplexing将一直被禁用。 表被锁 当执行了LOCK TABLE, LOCK TABLES 或 FLUSH TABLES WITH READ LOCK 时，将一直禁用multiplexing，直到执行UNLOCK TABLES。 使用GET_LOCK()时 如果执行了GET_LOCK()，将一直禁用multiplexing，且永不再次启用。 使用某些特定的会话/用户(session/user)变量 所有查询的query_digest部分包含了@的都将禁用multiplexing，且永不再次启用。 下面的命令是类似的情况，都会禁用multiplexing且不再次启用： SET SQL_SAFE_UPDATES=?,SQL_SELECT_LIMIT=?,MAX_JOIN_SIZE=? (mysql --safe-updates) SET FOREIGN_KEY_CHECKS SET UNIQUE_CHECKS SET AUTO_INCREMENT_INCREMENT (v 1.4.4+) SET AUTO_INCREMENT_OFFSET (v 1.4.4+) SET GROUP_CONCAT_MAX_LEN (v 1.4.4+) 但下面两个语句是例外，因为它们被硬编码为不禁用multiplexing： SELECT @@tx_isolation SELECT @@version 之所以要硬编码这两个例外，是因为很多应用程序在每个连接中都会执行这两个语句。 使用SQL_CALC_FOUND_ROWS 如果查询语句中包含了SQL_CALC_FOUND_ROWS，那么该连接上将禁用multiplexing且不再启用。 临时表 如果执行了CREATE TEMPORARY TABLE，那么该连接上将禁用multiplexing且不再启用。 使用PREPARE 如……
SE_You
2024-03-28
28 0 0 -
MySQL中间件之ProxySQL(11)：链式规则( flagIN 和 flagOUT )
1.理解链式规则 在mysql_query_rules表中，有两个特殊字段"flagIN"和"flagOUT"，它们分别用来定义规则的入口和出口，从而实现链式规则(chains of rules)。 链式规则的实现方式如下： 当入口值flagIN设置为0时，表示开始进入链式规则。如未显式指定规则的flagIN值，则默认都为0。 当语句匹配完当前规则后，将记下当前规则的flagOUT值，如果flagOUT值非空(NOT NULL)，则为该语句打上flagOUT标记。如果该规则的apply字段值不是1，则继续向下匹配。 如果语句的flagOUT标记和下一条规则的flagIN值不同，则跳过该规则，继续向下匹配。直到匹配到flagOUT=flagIN的规则，则匹配该规则。该规则是链式规则中的另一条规则。 直到某规则的apply字段设置为1，或者已经匹配完所有规则，则最后一次被评估的规则将直接生效，不再继续向下匹配。 通过下面两张图，应该很容易理解链式规则的生效方式。 必须注意，规则是按照rule_id的大小顺序进行的。且并非只有apply=1时才会应用规则，当无规则可匹配，或者某规则的flagIN和flagOUT值相同，都会应用最后一次被评估的规则。 以下几个示例，可以解释生效规则： # rule_id=3 生效 +---------+-------+--------+---------+ | rule_id | apply | flagIN | flagOUT | +---------+-------+--------+---------+ | 1 | 0 | 0 | 23 | | 2 | 0 | 23 | 23 | | 3 | 0 | 23 | NULL | +---------+-------+--------+---------+ # rule_id=2 生效 +---------+-------+--------+---------+ | rule_id | apply | flagIN | flagOUT | +---------+-------+--------+---------+ | 1 | 0 | 0 | 23 | | 2 | 0 | 23 | 23 | | 3 | 0 | 24 | NULL | +---------+-----……
SE_You
2024-03-27
19 0 0 -
MySQL中间件之ProxySQL(10)：读写分离方法论
1.不同类型的读写分离 数据库中间件最基本的功能就是实现读写分离，ProxySQL当然也支持。而且ProxySQL支持的路由规则非常灵活，不仅可以实现最简单的读写分离，还可以将读/写都分散到多个不同的组，以及实现分库sharding(分表sharding的规则比较难写，但也能实现)。 本文只描述通过规则制定的语句级读写分离，不讨论通过 ip/port, client, username, schemaname 实现的读写分离。 下面描述了ProxySQL能实现的常见读写分离类型。 1.1 最简单的读写分离 如图。 这种模式的读写分离，严格区分后端的master和slave节点，且slave节点必须设置选项read_only=1。在ProxySQL上，分两个组，一个写组HG=10，一个读组HG=20。同时在ProxySQL上开启monitor模块的read_only监控功能，让ProxySQL根据监控到的read_only值来自动调整节点放在HG=10(master会放进这个组)还是HG=20(slave会放进这个组)。 这种模式的读写分离是最简单的，只需在mysql_users表中设置用户的默认路由组为写组HG=10，并在mysql_query_rules中加上两条简单的规则(一个select for update，一个select)即可。 例如，下面实现的就是这种读写分离模式。 mysql_replication_hostgroups: +------------------+------------------+----------+ | writer_hostgroup | reader_hostgroup | comment | +------------------+------------------+----------+ | 10 | 20 | cluster1 | +------------------+------------------+----------+ mysql_servers: +--------------+----------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+----------+------+--------+--------+ | 10 | master | 3306 | ONLINE | 1 | | 20 | slave1 | 3306 | O……
SE_You
2024-03-26
26 0 0 -
MySQL中间件之ProxySQL(9)：ProxySQL的查询缓存功能
ProxySQL支持查询缓存的功能，可以将后端返回的结果集缓存在自己的内存中，在某查询的缓存条目被清理(例如过期)之前，前端再发起同样的查询语句，将直接从缓存中取数据并返回给前端。如此一来，ProxySQL处理的性能会大幅提升，也会大幅减轻后端MySQL Server的压力。 1.开启query cache功能 ProxySQL的查询缓存功能由mysql_query_rules表中的cache_ttl字段控制，该字段设置每个规则对应的缓存时长，时间单位为毫秒。 当前端发送的SQL语句命中了某规则后(严格地说，是最后应用的那条规则，因为链式规则下会操作多个规则)，如果这个规则同时还设置了"cache_ttl"字段的值，则这个SQL语句返回的结果将会被缓存一定时间，过期后将等待专门的线程(purge线程)来清理。 例如： delete from mysql_query_rules; select * from stats_mysql_query_digest_reset where 1=0; insert into mysql_query_rules(rule_id,active,apply,destination_hostgroup,match_pattern,cache_ttl) values(1,1,1,10,"^select .* test1.t1",20000); load mysql query rules to runtime; save mysql query rules to disk; select rule_id,destination_hostgroup,match_pattern,cache_ttl from mysql_query_rules; 这表示匹配上述规则(查询test1.t1表)的查询结果集将在ProxySQL上缓存20秒。 可以执行下面的语句进行测试，每个语句循环执行10次： # 在bash下执行 proc="mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e" for ((i=0;i<10;i++));do $proc "select * from test1.t1;" $proc "select * from test1.t2;" $proc "select * from test2.t1;" $proc "select * from test2.t2;" done 再去查看规则统计表： Admin> select * from stats_mysql_query_rules; +---------+------+ | rule_id | ……
SE_You
2024-03-25
20 0 0 -
MySQL中间件之ProxySQL(8)：SQL语句的重写规则
1.为什么要重写SQL语句 ProxySQL在收到前端发送来的SQL语句后，可以根据已定制的规则去匹配它，匹配到了还可以去重写这个语句，然后再路由到后端去。 什么时候需要重写SQL语句？ 对于下面这种简单的读、写分离，当然用不上重写SQL语句。 这样的读写分离，实现起来非常简单。如下： mysql_replication_hostgroups: +------------------+------------------+----------+ | writer_hostgroup | reader_hostgroup | comment | +------------------+------------------+----------+ | 10 | 20 | cluster1 | +------------------+------------------+----------+ mysql_servers: +--------------+----------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+----------+------+--------+--------+ | 10 | master | 3306 | ONLINE | 1 | | 20 | slave1 | 3306 | ONLINE | 1 | | 20 | slave2 | 3306 | ONLINE | 1 | +--------------+----------+------+--------+--------+ mysql_query_rules: +---------+-----------------------+----------------------+ | rule_id | destination_hostgroup | match_digest | +---------+-----------------------+----------------------+ | 1 | 10 | ^SELECT.*FOR UPDATE$ | | 2 | 20 | ^SELECT | +---------+-----------------------+----------------------+ 但是，复杂一点的，例如ProxySQL实现sharding功能。对db1库的select_1语句路由给hg=10的组，将db2库的select_2语句路由给hg=20的组，将db3库的select_3语句路由给hg=30的组。 ……
SE_You
2024-03-22
24 0 0 -
MySQL中间件之ProxySQL(7)：详述ProxySQL的路由规则
1.关于ProxySQL路由的简述 当ProxySQL收到前端app发送的SQL语句后，它需要将这个SQL语句(或者重写后的SQL语句)发送给后端的MySQL Server，然后收到SQL语句的MySQL Server执行查询，并将查询结果返回给ProxySQL，再由ProxySQL将结果返回给客户端(如果设置了查询缓存，则先缓存查询结果)。 ProxySQL可以实现多种方式的路由：基于ip/port、username、schema、SQL语句。其中基于SQL语句的路由是按照规则进行匹配的，匹配方式有hash高效匹配、正则匹配，还支持更复杂的链式规则匹配。 本文将简单演示基于端口、用户和schema的路由，然后再详细介绍基于SQL语句的路由规则。不过需要说明的是，本文只是入门，为后面ProxySQL的高级路由方法做铺垫。 在阅读本文之前，请确保： 已经理解ProxySQL的多层配置系统，可参考：ProxySQL的多层配置系统 会操作ProxySQL的Admin管理接口，可参考：ProxySQL的Admin管理接口 已经配置好了后端节点、mysql_users等。可参考：ProxySQL管理后端节点 如果想速成，可参考；ProxySQL初试读写分离 本文涉及到的实验环境如下： 角色 主机IP server_id 数据状态 Proxysql 192.168.100.21 null 无 Master 192.168.100.22 110 刚安装的全新MySQL实例 Slave1 192.168.100.23 120 刚安装的全新MySQL实例 Slave2 192.168.100.24 130 刚安装的全新MySQL实例 该实验环境已经在前面的文章中搭建好，本文不再赘述一大堆的内容。环境的搭建请参考前面给出的1、2、3。 2.ProxySQL基于端口的路由 我前面写了一篇通过MySQL Router实现MySQL读写分离的文章，MySQL Router实现读写分离的方式就是通过监听不同端口实现的：一个端口负责读操作，一个端口负责写操作。这样的路由逻辑非常简单，配置起来也很方便。 虽然基于端口实现读写分离配置起来非常简单，但是缺点也很明显：必须在前端ap……
SE_You
2024-03-21
73 0 0 -
MySQL中间件之ProxySQL(6)：管理后端节点
1.配置后端节点前的说明 为了让ProxySQL能够找到后端的MySQL节点，需要将后端的MySQL Server加入到ProxySQL中。ProxySQL的一切配置行为都是在修改main库中的对应的表，所以添加节点到ProxySQL中实际上也是通过修改相关表来实现的。 管理后端节点有几个过程： 将后端MySQL Server的主从复制结构配置好。 将后端MySQL Server节点加入到ProxySQL中。相关的表为mysql_servers。 监控后端节点。相关的表是全局变量表global_vairbles，相关的变量为mysql-monitor_开头的变量。 配置读、写组。相关的表为mysql_replication_hostgroups。 配置MySQL用户，用于发送SQL语句。相关的表为mysql_users。 几个注意点： (1).ProxySQL是通过监控后端节点的read_only值来自动调整节点所属组的，例如read_only=1的节点会移动到读组，read_only=0的节点会移动到写组。所以，在配置读、写组之前，需要先监控后端节点。ProxySQL也支持手动管理后端节点，这种模式不会根据read_only的值自动调整，在后面的文章中会介绍这种模式。 (2).对于传统的主从复制，默认的read_only=0，所以在第一步中，各slave节点的配置文件中需要加上read_only=1。对于组复制、Galera，因为会自动强制设置非写节点的read_only=1，所以无需额外配置该属性。 (3).ProxySQL支持传统主从复制结构(即异步、半同步、gtid复制)的后端，读、写组相关的表是mysql_replication_hostgroups。还支持MySQL组复制结构的后端，相关的表是mysql_group_replication_hostgroups，还支持Galera(如percona XtraDB cluster)结构的后端，不过ProxySQL是通过scheduler调度proxysql_galera_checker.sh脚本来支持Galera的，而且目前还没有mysql_galera_hostgroups(ProxySQL 2.0才新增该表)。 本文暂时只解释mysql_servers和mysql_replication_hostgroups，组复制相关的表在在后面介……
SE_You
2024-03-20
66 0 0
包含标签：ProxySQL 的文章
