一、Mysql(MGR)
1.1 statefulSet.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
labels:
app: mysql
name: mysql
namespace: yihuazt
spec:
replicas: 3
serviceName: mysql-headless
selector:
matchLabels:
app: mysql
template:
metadata:
labels:
app: mysql
spec:
affinity:
podAntiAffinity:
requiredDuringSchedulingIgnoredDuringExecution:
- labelSelector:
matchExpressions:
- key: app
operator: In
values:
- mysql
topologyKey: "kubernetes.io/hostname"
containers:
- name: mysql
image: registry.harbor.com:30002/yihuazt/mysql:8.0.28
resources:
requests:
cpu: "1"
memory: "1024Mi"
volumeMounts:
- name: mysql-data
mountPath: /var/lib/mysql/
- name: mysql-cm
mountPath: /etc/mysql/my.cnf
subPathExpr: $(POD_NAME).cnf
- name: mysql-cm
mountPath: /docker-entrypoint-initdb.d/init.sql
subPath: init.sql
- name: mysql-cm
mountPath: /var/lib/mysql-files/proxysql.sql
subPath: proxysql.sql
ports:
- containerPort: 3306
- containerPort: 24901
env:
- name: TZ
value: "Asia/Shanghai"
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-cert
key: password
- name: POD_IP
valueFrom:
fieldRef:
apiVersion: v1
fieldPath: status.podIP
- name: POD_NAME
valueFrom:
fieldRef:
apiVersion: v1
fieldPath: metadata.name
volumes:
- name: mysql-cm
configMap:
name: mysql-cm
items:
- key: mysql-0.cnf
path: mysql-0.cnf
- key: mysql-1.cnf
path: mysql-1.cnf
- key: mysql-2.cnf
path: mysql-2.cnf
- key: init.sql
path: init.sql
- key: proxysql.sql
path: proxysql.sql
volumeClaimTemplates:
- metadata:
name: mysql-data
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 100Gi
storageClassName: yihuazt-nfs
volumeMode: Filesystem1.2 service.yaml
apiVersion: v1
kind: Service
metadata:
name: mysql-headless
namespace: yihuazt
spec:
ports:
- name: mysql
protocol: TCP
port: 3306
targetPort: 3306
- name: mgr
protocol: TCP
port: 24901
targetPort: 24901
selector:
app: mysql
clusterIP: None
type: 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-cm
namespace: yihuazt
labels:
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.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Custom config should go here
!includedir /etc/mysql/conf.d/
default_authentication_plugin=mysql_native_password
plugin_dir=/usr/lib/mysql/plugin
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
loose-group_replication_recovery_use_ssl=ON
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-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=OFF
loose-group_replication_ip_whitelist='10.244.0.0/16'
report_host=mysql-0.mysql-headless.yihuazt.svc.cluster.local
mysql-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.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Custom config should go here
!includedir /etc/mysql/conf.d/
default_authentication_plugin=mysql_native_password
plugin_dir=/usr/lib/mysql/plugin
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
loose-group_replication_recovery_get_public_key=ON
loose-group_replication_recovery_use_ssl=ON
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-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=OFF
loose-group_replication_ip_whitelist='10.244.0.0/16'
report_host=mysql-1.mysql-headless.yihuazt.svc.cluster.local
mysql-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.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Custom config should go here
!includedir /etc/mysql/conf.d/
default_authentication_plugin=mysql_native_password
plugin_dir=/usr/lib/mysql/plugin
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
loose-group_replication_recovery_get_public_key=ON
loose-group_replication_recovery_use_ssl=ON
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-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=OFF
loose-group_replication_ip_whitelist='10.244.0.0/16'
report_host=mysql-2.mysql-headless.yihuazt.svc.cluster.local
init.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)
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();$$1.4 secret.yml
echo -n "rootmcafee123" | base64
echo "cm9vdG1jYWZlZTEyMw==" | base64 --decode
apiVersion: v1
kind: Secret
metadata:
name: mysql-cert
namespace: yihuazt
type: Opaque
data:
password: cm9vdG1jYWZlZTEyMw==二、ProxySQL
2.1 deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
name: proxysql
namespace: yihuazt
spec:
replicas: 1
selector:
matchLabels:
app: proxysql
template:
metadata:
labels:
app: proxysql
spec:
containers:
- name: proxysql
image: registry.harbor.com:30002/yihuazt/proxysql:2.6.5
ports:
- containerPort: 6033
- containerPort: 6032
- containerPort: 6070
env:
- name: TZ
value: "Asia/Shanghai"
volumeMounts:
- name: proxysql-data
mountPath: /var/lib/proxysql
- name: proxysql-config
mountPath: /etc/proxysql.cnf
subPath: proxysql.cnf
volumes:
- name: proxysql-config
configMap:
name: proxysql-cm
items:
- key: proxysql.cnf
path: proxysql.cnf
- name: proxysql-data
persistentVolumeClaim:
claimName: proxysql-pvc2.2 service.yaml
apiVersion: v1
kind: Service
metadata:
name: proxysql
namespace: yihuazt
spec:
selector:
app: proxysql
type: NodePort
ports:
- port: 6033
targetPort: 6033
nodePort: 30633
name: external2.3 persistentVolumeClaim.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: proxysql-pvc
namespace: yihuazt
spec:
storageClassName: "yihuazt-nfs"
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 30Gi2.4 configMap.yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: proxysql-cm
namespace: yihuazt
labels:
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=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"
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_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_password
monitor_username="monitor"
monitor_password="monitor@1025"
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
}
# defines all the MySQL servers
mysql_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 users
mysql_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 = 1
default_hostgroup = 10
transaction_persistent = 1
}
)
#defines MySQL Query Rules
mysql_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=1
active=1
match_digest="^SELECT.*FOR UPDATE$"
destination_hostgroup=10
apply=1
},
{
rule_id=2
active=1
match_digest="^SELECT"
destination_hostgroup=30
apply=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=10
backup_writer_hostgroup=20
reader_hostgroup=30
offline_hostgroup=40
active=1
max_writers=1
writer_is_also_reader=0
max_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部署一致，差异性的地方已经说明
![[实用指南]如何将视频从iPhone传输到iPad](https://img-blog.csdnimg.cn/direct/ae5df0d0381b4d09aa1da1b6d4981fca.jpeg)
