1.安裝MySQL

1.1MySQL安裝簡介

安裝mgr-MySQL，使用MySQL版本：8.0.12

其中用到的鏡像：
proxy SQL：severalnines/proxysql:1.4.12

mysql-exporter：kubedb/mysqld-exporter:v0.11.0

MySQL：mysql:8.0.12

存儲方案：

使用localpv的方式，減少了使用網(wǎng)絡(luò)的消耗。

1.2開始安裝

01-local-storage.yaml

kind: StorageClass
apiVersion: storage.k8s.io/v1
meta
  name: local-mysql
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer

02-pv.yaml

apiVersion: v1
kind: PersistentVolume
meta
  name: mysql-volume-a
  labels:
    volume-type: mysql
spec:
  capacity:
    storage: 30Gi 
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Retain
  storageClassName: local-mysql
  local:
    path: /data/mysql/pv
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - 192.168.1.133

---


apiVersion: v1
kind: PersistentVolume
meta
  name: mysql-volume-b
  labels:
    volume-type: mysql
spec:
  capacity:
    storage: 30Gi
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Retain
  storageClassName: local-mysql
  local:
    path: /data/mysql/pv
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - 192.168.1.134

---

apiVersion: v1
kind: PersistentVolume
meta
  name: mysql-volume-c
  labels:
    volume-type: mysql
spec:
  capacity:
    storage: 30Gi
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Retain
  storageClassName: local-mysql
  local:
    path: /data/mysql/pv
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - 192.168.1.135

03-configmap.yaml

apiVersion: v1

  int-mysql.sh: |
     set -ex
     [[ `hostname` =~ -([0-9]+)$ ]] || exit 1
     ordinal=${BASH_REMATCH[1]}
     echo [mysqld] > /etc/mysql/conf.d/server-id.cnf
     echo server-id=$((100 + $ordinal)) >> /etc/mysql/conf.d/server-id.cnf
     echo loose-group_replication_local_address=`hostname`.mysql:24901 >>/etc/mysql/conf.d/server-id.cnf
     echo report_host=`hostname`.mysql >>/etc/mysql/conf.d/server-id.cnf
     cp /mnt/conf/mysql-conf.cnf /etc/mysql/conf.d/
     mkdir -p /var/lib/mysql/{data,tmp,blog,rlog,ulog}
     chown -R mysql:mysql /var/lib/mysql
  mysql-conf.cnf: |
     [mysqld]
     skip-host-cache
     skip-name-resolve
     gtid_mode=ON
     enforce_gtid_consistency=ON
     master_info_repository=TABLE
     relay_log_info_repository=TABLE
     binlog_checksum=NONE
     log_slave_updates=ON
     binlog_format=ROW
     transaction_write_set_extraction=XXHASH64
     loose-group_replication_group_name="75320cff-9d1f-4aea-883a-8f5406513a9c"
     loose-group_replication_start_on_boot=off
     loose-group_replication_bootstrap_group=off
     loose-group_replication_ip_whitelist=172.20.0.0/16
     loose-group_replication_group_seeds= "mysql-0.mysql:24901,mysql-1.mysql:24901,mysql-2.mysql:24901"
     default_authentication_plugin=mysql_native_password
     event_scheduler=ON
     datadir=/var/lib/mysql/data
kind: ConfigMap
meta
  name: mysql-config

04-headless.yaml

apiVersion: v1
kind: Service
meta
  name: mysql
  labels:
    app: mysql
  namespace: mysql
spec:
  ports:
  - port: 3306
    name: client
  - port: 9104
    name: exporter
  clusterIP: None
  selector:
    app: mysql

05-secret.yaml

使用的是密碼是：ZWZRTWprMzI=，type: Opaque，使用base64轉(zhuǎn)碼后，真正的密碼是：efQMjk32

apiVersion: v1
kind: Secret
meta
  name: my-mysql
type: Opaque

  mysql-root-password: ZWZRTWprMzI=

06-statefulset.yaml

根據(jù)自己的需求更改MySQL的CPU，memory參數(shù)，以及持久化文件的大小storage: 30Gi（默認30G）

