Carry の Blog
Carry の Blog
2023-04-13
目录

MySQLReplicaSet 安装原创

# 1. 部署环境及初始化

为了简单起见，建议用yum方式标准化安装MySQL Shell、Router

  1. MySQL官方的yum源，需要先下载安装repo包，下载地址：
yum -y install https://dev.mysql.com/get/mysql80-community-release-el7-4.noarch.rpm
1
  1. 用yum安装MySQL相关的软件包了
 yum list|grep mysql-shell

yum install -y mysql-shell mysql-router
#也可以用rpm包安装
 wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.34-1.el7.x86_64.rpm  
 wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-community-8.0.34-1.el7.x86_64.rpm
1
2
3
4
5
6
7
  1. 网络调整
sudo iptables -I INPUT -s 192.168.01/32 -p tcp -j ACCEPT
sudo iptables -I INPUT -s 192.168.02/32 -p tcp -j ACCEPT
sudo iptables -I INPUT -s 192.168.03/32 -p tcp -j ACCEPT
sudo service iptables save
1
2
3
4
  1. CPU磁盘内存调整
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo 'echo never > /sys/kernel/mm/transparent_hugepage/defrag' >> /etc/rc.d/rc.local

cat /sys/block/sdb/queue/scheduler
echo noop >/sys/block/sdb/queue/scheduler

cpupower frequency-info --governors

cpupower frequency-set --governor performance

cpupower frequency-info --policy
1
2
3
4
5
6
7
8
9
10
11
  1. 运行GreatSQL可能需要依赖jemalloc库，因此请先安装上
yum -y install jemalloc jemalloc-devel

#检查
[root@greatdb]# ldconfig
[root@greatdb]# ldconfig -p | grep libjemalloc
1
2
3
4
5
  1. /etc/hosts绑定IP和主机名防止创建mgr时报错 192.168.01 mytest-dbtest01 192.168.02 mytest-dbtest02 192.168.03 mytest-dbtest03

# 安装mysql

  1. 步骤省略。
  2. 修改root密码
SET SQL_LOG_BIN=0;

alter user 'root'@'localhost' identified by '';

rename user 'root'@'localhost' to 'admin'@'localhost';

SET SQL_LOG_BIN=1;
1
2
3
4
5
6
7
  1. 避免使用root请创建mgr_admin用户
create user `dba_admin`@`192.168.0%` identified by '';

GRANT all privileges ON *.* TO `dba_admin`@`192.168.0%` WITH GRANT OPTION;
1
2
3

# 利用MySQL Shell构建MGR集群

  1. 登录第一台mysql
mysqlsh --uri dba_admin@192.168.01:3366
1
  1. 检查配置
dba.configureInstance();
dba.checkInstanceConfiguration('dba_admin@192.168.02:3366');
dba.checkInstanceConfiguration('dba_admin@192.168.03:3366');
1
2
3
  1. 创建集群
var rs = dba.createReplicaSet("My_replicaset")
1

如果失败或者报错可以删掉再来

dba.dropMetadataSchema();
1
  1. 添加节点
rs.addInstance('dba_admin@192.168.02:3366');
1

选择默认克隆模式添加直接回车 重复以上操作添加所有节点

  1. 查看状态以下命令均可查看 首先定义var
var rs = dba.getReplicaSet()
1

然后执行

rs.status();
rs.status({extended:1});
1
2
  1. 手动切换主从
var rs = dba.getReplicaSet()
rs.setPrimaryInstance('192.168.02:3366') # 主库运行时，可执行。否则会有以下报错

rs.forcePrimaryInstance('192.168.02:3366') # 强制切主
1
2
3
4
ERROR: Unable to connect to the PRIMARY of the ReplicaSet Core_rs: MYSQLSH 51118: Could not open connection to '192.168.01:3366': Can't connect to MySQL server on '192.168.01:3366' (110)
1
  1. 移除节点
 rs.removeInstance('192.168.01:3366') # 手动移除节点，节点已宕机的情况会报错
 ERROR: Unable to connect to the target instance 192.168.01:3366. Please make sure the instance is available and try again. If the instance is permanently not reachable, use the 'force' option to remove it from the replicaset metadata and skip reconfiguration of that instance.
1
2
rs.removeInstance('192.168.01:3366',{force:true}) # 强制移除宕机节点
NOTE: Unable to connect to the target instance 192.168.01:3366:3366. The instance will only be removed from the metadata, but its replication configuration cannot be updated. Please, take any necessary actions to make sure that the instance will not replicate from the replicaset if brought back online.
1
2
3

# 部署MySQL Router，实现读写分离以及故障自动转移

MySQL Router是一个轻量级的中间件，它采用多端口的方案实现读写分离以及读负载均衡，而且同时支持mysql和mysql x协议。

mysqlrouter初始化 MySQL Router对应的服务器端程序文件是 /usr/bin/mysqlrouter，第一次启动时要先进行初始化

# 参数解释

参数 --bootstrap 表示开始初始化

参数 dba_admin@192.168.01:3366 是MGR集群管理员账号

--user=mysqlrouter 是运行mysqlrouter进程的系统用户名

[root@greatsql]# mysqlrouter --bootstrap dba_admin@192.168.01:3366 --user=mysqlrouter

Please enter MySQL password for mic: <-- 输入密码
# 然后mysqlrouter开始自动进行初始化
# 它会自动读取MGR的元数据信息，自动生成配置文件
1
2
3
4
5

4.2 启动mysqlrouter服务 这就初始化完毕了，按照上面的提示，直接启动 mysqlrouter 服务即可：

[root@greatsql]# systemctl start mysqlrouter


检查
ps -ef | grep -v grep | grep mysqlrouter

netstat -lntp | grep mysqlrouter
1
2
3
4
5
6
7

mysqlrouter 初始化时自动生成的配置文件是 /etc/mysqlrouter/mysqlrouter.conf，主要是关于R/W、RO不同端口的配置

[routing:greatsqlMGR_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://greatsqlMGR/?role=PRIMARY
routing_strategy=first-available
protocol=classic
1
2
3
4
5
6

可以根据需要自行修改绑定的IP地址和端口。

4.3 确认读写分离效果 现在，用客户端连接到6446（读写）端口，确认连接的是PRIMARY节点：

[root@greatsql]# mysql -h192.168.01 -u dba_admin -p -P6446

mic@GreatSQL [(none)]>select @@server_uuid;
1
2
3

4.4 关闭ssl避免跟客户端应用不兼容通讯协议

分别登录mysqlrouter 的服务器，更改配置

 sudo vim /etc/mysqlrouter/mysqlrouter.conf

client_ssl_mode=DISABLED
server_ssl_mode=DISABLED
server_ssl_verify=DISABLED
1
2
3
4
5

分别重启mysqlrouter

sudo systemctl restart mysqlrouter.service
1

4.5 从元数据库剔除mysqlrouter节点

var rs = dba.getReplicaSet()
rs.listRouters()

rs.removeRouterMetadata('192.168.01::system');
1
2
3
4
#MySQL
上次更新: 8/7/2026

← 脚本实现MySQL ReplicaSet 高可用→

最近更新
01
Browserless 使用笔记：把 Chrome 变成一个可以被并发调用的网络服务
08-07
02
ORDER BY 配合 LIMIT 触发的索引选择陷阱 原创
08-07
03
Nginx 运维知识地图：从配置基础到反向代理实战 原创
07-29
更多文章>
Theme by Vdoing