2.Proxysql 安装配置(mgr)

原创

Asher_Hu 2022-05-04 22:22:48 ©著作权

文章标签 cqdba mysql sql 加载 文章分类 SQL Server 数据库

©著作权归作者所有：来自51CTO博客作者Asher_Hu的原创作品，请联系作者获取转载授权，否则将追究法律责任

1. 环境准备

1.MGR 三节点提前安装好

2.Proxy 环境准备

	ProxySQL-1	ProxySQL-2	ProxySQL-3
MySQL版本	8.0.21	8.0.21	8.0.21
Proxy版本	1.4.9-1	1.4.9-1	1.4.9-1
操作系统	redhat 7.6	redhat 7.6	redhat 7.6
服务器IP	10.85.10.51	10.85.10.52	10.85.10.53
端口	管理端口-6032	管理端口-6032	管理端口-6032
端口	服务端口-6033	服务端口-6033	服务端口-6033
服务器配置	2c4g	2c4g	2c4g

2.Proxy 安装

2.1软件安装

#安装依赖包
yum install perl-DBD*    
yum installperl-DBI*


cd/soft
rpm -ivh proxysql-1.4.9-1-centos7.x86_64.rpm

systemctlstatus proxysql
systemctlenable proxysql
systemctlstart proxysql
ps-ef|grep proxysql

2.2 Proxy 集群配置

vi /etc/proxysql.cnf
admin_variables=
{
admin_credentials="admin:admin;cluster01:cluster01"                #配置用于实例间通讯的账号

#mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"                                                                #全网开放登录
#refresh_interval=2000
# debug=true

#下面的内容复制到admin_variables中
cluster_username="cluster01"#集群用户名称  和上面的相同
cluster_password="cluster01"#集群用户这密码  和上面的相同
cluster_check_interval_ms=200
cluster_check_status_frequency=100
cluster_mysql_query_rules_save_to_disk=true
cluster_mysql_servers_save_to_disk=true
cluster_mysql_users_save_to_disk=true
cluster_proxysql_servers_save_to_disk=true
cluster_mysql_query_rules_diffs_before_sync=3
cluster_mysql_servers_diffs_before_sync=3
cluster_mysql_users_diffs_before_sync=3
cluster_proxysql_servers_diffs_before_sync=3
}

proxysql_servers=
(
{
hostname="10.85.10.51"
port=6032
weight=0
comment="primary"                     
},
{
hostname="10.85.10.52"
port=6032
weight=0
comment="secondary"
}
,
{
hostname="10.85.10.53"
port=6032
weight=0
comment="secondary"
}
)

将配置文件复制到其它节点

2.3 所有节点重新初始化proxysql

systemctlstop proxysql

