MySQL组复制卡在恢复中

Question

我正在测试MySQL组复制。

环境: Microsoft Azure Ubuntu 16.0.4

我遵循了这个指南：https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04

不过，我还在我正在测试的两台机器上添加了第二个NIC，以便在单独的LAN上进行复制，因此主要的复制只用于与应用程序的连接。

据我所知，节点可以看到彼此：

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | f0bcfc98-4255-11e8-b39f-000d3a1db637 | 10.3.1.4    |        3306 | ONLINE       |
| group_replication_applier | f8867ce8-4255-11e8-b03c-000d3a133573 | 10.3.1.5    |        3306 | RECOVERING   |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

10.3.1.0/24是我的复制局域网。10.3.0.0/24是应用程序/主局域网。

问题是，当我添加第二个节点时，它将保持在恢复状态。一段时间后，它将移除第二个节点。

2018-05-01T15:01:18.067031Z 0 [Note] Plugin group_replication reported: 'getstart group_id e459a5fc'
2018-05-01T15:01:19.067227Z 11 [Note] Plugin group_replication reported: 'Only one server alive. Declaring this server as online within the replication group'
2018-05-01T15:01:19.067238Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 10.3.1.4:3306 on view 15251868790666823:1.'
2018-05-01T15:01:19.074671Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'
2018-05-01T15:01:47.201473Z 0 [Note] Plugin group_replication reported: 'getstart group_id e459a5fc'
2018-05-01T15:01:52.684929Z 0 [Note] Plugin group_replication reported: 'Members joined the group: 10.3.1.5:3306'
2018-05-01T15:01:52.685016Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 10.3.1.4:3306, 10.3.1.5:3306 on view 15251868790666823:2.'

    2018-05-01T15:10:53.575405Z 0 [Note] Plugin group_replication reported: 'getstart group_id e459a5fc'
    2018-05-01T15:10:53.913923Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: 10.3.1.5:3306'
    2018-05-01T15:10:53.914018Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 10.3.1.4:3306 on view 15251868790666823:3.'

以下是/etc/mysql/mysql.conf.d/mysqld.cnf的内容

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
log-error       = /var/log/mysql/error.log

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
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

# Shared replication group configuration
loose-group_replication_group_name = "fe2d1be8-1ba8-403b-83df-f75711b2f3d1"
loose-group_replication_ip_whitelist = "10.3.1.0/24"
loose-group_replication_group_seeds = "10.3.1.4:33061,10.3.1.5:33061"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

# Host specific replication configuration
server_id = 1
bind-address = "10.3.0.23"
report_host = "10.3.1.4"
loose-group_replication_local_address = "10.3.1.4:33061"



lc-messages-dir = /usr/share/mysql
skip-external-locking
max_connections = 1000
table_open_cache=2048
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# By default we only accept connections from localhost
# bind-address  = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
# symbolic-links=0

# * Fine Tuning
#
key_buffer_size = 64M
max_allowed_packet      = 768M
thread_stack            = 256K
thread_cache_size       = 64
join_buffer_size = 16M
sort_buffer_size = 8M

long_query_time = 2
log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
max_heap_table_size=2G
tmp_table_size=2G


innodb_write_io_threads=8
innodb_read_io_threads=8

innodb_log_file_size = 1G
innodb_buffer_pool_size = 50G
innodb_log_buffer_size = 20M
#wait_timeout = 28800
#wait_timeout = 20
#interactive_timeout = 28800
interactive_timeout = 120
innodb_flush_log_at_trx_commit=0
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8
innodb_sort_buffer_size = 2M

如果我错过了一些重要的信息，请告诉我，我会尽快提供。请注意，上面的配置是主配置的。除了不同的IP之外，辅助配置是相同的。

我的一个问题是: MEMBER_PORT不应该是33061而不是3306，因为我们在配置中将其设置为复制端口吗？

提前感谢！

Answer 1

请确保您已经打开了MySQL组复制所需的所有端口(按照您的配置)：

1) 3306

2) 33061

此外，如果要将成员从MySQL集群中删除，则mysql日志中必须有某些内容。请提供详细的mysql日志。

我已经在生产中设置了一个具有3个节点的组复制集群，并且运行良好。