proxysql - MySQL读写分离-架构集群-Liunx(abc.htmltoo.com)

proxysql - MySQL读写分离主从架构 负载均衡

ihunter 2020-2-27 33

[百度收录]

[搜狗收录]

[360收录]

https://github.com/sysown/proxysql/releases

ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。

ProxySQL官网文档也是很齐全，MySQL5.7结合ProxySQL 案例讲述的也很清晰，现在先简单介绍下其特色功能点：

查询缓存

查询路由

故障转移

在线配置立刻生效无需重启

应用层代理

跨平台

高级拓展支持

防火墙

通过上述，我们可以看到ProxySQL可以做许多事情，已经不仅仅是纯粹的MySQL读写分离，其实我们通过后面所述结合业务发散，ProxySQL还可以支持以下高级功能：

读写分离

数据库集群、分片

分库分表

主从切换

SQL审计

连接池多路复用

负载均衡

查询重写

流量镜像

自动重连

自动下线

高可用

.........

本文所用环境：

系统均为CentOS7，并且关闭防火墙和selinux

ProxySQL版本：proxysql-2.0.10-1-centos7.x86_64

Mysql版本：mariadb

ProxySQL主机IP：192.168.100.2

Mysql主库IP：192.168.100.3

Mysql从库IP：192.168.100.4

前提条件： Mysql主从已经配置好了同步

# 安装ProxySQL：

wget https://github.com/sysown/proxysql/releases/download/v2.0.10/proxysql-2.0.10-1-centos7.x86_64.rpm

yum install -y proxysql-2.0.10-1-centos7.x86_64.rpm

# 安装生成的文件：

[root@ProxySQL ~]#rpm -ql proxysql

/etc/init.d/proxysql #启动脚本

/etc/proxysql.cnf #配置文件，仅在第一次(/var/lib/proxysql/proxysql.db文件不存在)启动时有效

#启动后可以在proxysql管理端中通过修改数据库的方式修改配置并生效(官方推荐方式。)

/usr/bin/proxysql #主程序文件

/usr/share/proxysql/tools/proxysql_galera_checker.sh

/usr/share/proxysql/tools/proxysql_galera_writer.pl

# 启动proxysql：

/etc/init.d/proxysql start

#proxysql客户端监听在6033端口上，管理端监听6032端口

ss -tanl # 查看端口

# 连接proxysql管理端进行配置：

mysql -uadmin -padmin -h127.0.0.1 -P6032 #默认的管理端账号密码都是admin，进去之后可以修改

# 开启WEB统计

update global_variables set variable_value='true' where variable_name='admin-web_enabled';

LOAD ADMIN VARIABLES TO RUNTIME;

SAVE ADMIN VARIABLES TO DISK;

select * from global_variables where variable_name LIKE 'admin-web%' or variable_name LIKE 'admin-stats%';

find / -name "proxysql.db" # /var/lib/proxysql/proxysql.db

访问ip:6080/stats?metric=system 并使用stats:stats登录即可查看一些统计信息。

# 添加后端的mysql主机：

#使用insert语句添加主机到mysql_servers表中，其中：hostgroup_id 1 表示写组，2表示读组。

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.100.3',3306,1,'Write Group');

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'192.168.100.4',3306,1,'Read Group');

select * from mysql_servers; # 查看

# 添加可以访问后端主机的账号：

GRANT ALL ON *.* TO 'proxysql'@'10.42.%' IDENTIFIED BY 'wdqdmm@p';

#在后端mysql中添加可以增删改查的账号

insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','wdqdmm@p',1,1);

#在proxysql主机的mysql_users表中添加刚才创建的账号，proxysql客户端需要使用这个账号来访问数据库。

#default_hostgroup默认组设置为写组，也就是1

#当读写分离的路由规则不符合时，会访问默认组的数据库

select * from mysql_users\G; # 查看

# 添加健康监测的账号：

GRANT SELECT ON *.* TO 'dongquan11'@'10.42.%' IDENTIFIED BY 'wdqdmm@0';

#在后端主机中添加可以访问数据库的账号,SELECT权限即可

set mysql-monitor_username='dongquan11'

set mysql-monitor_password='wdqdmm@0'

#在proxysql管理端中修改变量设置健康检测的账号

# 添加读写分离的路由规则：

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);

#将select语句全部路由至hostgroup_id=2的组(也就是读组)

#但是select * from tb for update这样的语句是修改数据的，所以需要单独定义，将它路由至hostgroup_id=1的组(也就是写组)

#其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users表中的default_hostgroup)

