mgr是什么意思_数据库

资料来源：网络整理时间：2023/3/5 17:10:16 共计：3609 浏览

mgr是什么意思？

MySQL Group Replication（下简称MGR）准确来说是官方推出的高知可用解决方案，基于原生复制技术，并以插件的方式提供。

1、使用前，关掉防火墙，包括 selinux，firewalld，或者 MySQL 企业版的firewall（如果用了企业版的话）2、两台机器：（4 台 MySQL 实例）192.168.2.219 centos-ytt57-1 3311/3312192.168.2.229 centos-ytt57-2 3311/33123、安装 MySQL（两台都装）, MySQL Shell（任意一台）, mysqlrouter(任意一台，官方建议和应用程序装在一个服务器上)yum install mysql-community-server mysql-shell mysql-router-community

4、搭建 InnoDB-Cluster（两台都装）

1. 配置文件如下：shell>vi /etc/my.cnfmaster-info-repository=tablerelay-log-info-repository=tablegtid_mode=ONenforce_gtid_consistency=ONbinlog_checksum=NONElog_slave_updates=ONbinlog_format=ROWtransaction_write_set_extraction=XXHASH642. 系统 HOSTS 配置（两台都配）

shell>vi /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.2.219 centos-ytt57-2

192.168.2.229 centos-ytt57-3

用 MySQL coalesce 函数确认下 report-host 是否设置正确(root@localhost) : [(none)] >SELECT coalesce(@@report_host, @@hostname) as r;+----------------+| r |+----------------+| centos-ytt57-1 |+----------------+1 row in set (0.00 sec)

3. 创建管理员用户搭建 GROUP REPLICATION （四个节点都要）

create user root identified by 'Root@123';grant all on *.* to root with grant option;flush privileges;

4. MySQLsh 连接其中一台节点：

[root@centos-ytt57-1 mysql]# mysqlsh root@localhost:3321

5. 检查配置正确性：（如果这里不显示 OK，把对应的参数加到配置文件重启 MySQL 再次检查）dba.checkInstanceConfiguration("root@centos-ytt57-2:3311");dba.checkInstanceConfiguration("root@centos-ytt57-2:3312");dba.checkInstanceConfiguration("root@centos-ytt57-3:3311");dba.checkInstanceConfiguration("root@centos-ytt57-3:3312");mysqlsh 执行检测

[root@centos-ytt57-1 mysql]# mysqlsh --log-level=8 root@localhost:3311

MySQL localhost:3311 ssl JS > dba.checkInstanceConfiguration("root@centos-ytt57-2:3311")

{

"status": "ok"

}

6. 创建集群，节点 1 上创建。（结果显示 Cluster successfully created 表示成功）

MySQL localhost:3311 ssl JS > var cluster = dba.createCluster('ytt_mgr');

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.

At least 3 instances are needed for the cluster to be able to withstand up to

one server failure.

7. 添加节点 2，3，4（全部显示 OK，表示添加成功）MySQL localhost:3311 ssl JS > cluster.addInstance('root@centos-ytt57-2:3312');MySQL localhost:3311 ssl JS > cluster.addInstance('root@centos-ytt57-3:3311');MySQL localhost:3311 ssl JS > cluster.addInstance('root@centos-ytt57-3:3312');

8. 查看拓扑图：（describe 简单信息，status 详细描述）

MySQL localhost:3311 ssl JS > cluster.describe()

