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

项目路径：D:\study\backend\mysql\mgr

一、Mysql(MGR)

 1.1 docker-compose.yaml

 volumes:
     # MySQL配置文件目录
     - "./mysql-1/conf.d/my.cnf:/etc/mysql/my.cnf"
     # MySQL工作目录
     - "./mysql-1/data:/var/lib/mysql"
     # MySQL用于文件导入导出操作的默认存储目录
     - "./mysql-1/mysql-files:/var/lib/mysql-files"
     # MySQL第一次启动自动加载的SQL文件目录
     - "./mysql-1/init:/docker-entrypoint-initdb.d"

# 域名解析
extra_hosts:
      - "n1:192.168.100.11"
      - "n2:192.168.100.12"
      - "n3:192.168.100.13"

expose:
      # MySQL通讯端口
      - "3306"
      # MGR集群通讯端口
      - "24901"

version: "3"
services:mysql-1:image: mysql:8.0.28container_name: mysql-1hostname: n1networks:mysql:ipv4_address: 192.168.100.11  # 指定静态 IP 地址extra_hosts:- "n1:192.168.100.11"- "n2:192.168.100.12"- "n3:192.168.100.13"volumes:- "./mysql-1/conf.d/my.cnf:/etc/mysql/my.cnf"- "./mysql-1/data:/var/lib/mysql"- "./mysql-1/mysql-files:/var/lib/mysql-files"- "./mysql-1/init:/docker-entrypoint-initdb.d"environment:TZ: Asia/ShanghaiMYSQL_ROOT_PASSWORD: rootmcafee123ports:- "33061:3306"expose:- "3306"- "24901"mysql-2:image: mysql:8.0.28container_name: mysql-2hostname: n2networks:mysql:ipv4_address: 192.168.100.12  # 指定静态 IP 地址extra_hosts:- "n1:192.168.100.11"- "n2:192.168.100.12"- "n3:192.168.100.13"volumes:- "./mysql-2/conf.d/my.cnf:/etc/mysql/my.cnf"- "./mysql-2/data:/var/lib/mysql"- "./mysql-2/mysql-files:/var/lib/mysql-files"- "./mysql-2/init:/docker-entrypoint-initdb.d"environment:TZ: Asia/ShanghaiMYSQL_ROOT_PASSWORD: rootmcafee123ports:- "33062:3306"expose:- "3306"- "24901"mysql-3:image: mysql:8.0.28container_name: mysql-3hostname: n3networks:mysql:ipv4_address: 192.168.100.13  # 指定静态 IP 地址extra_hosts:- "n1:192.168.100.11"- "n2:192.168.100.12"- "n3:192.168.100.13"volumes:- "./mysql-3/conf.d/my.cnf:/etc/mysql/my.cnf"- "./mysql-3/data:/var/lib/mysql"- "./mysql-3/mysql-files:/var/lib/mysql-files"- "./mysql-3/init:/docker-entrypoint-initdb.d"environment:TZ: Asia/ShanghaiMYSQL_ROOT_PASSWORD: rootmcafee123ports:- "33063:3306"expose:- "3306"- "24901"
networks:mysql:driver: bridge  # 使用默认的桥接网络驱动ipam:driver: default  # 默认 IPAM 驱动config:- subnet: 192.168.100.0/24  # 指定网络的子网gateway: 192.168.100.1     # 指定网络的网关

1.2 mysql-1

1.2.1 mysql-1/conf.d/my.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/
#使⽤mysql_native_password密码策略，防⽌navicat连不上mysql8
default_authentication_plugin=mysql_native_password
#设置MySQL插件所在⽬录，因为MGR基于插件，所以必须设置插件路径
plugin_dir=/usr/lib/mysql/plugin
#服务器编号
server_id=1
#开启binlog的GTID模式
gtid_mode=ON
#开启后MySQL只允许能够保障事务安全，并且能够被⽇志记录的SQL语句被执⾏
enforce_gtid_consistency=ON
#关闭binlog校验
binlog_checksum=NONE
#定义⽤于事务期间哈希写⼊提取的算法，组复制模式下必须设置为 XXHASH64
transaction_write_set_extraction=XXHASH64
#确定组复制恢复时是否应该应⽤ SSL，通常设置为“开”，但默认设置为“关”
loose-group_replication_recovery_use_ssl=ON
#该服务器的实例所在复制组的名称，必须是有效的 UUID，所有节点必须相同
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
#确定服务器是否应该在服务器启动期间启动组复制
loose-group_replication_start_on_boot=OFF
#为复制组中其他的成员提供的⽹络地址，指定为“主机:端⼝”的格式化字符串。
#很多⼈想当然认为端⼝应该是3306，起始不然，MGR需要开启新端⼝24901同步交换
#所以这⾥不要写错，同时，前⾯我们配置了hosts⽂件做了主机名与IP的映射，这⾥直接写主机名即可
loose-group_replication_local_address="n1:24901"
#⽤于建⽴新成员到组的连接组成员列表。
#这个列表指定为由分隔号间隔的组成员⽹络地址列表，类似 host1:port1、host2:port2 的格式。
#同样采⽤n0~n2的主机名替代
loose-group_replication_group_seeds="n1:24901,n2:24901,n3:24901"
#配置此服务器为引导组，这个选项必须仅在⼀台服务器上设置，
#并且仅当第⼀次启动组或者重新启动整个组时。成功引导组启动后，将此选项设置为关闭
loose-group_replication_bootstrap_group=OFF