MySQL [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;

# 将刚才我们修改的数据加载至RUNTIME中(参考ProxySQL的多层配置结构)：

load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;
#load进runtime，是配置生效
save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;
#save到磁盘(/var/lib/proxysql/proxysql.db)中，永久保存配置

# 测试读写分离

mysql -uproxysql -p123456 -h127.0.0.1 -P6033

show databases;

尝试修改数据库和查询：

create database bigboss;

create database weijinyun;

select user,host from mysql.user;

验证读写分离是否成功：

#proxysql有个类似审计的功能，可以查看各类SQL的执行情况。在proxysql管理端执行：

select * from stats_mysql_query_digest;

#从里面的hostgroup和digest_text值来看，所有的写操作都被路由至1组，读操作都被路由至2组，

#其中1组为写组，2组为读组！

#读写分离成功！！！

# 库、表说明

mysql -uadmin -padmin -h127.0.0.1 -P6032

show databases;

proxySQL默认有五个数据库，对每个库的功能介绍如下：

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

* disk库：是持久化到硬盘的配置库，对应/var/lib/proxysql/proxysql.db文件，也就是sqlite的数据文件。

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

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

# main库

show tables from main;

常用的几个表介绍如下：

 global_variables

设置变量，包括监听的端口、管理账号等。

 mysql_replication_hostgroups：

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

 mysql_servers

设置后端MySQL的表

 mysql_users

配置后端数据库的程序账号和监控账号。

 scheduler

调度器是一个类似于cron的实现，集成在ProxySQL中，具有毫秒的粒度。通过脚本检测来设置ProxySQL。

# stats库

show tables from stats;

常用的几个表介绍如下：

 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

路由命中次数统计。

# monitor库

show tables from monitor;

常用的几个表介绍如下：

 mysql_server_connect_log

连接到所有MySQL服务器以检查它们是否可用，该表用来存放检测连接的日志。

 mysql_server_ping_log

使用mysql_ping API ping后端MySQL服务器，检查它们是否可用，该表用来存放ping的日志。

 mysql_server_replication_lag_log

后端MySQL服务主从延迟的检测。

========================

# Add backends

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21891);

...

# 后端健康检查

SHOW DATABASES;

SHOW TABLES FROM monitor;

SELECT * FROM monitor.mysql_server_connect_log LIMIT 10;

SELECT * FROM monitor.mysql_server_ping_log LIMIT 10;

LOAD MYSQL SERVERS TO RUNTIME;

SELECT * FROM mysql_servers;

# MySQL复制主机组

SELECT * FROM monitor.mysql_server_read_only_log LIMIT 10;

SELECT * FROM mysql_replication_hostgroups;

SHOW CREATE TABLE mysql_replication_hostgroups\G;

INSERT INTO mysql_replication_hostgroups VALUES (1,2);

SELECT * FROM mysql_servers;

LOAD MYSQL SERVERS TO RUNTIME;

SELECT * FROM monitor.mysql_server_read_only_log LIMIT 10;

SELECT * FROM mysql_servers;

SAVE MYSQL SERVERS TO DISK;

SAVE MYSQL VARIABLES TO DISK;

# MySQL Users

SHOW CREATE TABLE mysql_users\G

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('msandbox','msandbox',1);

Query OK, 1 row affected (0.00 sec)

SELECT * FROM mysql_users;

LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL USERS TO DISK;

# ProxySQL统计

SHOW SCHEMAS;

SHOW TABLES FROM stats;

SELECT * FROM stats.stats_mysql_connection_pool;

SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;

SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;

SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

# MySQL Query Rules

SHOW CREATE TABLE mysql_query_rules\G

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'msandbox','^SELECT c FROM sbtest1 WHERE id=\?$',2,1);

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'msandbox','DISTINCT c FROM sbtest1',2,1);

SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='msandbox' ORDER BY rule_id;

SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;

LOAD MYSQL QUERY RULES TO RUNTIME;

SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

SELECT hostgroup hg, SUM(sum_time), SUM(count_star) FROM stats_mysql_query_digest GROUP BY hostgroup;

# Query Caching

UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=2;

LOAD MYSQL QUERY RULES TO RUNTIME;

SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; -- we reset the counters

SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

# Query Rewrite

INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (30,1,'msandbox','DISTINCT(.*)ORDER BY c','DISTINCT\1',1);

SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;

LOAD MYSQL QUERY RULES TO RUNTIME;

SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;

SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;

UPDATE mysql_query_rules SET apply=0 WHERE rule_id=20;

SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;

LOAD MYSQL QUERY RULES TO RUNTIME;

SELECT * FROM stats.stats_mysql_query_rules;

SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;

SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

LOAD MYSQL QUERY RULES TO RUNTIME;

SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules;

SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

========================

https://blog.csdn.net/CL_YD/article/details/80198766

https://my.oschina.net/cart/blog/2907232

https://github.com/sysown/proxysql/blob/v1.2.2/doc/configuration_howto.md

http://www.fordba.com/mysql_sharding_with_proxysql.html

https://www.cnblogs.com/f-ck-need-u/p/9300829.html

https://www.linuxidc.com/Linux/2019-05/158644.htm

追梦赤子心：domsn.com

http://www.htmltoo.com/

签名：这个人很懒，什么也没有留下！