mysql 成员离开组_Mysql Group Replication关闭和启动所有的组成员的注意点

这里将告诉您Mysql Group Replication关闭和启动所有的组成员的注意点,具体完成步骤:

Mysql Group Replication关闭和启动所有的组成员的注意点

2018年05月17日 11:45:02

zuozhiji 阅读数 1772

由于的我mgr建立在虚拟机上面(即使是正式环境，如果计划内的停机或者断电都需要关闭所有的节点)，如何关闭所有的组成员，关闭的顺序还是比较重要的。

我的环境是一个primary，多个slave的架构，qht131为parmary,其它qht132,qht133,qht134为slave数据库。

1.关闭所有的组成员必须先先关闭slave，最后关闭master.

qht132,qht133,qht134：

[root@qht133 mysql]# service mysql stop

Shutting down MySQL.............. [ OK ]

qht131:

所有的slave关闭后都被移出了组成员：

mysql> select * from performance_schema.replication_group_members ;

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

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89b | qht131 | 3306 | ONLINE |

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

1 row in set (0.00 sec)

最后也关闭primary数据库

[root@qht131 mysql]# service mysql stop

Shutting down MySQL.............. [ OK ]

2.开启组复制需要先开始primary，接着一个启动slave server.

[root@qht131 mysql]# service mysql start

Starting MySQL........... [ OK ]

由于group_replication_bootstrap_group参数我们在配置文件中设为了OFF，所以需要手动打开设为ON.

mysql> show variables like 'group_replication_bootstrap_group';

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

| Variable_name | Value |

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

| group_replication_bootstrap_group | OFF |

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

1 row in set (0.24 sec)

mysql> set global group_replication_bootstrap_group=on;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members ;

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

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89b | qht131 | 3306 | OFFLINE |

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

1 row in set (0.02 sec)

在没有开启组复制之前member_state为OFFLINE

开启组复制之后记得将group_replication_bootstrap_group再设为off

mysql> start group_replication;

Query OK, 0 rows affected (2.12 sec)

mysql> set global group_replication_bootstrap_group=off;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members ;

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

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89b | qht131 | 3306 | ONLINE |

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

1 rows in set (0.00 sec)

待primaryserver准备好后，其它的slave数据库可以一个个打开并开启组复制

qht132,qht133,qht134:

[root@qht132 mysql]# service mysql start

Starting MySQL....... [ OK ]

mysql> start group_replication;

Query OK, 0 rows affected (6.52 sec)

可能出现的问题：

mysql> 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.

同时错误日志：

2018-05-17T03:08:40.654242Z 2 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.' 2018-05-17T03:08:40.654369Z 2 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1' 2018-05-17T03:08:40.654378Z 2 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1' 2018-05-17T03:08:40.654551Z 7 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed

2018-05-17T03:08:40.657610Z 4 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'

解决办法：检查一下primaryserver组复制的状态，group_replication_bootstrap_group是否为on，performance_schema.replication_group_members是否为ONLINE。

全部启动好后：

mysql> select * from performance_schema.replication_group_members ;

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

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89b | qht131 | 3306 | ONLINE |

| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89c | qht134 | 3306 | ONLINE |

| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e90b | qht132 | 3306 | ONLINE |

| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e91b | qht133 | 3306 | ONLINE |

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

4 rows in set (0.00 sec)

Mysql Group Replication关闭和启动所有的组成员的注意点就为您介绍到这里，感谢您关注懒咪学编程c.lanmit.com.

本文地址：https://c.lanmit.com/shujuku/qita/5516.html