cd       /var/lib/proxysql/
rm -rf  /var/lib/proxysql/*

/etc/init.d/proxysql initial

2.4 观察集群状况：

mysql -uadmin-padmin-h127.0.0.1 -P6032

select* from proxysql_servers;
select* from runtime_proxysql_servers;
select* from stats_proxysql_servers_metrics;

systemctlstop proxysql
sleep10
systemctlstart proxysql

注意：

如果是mysql 8.0 客户端登录需要加上--default-auth=mysql_native_password否则会报ERROR 1045错误

mysql -uadmin -padmin -P6032 -h127.0.0.1--default-auth=mysql_native_password

3. 配置读写分离

3.1 MYSQL 主库创建用户

#  业务帐户
mysql -uroot -proot 

CREATE USER 'appuser'@'%' IDENTIFIED BY 'appuser'; 
GRANTALL ON * . * TO 'itpux'@'%';

# 监控帐户 
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';   
GRANTUSAGE ON *.* TO 'proxy'@'%';

FLUSHPRIVILEGES;

3.2 在任意一台proxysql 加入节点信息

#组号100 定义为读写组，200定义为只读组
 mysql -uadmin -padmin -h127.0.0.1 -P6032 --default-auth=mysql_native_password

insert intomysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)
values(100,'10.85.10.51',3306,1,1000,10,'gwpt-mysql-proxy');

insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)
values(200,'10.85.10.51',3306,1,1000,10,'gwpt-mysql-proxy');

insert intomysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)
values(200,'10.85.10.52',3306,1,1000,10,'gwpt-mysql-proxy');

insert intomysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)
values(200,'10.85.10.53',3306,1,1000,10,'gwpt-mysql-proxy');

3.3 加入监控用户信息：

业务用户，默认组为读写组 ，组号100 
insert intomysql_users(username,password,active,default_hostgroup,transaction_persistent)
values('appuser','appuser',1,100,1);                      

监控用户
set mysql-monitor_username='monitor';               
setmysql-monitor_password='monitor';
select variable_name,variable_valuefrom global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');

3.4 读写分离路由规则定义

#在ProxySQL中执行
-- 发送到主库，默认组号为100 自己定义 
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);

-- 发送到从库，默认组号为200 自己定义 
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',200,1);

-- 加载（在ProxySQL中执行）
save mysql query rules to disk;
load mysql query rules to runtime;

3.5 加载配置和变量

-- 持久化（在ProxySQL中执行）
save mysql servers to disk;
save mysql users to disk;
save mysql variables to disk;
save mysql query rules to disk;

-- 加载到线上（在ProxySQL中执行）
load mysql servers to runtime;
load mysql users to runtime;
load mysql variables to runtime;
load mysql query rules to runtime;

3.6 观察节点间的信息，并测试

节点信息查看：
select* from global_variables;

select* from mysql_servers;
select* from mysql_users;
select* from mysql_query_rules;

测试：
mysql   -uitpux -pitpux123 -h 10.85.10.51 -P 6033-e "select @@hostname";
mysql   -uitpux -pitpux123 -h 10.85.10.52 -P 6033-e "select @@hostname";
mysql  -uitpux -pitpux123 -h 10.85.10.53 -P 6033 -e "select @@hostname";

在proxysql 中查看执行的SQL语句

mysql -uadmin-padmin -P6032 -h127.0.0.1   --default-auth=mysql_native_password
select
hostgroup,schemaname,username,substr(digest_text,120,-120),count_star
fromstats_mysql_query_digest ORDER BY sum_time DESC;

在proxysql中用下面的语句先清空stats_mysql_query_digest的数据：
 SELECT1 FROM stats_mysql_query_digest_reset LIMIT 1;

4.配置MGR

4.1 配置MGR信息配置（在每个ProxySQL中执行）

所谓组的配置，即定义读组、写组等，可以使用如下两个表来定义读写组：

mysql_replication_hostgroups：该表用于传统的master/slave的异步复制或者半同步复制的配置。

mysql_group_replication_hostgroups:该表用于MySQL Group Replication、InnoDB Cluster or Galera/Percona XtraDB Cluster的配置

insert into mysql_group_replication_hostgroups (writer_hostgroup,reader_hostgroup,backup_writer_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) 
values (100,200,300,400,1,1,1,100);
select * from mysql_group_replication_hostgroups;

#mysql_group_replication_hostgroups字段说明：
write_hostgroup：默认情况下会将所有流量发送到这个组。具有read_only=0的节点也将分配到这个组；
reader_hostgroup：读取的流量应该发送到该组，只读节点(read_only=1)会被分配到该组；
backup_writer_hostgroup：如果集群有多个写节点(read_only=0)且超过了max_writers规定数量，则会把多出来的写节点放到备用写组里面；
offline_hostgroup：当ProxySQL监视到某个节点不正常时，会被放入该组；
active：是否启用主机组，当启用时，ProxySQL将监视主机在各族之间移动；
max_writers：最大写节点的数量，超过该值的节点应该被放入backup_write_hostgroup；
writer_is_also_reader：一个节点既做写节点也做读节点，如果该值为2，则backup_writer_hostgroup的节点做读写点，但是writer_hostgroup不会做读节点；
————————————————

加载（在每个ProxySQL中执行）
save mysql servers to disk;
load mysql servers to runtime;

4.2 导入sys视图（在MySQL主库中执行）

addition_to_sys.sql 可以从http://lefred.be/content/mysql-group-replication-native-support-in-proxysql 下载;

在8.0中使用，提示子查询查询超过一行。是因为mysql版本的问题。你可以在replication_group_member_stats 表添加筛选当前主机的条件。

内容如下：

source /software/addition_to_sys.sql

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 rgms USING(member_id) WHERE rgms.MEMBER_ID=@@SERVER_UUID);
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 rgms 
where rgms.MEMBER_ID=(select gv.VARIABLE_VALUE 
 from `performance_schema`.global_variables gv where gv.VARIABLE_NAME='server_uuid');$$

DELIMITER ;

 授权sys库（在MySQL主库中执行） 
GRANT  SELECT on sys.* to 'monitor'@'%';

4.4 状态查询

--查看MGR成员基本信息（在MySQL中执行），监测节点的健康与落后情况
select * from sys.gr_member_routing_candidate_status;

--查看MGR各节点状态（在ProxySQL中执行）
select hostgroup_id, hostname, status from runtime_mysql_servers;

--查看MGR各节点日志信息（在ProxySQL中执行）
select * from mysql_server_group_replication_log order by time_start_us desc limit 5;

5.日常维护

5.1 常用SQL

启动 ProxySQL：
service proxysql start

停止 ProxySQL：
service proxysql stop

连接 ProxySQL 管理端口：
mysql -uadmin -padmin -h127.0.0.1 -P6032;

连接 ProxySQL 服务端口：
mysql -uadmin_user -padmin_user -h127.0.0.1 -P6033;

 sql 执行统计：
select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest;

注意：
如果是mysql 8.0 客户端登录需要加上 --default-auth=mysql_native_password 否则会报ERROR 1045 错误，如：
mysql -uadmin -padmin -P6032 -h127.0.0.1 --default-auth=mysql_native_password

5.2 ProxySQL日志

ProxySQL日志位于/var/lib/proxysql下面，当出现OFFLINE_HARD 的时候我们就需要检查日志，如下

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host;
+-----------+----------------+--------------+----------+-------------+---------+------------+
| hostgroup | srv_host       | status       | ConnUsed | MaxConnUsed | Queries | Latency_us |
+-----------+----------------+--------------+----------+-------------+---------+------------+
| 4         | 10.85.10.51 | OFFLINE | 0        | 0           | 0       | 1362       |
| 4         | 10.85.10.52 | OFFLINE | 0        | 0           | 0       | 1608       |
| 4         | 10.85.10.53 | OFFLINE | 0        | 0           | 0       | 969        |
| 4         | 10.85.10.53 | OFFLINE_HARD | 0        | 0           | 0       | 969        |
+-----------+----------------+--------------+----------+-------------+---------+------------+
针对这个问题，我们要做的首先是检查proxysql.log日志，该日志位于/var/lib/proxysql下面。

[root@huyidb01 proxysql]# grep -i OFFLINE proxysql.log | grep because | cut -b 124- | sort | uniq -c
      1 setting host 10.85.10.51:3306 offline because: SELECT command denied to user 'proxy'@'huyidb01' for table 'gr_member_routing_candidate_status'
       4 setting host 10.85.10.51:3306 offline because: viable_candidate=NO
       4 setting host 10.85.10.52:3306 offline because: viable_candidate=NO
       4 setting host 10.85.10.53:3306 offline because: viable_candidate=NO

该问题需要我们修改mgr_addition_to_sys.sql 建函数和视图