一、Mysql(MGR)
1.1 statefulSet.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:labels:app: mysqlname: mysqlnamespace: yihuazt
spec:replicas: 3serviceName: mysql-headlessselector:matchLabels:app: mysqltemplate:metadata:labels:app: mysqlspec:affinity:podAntiAffinity:requiredDuringSchedulingIgnoredDuringExecution:- labelSelector:matchExpressions:- key: appoperator: Invalues:- mysqltopologyKey: "kubernetes.io/hostname"containers:- name: mysqlimage: registry.harbor.com:30002/yihuazt/mysql:8.0.28resources:requests:cpu: "1"memory: "1024Mi"volumeMounts:- name: mysql-datamountPath: /var/lib/mysql/- name: mysql-cmmountPath: /etc/mysql/my.cnfsubPathExpr: $(POD_NAME).cnf- name: mysql-cmmountPath: /docker-entrypoint-initdb.d/init.sqlsubPath: init.sql- name: mysql-cmmountPath: /var/lib/mysql-files/proxysql.sqlsubPath: proxysql.sqlports:- containerPort: 3306- containerPort: 24901env:- name: TZvalue: "Asia/Shanghai"- name: MYSQL_ROOT_PASSWORDvalueFrom:secretKeyRef:name: mysql-certkey: password- name: POD_IPvalueFrom:fieldRef:apiVersion: v1fieldPath: status.podIP- name: POD_NAMEvalueFrom:fieldRef:apiVersion: v1fieldPath: metadata.namevolumes:- name: mysql-cmconfigMap:name: mysql-cmitems:- key: mysql-0.cnfpath: mysql-0.cnf- key: mysql-1.cnfpath: mysql-1.cnf- key: mysql-2.cnfpath: mysql-2.cnf- key: init.sqlpath: init.sql- key: proxysql.sqlpath: proxysql.sqlvolumeClaimTemplates:- metadata:name: mysql-dataspec:accessModes:- ReadWriteOnceresources:requests:storage: 100GistorageClassName: yihuazt-nfsvolumeMode: Filesystem1.2 service.yaml
apiVersion: v1
kind: Service
metadata:name: mysql-headlessnamespace: yihuazt
spec:ports:- name: mysqlprotocol: TCPport: 3306targetPort: 3306- name: mgrprotocol: TCPport: 24901targetPort: 24901selector:app: mysqlclusterIP: Nonetype: ClusterIP1.3 configMap.yaml
注意：
# 用于限制哪些 IP 地址或 IP 网段可以与 Group Replication 集群进行通信
，由于k8s部署Pod是不同网段，跨网段的 MySQL 实例进行 Group Replication必须配置参数，指定哪些 IP 地址或子网允许连接。
- loose-group_replication_ip_whitelist='10.244.0.0/16'
# 用于设置主机名（hostname）的配置参数。这个参数通常用于配置 MySQL Replication 环境中的主机名。如果未配置，MGR集群主机名与无头服务DNS不匹配，通讯失败。
- report_host=mysql-1.mysql-headless.yihuazt.svc.cluster.local
# server_id一定不能设置为0
- server_id=1
apiVersion: v1
kind: ConfigMap
metadata:name: mysql-cmnamespace: yihuaztlabels:app: mysql
data:mysql-0.cnf: |# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; version 2 of the License.## This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the# GNU General Public License for more details.## You should have received a copy of the GNU General Public License# along with this program; if not, write to the Free Software# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA## The MySQL Server configuration file.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html[mysqld]pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlsecure-file-priv= NULL# Custom config should go here!includedir /etc/mysql/conf.d/default_authentication_plugin=mysql_native_passwordplugin_dir=/usr/lib/mysql/pluginserver_id=1gtid_mode=ONenforce_gtid_consistency=ONbinlog_checksum=NONEtransaction_write_set_extraction=XXHASH64loose-group_replication_recovery_use_ssl=ONloose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"loose-group_replication_start_on_boot=OFFloose-group_replication_local_address="mysql-0.mysql-headless.yihuazt.svc.cluster.local:24901"loose-group_replication_group_seeds="mysql-0.mysql-headless.yihuazt.svc.cluster.local:24901,mysql-1.mysql-headless.yihuazt.svc.cluster.local:24901,mysql-2.mysql-headless.yihuazt.svc.cluster.local:24901"loose-group_replication_bootstrap_group=OFFloose-group_replication_ip_whitelist='10.244.0.0/16'report_host=mysql-0.mysql-headless.yihuazt.svc.cluster.localmysql-1.cnf: |# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; version 2 of the License.## This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the# GNU General Public License for more details.## You should have received a copy of the GNU General Public License# along with this program; if not, write to the Free Software# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA## The MySQL Server configuration file.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html[mysqld]pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlsecure-file-priv= NULL# Custom config should go here!includedir /etc/mysql/conf.d/default_authentication_plugin=mysql_native_passwordplugin_dir=/usr/lib/mysql/pluginserver_id=2gtid_mode=ONenforce_gtid_consistency=ONbinlog_checksum=NONEloose-group_replication_recovery_get_public_key=ONloose-group_replication_recovery_use_ssl=ONloose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"loose-group_replication_start_on_boot=OFFloose-group_replication_local_address="mysql-1.mysql-headless.yihuazt.svc.cluster.local:24901"loose-group_replication_group_seeds="mysql-0.mysql-headless.yihuazt.svc.cluster.local:24901,mysql-1.mysql-headless.yihuazt.svc.cluster.local:24901,mysql-2.mysql-headless.yihuazt.svc.cluster.local:24901"loose-group_replication_bootstrap_group=OFFloose-group_replication_ip_whitelist='10.244.0.0/16'report_host=mysql-1.mysql-headless.yihuazt.svc.cluster.localmysql-2.cnf: |# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; version 2 of the License.## This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the# GNU General Public License for more details.## You should have received a copy of the GNU General Public License# along with this program; if not, write to the Free Software# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA## The MySQL Server configuration file.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html[mysqld]pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlsecure-file-priv= NULL# Custom config should go here!includedir /etc/mysql/conf.d/default_authentication_plugin=mysql_native_passwordplugin_dir=/usr/lib/mysql/pluginserver_id=3gtid_mode=ONenforce_gtid_consistency=ONbinlog_checksum=NONEloose-group_replication_recovery_get_public_key=ONloose-group_replication_recovery_use_ssl=ONloose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"loose-group_replication_start_on_boot=OFFloose-group_replication_local_address="mysql-2.mysql-headless.yihuazt.svc.cluster.local:24901"loose-group_replication_group_seeds="mysql-0.mysql-headless.yihuazt.svc.cluster.local:24901,mysql-1.mysql-headless.yihuazt.svc.cluster.local:24901,mysql-2.mysql-headless.yihuazt.svc.cluster.local:24901"loose-group_replication_bootstrap_group=OFFloose-group_replication_ip_whitelist='10.244.0.0/16'report_host=mysql-2.mysql-headless.yihuazt.svc.cluster.localinit.sql: |CREATE USER rpl_user@'%' IDENTIFIED BY 'asAS123456!';GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';FLUSH PRIVILEGES;RESET MASTER;INSTALL PLUGIN group_replication SONAME 'group_replication.so';/*SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication' \G;*/CHANGE MASTER TO MASTER_USER="rpl_user", MASTER_PASSWORD="asAS123456!" FOR CHANNEL 'group_replication_recovery';/*SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF;SELECT * FROM performance_schema.replication_group_members;*/proxysql.sql: |/*mysql -uroot -prootmcafee123 < /var/lib/mysql-files/proxysql.sql*/use sys;DELIMITER $$CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@1025";CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@1025";GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ;FLUSH PRIVILEGES;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)DETERMINISTICBEGINRETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROMperformance_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 JOINperformance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());END$$CREATE VIEW gr_member_routing_candidate_status AS SELECTsys.gr_member_in_primary_partition() as viable_candidate,IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROMperformance_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();$$1.4 secret.yml
echo -n "rootmcafee123" | base64
echo "cm9vdG1jYWZlZTEyMw==" | base64 --decode
apiVersion: v1
kind: Secret
metadata:name: mysql-certnamespace: yihuazt
type: Opaque
data:password: cm9vdG1jYWZlZTEyMw==二、ProxySQL
2.1 deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:name: proxysqlnamespace: yihuazt
spec:replicas: 1selector:matchLabels:app: proxysqltemplate:metadata:labels:app: proxysqlspec:containers:- name: proxysqlimage: registry.harbor.com:30002/yihuazt/proxysql:2.6.5ports:- containerPort: 6033- containerPort: 6032- containerPort: 6070env:- name: TZvalue: "Asia/Shanghai"volumeMounts:- name: proxysql-datamountPath: /var/lib/proxysql- name: proxysql-configmountPath: /etc/proxysql.cnfsubPath: proxysql.cnfvolumes:- name: proxysql-configconfigMap:name: proxysql-cmitems:- key: proxysql.cnfpath: proxysql.cnf- name: proxysql-datapersistentVolumeClaim:claimName: proxysql-pvc2.2 service.yaml
apiVersion: v1
kind: Service
metadata:name: proxysqlnamespace: yihuazt
spec:selector:app: proxysqltype: NodePortports:- port: 6033targetPort: 6033nodePort: 30633name: external2.3 persistentVolumeClaim.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:name: proxysql-pvcnamespace: yihuazt
spec:storageClassName: "yihuazt-nfs"accessModes:- ReadWriteOnceresources:requests:storage: 30Gi2.4 configMap.yaml
apiVersion: v1
kind: ConfigMap
metadata:name: proxysql-cmnamespace: yihuaztlabels:app: proxysql
data:proxysql.cnf: |#file proxysql.cfg######################################################################################### This config file is parsed using libconfig , and its grammar is described in: # http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar # Grammar is also copied at the end of this file ################################################################################################################################################################################# IMPORTANT INFORMATION REGARDING THIS CONFIGURATION FILE: ######################################################################################### On startup, ProxySQL reads its config file (if present) to determine its datadir. # What happens next depends on if the database file (disk) is present in the defined# datadir (i.e. "/var/lib/proxysql/proxysql.db").## If the database file is found, ProxySQL initializes its in-memory configuration from # the persisted on-disk database. So, disk configuration gets loaded into memory and # then propagated towards the runtime configuration. ## If the database file is not found and a config file exists, the config file is parsed # and its content is loaded into the in-memory database, to then be both saved on-disk # database and loaded at runtime.## IMPORTANT: If a database file is found, the config file is NOT parsed. In this case# ProxySQL initializes its in-memory configuration from the persisted on-disk# database ONLY. In other words, the configuration found in the proxysql.cnf# file is only used to initial the on-disk database read on the first startup.## In order to FORCE a re-initialise of the on-disk database from the configuration file # the ProxySQL service should be started with "systemctl start proxysql-initial".#########################################################################################datadir="/var/lib/proxysql"errorlog="/var/lib/proxysql/proxysql.log"admin_variables={admin_credentials="admin:admin"# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"mysql_ifaces="0.0.0.0:6032"# refresh_interval=2000# debug=true}mysql_variables={threads=4max_connections=2048default_query_delay=0default_query_timeout=36000000have_compress=truepoll_timeout=2000# interfaces="0.0.0.0:6033;/tmp/proxysql.sock"interfaces="0.0.0.0:6033"default_schema="information_schema"stacksize=1048576server_version="8.0.28 (ProxySQL)"connect_timeout_server=3000# make sure to configure monitor username and password# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_passwordmonitor_username="monitor"monitor_password="monitor@1025"monitor_history=600000monitor_connect_interval=60000monitor_ping_interval=10000monitor_read_only_interval=1500monitor_read_only_timeout=500ping_interval_server_msec=120000ping_timeout_server=500commands_stats=truesessions_sort=trueconnect_retries_on_failure=10}# defines all the MySQL serversmysql_servers =(# {# address = "127.0.0.1" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain# port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain# hostgroup = 0 # no default, required# status = "ONLINE" # default: ONLINE# weight = 1 # default: 1# compression = 0 # default: 0# max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned# },# {# address = "/var/lib/mysql/mysql.sock"# port = 0# hostgroup = 0# },# {# address="127.0.0.1"# port=21891# hostgroup=0# max_connections=200# },# { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },# { address="127.0.0.1" , port=21892 , hostgroup=1 },# { address="127.0.0.1" , port=21893 , hostgroup=1 }# { address="127.0.0.2" , port=3306 , hostgroup=1 },# { address="127.0.0.3" , port=3306 , hostgroup=1 },# { address="127.0.0.4" , port=3306 , hostgroup=1 },# { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }{ address="mysql-0.mysql-headless.yihuazt.svc.cluster.local" , port=3306 , hostgroup=10 }, { address="mysql-1.mysql-headless.yihuazt.svc.cluster.local" , port=3306 , hostgroup=10 }, { address="mysql-2.mysql-headless.yihuazt.svc.cluster.local" , port=3306 , hostgroup=10 })# defines all the MySQL usersmysql_users:(# {# username = "username" # no default , required# password = "password" # default: ''# default_hostgroup = 0 # default: 0# active = 1 # default: 1# },# {# username = "root"# password = ""# default_hostgroup = 0# max_connections=1000# default_schema="test"# active = 1# },# { username = "user1" , password = "password" , default_hostgroup = 0 , active = 0 }{username = "proxysql"password = "proxysql@1025"active = 1default_hostgroup = 10transaction_persistent = 1})#defines MySQL Query Rulesmysql_query_rules:(# {# rule_id=1# active=1# match_pattern="^SELECT .* FOR UPDATE$"# destination_hostgroup=0# apply=1# },# {# rule_id=2# active=1# match_pattern="^SELECT"# destination_hostgroup=1# apply=1# }{rule_id=1active=1match_digest="^SELECT.*FOR UPDATE$"destination_hostgroup=10apply=1},{rule_id=2active=1match_digest="^SELECT"destination_hostgroup=30apply=1})scheduler=(# {# id=1# active=0# interval_ms=10000# filename="/var/lib/proxysql/proxysql_galera_checker.sh"# arg1="0"# arg2="0"# arg3="0"# arg4="1"# arg5="/var/lib/proxysql/proxysql_galera_checker.log"# })mysql_replication_hostgroups=(# {# writer_hostgroup=30# reader_hostgroup=40# comment="test repl 1"# },# {# writer_hostgroup=50# reader_hostgroup=60# comment="test repl 2"# })mysql_group_replication_hostgroups=({writer_hostgroup=10backup_writer_hostgroup=20reader_hostgroup=30offline_hostgroup=40active=1max_writers=1writer_is_also_reader=0max_transactions_behind=100})# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar## Below is the BNF grammar for configuration files. Comments and include directives are not part of the grammar, so they are not included here. ## configuration = setting-list | empty## setting-list = setting | setting-list setting# # setting = name (":" | "=") value (";" | "," | empty)# # value = scalar-value | array | list | group# # value-list = value | value-list "," value# # scalar-value = boolean | integer | integer64 | hex | hex64 | float# | string# # scalar-value-list = scalar-value | scalar-value-list "," scalar-value# # array = "[" (scalar-value-list | empty) "]"# # list = "(" (value-list | empty) ")"# # group = "{" (setting-list | empty) "}"# # empty =运行方法与Docker部署一致，差异性的地方已经说明
