侧边栏壁纸
博主头像
叶子博主等级

  • 累计撰写 39 篇文章
  • 累计创建 51 个标签
  • 累计收到 2 条评论
标签搜索

目 录CONTENT

文章目录
中间件

Windows下Docker部署MGR + MySQL Shell + MySQL Router（在原有MGR集群上加入MySQL Shell + MySQL Router）

叶子
叶子
2024-04-30 / 0 评论 / 0 点赞 / 77 阅读 / 2,142 字

零、部署架构

mysql 3 节点
router 2 节点

一、MGR部署

Docker命令

docker network create mysql-network-bak
docker inspect mysql-network-bak

docker run -itd --name mysql-mgr-master11 -h mysql-mgr-master11 -p 43066:3306 -p 43061:43061 --net=mysql-network-bak -v E:\Docker\mgr1\master1\conf.d:/etc/mysql/conf.d -v E:\Docker\mgr1\master1\conf.d\my.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=SpmsDB_1834shark -e TZ=Asia/Shanghai --privileged=true mysql/mysql-server:5.7.34


docker run -itd --name mysql-mgr-master22 -h mysql-mgr-master22 -p 53066:3306 -p 53062:53062 --net=mysql-network-bak -v E:\Docker\mgr1\master2\conf.d:/etc/mysql/conf.d -v E:\Docker\mgr1\master2\conf.d\my.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=SpmsDB_1834shark -e TZ=Asia/Shanghai --privileged=true mysql/mysql-server:5.7.34

docker run -itd --name mysql-mgr-master33 -h mysql-mgr-master33 -p 63066:3306 -p 63063:63063 --net=mysql-network-bak -v E:\Docker\mgr1\master3\conf.d:/etc/mysql/conf.d -v E:\Docker\mgr1\master3\conf.d\my.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=SpmsDB_1834shark -e TZ=Asia/Shanghai --privileged=true mysql/mysql-server:5.7.34

my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

skip-name-resolve

relay-log-recovery=1
server_id=1 #每个节点要不一样
log-bin=mysql-bin
log-slave-updates
binlog-format=row
binlog-checksum=NONE
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction-write-set-extraction=XXHASH64
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4
slave_preserve_commit_order=1

transaction_write_set_extraction = XXHASH64  #以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于检测冲突。
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212889f856' #组的名字可以随便起,但不能用主机的GTID，select uuid()可随机获取一个UUID
loose-group_replication_start_on_boot = off  #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
loose-group_replication_bootstrap_group = off #同上
loose-group_replication_local_address = '172.20.0.2:43061' #写自己主机所在IP
loose-group_replication_group_seeds ='172.20.0.2:43061,172.20.0.3:53062,172.20.0.4:63063'
loose-group_replication_single_primary_mode = off  #关闭单主模式的参数
loose-group_replication_enforce_update_everywhere_checks = on #开启多主模式的参数
loose-group_replication_ip_whitelist="172.20.0.2,172.20.0.3,172.20.0.4"
 
report_host=172.20.0.2
report_port=3306

plugin_load_add='group_replication.so'

Docker run 之后

  • chmod 644 /etc/my.cnf
  • 重启容器
mysql -uroot -pSpmsDB_1834shark

use mysql;

update mysql.user set authentication_string=password('SpmsDB_1834shark') where user='root';


flush privileges;

select @@hostname,@@server_id,@@server_uuid;

show plugins;

SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

CHANGE MASTER TO MASTER_USER='zhongruan', MASTER_PASSWORD='Zhongruan@2021' FOR CHANNEL 'group_replication_recovery';

异常处理

  • 异常一
2024-04-09T23:54:43.347587Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 6a778739-f6ca-11ee-9794-0242c0a84116:1-2 > Group transactions: ce9be252-2b71-11e6-b8f4-00212889f856:1-2'
2024-04-09T23:54:43.347625Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

解决方案：

根据提示打开 set global group_replication_allow_local_disjoint_gtids_join=ON;

开启组复制

M1节点：
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT * FROM performance_schema.replication_group_members;

M2、M3节点：
set global group_replication_single_primary_mode =OFF;
set global group_replication_allow_local_disjoint_gtids_join=ON;
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;

image-20240410080236572

添加访问用户

SET SQL_LOG_BIN=0;
CREATE USER zhongruan@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON spms_sioc_message.* TO 'zhongruan'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

测试

参考：https://blog.csdn.net/zhou920786312/article/details/125072639

二、现有节点安装MySQL Shell

  • 创建用户
