k8s部署ProxySQL读写分离

前提已经通过k8s部署了一主二从的mysql集群
[https://www.cnblogs.com/maroon6/p/18975476]

node01	192.168.31.132	mysql主节点
node02	192.168.31.133	mysql从节点
node03	192.168.31.134	mysql从节点

编写配置文件（文件名 proxysql.yaml）: 采用nfs作为ProxySQL本地数据存储

# PV配置
apiVersion: v1
kind: PersistentVolume
metadata:
  name: deploy-proxysql-dada-pv
  namespace: deploy-test
spec:
  capacity:
    storage: 1Gi
  accessModes:
    - ReadWriteMany
  nfs:
    server: 192.168.31.132
    path: /data/nfs/proxysql-dada
  storageClassName: "nfs"

# PVC配置
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: deploy-proxysql-dada-pvc
  namespace: deploy-test
spec:
  accessModes:
    - ReadWriteMany
  storageClassName: "nfs"
  resources:
    requests:
      storage: 1Gi
  volumeName: deploy-proxysql-dada-pv

# ConfigMap配置
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: proxysqlcm
  namespace: deploy-test
data:
  proxysql.cnf: |
    datadir="/var/lib/proxysql"
    admin_variables={
      admin_credentials="admin:admin;radmin:radmin"
      mysql_ifaces="0.0.0.0:6032"
      restapi_enabled=true
      restapi_port=6070
      prometheus_memory_metrics_interval=30
    }
    
    mysql_variables={
      threads=4
      max_connections=5000
      default_query_delay=0
      default_query_timeout=36000000
      have_compress=true
      poll_timeout=2000
      interfaces="0.0.0.0:6033"
      default_schema="information_schema"
      stacksize=1048576
      server_version="8.0.36"
      connect_timeout_server=3000
      monitor_username="monitor"
      monitor_password="monitor"
      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
    }

# Deployment配置
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: proxysql-deployment
  namespace: deploy-test
spec:
  replicas: 1
  selector:
    matchLabels:
      app: proxysql
  template:
    metadata:
      labels:
        app: proxysql
    spec:
      nodeSelector:
        kubernetes.io/hostname: node01
      containers:
        - name: proxysql-container
          image: proxysql/proxysql:2.0.7
          ports:
            - containerPort: 6033
            - containerPort: 6032
            - containerPort: 6070
          volumeMounts:
            - name: proxysql-data
              mountPath: /var/lib/proxysql
            - name: proxysql-config
              mountPath: /etc/proxysql.cnf
              subPath: proxysql.cnf
      volumes:
        - name: proxysql-config
          configMap:
            name: proxysqlcm
        - name: proxysql-data
          persistentVolumeClaim:
            claimName: deploy-proxysql-dada-pvc

# Service配置
---
apiVersion: v1
kind: Service
metadata:
  name: proxysql-service
  namespace: deploy-test
spec:
  type: NodePort
  selector:
    app: proxysql
  ports:
    - port: 6033
      targetPort: 6033
      nodePort: 32033
      name: external
    - port: 6032
      targetPort: 6032
      nodePort: 32032
      name: internal
    - port: 6070
      targetPort: 6070
      nodePort: 32070
      name: spare

k8s 加载配置文件

kubectl apply -f  proxysql.yaml

在装有mysql客户端的节点连接proxysql

[root@node01 ~]# mysql -h 192.168.31.132  -P32032 -uradmin -pradmin --prompt "ProxySQL RAdmin>"

创建读写组：

#注意：ProxySQL会根据serverd的/etc/my.cnf配置文件中额的read_only的取值将服务器进行分组。read_only=0的server，master被分到编号为1的写组，read_only=1的server，slave则分到编号为0的读组，read_only默认为0
insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');

添加后端数据库实例

INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES 
(1,'192.168.31.132',30306,100),(0,'192.168.31.133',30308,50),(0,'192.168.31.134',30308,50);
load mysql servers to runtime;
save mysql servers to disk;

查看实时生效的后端数据库

配置 ProxySQL 所需账户:
在 (192.168.31.132) 的MySQL主节点 上创建 ProxySQL 的监控账户和对外访问账户

 #监控账户
 mysql>create user 'monitor'@'%' identified with mysql_native_password by 'monitor@123';
 grant  usage,replication client on  *.* to 'monitor'@'%';
 
 #proxysql 的对外访问账户
 mysql>create user 'lay'@'%' identified with mysql_native_password by 'lay@123';
 grant all privileges on *.* to 'lay'@'%'；

proxysql 添加用户，需要指定一个默认的主机组，当客户端连接没指定后端数据的时候默认会连到这个主机组，-D 指定数据库。并应用到实时配置并保存到磁盘。

insert into mysql_users(username,password,default_hostgroup) values('lay','lay@123',1);
load mysql users to runtime;
save mysql users to disk;

peoxysql 配置监控用户，并查看实时配置

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor@123';
load mysql variables to runtime;
save mysql variables to disk;
SELECT * FROM runtime_global_variables WHERE variable_name IN ('mysql-monitor_username', 'mysql-monitor_password');

对connect指标的监控

对心跳信息的监控：

查看read_only日志监控

添加读写分离规则(mysql_query_rules)

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES 
(1, 1, '^SELECT.*FOR UPDATE$', 1, 1),
(2, 1, '^SELECT', 0, 1),
(3, 1, '^(INSERT|UPDATE|DELETE) ', 1, 1);

 load mysql query rules to runtime;
 save mysql query rules to disk;

测试读写分离

mysql -ulay -play@123 -h 192.168.31.132 -P 32033 -e "select @@server_id";
mysql -ulay -play@123 -h 192.168.31.132 -P 32033 -e "begin;select @@server_id commit";