apiVersion: apps/v1
kind: StatefulSet
meta
  labels:
    app: mysql
  name: mysql
spec:
  replicas: 3
  serviceName: mysql
  selector:
       matchLabels:
         app: mysql
  template:
       meta
         labels:
           app: mysql
         annotations:
           prometheus.io/scrape: "true"
           prometheus.io/port: "9104"
       spec:
         tolerations:
           - key: node-role.kubernetes.io/master
             effect: NoSchedule
         initContainers:
         - name: init-mysql
           image: mysql:8.0.12
           command:
           - bash
           - "/mnt/conf/int-mysql.sh"
           volumeMounts:
             - name: config-volume
               mountPath: /mnt/conf/
               readOnly: true
             - name: conf
               mountPath: /etc/mysql/conf.d/
             - name: mysql-data
               mountPath: /var/lib/mysql/
         containers:
         - name: mysql
           image: mysql:8.0.12
           resources:
             limits:
               cpu: "2"
             requests:
               memory: "2048Mi"
           volumeMounts:
             - name: conf
               mountPath: /etc/mysql/conf.d/
             - name: mysql-data
               mountPath: /var/lib/mysql/
           ports:
             - containerPort: 3306
               name: client
             - containerPort: 24901
           env:
             - name: TZ
               value: "Asia/Shanghai"
             - name: MYSQL_ROOT_PASSWORD
               valueFrom:
                 secretKeyRef:
                   key: mysql-root-password
                   name: my-mysql
             - name: MYSQL_INITDB_SKIP_TZINFO
               value: "1"
         - name: mysql-exporter
           env:
             - name: DATA_SOURCE_NAME
               value: exporter:018P4NUDZNaqeStsSkmO0A@tcp(127.0.0.1:3306)/performance_schema
           image: kubedb/mysqld-exporter:v0.11.0
           ports:
             - containerPort: 9104
               name: exporter
         volumes:
           - name: config-volume
             configMap:
              name: mysql-config
           - name: conf
             emptyDir: {}
  volumeClaimTemplates:
  - meta
      name: mysql-data
    spec:
      accessModes:
      - ReadWriteOnce
      storageClassName: local-mysql
      resources:
        requests:
          storage: 30Gi 
      selector:
        matchLabels:
          volume-type: mysql

1.3初始化集群

1.3.1主節(jié)點操作

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password'  FOR CHANNEL 'group_replication_recovery';

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

1.3.2從節(jié)點操作

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;


CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password'  FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

1.3.3查看集群狀態(tài)

SELECT * FROM performance_schema.replication_group_members;

2.安裝Proxy SQL

2.1Proxy SQL簡介

proxy SQL版本：1.4.12

其中用到的鏡像：

            proxy SQL：severalnines/proxysql:1.4.12

2.2開始安裝

proxysql.cnf

執(zhí)行命令：kubectl create configmap proxysql-configmap --from-file=proxysql.cnf

datadir="/var/lib/proxysql"
admin_variables=
{
    admin_credentials="proxysql-admin:adminpassw0rd;cluster1:secret1pass"
    mysql_ifaces="0.0.0.0:6032"
    refresh_interval=2000
    cluster_username="cluster1"
    cluster_password="secret1pass"
    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
}
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;/tmp/proxysql.sock"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.1.30"
    connect_timeout_server=10000
    monitor_history=60000
    monitor_connect_interval=200000
    monitor_ping_interval=200000
    ping_interval_server_msec=10000
    ping_timeout_server=200
    commands_stats=true
    sessions_sort=true
    monitor_username="proxysql"
    monitor_password="proxysqlpassw0rd"
    monitor_galera_healthcheck_interval=2000
    monitor_galera_healthcheck_timeout=800
}
mysql_galera_hostgroups =
(
    {
        writer_hostgroup=10
        backup_writer_hostgroup=20
        reader_hostgroup=30
        offline_hostgroup=9999
        max_writers=1
        writer_is_also_reader=1
        max_transactions_behind=30
        active=1
    }
)
mysql_servers =
(
    { address="mysql-0.mysql.mysql" , port=3306 , hostgroup=10, max_connections=100 },
    { address="mysql-0.mysql.mysql" , port=3306 , hostgroup=30, max_connections=100 },
    { address="mysql-0.mysql.mysql" , port=3306 , hostgroup=30, max_connections=100 }
)
mysql_query_rules =
(
    {
        rule_id=100
        active=1
        match_pattern="^SELECT .* FOR UPDATE"
        destination_hostgroup=10
        apply=1
    },
    {
        rule_id=200
        active=1
        match_pattern="^SELECT .*"
        destination_hostgroup=30
        apply=1
    },
    {
        rule_id=300
        active=1
        match_pattern=".*"
        destination_hostgroup=10
        apply=1
    }
)
mysql_users =
(
    { username = "root", password = "efQMjk32", default_hostgroup = 10, transaction_persistent = 0, active = 1 },
    { username = "slave", password = "efQMjk32", default_hostgroup = 30, transaction_persistent = 0, active = 1 }
)
proxysql_servers =
(
    { hostname = "proxysql-0.proxysqlcluster", port = 6032, weight = 1 }
    
)

