Centos7.9搭建MySQL-8.0.32 MGR集群

环境准备:

三台centos7.9系统

192.168.20.6 master

192.168.20.7 node1

192.168.20.8 node2

Mysql版本:8.0.32

下载地址: https://dev.mysql.com/downloads/mysql/

操作步骤:

1.前提条件(三台)

1.1关闭防火墙

systemctl stop firewalld&& systemctl disable firewalld

1.2关闭selinux

vi /etc/selinux/config

改为disabled

1.3修改hostname主机名

vi /etc/hostname

reboot重启

重启之后也查看一下selinux状态

getenforce

disable 是永久关闭selinux

1.4添加host

vi /etc/hosts

2.安装mysql数据库(三台)

2.1删除所有mysql,mariadb

rpm -qa |grep mysql

rpm -qa |grep mariadb

rpm -e qt-mysql-4.8.7-8.el7.x86_64 –nodeps

rpm -e akonadi-mysql-1.9.2-4.el7.x86_64 –nodeps

rpm -e mariadb-server-5.5.68-1.el7.x86_64 –nodeps

rpm -e mariadb-5.5.68-1.el7.x86_64 –nodeps

rpm -e mariadb-libs-5.5.68-1.el7.x86_64 –nodeps

2.2安装到home目录下

mkdir /home/mysql

mv /root/mysql-8.0.32-el7-x86_64.tar.gz/home/mysql/

cd /home/mysql

tar -zxvf mysql-8.0.32-el7-x86_64.tar.gz

mv mysql-8.0.32-el7-x86_64 mysql-8.0.32

mkdir -p /home/mysql/data

2.3创建用户和组

groupadd mysql

useradd -g mysql mysql

chown -R mysql.mysql /home/mysql/

chmod -R 755 /home/mysql/

2.4修改配置文件

vi /etc/my.cnf

[client]

port=3306

socket=/home/mysql/mysql.sock

default-character-set = utf8mb4

[mysql]

default-character-set = utf8mb4

[mysqld]

server-id=1 #注意三台机器必须不同用1 2 3即可

port=3306

user=mysql

socket=/home/mysql/mysql.sock

basedir=/home/mysql/mysql8.0.32

datadir=/home/mysql/data

lower-case-table-names=1

character_set_server=utf8mb4

2.5初始化

./mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql--lower-case-table-names=1 --basedir=/home/mysql/mysql-8.0.32--datadir=/home/mysql/data

2.6配置环境变量

vi /etc/profile

export MYSQL_HOME=/home/mysql/mysql-8.0.32

export PATH=$MYSQL_HOME/bin:$PATH

末尾添加

2.7启动数据库

cp /home/mysql/mysql-8.0.32/support-files/mysql.server/etc/init.d/mysql

service mysql start

mysql -u root -p

3.配置MGR集群

3.1增加my.cnf配置文件(三台)

除主节点192.168.20.6外其他节点只需要修改loose-group_replication_local_address

和report_host即可。

vi /etc/my.cnf

log_bin=binlog-bin

log_slave_updates=ON

binlog_format=ROW

binlog_checksum=NONE

master_info_repository=TABLE

relay_log_info_repository=TABLE

gtid_mode=ON

enforce_gtid_consistency=true

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"

loose-group_replication_start_on_boot=OFF

loose-group_replication_local_address="192.168.20.6:33061"

loose-group_replication_group_seeds="192.168.20.6:33061,192.168.20.7:33061,192.168.20.8:33061"

loose-group_replication_bootstrap_group=OFF

report_host=192.168.20.6

report_port=3306

loose-group_replication_recovery_get_public_key=ON

3.2 重新启动mysql(三台)

service mysql restart

3.3增加mgr复制用户(三台)

SET SQL_LOG_BIN=0;

CREATE USER mgruser@'%' IDENTIFIED BY'mgruser';

GRANT REPLICATION SLAVE ON *.* TOmgruser@'%';

FLUSH PRIVILEGES;

SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='mgruser',MASTER_PASSWORD='mgruser' FOR CHANNEL 'group_replication_recovery';

3.4下载mgr插件(三台)

install PLUGIN group_replication SONAME'group_replication.so';

查看是否安装成功

show plugins;

3.5启用mgr单主模式

3.5.1:主节点执行(20.6)

SET GLOBAL group_replication_bootstrap_group=ON;

START GROUP_REPLICATION;

SET GLOBALgroup_replication_bootstrap_group=OFF;

查看mgr组信息

SELECT * FROMperformance_schema.replication_group_members;

可以看到master已经成功启动

3.5.2从节点执行(20.7和20.8)

START GROUP_REPLICATION;

3.6查看mgr集群状态

SELECT * FROMperformance_schema.replication_group_members;

4.测试mgr群集读写

4.1创建数据库(master)

create database mgr;

查看数据库是否建成

show databases;

4.2在创建的库里面创建表和插入数据(master)

进去创建的库

use mgr;

创建表

CREATE TABLE `user` (

`id` bigint NOT NULL ,

`account` varchar(30) NOT NULL ,

`name` varchar(50) NOT NULL ,

PRIMARY KEY (`id`) USING BTREE,

UNIQUE KEY `UN_ACCOUNT` (`account`) USING BTREE

插入数据

INSERT INTO user VALUES (1, 'zhangsan', '张三');

INSERT INTO user VALUES (2, 'lisi', '李四');

4.3从库(20.7和20.8)里面查询库和表信息

查库

show databases;

查表

show tables;

查看数据

select * from user;

尝试用从库删除user表

delete from user

删除失败

因为从库只有查询的权限，没有更改的权限