1.2.2 mysql-1/init/init.sql

#创建rpl_user账户，此账户⽤于实现主从数据同步

CREATE USER rpl_user@'%' IDENTIFIED BY 'asAS123456!';

#赋予主从同步权限
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

#让刚才的修改⽣效
FLUSH PRIVILEGES;

#删除已产⽣的Binlog

#⼀定要RESET MASTER，它会删除刚才已产⽣的Binlog

#因为刚才Binglog包含创建⽤户这种⾼权限操作，⽤于主从同步的rpl_user账户是没有权限执⾏的

#这就会导致RelayLog重放⽆法正确执⾏，导致从属服务器卡死在"RECEVING"状态

#利⽤RESET MASTER删除这些⽆法执⾏的binlog，就没问题了
RESET MASTER;

#安装MGR插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

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;
*/

1.2.3 mysql-1/mysql-files/proxysql.sql（仅在主节点执行，同步给从节点）

proxysql.sql是使用proxysql的前置条件

# 执行命令
mysql -uroot -prootmcafee123 < /var/lib/mysql-files/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 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();$$

# 选择数据库

use sys;
# 创建proxysql监控用户
CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@1025";

# 创建应用连接proxysql用户
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@1025";

#赋权
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;

#赋权
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ;

#生效
FLUSH PRIVILEGES;

# proxysql监控mysql mgr集群状态的视图

# 函数

 my_id()

# 函数

gr_member_in_primary_partition()

# 视图

gr_member_routing_candidate_status

注意：一定要使用DELIMITER $$，否则执行创建函数和视图会报错

1.3 mysql-2

1.3.1 mysql-1/conf.d/my.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="n2:24901"
loose-group_replication_group_seeds="n1:24901,n2:24901,n3:24901"
loose-group_replication_bootstrap_group=OFF

1.3.2 mysql-1/init/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';
/*
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
*/

1.4 mysql-3

1.4.1 mysql-1/conf.d/my.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="n3:24901"
loose-group_replication_group_seeds="n1:24901,n2:24901,n3:24901"
loose-group_replication_bootstrap_group=OFF

1.4.2 mysql-1/init/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';
/*
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
*/

 1.5、启动

cd  D:\study\backend\mysql\mgrdocker-compose up -d# mysql-1
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# mysql-2
START GROUP_REPLICATION;
# mysql-3
START GROUP_REPLICATION;#查看集群状态
SELECT * FROM performance_schema.replication_group_members;#若搭配使用中间件proxysql，待集群状态正常后到mysql-1节点（master）执行proxysql.sql
mysql -uroot -prootmcafee123 < /var/lib/mysql-files/proxysql.sql

二、ProxySQL

项目地址：D:\study\backend\proxysql

2.1 docker-compose.yaml

version: "3"
services:proxysql:image: proxysql:2.6.5container_name: proxysqlnetworks:mgr_mysql:ipv4_address: 192.168.100.14  # 指定静态 IP 地址extra_hosts:- "n1:192.168.100.11"- "n2:192.168.100.12"- "n3:192.168.100.13"volumes:- "./proxysql.cnf:/etc/proxysql.cnf"- "./data:/var/lib/proxysql"environment:TZ: Asia/Shanghaiports:- "6033:6033"expose:- "6033"- "6032"- "6080"
networks:mgr_mysql:external: true
#networks:
#  mysql:
#    driver: bridge  # 使用默认的桥接网络驱动
#    ipam:
#      driver: default  # 默认 IPAM 驱动
#      config:
#      - subnet: 192.168.100.0/24  # 指定网络的子网
#        gateway: 192.168.100.1     # 指定网络的网关

