[Mysql] mysql-mgr单主模式切换为多主，多主切换为单主


本文总阅读量次

1、停止组复制(所有节点执行)：

1
2
3
mysql> stop group_replication; 
mysql> set global group_replication_single_primary_mode=OFF; 
mysql> set global group_replication_enforce_update_everywhere_checks=ON;

2、随便选择某个节点执行

1
2
3
mysql> SET GLOBAL group_replication_bootstrap_group=ON; 
mysql> START GROUP_REPLICATION; 
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

3、其他节点执行

1
mysql> START GROUP_REPLICATION;

查看节点启动报错：

1
2
2018-11-08T14:00:52.098660Z 56 [ERROR] 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.'
2018-11-08T14:00:52.098689Z 56 [ERROR] 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.'

手动配置复制信息：

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

再次启动：

1
mysql> START GROUP_REPLICATION;

4、查看组信息，所有节点的信息正常，所有节点均为主：

1
2
3
4
5
6
7
SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+------------------+-------------+--------------+
| group_replication_applier | bddd9c32-8fee-11e8-ac79-525400edbe8d | dax-mysql-slave  |        3306 | ONLINE       |
| group_replication_applier | d5bd8edd-9a1d-11e8-993e-525400578639 | dax-mysql-master |        3306 | ONLINE       |
+---------------------------+--------------------------------------+------------------+-------------+--------------+

5、查看集群状态

1
2
3
4
5
 MySQL  dax-mysql-master:3306  JS > cluster = dba.getCluster("prodCluster")
<Cluster:prodCluster>

 MySQL  dax-mysql-master:3306  JS > cluster.status();
Cluster.status: The InnoDB Cluster topology type (Single-Master) does not match the current Group Replication configuration (Multi-Master). Please use <cluster>.rescan() or change the Group Replication configuration accordingly. (RuntimeError)

提示cluster 拓扑类型为单主与当前的mgr(多主)不匹配，使用cluster.rescan()参数重新扫描：

1
2
3
4
5
6
7
8
9
10
11
 MySQL  dax-mysql-master:3306  JS > cluster.rescan()
Rescanning the cluster...

Result of the rescanning operation:
{
    "defaultReplicaSet": {
        "name": "default", 
        "newlyDiscoveredInstances": [], 
        "unavailableInstances": []
    }
}

再次查看集群状态仍然报错：

1
2
 MySQL  dax-mysql-master:3306  JS > cluster.status();
Cluster.status: The InnoDB Cluster topology type (Single-Master) does not match the current Group Replication configuration (Multi-Master). Please use <cluster>.rescan() or change the Group Replication configuration accordingly. (RuntimeError)

尝试删除集群元数据：

1
2
3
dba.dropMetadataSchema();
Are you sure you want to remove the Metadata? [y/N]: y
Metadata Schema successfully removed.

重新创建集群

1
var cluster = dba.createCluster('prodCluster', {adoptFromGR: true ,force: true});

报错提示：

1
[ERROR] Plugin group_replication reported: 'Table instances has a foreign key with 'CASCADE' clause. This is not compatible with Group Replication'

创建集群会在mysql_innodb_cluster_metadata库下建立一个instances的表，在单主模式下创建集群没有外键级联的报错问题，在多主模式下提示有外键级联的问题，表内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE `instances` (
  `instance_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `host_id` int(10) unsigned NOT NULL,
  `replicaset_id` int(10) unsigned DEFAULT NULL,
  `mysql_server_uuid` varchar(40) NOT NULL,
  `instance_name` varchar(256) NOT NULL,
  `role` enum('HA','readScaleOut') NOT NULL,
  `weight` float DEFAULT NULL,
  `addresses` json NOT NULL,
  `attributes` json DEFAULT NULL,
  `version_token` int(10) unsigned DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`instance_id`),
  UNIQUE KEY `mysql_server_uuid` (`mysql_server_uuid`),
  UNIQUE KEY `instance_name` (`instance_name`),
  KEY `host_id` (`host_id`),
  KEY `instances_ibfk_2` (`replicaset_id`),
  CONSTRAINT `instances_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`host_id`),
  CONSTRAINT `instances_ibfk_2` FOREIGN KEY (`replicaset_id`) REFERENCES `replicasets` (`replicaset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

查阅官方文档有提示说多主模式下存在这个bug，将该表下的外键约束去掉：

1
2
ALTER TABLE `mysql_innodb_cluster_metadata`.`instances` DROP FOREIGN KEY `instances_ibfk_1`;
ALTER TABLE `mysql_innodb_cluster_metadata`.`instances` DROP FOREIGN KEY `instances_ibfk_2`;

重建添加外键使用下面的命令：

1
2
3
ALTER TABLE `mysql_innodb_cluster_metadata`.`instances` ADD CONSTRAINT `instances_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`host_id`);
ALTER TABLE `mysql_innodb_cluster_metadata`.`instances` ADD CONSTRAINT `instances_ibfk_2` 
FOREIGN KEY (`replicaset_id`) REFERENCES `replicasets` (`replicaset_id`);

