Mysql(MGR)和ProxySQL搭建部署-Kubernetes版本

一、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: Filesystem

1.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: ClusterIP

1.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-pvc

2.2 service.yaml

apiVersion: v1
kind: Service
metadata:name: proxysqlnamespace: yihuazt
spec:selector:app: proxysqltype: NodePortports:- port: 6033targetPort: 6033nodePort: 30633name: external

2.3 persistentVolumeClaim.yaml

apiVersion: v1
kind: PersistentVolumeClaim
metadata:name: proxysql-pvcnamespace: yihuazt
spec:storageClassName: "yihuazt-nfs"accessModes:- ReadWriteOnceresources:requests:storage: 30Gi

2.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部署一致，差异性的地方已经说明