centos7下搭建Mysql8.0集群

MySQL8.0搭建MGR集群(MySQL-shell、MySQL-router)

参考url:https://www.cnblogs.com/javasl/p/14653506.html

一、环境准备

服务器:192.168.111.129、192.168.111.130、192.168.111.131,在三台服务器上分别安装MySQL及MySQL-shell

安装MySQL:https://www.cnblogs.com/javasl/p/14650316.html

安装MySQL-shell:https://www.cnblogs.com/javasl/p/14652345.html

二、MySQL配置

修改配置文件 /etc/my.cnf

node01服务器
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 不同点
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 = '5dbabbe6-8050-49a0-9131-1de449167446'
loose-group_replication_start_on_boot = OFF
# 不同点:ip和端口号
loose-group_replication_local_address = '192.168.111.129:24901'
loose-group_replication_group_seeds = '192.168.111.129:24901,192.168.111.130:24902,192.168.111.131:24903'
loose-group_replication_bootstrap_group = OFF
node02服务器
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
gtid_mode=on
enforce_gtid_consistency=on
binlog_checksum=none
log_slave_updates = ON
loose-group_replication_recovery_get_public_key= ON
loose-group_replication_recovery_use_ssl= ON
loose-group_replication_group_name = '5dbabbe6-8050-49a0-9131-1de449167446'
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = '192.168.111.130:24902'
loose-group_replication_group_seeds = '192.168.111.129:24901,192.168.111.130:24902,192.168.111.131:24903'
loose-group_replication_bootstrap_group = OFF
node03服务器
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=3
gtid_mode=on
enforce_gtid_consistency=on
binlog_checksum=none
log_slave_updates = ON
loose-group_replication_recovery_get_public_key= ON
loose-group_replication_recovery_use_ssl= ON
loose-group_replication_group_name = '5dbabbe6-8050-49a0-9131-1de449167446'
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = '192.168.111.131:24903'
loose-group_replication_group_seeds = '192.168.111.129:24901,192.168.111.130:24902,192.168.111.131:24903'
loose-group_replication_bootstrap_group = OFF

三、服务器配置

1)映射3台服务器的主机名(每台服务器都如下配置)。文件路径:/etc/hosts

192.168.111.129 mysql129
192.168.111.130 mysql130
192.168.111.131 mysql131

2)关闭3台服务器的SELINUX(暂时简单粗暴的处理)。文件路径:/etc/selinux/config

SELINUX=disabled

3)关闭3台服务器的防火墙(暂时简单粗暴的处理)

systemctl disable firewalld

4)设置3台服务器之间免秘钥登录。分别在129\130\131服务器上执行

ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.111.129
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.111.130
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.111.131

服务器配置完重启一下

四、创建集群

1、准备工作

1)每台机器的mysqlsh都连接一下3台机器的MySQL(三台服务器上都执行如下操作),以129举例。

# 执行命令,进入mysql-shell环境里
mysqlsh

MySQL  JS > \c root@mysql129:3306
MySQL  JS > \c root@mysql130:3306
MySQL  JS > \c root@mysql131:3306

2)3台服务器都执行如下操作,以129服务器举例

# 129
MySQL  mysql129:3306 ssl  JS > dba.configureInstance();
MySQL  mysql129:3306 ssl  JS > dba.checkInstanceConfiguration("root@mysql129:3306");

# 130
MySQL  mysql130:3306 ssl  JS > dba.configureInstance();
MySQL  mysql130:3306 ssl  JS > dba.checkInstanceConfiguration("root@mysql130:3306");

# 131
MySQL  mysql131:3306 ssl  JS > dba.configureInstance();
MySQL  mysql131:3306 ssl  JS > dba.checkInstanceConfiguration("root@mysql131:3306");
2、创建集群

1)安装组复制插件,3台服务器器都执行如下操作

# 进入mysql执行
mysql > INSTALL PLUGIN group_replication SONAME 'group_replication.so';

请添加图片描述

2)创建集群(只在一台服务器执行即可,因为此时在任何一台服务器上都可以连接3台mysql服务)