{

"clusterName": "ytt_mgr",

"defaultReplicaSet": {

"name": "default",

"topology": [

{

"address": "centos-ytt57-2:3311",

"label": "centos-ytt57-2:3311",

"role": "HA",

"version": "8.0.17"

{

"address": "centos-ytt57-2:3312",

"label": "centos-ytt57-2:3312",

"role": "HA",

"version": "8.0.17"

{

"address": "centos-ytt57-3:3311",

"label": "centos-ytt57-3:3311",

"role": "HA",

"version": "8.0.17"

{

"address": "centos-ytt57-3:3312",

"label": "centos-ytt57-3:3312",

"role": "HA",

"version": "8.0.17"

}

"topologyMode": "Single-Primary"

}

MySQL localhost:3311 ssl JS > cluster.status()

"clusterName": "ytt_mgr",

"defaultReplicaSet": {

"name": "default",

"primary": "centos-ytt57-2:3311",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"centos-ytt57-2:3311": {

"address": "centos-ytt57-2:3311",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE",

"version": "8.0.17"

"centos-ytt57-2:3312": {

"address": "centos-ytt57-2:3312",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE",

"version": "8.0.17"

"centos-ytt57-3:3311": {

"address": "centos-ytt57-3:3311",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE",

"version": "8.0.17"

"centos-ytt57-3:3312": {

"address": "centos-ytt57-3:3312",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE",

"version": "8.0.17"

}

"topologyMode": "Single-Primary"

"groupInformationSourceMember": "centos-ytt57-2:3311"

9. 简单测试下数据是否同步

(root@localhost) : [(none)] >create database ytt;

Query OK, 1 row affected (0.03 sec)

(root@localhost) : [(none)] >use ytt;

Database changed

(root@localhost) : [ytt] >create table p1(id int primary key, log_time datetime);

Query OK, 0 rows affected (0.08 sec)

(root@localhost) : [ytt] >insert into p1 values (1,now());

Query OK, 1 row affected (0.04 sec)

(root@localhost) : [ytt] >show master status;

+---------------+----------+--------------+------------------+-------------------------------------------+

+---------------+----------+--------------+------------------+-------------------------------------------+

+---------------+----------+--------------+------------------+-------------------------------------------+

1 row in set (0.00 sec)

查看其他三个节点

(root@localhost) : [ytt] >show tables;

+---------------+

| Tables_in_ytt |

+---------------+

| p1 |

+---------------+

1 row in set (0.00 sec)

(root@localhost) : [ytt] >select * from p1;

+----+---------------------+

| id | log_time |

+----+---------------------+

| 1 | 2019-08-05 16:44:20 |

+----+---------------------+

1 row in set (0.00 sec)

停掉主节点：[root@centos-ytt57-2 mysql0]# systemctl stop mysqld@0

现在查看，主节点已经变为本机 3312节点"centos-ytt57-2:3312": { "address": "centos-ytt57-2:3312", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE"}

10. 报错处理

错误日志里显示2019-08-05T09:01:35.125591Z 0 [ERROR] Plugin group_replication reported: 'The group name option is mandatory'2019-08-05T09:01:35.125622Z 0 [ERROR] Plugin group_replication reported: 'Unable to start Group Replication on boot'

同时用 cluster.rescan() 扫描，发现The instance 'centos-ytt57-2:3311' is no longer part of the ReplicaSet.

重新加入此节点到集群：cluster.rejoinInstance('centos-ytt57-2:3311')

再次执行cluster.status()查看集群状态："status": "OK",11. 移除和加入cluster.removeInstance("centos-ytt57-3:3312");The instance 'centos-ytt57-3:3312' was successfully removed from the cluster.cluster.addInstance("centos-ytt57-3:3312");The instance 'centos-ytt57-3:3312' was successfully added to the cluster.

12. 用 mysqlrouter 生成连接 MGR 相关信息。涉及到两个用户：--user=mysqlrouter 是使用mysqlrouter的系统用户自动创建的MySQL 用户是用来与MGR通信的用户。如果想查看这个用户的用户名以及密码，就加上--force-password-validation，不过一般也没有必要查看。

版权说明：
本网站凡注明“广州京杭原创”的皆为本站原创文章，如需转载请注明出处！
本网转载皆注明出处，遵循行业规范，如发现作品内容版权或其它问题的，请与我们联系处理！
欢迎扫描右侧微信二维码与我们联系。

·上一条：mysql主键和唯一索引的区别_数据库 | ·下一条：mysql服务器最高权限是_数据库