MySQL8.0MGR单主/多主安装与切换

一、基础环境配置

1.1 关闭防火墙和selinux

1.1.1 关闭防火墙

systemctl stop firewalld.service #停止防火墙
systemctl disable firewalld.service #禁止开机启动

1.1.2 关闭selinux

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux
cat /etc/sysconfig/selinux

1.2 配置IP域名映射

vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.118 zj1
192.168.0.119 zj2
192.168.0.120 zj3

1.3 更改I/O调度模式

echo deadline > /sys/block/sda/queue/scheduler
cat /sys/block/sda/queue/scheduler

1.4 设置swap分区大小

echo "vm.swappiness = 0" >>/etc/sysctl.conf
sysctl -p
#通过sysctl -a 查看
PS :swappiness值在0-100之间，0尽力使用物理内存，100尽力使用swap分区。

1.5 文件系统选择

  文件系统最好选择xfs，它比ext4更方便管理。

1.6 操作系统限制

cat /etc/security/limits.conf
...
* soft nproc 65536
* hard nproc 65536
* soft nofile 65536
* hard nofile 65536
...

1.7 关闭numa

# sed -i 's/GRUB_CMDLINE_LINUX.*/GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=centos\/root rd.lvm.lv=centos\/swap rhgb quiet numa=off"/g' /etc/default/grub
# grub2-mkconfig -o /etc/grub2.cfg
# cat /etc/grub2.cfg
# reboot
# cat /proc/cmdline
# dmesg | grep -i numa

二、实例安装

2.1 创建MySQL用户组和用户

# groupadd mysql
# useradd -g mysql mysql -s /sbin/nologin
# id mysql

2.2 创建相关目录

# mkdir -p /usr/local/mysql/{program,data,conf} 
# mkdir -p /usr/local/mysql/data/{data,sock,tmpdir,log,ib,redo,undo,slowlog,binlog,relaylog}

2.3 解压安装包并设置权限

# tar xf mysql-8.0.17-el7-x86_64.tar -C /usr/local/mysql/program/
# cd /usr/local/mysql/program
# tar xf mysql-8.0.17-el7-x86_64.tar.gz
# tar xf mysql-test-8.0.17-el7-x86_64.tar.gz
# chown mysql:mysql /usr/local/mysql -R

2.4 软链接程序目录并配置环境变量

ln -s /usr/local/mysql/program/mysql-8.0.17-el7-x86_64 /usr/local/mysql/program/mysql
export PATH=$PATH:/usr/local/mysql/program/mysql/bin/
echo 'export PATH=$PATH:/usr/local/mysql/program/mysql/bin/' >> /etc/profile
# 查看环境变量配置是否成功添加到/etc/profile文件中
tail -1 /etc/profile 

2.5 修改配置文件

#根据实际情况配置
[client]
socket=/usr/local/mysql/data/sock/mysql.sock #sock文件所在路径
[mysqld]
user=mysql
server-id=1
basedir=/usr/local/mysql/program/mysql
socket=/usr/local/mysql/data/sock/mysql.sock
pid-file=/usr/local/mysql/data/sock/mysql.pid
datadir=/usr/local/mysql/data/data
tmpdir=/usr/local/mysql/data/tmpdir
log-error=/usr/local/mysql/data/log/error.log
slow_query_log=1
log_queries_not_using_indexes=1
slow_query_log_file=/usr/local/mysql/data/slowlog/slow-query.log
log-bin=/usr/local/mysql/data/binlog/mysql-bin
relay-log=/usr/local/mysql/data/relaylog/mysql-relay-bin
innodb_data_home_dir=/usr/local/mysql/data/ib
innodb_log_group_home_dir=/usr/local/mysql/data/redo
innodb_undo_directory=/usr/local/mysql/data/undo/
innodb_undo_tablespaces=3
character_set_server=utf8mb4
#MGR使用乐观锁,所以官网建议隔离级别是RC,减少锁粒度
transaction_isolation=READ-COMMITTED
gtid_mode=on
enforce_gtid_consistency=1
binlog_format=row
secure_file_priv=/tmp
performance_schema=ON
innodb_redo_log_archive_dirs=/usr/local/mysql/data/redo_archive
#因为集群会在故障恢复时互相检查binlog的数据,所以需要记录下集群内其他服务器发过来已经执行过的binlog,按GTID来区分是否执行过.
log-slave-updates=1
#binlog校验规则,5.6之后的高版本是CRC32,低版本都是NONE,但是MGR要求使用NONE
binlog_checksum=NONE
#基于安全的考虑,MGR集群要求复制模式要改成slave记录记录到表中,不然就报错
master_info_repository=TABLE
relay_log_info_repository=TABLE
#记录事务的算法,官网建议设置该参数使用 XXHASH64 算法
transaction_write_set_extraction = XXHASH64
#相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成
loose-group_replication_group_name = 'de720075-8b42-4833-a4d9-a60e8efb8f5a'
#是否随服务器启动而自动启动组复制,不建议直接启动,怕故障恢复时有扰乱数据准确性的特殊情况
loose-group_replication_start_on_boot = OFF
#本地MGR的IP地址和端口，host:port,是MGR的端口,不是数据库的端口
loose-group_replication_local_address = '192.168.0.118:33061'
#需要接受本MGR实例控制的服务器IP地址和端口,是MGR的端口,不是数据库的端口
loose-group_replication_group_seeds = '192.168.0.118:33061,192.168.0.119:33061,192.168.0.120:33061'
#开启引导模式,添加组成员，用于第一次搭建MGR或重建MGR的时候使用,只需要在集群内的其中一台开启
loose-group_replication_bootstrap_group = OFF
#IP地址白名单,不会允许来自外部主机的连接,按需安全设置
#loose-group_replication_ip_whitelist =
report_host=192.168.0.118
report_port=3306
#ln -s /usr/local/mysql/conf/my.cnf /etc/my.cnf