2.2 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 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="n1" , port=3306 , hostgroup=10 },  { address="n2" , port=3306 , hostgroup=10 },  { address="n3" , 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 = 1default_hostgroup = 10transaction_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=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 =

下列介绍命令行方式和配置两种不同方法 ，以及对照关系

#命令方式
# 我的MGR集群的三个节点
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'n1',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'n2',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'n3',3306);
# 启用上述配置 + 持久化保存
load mysql servers to runtime;
save mysql servers to disk;#配置文件方式
mysql_servers =
({ address="n1" , port=3306 , hostgroup=10 },  { address="n2" , port=3306 , hostgroup=10 },  { address="n3" , port=3306 , hostgroup=10 }
)#注意：address或者hostname一定要和mgr集群配置的保持一致，否则查询runtime_mysql_servers表会
出现重复节点。mgr集群配置使用域名这里就配置域名，mgr集群使用Ip这里就配置Ip。我猜测proxysql无法通过域名解析将域名与Ip进行匹配。---
# 命令方式
# 设置proxysql监控用户和密码
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor@1025';
# 设置应用连接mysql用户和密码
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql@1025',1,10,1);# 配置方式
mysql_variables=
{monitor_username="monitor"monitor_password="monitor@1025"}
mysql_users:
({username = "proxysql"password = "proxysql@1025"active = 1default_hostgroup = 10transaction_persistent = 1}
)---
# 命令方式
# 主负责写、从负责读，当MGR主库切换后，代理自动识别主从。
# ProxySQL代理每一个后端MGR集群时，都必须为这个MGR定义写组10、备写组20、读组30、离线组40，
# 注意：max_transactions_behind 是设置延迟大小，可以给大点,建议自己去开个并行复制。
insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,
offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100);# 配置方式
mysql_group_replication_hostgroups=
({writer_hostgroup=10backup_writer_hostgroup=20reader_hostgroup=30offline_hostgroup=40active=1max_writers=1writer_is_also_reader=0max_transactions_behind=100}
)# 通过命令行方式，记得启动规则
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;

# 读写分离配置
# 命令方式
# select for update走主节点，其他select走从节点
# 其他 insert update delete 走主节点
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',30,1);# 配置方式
mysql_query_rules:
({rule_id=1active=1match_digest="^SELECT.*FOR UPDATE$"destination_hostgroup=10apply=1},{rule_id=2active=1match_digest="^SELECT"destination_hostgroup=30apply=1}
)# 选择命令行方式记得配置加载 + 持久化
load mysql query rules to runtime;
save mysql query rules to disk;

2.3 启动

cd D:\study\backend\proxysql
docker-compose up -d# 连接到Proxy 6032
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '# 连接到Proxy 6033
mysql -uproxysql -pproxysql@1025 -h127.0.0.1 -P6033# 查看proxysql配置mgr节点信息
select * from mysql_servers;
# 查看proxysql监控mgr状态
select hostgroup_id, hostname, port,status from runtime_mysql_servers;
# 查看路由日志（6032）
select hostgroup,digest_text from stats_mysql_query_digest order by digest_text desc limit 10;

 三、读写分离测试

 这里注意，进行读写分离实战测试时，要连接ProxySQL 6033端口，6032是Admin，6033才是客户端（Java程序也连6033）

---

# 链接 ProxySQL 6033
mysql -uproxysql -pproxysql@1025 -h127.0.0.1 -P6033# 创建一个test数据库，查询一条数据，写一条数据
create database test;
use test;
CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL
);
select * from test;
INSERT INTO `test`.`test` (`id`, `name`) VALUES ('1', 'test1');
select * from test for update;# 回到6032，查看路由日志
mysql -uadmin -padmin -h127.0.0.1 -P6032
select hostgroup,digest_text from stats_mysql_query_digest order by digest_text desc limit 10;

 四、SpringBoot 整合

 4.1 yml文件

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.8.168:6033/quick_chat?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&rewriteBatchedStatements=trueusername: proxysqlpassword: proxysql@1025

4.2 query_cache_size问题

proxysql Caused by: java.sql.SQLException: Unknown system variable 'query_cache_size'

# 命令行方式
# 连接到Proxy 6032
mysql -uadmin -padmin -h127.0.0.1 -P6032# 解决 Unknown system variable 'query_cache_size' 问题
update global_variables set variable_value='8.0.4 (ProxySQL)' where variable_name='mysql-server_version';
load mysql variables to run;save mysql variables to disk;# 配置方式
mysql_variables=
{server_version="8.0.28 (ProxySQL)"
}

参考博客

ProxySQL + MySQL MGR 实现读写分离实战_proxysql mgr-CSDN博客

万字详解 MySQL MGR 高可用集群搭建-CSDN博客