将外键约束删除之后，重新创建集群：

1
2
3
4
5
6
7
8
 MySQL  dax-mysql-master:3306  JS > var cluster = dba.createCluster('prodCluster', {adoptFromGR: true ,force: true});
A new InnoDB cluster will be created based on the existing replication group on instance 'repl@dax-mysql-master:3306'.

Creating InnoDB cluster 'prodCluster' on 'repl@dax-mysql-master:3306'...
Adding Instance 'dax-mysql-slave:3306'...
Adding Instance 'dax-mysql-master:3306'...

Cluster successfully created based on existing replication group.

集群创建成功，查看集群状态,两个节点均为读写模式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 MySQL  dax-mysql-master:3306  JS > cluster.status()
{
    "clusterName": "prodCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "ssl": "DISABLED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "dax-mysql-master:3306": {
                "address": "dax-mysql-master:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "dax-mysql-slave:3306": {
                "address": "dax-mysql-slave:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://repl@dax-mysql-master:3306"
}

6、mysql-mgr多主模式切换成功，现在将模式从多主切换为单主：

7、所有节点执行

1
2
3
mysql> stop group_replication; 
mysql> set global group_replication_enforce_update_everywhere_checks=OFF; 
mysql> set global group_replication_single_primary_mode=ON;

8、主节点（dax-mysql-master）执行

1
2
3
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;

9、从节点执行：

1
START GROUP_REPLICATION;

10、查看MGR组信息,所有节点同步正常：

1
2
3
4
5
6
7
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+------------------+-------------+--------------+
| group_replication_applier | bddd9c32-8fee-11e8-ac79-525400edbe8d | dax-mysql-slave  |        3306 | ONLINE       |
| group_replication_applier | d5bd8edd-9a1d-11e8-993e-525400578639 | dax-mysql-master |        3306 | ONLINE       |
+---------------------------+--------------------------------------+------------------+-------------+--------------+

查看当前主节点信息：

1
2
3
4
5
6
7
show status like '%primary%';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | d5bd8edd-9a1d-11e8-993e-525400578639 |
+----------------------------------+--------------------------------------+
1 row in set (0.01 sec)

11、登录mysql-shell查看集群信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MySQL  dax-mysql-master:3306  JS > cluster = dba.getCluster("prodCluster")
<Cluster:prodCluster>

 MySQL  dax-mysql-master:3306  JS > cluster.status();
Cluster.status: The InnoDB Cluster topology type (Multi-Master) does not match the current Group Replication configuration (Single-Master). Please use <cluster>.rescan() or change the Group Replication configuration accordingly. (RuntimeError)

 MySQL  dax-mysql-master:3306  JS > cluster.rescan()
Rescanning the cluster...

Result of the rescanning operation:
{
    "defaultReplicaSet": {
        "name": "default", 
        "newlyDiscoveredInstances": [], 
        "unavailableInstances": []
    }
}

 MySQL  dax-mysql-master:3306  JS > cluster.status();
Cluster.status: The InnoDB Cluster topology type (Multi-Master) does not match the current Group Replication configuration (Single-Master). Please use <cluster>.rescan() or change the Group Replication configuration accordingly. (RuntimeError)

跟多主切换为单主报错相同，尝试删除集群元数据：

1
2
3
dba.dropMetadataSchema();
Are you sure you want to remove the Metadata? [y/N]: y
Metadata Schema successfully removed.

重新创建集群：

1
2
3
4
5
6
7
8
 MySQL  dax-mysql-master:3306  JS > var cluster = dba.createCluster('prodCluster', {adoptFromGR: true});
A new InnoDB cluster will be created based on the existing replication group on instance 'repl@dax-mysql-master:3306'.

Creating InnoDB cluster 'prodCluster' on 'repl@dax-mysql-master:3306'...
Adding Seed Instance...
Adding Instance 'dax-mysql-slave:3306'...

Cluster successfully created based on existing replication group.

集群创建成功，查看集群状态为一主一从模式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 MySQL  dax-mysql-master:3306  JS >  cluster.status();
{
    "clusterName": "prodCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "dax-mysql-master:3306", 
        "ssl": "DISABLED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "dax-mysql-master:3306": {
                "address": "dax-mysql-master:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "dax-mysql-slave:3306": {
                "address": "dax-mysql-slave:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://repl@dax-mysql-master:3306"
}
mysql
目录
  1. 1. 1、停止组复制(所有节点执行)：
  2. 2. 2、随便选择某个节点执行
  3. 3. 3、其他节点执行
  4. 4. 4、查看组信息，所有节点的信息正常，所有节点均为主：
  5. 5. 5、查看集群状态
  6. 6. 6、mysql-mgr多主模式切换成功，现在将模式从多主切换为单主：
  7. 7. 7、所有节点执行
  8. 8. 8、主节点（dax-mysql-master）执行
  9. 9. 9、从节点执行：
  10. 10. 10、查看MGR组信息,所有节点同步正常：
  11. 11. 11、登录mysql-shell查看集群信息：

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog