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 proxysql2.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 initial2.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 建函数和视图