day13-02-MGR部署搭建

MGR部署实战

创建用户

useradd mysql

上传5.7.20软件到/usr/local解压

tar xf /usr/local/src/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /opt/
ln -s /opt/mysql-5.7.31-linux-glibc2.12-x86_64 /opt/mysql

环境变量

yum install -y libaio-devel
useradd mysql
echo 'export PATH=/opt/mysql/bin:$PATH' >> /etc/profile
source /etc/profile
mkdir -p /data/mysql/{3306..3310}/{data,conf,logs,binlog,socket}
chown -R mysql.mysql /data/mysql /opt/mysql

配置文件说明

配置示例：
++++++++++node01++++++
cat << 'EOF' > /data/mysql/3306/conf/my.cnf
[mysqld]
user=mysql
datadir=/data/mysql/3306/data
basedir=/opt/mysql
port=3306
socket=/data/mysql/3306/socket/mysql.sock
log-error=/data/mysql/3306/logs/mysql-err.log
pid-file=/data/mysql/3306/mysqld.pid
character_set_server=utf8mb4
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="0ca2641d-03e6-4410-8033-66d66e87ed39"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.0.50.61:33061"
loose-group_replication_group_seeds="10.0.50.61:33061,10.0.50.62:33062,10.0.50.63:33063"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
EOF
  
++++++++++node02++++++
cat << 'EOF' > /data/mysql/3306/conf/my.cnf
[mysqld]
user=mysql
datadir=/data/mysql/3306/data
basedir=/opt/mysql
port=3306
socket=/data/mysql/3306/socket/mysql.sock
log-error=/data/mysql/3306/logs/mysql-err.log
pid-file=/data/mysql/3306/mysqld.pid
character_set_server=utf8mb4
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="0ca2641d-03e6-4410-8033-66d66e87ed39"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.0.50.62:33062"
loose-group_replication_group_seeds="10.0.50.61:33061,10.0.50.62:33062,10.0.50.63:33063"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
EOF
    
++++++++++node03++++++
cat << 'EOF' > /data/mysql/3306/conf/my.cnf
[mysqld]
user=mysql
datadir=/data/mysql/3306/data
basedir=/opt/mysql
port=3306
socket=/data/mysql/3306/socket/mysql.sock
log-error=/data/mysql/3306/logs/mysql-err.log
pid-file=/data/mysql/3306/mysqld.pid
character_set_server=utf8mb4
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="0ca2641d-03e6-4410-8033-66d66e87ed39"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.0.50.63:33063"
loose-group_replication_group_seeds="10.0.50.61:33061,10.0.50.62:33062,10.0.50.63:33063"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
EOF

  
组复制部分，配置文件介绍：
group_replication变量使用的loose-前缀是指示Server启用时尚未加载复制插件也将继续启动
transaction_write_set_extraction = XXHASH64
##指示Server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
loose-group_replication_group_name="0ca2641d-03e6-4410-8033-66d66e87ed39"
##表示将加入或者创建的复制组命名为ca2641d-03e6-4410-8033-66d66e87ed39
##可自定义(通过cat /proc/sys/kernel/random/uuid)
loose-group_replication_start_on_boot=off 
##设置为Server启动时不自动启动组复制
loose-group_replication_local_address="10.0.50.61:33061" 
##绑定本地的192.168.29.128及33061端口接受其他组成员的连接，IP地址必须为其他组成员可正常访问
loose-group_replication_group_seeds="10.0.50.61:33061,10.0.50.62:33062,10.0.50.63:33063"
##本行为告诉服务器当服务器加入组时，应当连接到10.0.50.61:33061,10.0.50.62:33062,10.0.50.63:33063
##这些种子服务器进行配置。本设置可以不是全部的组成员服务地址。
loose-group_replication_bootstrap_group = off 
##配置是否自动引导组
loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″
##配置白名单，默认情况下只允许10.0.50.61连接到复制组，如果是其他IP则需要配置。

官方文档，配置参数说明

https://dev.mysql.com/doc/refman/5.7/en/group-replication-configuring-instances.html

初始化数据,并启动数据库节点

/opt/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql --datadir=/data/mysql/3306/data

mysqld_safe --defaults-file=/data/mysql/3306/conf/my.cnf &

node01节点加入GR

创建复制用户
mysql -S /data/mysql/3306/socket/mysql.sock
set sql_log_bin=0;
 grant replication slave,replication client on *.* to repl@'localhost' identified by '123';
 grant replication slave,replication client on *.* to repl@'127.0.0.1' identified by '123';
 grant replication slave,replication client on *.* to repl@'10.0.50.%' identified by '123';
  SET SQL_LOG_BIN=1;
 注：如果为三台独立节点，需要将localhost、127.0.0.1和远程主机域都授权用户
开启分布式复制

 change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';

加载GR插件

install plugin group_replication soname 'group_replication.so';
show plugins;

启动复制程序
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
#检测组是否创建并已加入新成员
select * from performance_schema.replication_group_members;

node02加入GR

创建复制用户
mysql -S /data/mysql/3306/socket/mysql.sock
set sql_log_bin=0;
 grant replication slave,replication client on *.* to repl@'localhost' identified by '123';
 grant replication slave,replication client on *.* to repl@'127.0.0.1' identified by '123';
 grant replication slave,replication client on *.* to repl@'10.0.50.%' identified by '123';
 SET SQL_LOG_BIN=1;
 注：如果为三台独立节点，需要将localhost、127.0.0.1和远程主机域都授权用户
 开启分布式复制
 change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';


加载GR插件
install plugin group_replication soname 'group_replication.so';
show plugins;

启动复制程序
start group_replication;

#检测组是否创建并已加入新成员
select * from performance_schema.replication_group_members;

注： 前面的用户密码修改和创建用户操作必须设置binlog不记录，执行后再打开，否则会引起START GROUP_REPLICATION执行报错:
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
解决方案是：根据提示打开group_replication_allow_local_disjoint_gtids_join选项，mysql命令行执行:
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
然后再执行:
mysql> start group_replication;

node03加入MGR

创建复制用户
mysql -S /data/mysql/3306/socket/mysql.sock
set sql_log_bin=0;
 grant replication slave,replication client on *.* to repl@'localhost' identified by '123';
 grant replication slave,replication client on *.* to repl@'127.0.0.1' identified by '123';
 grant replication slave,replication client on *.* to repl@'10.0.50.%' identified by '123';
 set sql_log_bin=1;

 注：如果为三台独立节点，需要将localhost、127.0.0.1和远程主机域都授权用户

 开启分布式复制
 change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';

加载GR插件
install plugin group_replication soname 'group_replication.so';
show plugins;

启动复制程序
start group_replication;
#检测组是否创建并已加入新成员
select * from performance_schema.replication_group_members;