K8s部署proxysql代理mgr

1.创建镜像

暂时没有发现proxysql的docker官方镜像，所以自己用rpm和centos7镜像自己打包一个首先去,https://github.com/sysown/proxysql/releases/ 下载想要版本的rpm包,我这里下载到的是 proxysql-1.4.10-1-centos7.x86_64.rpm

Dockerfile 如下

FROM centos:7

COPY  ./proxysql-1.4.10-1-centos7.x86_64.rpm /

RUN yum install -y /proxysql-1.4.10-1-centos7.x86_64.rpm && yum clean all && mkdir /var/lib/proxysql

用下面的命令build

docker build -t proxysql:1.4.10 .

上传到自己的registry

docker tag proxysql:1.4.10 10.168.136.193:5000/proxysql:1.4.10
docker push 10.168.136.193:5000/proxysql:1.4.10

配置文件

一般情况下，proxysql的配置是用sqllite持久化到磁盘中，这种方式不适合在容器中，因此需要将proxysql做成没有状态的，我们将所有配置都写入 proxysql.cnf ，然后docker启动的时候强制加载镜像，不登录proxysql进行配置、写盘等。配置通过configmap的形式挂载到pod中。

configmap的配置如下。

apiVersion: v1
data:
  proxysql.cnf: |
    datadir="/var/lib/proxysql"
    admin_variables=
    {
        admin_credentials="admin:admin"
        mysql_ifaces="0.0.0.0:6032"
    }
    mysql_variables=
    {
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
        interfaces="0.0.0.0:6033;0.0.0.0:6034"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"
        connect_timeout_server=3000
        monitor_username="proxysql"
        monitor_password="proxysql"
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
        monitor_read_only_interval=1500
        monitor_read_only_timeout=500
        ping_interval_server_msec=120000
        ping_timeout_server=500
        commands_stats=true
        sessions_sort=true
        connect_retries_on_failure=10
    }
    mysql_servers =
    (
        { address="mysql-0.mysql" , port=3306 , hostgroup=2 },
        { address="mysql-1.mysql" , port=3306 , hostgroup=2 },
        { address="mysql-2.mysql" , port=3306 , hostgroup=2 }
    )
    mysql_users:
    (
        { username = "proxysql" , password = "proxysql" , default_hostgroup = 2 , active = 1,max_connections=1000 }
    )
    mysql_query_rules:
    (
        {
            rule_id=1
            active=1
            proxy_port=6033
            destination_hostgroup=2
            apply=1
        },
        {
            rule_id=2
            active=1
            proxy_port=6034
            destination_hostgroup=3
            apply=1
        }
    )
    scheduler=
    (
    )
    mysql_group_replication_hostgroups=
    (
        {
            writer_hostgroup=2
            reader_hostgroup=3
            backup_writer_hostgroup=4
            offline_hostgroup=5
            active=1
            max_writers=1
            writer_is_also_reader=0
            max_transactions_behind=100
            comment="test repl 1"
           }
     )
kind: ConfigMap
metadata:
  name: proxysql-config
  namespace: test

其中
interfaces=”0.0.0.0:6033;0.0.0.0:6034”是监听了2个端口。
mysql_servers ，mysql_users 配置了服务器的地址和用户名密码。
mysql_group_replication_hostgroups 里定义了mgr的配置，一般不用修改，除非你需要将master也设置为读节点，则修改 writer_is_also_reader

配置deployment

apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: proxysql
  name: proxysql
  namespace: test
spec:
  replicas: 1
  revisionHistoryLimit: 10
  selector:
    matchLabels:
      app: proxysql
  strategy:
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 1
    type: RollingUpdate
  template:
    metadata:
      labels:
        app: proxysql
    spec:
      containers:
      - env:
        - name: TZ
          value: Asia/Shanghai
        image: 10.168.136.193:5000/proxysql:1.4.10
        name: proxysql
        command:
        - sh
        - -c
        - "proxysql -c /etc/proxysql/proxysql.cnf -D /var/lib/proxysql  -f --initial"
        ports:
        - name: write-port
          containerPort: 6033
          protocol: TCP
        - name: read-port
          containerPort: 6034
          protocol: TCP
        volumeMounts:
        - mountPath: /etc/proxysql/
          name: config-volume
          readOnly: true
      volumes:
      - configMap:
          defaultMode: 420
          name: proxysql-config
        name: config-volume

除了 “proxysql -c /etc/proxysql/proxysql.cnf -D /var/lib/proxysql -f –initial” 这一行定义了启动的命令行,设置从配置文件加载配置，没有什么特别的地方。

mysql配置

创建proxysql用户名密码

CREATE USER proxysql@'172.30.%.%' IDENTIFIED BY 'proxysql';
grant all privileges on *.* to 'proxysql'@'172.30.%.%';

Proxy 探测 mgr，是需要额外的配置的， 在mysql中执行一下脚本

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

DELIMITER ;

注意事项

在本文的配置中，没有单独使用monitor的用户，如果觉得有安全问题，请修改下面的配置。

monitor_username="proxysql"
monitor_password="proxysql"

然后在mysql配置monitor的用户的权限

CREATE USER monitor@'%' IDENTIFIED BY 'monitor';
grant  SELECT on sys.* to 'monitor'@'%' ；

如果你是mysql 8.0，由于从网上大部分攻略链接到 https://gist.github.com/lefred/77ddbde301c72535381ae7af9f968322 来下载最后配置mgr视图的脚本。

由于mysql 8.0中 performance_schema.replication_group_member_stats 表的内容有整个集群的全部节点。因此创建的viewselect会出错。

测试方法

select * from gr_member_routing_candidate_status;

，如果报错，则执行下面的语句，替换相关的函数是view定义

（该脚本可以在后面的留言中看到）

USE sys;

DELIMITER $$

CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$

-- new function, contribution from Bruce DeFrang
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 NOT IN ('ONLINE', 'RECOVERING')) >=
    ((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 USING(member_id) where member_id=my_id());
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,
Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' 
from performance_schema.replication_group_member_stats where member_id=my_id();$$

DELIMITER ;