备注:只在mysql主节点里执行,怎么判断哪台是主节点呢?先尝试执行,如果是只读的话,则换一台执行,或者在谁执行,谁就是主节点
# 进入mysql-shell环境
mysqlsh
MySQL  mysql129:3306 ssl  JS > var cluster = dba.createCluster('myCluster');

请添加图片描述

查看此时集群的状态

请添加图片描述

3)添加节点130、131

# 获取集群节点
var cluster = dba.getCluster("myCluster")

cluster.addInstance('root@mysql129:3306');
cluster.addInstance('root@mysql130:3306');
cluster.addInstance('root@mysql131:3306');

请添加图片描述

5)查看集群状态

cluster.status()

6)查询节点信息(随便登录一个节点)

mysql > select * from performance_schema.replication_group_members;

请添加图片描述

到这里其实集群已经搭建好了,可以连接每个节点的数据库进行操作

五、安装MySQL-router

router_master 主服务器(读写):192.168.111.131
router_slave01 从服务器(读,热备份):192.168.111.130  又为主,又为从
router_slave02 从服务器(读):192.168.111.129
mysql-router 路由服务器:192.168.111.128
# 在路由服务器上安装MySQL-router
1、下载:https://downloads.mysql.com/archives/router/
wget https://cdn.mysql.com/archives/mysql-router/mysql-router-community-8.0.20-1.el7.x86_64.rpm

2、安装:
rpm -ivh mysql-router-community-8.0.20-1.el7.x86_64.rpm

3、配置:vim /etc/mysqlrouter/mysqlrouter.conf 添加如下配置:
# 主节点故障转移配置
[routing:basic_failover]
# 宿主机的ip(路由服务器的ip)
bind_address=192.168.111.128
# 写节点端口
bind_port = 7001
# 模式,读写
mode = read-write
# 主节点地址:默认情况下第一台主数据库为写主库,当第一台主数据库DOWN机后,第二台数据库被提升为主库
destinations = 192.168.111.131:3306,192.168.111.130:3306

# 从节点负载均衡配置
[routing:balancing]
# 宿主机的ip(路由服务器的ip)
bind_address=192.168.111.128
# 监听的端口
bind_port = 7002
# 连接超时时间
connect_timeout = 3
# 最大连接数
max_connections = 1024
# 后端服务器地址
destinations = 192.168.111.130:3306,192.168.111.129:3306
# 模式:读还是写
mode = read-only
4、后台启动:
mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &

六、测试集群

1、宕掉主节点A后,会从另外两个中选择出一个作为主节点,且A恢复后,不会成为主节点。

2、使用客户端工具Navicat可以连接路由节点,IP:192.168.111.131,端口7001,用户名密码与实例节点一致。

3、JavaWeb应用程序,连接路由节点,且任意宕掉其中一个或两个实例节点后,应用程序不受影响。

七、服务器重启

1)重新创建mgr消息

# 每个节点上
记得 清理残留的mgr信息
dba.dropMetadataSchema()

# 129
MySQL  mysql129:3306 ssl  JS > dba.configureInstance();
MySQL  mysql129:3306 ssl  JS > dba.checkInstanceConfiguration("root@mysql129:3306");

# 130
MySQL  mysql130:3306 ssl  JS > dba.configureInstance();
MySQL  mysql130:3306 ssl  JS > dba.checkInstanceConfiguration("root@mysql130:3306");

# 131
MySQL  mysql131:3306 ssl  JS > dba.configureInstance();
MySQL  mysql131:3306 ssl  JS > dba.checkInstanceConfiguration("root@mysql131:3306");

2)创建集群(只在一台服务器执行即可,因为此时在任何一台服务器上都可以连接3台mysql服务)

备注:只在mysql主节点里执行,怎么判断哪台是主节点呢?先尝试执行,如果是只读的话,则换一台执行,或者在谁执行,谁就是主节点
# 进入mysql-shell环境
mysqlsh
MySQL  mysql129:3306 ssl  JS > var cluster = dba.createCluster('myCluster');

请添加图片描述

3)添加节点130、131

# 获取集群节点
var cluster = dba.getCluster("myCluster")

cluster.addInstance('root@mysql129:3306');
cluster.addInstance('root@mysql130:3306');
cluster.addInstance('root@mysql131:3306');

请添加图片描述

5)查看集群状态

