#安装依赖包
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

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"
}
)

systemctlstop proxysql

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

/etc/init.d/proxysql initial

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 -uadmin -padmin -P6032 -h127.0.0.1--default-auth=mysql_native_password 

#  业务帐户
mysql -uroot -proot 

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

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

FLUSHPRIVILEGES;

#组号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');

业务用户，默认组为读写组 ，组号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');

#在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;

-- 持久化（在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;

节点信息查看：
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";

  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;

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;

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'@'%';

--查看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;

启动 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 

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