mysql MGR 高可用方案搭建过程记录

一、节点Mysql 5.7 环境搭建

#/home/admin/Downloads
sudo tar zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

# 创建软连接
cd /usr/local
sudo ln -s mysql-5.7.24-linux-glibc2.12-x86_64 mysql

# 修改目录权限
groupadd mysql
useradd -r -g mysql mysql
cd /usr/local/mysql
sudo chown -R mysql:mysql ./

# 初始化mysql数据库（建立默认的库和表） 注意这一步会自动生成一个密码，后面修改密码要用 
sudo ./bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize

# 修改配置文件
sudo vim /etc/my.cnf
```
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
explicit_defaults_for_timestamp=true
symbolic-links=0

[mysqld_safe]
log-error=/usr/local/mysql/data/mysql.log
pid-file=/usr/local/mysql/data
```

# 运行MYSQL
./support-files/mysql.server start

# 将mysqd服务添加到系统服务中
sudo cp support-files/mysql.server /etc/init.d/mysqld
sudo ls -l /etc/init.d/mysqld

# 修改root密码
sudo ./bin/mysqladmin -u root -p'dvhAYKl!k0!C' password '123456'           #dvhAYKl!k0!C 为之前生成的密码

# 将mysql命令添加到系统命令执行路径中，便于使用
sudo ln -s /usr/local/mysql/bin/* /usr/local/bin/

# 设置允许外部访问
mysql -u root -p123456
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

二、MGR 集群搭建 参考文档：https://dev.mysql.com/doc/refman/5.7/en/group-replication.html

#/etc/my.cnf
```
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
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

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="0a9bac6c-eeb0-11eb-945d-005056a5d56d"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.92.152:33061"        #不同实例要修改
group_replication_group_seeds= "192.168.92.152:33061,192.168.92.153:33061,192.168.92.154:33061"
group_replication_bootstrap_group=off

[mysqld_safe]
log-error=/usr/local/mysql/data/mysql.log
pid-file=/usr/local/mysql/data
```

【所有节点】
# 可选，如果跨网段需要配置白名单
set global group_replication_ip_whitelist = '192.168.92.150,192.168.92.151,192.168.91.164';

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

【主节点】
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

SELECT * FROM performance_schema.replication_group_members;

【从节点】
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;

三、安装mysql shell
sudo rpm -Uvh mysql80-community-release-el7-3.noarch.rpm
yum repolist all | grep mysql
sudo yum-config-manager --disable mysql80-community
sudo yum-config-manager --enable mysql57-community
sudo cat /etc/yum.repos.d/mysql-community.repo
yum repolist enabled | grep mysql
sudo yum module disable mysql
sudo yum remove mysql-community-release
sudo yum install mysql-shell

四、创建集群

#使用 mysql-shell 创建集群并查看集群状态
mysqlsh
    
 MySQL  JS > \connect root@localhost:3306
 MySQL  localhost:3306  JS > var cluster = dba.createCluster("test")
 MySQL  localhost:3306  JS > var cluster = dba.getCluster();
 MySQL  localhost:3306  JS > cluster.status()
 
# 如果某台机器重启，先启动mysql，然后在其他节点使其重新加入集群（手动方式）
 MySQL  localhost:3306  JS > cluster.rejoinInstance("root@ATCRMZTAQ05:3306")
 
# 如果想要某台机器重启之后自动加入集群，需要将配置持久化（自动方式）
sudo mysqlsh        #在每台实例上执行
dba.configureLocalInstance('root@localhost:3306')    #一定是要本地机器

# 如果三台同时重启，则在某台机器以下命令加入集群
dba.rebootClusterFromCompleteOutage();

五、router 相关
#设置集群router 用户
 MySQL  localhost:3306  JS > cluster.setupRouterAccount("routerAdmin")    #我填的密码是123456
 
#安装router
sudo rpm -Uvh mysql80-community-release-el7-3.noarch.rpm
sudo yum -y install mysql-router

#引导并启动router
sudo mysqlrouter --bootstrap root@localhost:3306 --user=mysqlrouter
sudo systemctl start mysqlrouter

# 开放端口
sudo firewall-cmd --zone=public --add-port=6446/tcp --permanent
sudo firewall-cmd --zone=public --add-port=6447/tcp --permanent 
sudo firewall-cmd --reload