本文章没有找到对应的语言版本
K8S
  • 1.创建configmap配置文件 proxysql-cnf.yaml
apiVersion: v1
kind: Service
metadata:
  labels:
    app: proxysql
  name: proxysqlcluster
  namespace: cloud2
spec:
  clusterIP: None
  ports:
  - name: proxysql-admin
    port: 6032
    protocol: TCP
    targetPort: 6032
  selector:
    app: proxysql
  sessionAffinity: None
  type: ClusterIP

---

apiVersion: v1
kind: ConfigMap
metadata:
  name: proxysql-cnf
  namespace: cloud2
data:
  proxysql.cnf: |
    datadir="/var/lib/proxysql"

    # 配置管理账号，即 6032 端口登陆的管理员账号密码。默认账号 admin 无法远程登陆，不适用于容器环境
    admin_variables=
    {
        admin_credentials="admin:admin;proxysql-admin:Aa123456"
        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"
        default_schema="information_schema"
        stacksize=1048576
        #mysql数据库版本
        server_version="8.0.30"
        connect_timeout_server=3000
        #mysql中创建并授权的监控用户密码
        monitor_username="proxysql_monitor"
        monitor_password="Aa.123456"
        monitor_history=600000
        monitor_connect_interval=2000
        monitor_ping_interval=2000
        monitor_read_only_interval=2000
        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地址, hostgroup_id=4设置为离线组,会自动探测节点角色进行分组
    mysql_servers =
    (
        { address="192.168.7.52" , port=3306 , hostgroup_id=4 , max_connections=2000 },
        { address="192.168.7.53" , port=3306 , hostgroup_id=4 , max_connections=2000 },
        { address="192.168.7.54" , port=3306 , hostgroup_id=4 , max_connections=2000 }
    )

    # 定义了MySQL连接的用户，这里需要在数据库中已经存在的用户以及密码
    mysql_users =
    (
        { username = "test_w", password = "Aa.123456", default_hostgroup = 1, transaction_persistent = 0, active = 1 },
        { username = "test_r", password = "Aa.123456", default_hostgroup = 3, transaction_persistent = 0, active = 1 }
    )

    # 定义mgr组
    mysql_group_replication_hostgroups =
    (
        {
            writer_hostgroup=1
            backup_writer_hostgroup=2
            reader_hostgroup=3
            offline_hostgroup=4
            active=1
            max_writers=1
            writer_is_also_reader=0
            max_transactions_behind=10800000
        }
    )

    #定义proxysql集群
    proxysql_servers =
    (
        { hostname = "proxysql-0.proxysqlcluster", port = 6032, weight = 1 },
        { hostname = "proxysql-1.proxysqlcluster", port = 6032, weight = 1 },
        { hostname = "proxysql-2.proxysqlcluster", port = 6032, weight = 1 }
    )
  • 2.创建StatefulSet文件 proxysql-statefulset.yaml
apiVersion: v1
kind: Service
metadata:
  annotations:
  labels:
    app: proxysql
  name: proxysql
  namespace: cloud2
spec:
  ports:
  - name: proxysql-mysql
    port: 6033
    protocol: TCP
    targetPort: 6033
    nodePort: 30633
  - name: proxysql-admin
    nodePort: 30632
    port: 6032
    protocol: TCP
    targetPort: 6032
  selector:
    app: proxysql
  type: NodePort
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: proxysql
  labels:
    app: proxysql
  namespace: cloud2
spec:
  replicas: 3
  serviceName: proxysqlcluster
  selector:
    matchLabels:
      app: proxysql
  updateStrategy:
    type: RollingUpdate
  template:
    metadata:
      labels:
        app: proxysql
    spec:
      restartPolicy: Always
      nodeSelector:
        feature: app
      containers:
      - image: ccr.ccs.tencentyun.com/zoehuawang/proxysql:2.5.1
        imagePullPolicy: IfNotPresent
        name: proxysql
        volumeMounts:
        - name: proxysql-cnf
          mountPath: /etc/proxysql.cnf
          subPath: proxysql.cnf
        ports:
        - containerPort: 6033
          name: proxysql-mysql
        - containerPort: 6032
          name: proxysql-admin
      volumes:
      - name: proxysql-cnf
        configMap:
          name: proxysql-cnf
  • 3.在MGR模式MySQL主库上创建用户
CREATE USER proxysql_monitor@'%' IDENTIFIED BY 'Aa.123456';
CREATE USER test_w@'%' IDENTIFIED BY 'Aa.123456';
CREATE USER test_r@'%' IDENTIFIED BY 'Aa.123456';

GRANT SELECT, USAGE, REPLICATION CLIENT ON *.* TO proxysql_monitor@'%';
GRANT SELECT ON sys.* TO proxysql_monitor@'%';
GRANT SELECT ON performance_schema.* TO proxysql_monitor@'%';
GRANT SELECT ON *.* TO test_r@'%';
GRANT ALL PRIVILEGES ON *.* TO test_w@'%';

FLUSH PRIVILEGES;
  • 4.在MGR模式MySQL主库上创建视图
use sys;
drop function my_id;
drop function gr_member_in_primary_partition;
drop view gr_member_routing_candidate_status;

DELIMITER $$

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

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 ;
  • 5.启动proxysql
kubectl apply -f proxysql-cnf.yaml -f proxysql-statefulset.yaml
  • 6.查询（进入proxysql容器）
mysql -hproxysql -P6032 -uproxysql-admin -pAa123456 --prompt 'ProxySQL Admin>'

#查看连接mysql是否成功
ProxySQL Admin> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 10;

#查看mysql的组状态
ProxySQL Admin> select * from mysql_server_group_replication_log order by time_start_us desc limit 10;

#查看mgr状态
ProxySQL Admin> select * from runtime_mysql_servers;
  • 7.pod通过域名访问：proxysql.cloud2:6033
  • 当MGR集群只有主节点一个存活的故障处理：
# 如果mgr集群中只有一个正常节点,mysql本身其实是可以正常读写的。但由于proxysql无法找到读节点，会导致使用读账号的应用受到影响。
# 可临时通过改proxysql配置，让这唯一的正常节点即能读又能写

mysql -hproxysql -P6032 -uproxysql-admin -pAa123456 --prompt 'ProxySQL Admin>'

# 配置写库，读账号也能访问
ProxySQL Admin> update mysql_group_replication_hostgroups set writer_is_also_reader = 1;
ProxySQL Admin> LOAD MYSQL SERVERS TO RUNTIME; 
ProxySQL Admin> SAVE MYSQL SERVERS TO DISK;

# 等其他节点恢复正常后再恢复proxysql读写分离配置
ProxySQL Admin> update mysql_group_replication_hostgroups set writer_is_also_reader = 0;
ProxySQL Admin> LOAD MYSQL SERVERS TO RUNTIME; 
ProxySQL Admin> SAVE MYSQL SERVERS TO DISK;