CentOS 7部署MySQL 8.0 MGR集群

MGR集群节点规划

节点名称	MySQL版本	节点IP地址
node1	8.0.28	10.30.162.51
node2	8.0.28	10.30.162.52
node3	8.0.28	10.30.162.53

各节点添加所有节点的hosts解析记录

cat >> /etc/hosts <<EOF
10.30.162.51  node1
10.30.162.52  node2
10.30.162.53  node3
10.30.162.54  node4
EOF

所有集群节点部署MySQL实例

curl http://download-soft.lead.cn/MySQL/linux/mysql80-general-binary-install.sh | bash

选第一个主节点进行集群初始化操作

# 1. 修改主节点配置文件
cat > /etc/my.cnf <<EOF
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

server_id=51
gtid_mode=ON
enforce_gtid_consistency=ON

binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "10.30.162.51:33061"
group_replication_group_seeds= "10.30.162.51:33061,10.30.162.52:33061,10.30.162.53:33061,10.30.162.54:33061"
[root@node1 ~]# cat /etc/my.cnf 
[client]
port=3306
socket=/data/mysql3306.socket

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql3306
port=3306
socket=/data/mysql3306.socket

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

server_id=51   # （跟集群中的其它节点必须区分开）
gtid_mode=ON
enforce_gtid_consistency=ON

binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

# MGR集群组复制功能配置项
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "10.30.162.51:33061" # （跟集群中的其它节点必须区分开）
group_replication_group_seeds= "10.30.162.51:33061,10.30.162.52:33061,10.30.162.53:33061" # （集群所有节点都要有）
group_replication_bootstrap_group=off

# buffers & cache
table_open_cache=2048
table_definition_cache=2048
max_heap_table_size=96M
sort_buffer_size=128K
join_buffer_size=128K
thread_stack=192K
tmp_table_size=96M
key_buffer_size=8M
read_buffer_size=2M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=32M

# innodb
innodb_buffer_pool_size=128M
innodb_buffer_pool_instances=1
innodb_data_file_path=ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=8M
innodb_log_file_size=200M
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=50
innodb_file_per_table=1
innodb_rollback_on_timeout
innodb_status_file=1
innodb_io_capacity=100
innodb_flush_method=O_DIRECT


[mysql]
prompt="\u@\h [\d]>" 
pager="less -i -n -S"
no-auto-rehash
EOF
# 2. 如果没有初始化过数据目录，需要执行初始化,实际执行上面的安装脚本已经初始化（此处跳过）
mysqld --initialize-insecure --user=mysql
# 3. 启动MySQL实例
mysqld_safe --user=mysql &
# 4. 修改root密码并创建同步账号
SET SQL_LOG_BIN=0;
alter user root@localhost identified with mysql_native_password by '123456';
CREATE USER repl@'%' IDENTIFIED with mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
GRANT CONNECTION_ADMIN ON *.* TO repl@'%';
GRANT BACKUP_ADMIN ON *.* TO repl@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
# 5. 开启同步操作
CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
# 6. 检查group_replication.so插件是否成功激活
show plugins;
# 7. 只在第一个节点上启用引导组
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='repl', PASSWORD='123456';
SET GLOBAL group_replication_bootstrap_group=OFF;
# 8. 查看组成员信息并确定是否只有一个Primary且是online状态
SELECT * FROM performance_schema.replication_group_members;

依次（添加完一个再操作下一个）在余下的两个从节点上执行如下操作

# 1. MySQL实例部署及数据目录初始化操作
  # 如已初始化就跳过
# 2. 修改MySQL配置文件
cat > /etc/my.cnf <<EOF
[client]
port=3306
socket=/data/mysql3306.socket

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql3306
port=3306
socket=/data/mysql3306.socket

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

server_id=53  # 集群各节点必须区分开
gtid_mode=ON
enforce_gtid_consistency=ON

binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="10.30.162.52:33061"  #(此处为各节点的地址和端口号，不同节点不一样)
group_replication_group_seeds="10.30.162.51:33061,10.30.162.52:33061,10.30.162.53:33061"
group_replication_bootstrap_group=off



# buffers & cache
table_open_cache=2048
table_definition_cache=2048
max_heap_table_size=96M
sort_buffer_size=128K
join_buffer_size=128K
thread_stack=192K
tmp_table_size=96M
key_buffer_size=8M
read_buffer_size=2M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=32M

# innodb
innodb_buffer_pool_size=128M
innodb_buffer_pool_instances=1
innodb_data_file_path=ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=8M
innodb_log_file_size=200M
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=50
innodb_file_per_table=1
innodb_rollback_on_timeout
innodb_status_file=1
innodb_io_capacity=100
innodb_flush_method=O_DIRECT


[mysql]
prompt="\u@\h [\d]>"
pager="less -i -n -S"
no-auto-rehash
EOF

# 3. 启动节点上的MySQL实例
mysqld_safe --user=mysql &
# 4. 修改root密码并创建同步帐号
SET SQL_LOG_BIN=0;
--alter user root@localhost identified with mysql_native_password by '123456';
CREATE USER repl@'%' IDENTIFIED with mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
GRANT CONNECTION_ADMIN ON *.* TO repl@'%';
GRANT BACKUP_ADMIN ON *.* TO repl@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
# 5. 开启同步
CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
# 6. 检查插件是否成功激活，没激活需要确保激活
show plugins;
# 7. 启用组Secondary节点
# 不需要对参数group_replication_bootstrap_group进行任何处理
START GROUP_REPLICATION USER='repl', PASSWORD='123456';
# 8. 查看组成员信息
SELECT * FROM performance_schema.replication_group_members;
# 确保组成员中一个主节点和一个从节点，且都是online状态

# 第三个节点重复以上操作添加

三个节点都加入集群后操作

# 测试操作
# 在主节点上创建数据库及表，查看各节点间数据是否正常同步
create database d1;
use d1
create table t1(id int primary key);
insert into t1 values(1), (11), (111);

# 在从库上都查看是否正常同步
select * from d1.t1;

# 如能正常在从库上查询到主库的修改，则集群正常