下面是proxysql的默认配置
#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;wxhntmy:CWCcwy12"
# 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="5.5.30"
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="CWCcwy12"
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
mysql-set_query_lock_on_hostgroup=0
}
# defines all the MySQL servers
#MySQL节点，这里host是根据mysql的无头服务
mysql_servers =
(
{
address= "mysql-0.mysql-headless" ,
port=3306 ,
hostgroup=10,
max_connections=100
},
{
address= "mysql-1.mysql-headless" ,
port=3306 ,
hostgroup=20,
max_connections=100
},
{
address= "mysql-2.mysql-headless" ,
port=3306 ,
hostgroup=20,
max_connections=100
}
# {
# 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 }
)
# defines all the MySQL users
#MySQL的用户
mysql_users:
(
{
username = "proxysql" ,
password = "CWCcwy12" ,
default_hostgroup = 10,
transaction_persistent = 0,
active = 1
}
# {
# 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 }
)
#defines MySQL Query Rules
mysql_query_rules:
(
#select * from tb for update 这样的语句是会修改数据的，
# 所以需要单独定义，将它路由至 hostgroup_id=10 的组 (写组)
{
rule_id=1
active=1
match_digest= "^SELECT.*FOR UPDATE$"
destination_hostgroup=10
apply=1
},
# 将 select 查询语句全部路由至 hostgroup_id=20 的组 (读组)
#其他没有被规则匹配到的组将会被路由至用户默认的组 (mysql_users 表中的 default_hostgroup)
{
rule_id=2
active=1
match_digest= "^SELECT"
destination_hostgroup=20
apply=1
}
# {
# 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
# }
)
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=10
reader_hostgroup=20
comment="cluster1"
}
# {
# writer_hostgroup=50
# reader_hostgroup=60
# comment="test repl 2"
# }
)
proxysql_servers =
(
{
hostname = "proxysql-0.proxysqlcluster" ,
port = 6032,
weight = 1
},
{
hostname = "proxysql-1.proxysqlcluster" ,
port = 6032,
weight = 1
}
)
# 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 =
kubectl create configmap proxysql-configmap --from-file=proxysql.cnf
vim nfs-client-provisioner-proxysql.yaml
kind: Deployment apiVersion: apps/v1 metadata: name: nfs-client-provisioner-proxysql namespace: default #命名空间 spec: replicas: 1 selector: matchLabels: app: nfs-client-provisioner strategy: type: Recreate template: metadata: labels: app: nfs-client-provisioner spec: serviceAccountName: nfs-client-provisioner #指定Service Account账户 containers: - name: nfs-client-provisioner image: quay.io/external_storage/nfs-client-provisioner:latest imagePullPolicy: IfNotPresent volumeMounts: - name: nfs-client-root mountPath: /persistentvolumes env: - name: TZ value: Asia/Shanghai #容器时区 - name: PROVISIONER_NAME value: nfs-storage-proxysql #配置provisioner的Name，确保该名称与StorageClass资源中的provisioner名称保持一致 - name: NFS_SERVER value: 192.168.102.130 #配置绑定的nfs服务器 - name: NFS_PATH value: /data/mysql #配置绑定的nfs服务器目录，挂载路径 volumes: #申明nfs数据卷 - name: nfs-client-root nfs: server: 192.168.102.130 #配置绑定的nfs服务器 path: /data/mysql
kubectl apply -f nfs-client-provisioner-proxysql.yaml kubectl get pods -n default
vim nfs-client-storageclass-proxysql.yaml
apiVersion: storage.k8s.io/v1 kind: StorageClass metadata: name: nfs-client-storageclass-proxysql provisioner: nfs-storage-proxysql #这里的名称要和provisioner配置文件中的环境变量PROVISIONER_NAME保持一致 parameters: archiveOnDelete: "true"
kubectl apply -f nfs-client-storageclass-proxysql.yaml kubectl get storageclasses
apiVersion: v1 kind: Service metadata: name: proxysqlcluster labels: app: proxysql spec: clusterIP: None ports: - port: 6032 name: proxysql-admin selector: app: proxysql --- apiVersion: v1 kind: Service metadata: annotations: labels: app: proxysql tier: frontend name: proxysql spec: ports: - name: proxysql-mysql port: 6033 nodePort: 30033 protocol: TCP targetPort: 6033 - name: proxysql-admin nodePort: 30032 port: 6032 protocol: TCP targetPort: 6032 selector: app: proxysql tier: frontend type : NodePort
kubectl create -f proxysql-services.yml kubectl get svc -o wide
kubectl exec -it mysql-0 -n default -c mysql /bin/bash mysql -uroot -pCWCcwy12 -h 127.0.0.1
grant all on *.* to 'proxysql'@'%' identified by 'CWCcwy12'; grant select on *.* to 'monitor'@'%' IDENTIFIED BY 'CWCcwy12'; flush privileges; use mysql; select host,user from user;
apiVersion: apps/v1 kind: StatefulSet metadata: name: proxysql namespace: default labels: app: proxysql spec: replicas: 2 serviceName: proxysqlcluster selector: matchLabels: app: proxysql tier: frontend updateStrategy: type : RollingUpdate template: metadata: labels: app: proxysql tier: frontend spec: restartPolicy: Always containers: - image: proxysql/proxysql:latest-centos name: proxysql volumeMounts: - name: proxysql-config mountPath: /etc/proxysql.cnf subPath: proxysql.cnf - name: data mountPath: /var/lib/proxysql subPath: proxysql ports: - containerPort: 6033 name: proxysql-mysql - containerPort: 6032 name: proxysql-admin env: - name: TZ value: Asia/Shanghai volumes: - name: proxysql-config configMap: name: proxysql-configmap volumeClaimTemplates: - metadata: name: data namespace: default #属于的命名空间 spec: storageClassName: "nfs-client-storageclass-proxysql" #StorageClass的名称 accessModes: ["ReadWriteOnce"] resources: requests: storage: 1Gi
kubectl apply -f proxysql-statefulset.yml
mysql -uproxysql -pCWCcwy12 -P30033 -h172.17.0.10
create database test;
CREATE TABLE test.test_table (message VARCHAR(250));
INSERT INTO test.test_table VALUES ('hello');
select * from test.test_table;
mysql -uwxhntmy -pCWCcwy12 -P30032 -h172.17.0.10
查看读写节点
select * from mysql_servers;
查看用户
select * from mysql_users;
查看monitor用户
select @@mysql-monitor_username; select @@mysql-monitor_password;
查看路由规则
select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
验证读写分离是否成功
select hostgroup,schemaname,username,client_address,digest,digest_text,count_star from stats_mysql_query_digest limit 10;