SET SQL_LOG_BIN=0;
CREATE USER bootstrapuser@'%' IDENTIFIED BY '123456';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON mysql_innodb_cluster_metadata.* TO 'bootstrapuser'@'%';
GRANT ALL PRIVILEGES ON mysql.user TO 'bootstrapuser'@'%';
GRANT SELECT ON performance_schema.* TO 'bootstrapuser'@'%';
GRANT SELECT ON performance_schema.threads TO bootstrapuser@'%' WITH GRANT OPTION;
GRANT CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHUTDOWN, SUPER ON *.* TO 'bootstrapuser'@'%' WITH GRANT OPTION;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'bootstrapuser'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'bootstrapuser'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'bootstrapuser'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'bootstrapuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
  • 在MGR节点安装mysql-shell-8.0.33-1.el7.x86_64.rpm ，每个节点都安装
yum install -y mysql-shell-8.0.33-1.el7.x86_64.rpm

  • 连接 mysql shell & 创建集群

    • 所有节点都执行

      mysqlsh bootstrapuser@172.20.0.2:3306
dba.checkInstanceConfiguration('bootstrapuser@172.20.0.2:3306')

image-20240417092145536

  • 主节点操作即可

    dba.createCluster('prodCluster',{adoptFromGR: true})
dba.configureLocalInstance('bootstrapuser@172.20.0.2:3306')
dba.getCluster("prodCluster").status()

    ​ image-20240417092156592

  • 如果有节点下线，则显示异常

image-20240417092233215

三、MySQL Router

docker run --name spms-mysql-router-1 -p 3310:3310 --net=mysql-network-bak -itd centos:centos7
docker run --name spms-mysql-router-2 -p 3320:3320 --net=mysql-network-bak -itd centos:centos7

  • 下载mysql-router-8.0.33-el7-x86_64.tar.gz包
tar -zxvf /opt/mysql-router-8.0.33-el7-x86_64.tar.gz
ln -s /opt/mysql-router-8.0.19-el7-x86_64 /usr/local/mysqlrouter

vi ~/.bashrc
## 新增内容 ##
export MYSQL_ROUTER_HOME=/usr/local/mysqlrouter
export PATH=$PATH:$MYSQL_ROUTER_HOME/bin
mysqlrouter -c /usr/local/mysqlrouter/share/doc/mysqlrouter/mysqlrouter.conf
# 使配置文件生效
source ~/.bashrc

#从示例配置中复制一份配置文件
cp /usr/local/mysqlrouter/share/doc/mysqlrouter/sample_mysqlrouter.conf /usr/local/mysqlrouter/share/doc/mysqlrouter/mysqlrouter.conf

# 创建秘钥
mysqlrouter_keyring init --master-key-file=mysqlrouter.key /usr/local/mysqlrouter/keyring
mysqlrouter_keyring set --master-key-file=mysqlrouter.key /usr/local/mysqlrouter/keyring bootstrapuser password 123456
  • 配置文件
# Copyright (c) 2018, 2023, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.

# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.

# 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


# MySQL Router sample configuration
#
# The following is a sample configuration file which shows
# most of the plugins available and most of their options.
#
# The paths used are defaults and should be adapted based
# on how MySQL Router was installed, for example, using the
# CMake option CMAKE_INSTALL_PREFIX
#
# The logging_folder is kept empty so message go to the
# console.
#

[DEFAULT]
logging_folder = /usr/local/mysqlrouter/logs
#plugin_folder = /usr/local/lib/mysqlrouter
#config_folder = /etc/mysql
#runtime_folder = /var/run
#data_folder = /var/lib
#keyring_path = /var/lib/keyring-data
#master_key_path = /var/lib/keyring-key
keyring_path=/usr/local/mysqlrouter/keyring
master_key_path=/usr/local/mysqlrouter/mysqlrouter.key

[metadata_cache:basic]
router_id=1
auth_cache_refresh_interval=2
auth_cache_ttl=-1
bootstrap_server_addresses=mysql://172.20.0.2:3306,mysql://172.20.0.3:3306,mysql://172.20.0.4:3306
cluster_type=gr
user=bootstrapuser
metadata_cluster=prodCluster
ttl=0.5
use_gr_notifications=0

[logger]
level = INFO
#filename = mysqlrouter.log
#timestamp_precision = second

[routing:basic_failover_rw]
# To be more transparent, use MySQL Server port 3306
bind_address = 0.0.0.0
bind_port = 3310
routing_strategy = round-robin
#mode = read-write
#destinations = 172.20.0.2:3306,172.20.0.3:3306,172.20.0.4:3306
destinations = metadata-cache://basic/?role=PRIMARY

# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval = 60

image-20240417093025040

四、防火墙相关操作

firewall-cmd --zone=public --add-port=6800/tcp --permanent; 

firewall-cmd --reload;  

firewall-cmd --list-all

五、场景测试

  • Jar 直接 loadbalance MGR 集群，如果某个节点挂了，读操作正常，写操作异常
0
MGR
版权归属： 叶子
本文链接： https://www.benbenye.top/archives/windowsdocker-bu-shu-mgrmysqlshellmysqlrouter-zhen-zheng-gao-ke-yong-
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区