cluster.status()

6)查询节点信息(随便登录一个节点)

mysql > select * from performance_schema.replication_group_members;

请添加图片描述

重启完成

八、重启遇到的问题

1、清理残留的mgr信息

dba.dropMetadataSchema()

Dba.checkInstanceConfiguration: Dba.checkInstanceConfiguration: This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) (RuntimeError)

mysql 8.0安装

一、删除已有mysql

rpm -qa | grep mysql  查找是否存在已安装的mysql
yum remove mysql-community-common-x.x.xx-1.el7.x86_64  如果找到,则依次卸载全部安装包
rpm -qa | grep mysql | xargs yum remove -y  # 批量删除
find / -name *mysql*  查找mysql的残余文件
find / -name *mysql* | xargs rm -rf  # 批量删除
rm -rf /usr/lib64/mysql  如果找到,则依次删除所有残余文件

二、安装mysql服务

1.下载地址:https://downloads.mysql.com/archives/community/,本文选择8.0.20,依次下载、安装如下文件

# 下载
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-common-8.0.20-1.el7.x86_64.rpm
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-libs-8.0.20-1.el7.x86_64.rpm
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-client-8.0.20-1.el7.x86_64.rpm
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-server-8.0.20-1.el7.x86_64.rpm

# 安装
rpm -ivh mysql-community-common-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.20-1.el7.x86_64.rpm

# 可能遇到的问题
问题1.linux安装mysql8.0.20 依赖检测失败:mariadb-libs is obsoleted by mysql-community-libs-8.0.20-1.el7.x86_64
执行命令:yum remove mysql-libs

问题2.MYSQL安装出现error: Failed dependencies:net-tools is needed by mysql-community-libs-8.0.20-1.el7.x86_64报错
执行命令:yum install net-tools.x86_64

2、启动mysql服务

systemctl start mysqld

# 启动mysql
service mysqld start

# 关闭mysql
service mysqld stop

# 查看启动状态
service mysqld status

# 重启mysql
service mysqld restart

# 设置开机启动
systemctl enable mysqld
systemctl daemon-reload

3、查看临时密码

grep 'temporary password' /var/log/mysqld.log

4、使用临时密码登录

mysql -uroot -p

5、修改临时密码为自己指定密码

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

6、创建远程访问用户和密码、并赋予权限

# 创建用户并指定密码
create user 'root'@'%' identified with mysql_native_password by 'Mysql123!';
create user 'bot'@'%' identified with mysql_native_password by 'Mysql123!';
# 授权 可以远程连接
grant all privileges on *.* to 'root'@'%' with grant option;
grant all privileges on botzero_dev.* to 'bot'@'%' with grant option;
# 刷新权限
flush privileges;

安装MySQL-shell

一、说明

MySQL Shell是自MySQL 5.7.12开始的高级命令行工具,支持两种模式(交互式&批处理式)、三种语言(javascript\python\sql)

下载地址:https://downloads.mysql.com/archives/shell/,本文选择8.0.20,下载并安装

# 下载
wget https://cdn.mysql.com/archives/mysql-shell/mysql-shell-8.0.20-1.el7.x86_64.rpm

rpm -ivh mysql-shell-8.0.20-1.el7.x86_64.rpm

二、用法

1、连接到服务

1)方法一,进入shell,输入用户名、输入密码

[root@localhost ~]# mysqlsh
 MySQL  JS > \c root@localhost:3306   # 输入密码即可登录

2)方法二,shell中指定用户名

[root@localhost ~]# mysqlsh root@localhost:3306
输入密码即可登录

3)方法三,shell中指定用户名、密码

[root@localhost ~]# mysqlsh 'root':'shenL1234!'@localhost:3306  或
[root@localhost ~]# mysqlsh 'root'@'localhost':3306 --password=Mysql123!

4)方法四,连接的同时指定数据库

[root@localhost ~]# mysqlsh 'root':'Mysql123!'@localhost:3306/testDB  或
[root@localhost ~]# mysqlsh 'root'@'localhost':3306/testDB --password=Mysql123!

2、退出命令行

MySQL  localhost:3306 ssl  JS > \quit
MySQL  localhost:3306 ssl  JS > \exit
  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值