參數(shù)根據(jù)需求更改：

mysql_servers：address使用的內(nèi)部域名服務(wù)器，mysql為命名空間 , port端口

proxysql_servers：proxysql-0.proxysqlcluster 服務(wù)，下問也會提到

mysql_users： password密碼為上述base64編碼后的結(jié)果。

08-proxysql-headless-svc.yml

apiVersion: v1
kind: Service
meta
  name: proxysqlcluster
  labels:
    app: proxysql
spec:
  clusterIP: None
  ports:
  - port: 6032
    name: proxysql-admin
  selector:
    app: proxysql

09-proxysql-ss-svc.yml

apiVersion: apps/v1
kind: StatefulSet
meta
  name: proxysql
  labels:
    app: proxysql
spec:
  replicas: 1
  serviceName: proxysqlcluster
  selector:
    matchLabels:
      app: proxysql
      tier: frontend
  updateStrategy:
    type: RollingUpdate
  template:
    meta
      labels:
        app: proxysql
        tier: frontend
    spec:
      restartPolicy: Always
      containers:
      - image: severalnines/proxysql:1.4.12
        name: proxysql
        volumeMounts:
        - name: proxysql-config
          mountPath: /etc/proxysql.cnf
          subPath: proxysql.cnf
        ports:
        - containerPort: 6033
          name: proxysql-mysql
        - containerPort: 6032
          name: proxysql-admin
      volumes:
      - name: proxysql-config
        configMap:
          name: proxysql-configmap
---
apiVersion: v1
kind: Service
meta
  annotations:
  labels:
    app: proxysql
    tier: frontend
  name: proxysql
spec:
  ports:
  - name: proxysql-mysql
    nodePort: 30033
    port: 6033
    protocol: TCP
    targetPort: 6033
  - name: proxysql-admin
    nodePort: 30032
    port: 6032
    protocol: TCP
    targetPort: 6032
  selector:
    app: proxysql
    tier: frontend
  type: NodePort

3.MGR和ProxySQL的配置

在MGR主節(jié)點執(zhí)行下面SQL語句,創(chuàng)建監(jiān)控用戶用于ProxySQL監(jiān)控數(shù)據(jù)庫狀態(tài)：

create user 'proxysql'@'%' identified by 'proxysqlpassw0rd';
grant ALL on *.* to 'proxysql'@'%';
flush privileges;

創(chuàng)建addition_to_sys.sql文件并添加以下內(nèi)容：

USE sys;

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 ;

在各個節(jié)點下檢查視圖是否創(chuàng)建成功：

SELECT * FROM sys.gr_member_routing_candidate_status;

安裝完成。

4.使用

通過域名使用：

proxysql-0.proxysqlcluster.redis:6033 數(shù)據(jù)傳輸端口

proxysql-0.proxysqlcluster.redis:6032 管理層輸端口

通過ip訪問：

根據(jù)自己的需求，配置Loadbalaner，對外提供訪問。（為proxy SQL添加 svc即可）