Mysql MGR单主内网部署

                                                       Mysql MGR单主内网部署

1.关闭防火墙：

systemctl stop firewalld

systemctl disable firewalld 

setenforce 0

2.互ping检查网络:

3.检查如有mariadb卸载：

rpm -qa | grep mariadb

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

4.创建部署目录：

mkdir mysql

cd mysql/

5.上传MySQL安装包，解压

tar -xvf mysql-5.7.41-1.el7.x86_64.rpm-bundle.tar

6.rpm安装

按照依赖关系安装，依赖关系依次是common ->libs ->client ->server

rpm -ivh mysql-community-common-5.7.41-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.41-1.el7.x86_64.rpm

（rpm -ivh mysql-community-libs-5.7.41-1.el7.x86_64.rpm        --force --nodeps）

rpm -ivh mysql-community-client-5.7.41-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.41-1.el7.x86_64.rpm

若遇如下情况，上传该依赖包，然后安装，有网络直接yum安装

(yum install -y libaio)

7.安装完成，启动数据库，查看状态

systemctl start mysqld

systemctl status mysqld

8.查看初始密码：

cat /var/log/mysqld.log | grep 'password'

9.登录mysql：

mysql -uroot -pXUod=e6jWuMu

10.修改数据库初始密码：

set password=password('XUod=e6jwumu');

（提示：

mysql5.7通过上面默认安装后，执行语句可能会报错：

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

   

这个报错与Mysql 密码安全策略validate_password_policy的值有关，validate_password_policy可以取0、1、2三个值：

解决办法：

set global validate_password_policy=0;

set global validate_password_length=1; ）

部署MGR集群

1.查看主机IP，三台主机均修改/etc/hosts

三台主机：

172.18.0.136    mysql-master

172.18.0.137    mysql-slave01

172.18.0.138    mysql-slave02

2.修改/etc/my.cnf,添加配置文件，注意要修改的配置

vim /etc/my.cnf

server_id = 11 #服务ID

port = 3306

secure-file-priv = /tmp

binlog_format= row

gtid_mode = ON #全局事务

validate-password = OFF #为了测试方便输入简单密码这里关闭密码强度审计插件

enforce_gtid_consistency = TRUE #强制 GTID 的一致性

log-error = /var/log/mysqld.log

skip_name_resolve

master_info_repository = TABLE #将 master.info 元数据保存在系统表中

relay_log_info_repository = TABLE #将 relay.info 元数据保存在系统表中

report_host = 172.18.0.136

report_port = 3306

default_authentication_plugin = mysql_native_password

binlog_checksum = NONE #禁用二进制日志事件校验

binlog_transaction_dependency_tracking = WRITESET

log_slave_updates = ON #级联复制

log_bin = binlog #开启二进制日志记录

binlog_format = ROW #以行的格式记录

transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列

loose-group_replication_group_name = 'b46b02b8-3305-494d-b6ed-4d38cb123cab' #加入的组名

loose-group_replication_start_on_boot = off #不自动启用组复制集群

loose-group_replication_local_address = '172.18.0.136:33060' #以本机端口 33060 接受来自组中成员的传入连接

loose-group_replication_group_seeds = '172.18.0.136:33060,172.18.0.137:33060,172.18.0.138:33060' #组中成员访问表

loose-group_replication_bootstrap_group = off #不启用引导组

[mysql]

prompt=mysql-master [\\d]>

3.修改完成重启数据库

systemctl restart mysqld

4.检查数据库状态

systemctl status mysqld

5.配置完成后, 重启动数据库，安装MGR插件，设置复制账号（所有MGR节点都要执行）：

mysql-master [(none)]>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Query OK, 0 rows affected (0.05 sec)

mysql-master [(none)]>SET SQL_LOG_BIN=0;

Query OK, 0 rows affected (0.00 sec)

mysql-master [(none)]>CREATE USER repl@'%' IDENTIFIED BY 'repl';

Query OK, 0 rows affected (0.00 sec)

mysql-master [(none)]>GRANT REPLICATION SLAVE ON *.* TO repl@'%';

Query OK, 0 rows affected (0.00 sec)

mysql-master [(none)]>FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql-master [(none)]>CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

Query OK, 0 rows affected, 2 warnings (0.01 sec)

6.启动MGR单主模式：

mysql-master [(none)]>SET GLOBAL group_replication_bootstrap_group=ON;

Query OK, 0 rows affected (0.00 sec)

mysql-master [(none)]>START GROUP_REPLICATION;

Query OK, 0 rows affected (7.17 sec)

mysql-master [(none)]>SET GLOBAL group_replication_bootstrap_group=OFF;

Query OK, 0 rows affected (0.00 sec)

7.查看MGR组信息：

mysql-master[(none)]>SELECT * FROM performance_schema.replication_group_members;

8.其他节点加入MGR集群

mysql-slave01 [(none)]>START GROUP_REPLICATION;

mysql-slave02 [(none)]>START GROUP_REPLICATION;

9.查看MGR组信息

mysql-master [(none)]>SELECT * FROM performance_schema.replication_group_members;

重置密码：

UPDATE mysql.user SET authentication_string=PASSWORD(‘123456’) where USER=‘root’;