[系统运维]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
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

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应用程序，连接路由节点，且任意宕掉其中一个或两个实例节点后，应用程序不受影响。

mysql 8.0安装

一、删除已有mysql

rpm -qa | grep mysql　　查找是否存在已安装的mysql
yum remove mysql-community-common-x.x.xx-1.el7.x86_64　　如果找到，则依次卸载全部安装包
find / -name *mysql*　　查找mysql的残余文件
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':'Mysql123!'@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