2.6 初始化数据库

mysqld --defaults-file=/etc/my.cnf --initialize-insecure &

--initialize：该选项初始化时会在错误日志中写一个随机root密码，初始化完成之后在错误日志中搜索password，紧跟其后的一串字符串就是这个随机密码，在初始化完成并启动mysqld之后，初次登录需要使用这个随机密码才能够登录。

--initialize-insecure：该选项初始化时不会产生随机密码，而是像5.7版本之前一样初始化完成之后第一次登录数据库使用空的root密码

2.7 启动数据库服务

mysqld_safe --defaults-file=/etc/my.cnf &
#扫描配置文件的顺序，也可以人为指定其他位置
/etc/my.cnf>/etc/mysql/my.cnf>$mysql_home/my.cnf>~/.my.cnf
#查看是否启动成功，若失败，则去错误日志查看原因
ps -ef | grep mysql
netstat -anultp | grep 端口号

三、启动MGR

3.1 启动MGR单主模式

#以下步骤所有节点均执行
mysql -S /usr/local/mysql/data/sock/mysql.sock #登陆数据库
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #安装插件
mysql> SET SQL_LOG_BIN=0; #关闭binlog
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl'; #创建复制账号
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; #授权
mysql> FLUSH PRIVILEGES; #刷新权限
mysql> SET SQL_LOG_BIN=1; #开启binlog
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery'; #创建复制
# 启动MGR，在192.168.0.118上执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 | 192.168.0.118 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)
# 将其他节点加入MGR，在从库192.168.0.119、192.168.0.120上执行
mysql> START GROUP_REPLICATION;
# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 | 192.168.0.118 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
| group_replication_applier | 9bff969a-c743-11e9-90c0-000c29a2154f | 192.168.0.119 |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
| group_replication_applier | c8dca41c-c744-11e9-8443-000c2943d916 | 192.168.0.120 |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.04 sec)

  如果在启动过程中报错，可以参考performance_schema.replication_connection_status或者日志输出解决问题。

2019-08-26T06:44:59.710514Z 17 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'repl@192.168.0.118:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
2019-08-26T06:44:59.713143Z 16 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'
2019-08-26T06:44:59.713159Z 16 [ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.'
从报错可以看出是caching_sha2_password插件的问题，进行更改即可，更改前后记得关闭和开启binlog。
# ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl'; 

3.2 切换为MGR多主模式

#以下步骤所有节点均执行
mysql> stop group_replication;
#是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了
mysql> set global group_replication_single_primary_mode=OFF;
#多主模式下,强制检查每一个实例是否允许该操作,如果不是多主,可以关闭
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
#只在一个节点上执行，任意节点都可以
mysql> SET GLOBAL group_replication_bootstrap_group=ON; 
mysql> START GROUP_REPLICATION; 
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
#在其余节点执行
mysql> START GROUP_REPLICATION; 
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 | 192.168.0.118 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
| group_replication_applier | 9bff969a-c743-11e9-90c0-000c29a2154f | 192.168.0.119 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
| group_replication_applier | c8dca41c-c744-11e9-8443-000c2943d916 | 192.168.0.120 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.03 sec)

3.3 切换回MGR单主模式

#以下步骤所有节点均执行
mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;
#主节点执行，在192.168.0.118上执行
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;
#在其余节点执行
START GROUP_REPLICATION; 
# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 | 192.168.0.118 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
| group_replication_applier | 9bff969a-c743-11e9-90c0-000c29a2154f | 192.168.0.119 |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
| group_replication_applier | c8dca41c-c744-11e9-8443-000c2943d916 | 192.168.0.120 |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

四、总结

  本文介绍了从MySQL单实例安装到MGR单主、多主的配置及切换步骤，MGR相关原理和特性将在后续文章中进行讲解。