Common problems and solutions during MySQL MGR construction

01 Common Faults 1
02 Common Faults 2
03 Common Faults 3
04 Common Faults 4
05 FAQ 5
06 FAQ6
07 FAQ7
08 FAQ8
09 FAQ9

Some faults encountered during the MGR construction process

In practice, I have deployed a total of three MGR environments, namely a single-machine multi-instance MGR environment, an MGR environment with multiple machines in the same network segment, and an MGR environment with multiple machines in different network segments. The deployment process is similar, but there are still some differences. Here are the faults encountered in the deployment process for your reference. It would be great if I could solve your deployment problem.

01 Common Faults 1

[ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: bb874065-c485-11e8-8b52-000c2934472e:1 > Group transactions: 3db33b36-0e51-409f-a61d-c99756e90155:1-11'
[ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
[Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'

Solution:

According to the prompt, turn on set global group_replication_allow_local_disjoint_gtids_join=ON;

02 Common Faults 2

[ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: bb874065-c485-11e8-8b52-000c2934472e:1 > Group transactions: 3db33b36-0e51-409f-a61d-c99756e90155:1-15'
[Warning] Plugin group_replication reported: 'The member contains transactions not present in the group. It is only allowed to join due to group_replication_allow_local_disjoint_gtids_join option'
[Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address localhost.localdomaion:3306.'

Solution:

The difference between this fault and fault 1 is that when this problem occurs, the parameter group_replication_allow_local_disjoint_gtids_join has been set to on. The solution to this problem is to execute reset master, and then reopen the channel on the master node and the slave node.

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

03 Common Faults 3

When testing this machine, I encountered the following problem

[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master '[email protected]:' - retry-time: 60 retries: 1, Error_code: 2005
 [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.'
 [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.'
 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt /'

Solution:

This problem is caused by the fact that the hostnames of the three hosts in the test environment are set to the same name. After changing the hostname, this problem is solved.

04 Common Faults 4

#When operating in the online formal environment, the following error occurs:
mysql--root@localhost:(none) ::>>START GROUP_REPLICATION;
ERROR (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
#Check the log file and find only one warning:
2019-02-20T07::30.233937Z [Warning] Plugin group_replication reported: 'Group Replication requires slave-preserve-commit-order to be set to ON when using more than 1 applier threads.

Solution:

mysql--root@localhost:(none) ::>>show variables like "%preserve%";
+--------------------------------+---------+
| Variable_name | Value |
+--------------------------------+---------+
| slave_preserve_commit_order | OFF |
+--------------------------------+---------+
 row in set (0.01 sec)
mysql--root@localhost:(none) ::>>set global slave_preserve_commit_order=;
Query OK, rows affected (0.00 sec)

05 FAQ 5

2019-02-20T08::31.088437Z [Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 192.168.9.208 refused. 
Address is not in the IP whitelist.'
2019-02-20T08::32.088676Z [Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 192.168.9.208 refused.
 Address is not in the IP whitelist.'

Solution:

This can be solved by configuring the group_replication_ip_whitelist parameter in my.cnf

06 FAQ6

2019-02-20T08::44.087492Z [Warning] Plugin group_replication reported: 'read failed'
2019-02-20T08::44.096171Z [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 24801'
2019-02-20T08::14.065775Z [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group

Solution:

Set the parameter group_replication_group_seeds in my.cnf to include only the IP addresses of other group members except itself and the internal communication port. If it is written as the IP addresses of all group members, this error will occur. This is somewhat different from the MGR deployment method in the same network segment.

07 FAQ7

 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to oceanbase07: on local port: '.'
 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to oceanbase08: on local port: '.'
 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to oceanbase07: on local port: '.'

Solution:

The fixed port on the firewall is not opened. This can be solved after the firewall is opened.

08 FAQ8

[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
 [ERROR] Slave I/O for channel 'group_replication_recovery': Master command COM_REGISTER_SLAVE failed: Access denied for user 'rpl_user'@'%' (using password: YES) (Errno: 1045), Error_code: 1597
 [ERROR] Slave I/O thread couldn't register on master
 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position

Solution:

For users who missed a node, to be on the safe side, execute

CREATE USER rpl_user@'%';

GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';

09 FAQ9

 [ERROR] Failed to open the relay log './localhost-relay-bin.000011' (relay_log_pos ).
 [ERROR] Could not find target log file mentioned in relay log info in the index file './work_NAT_1-relay-bin. index' during relay log initialization.
 [ERROR] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
 [ERROR] Failed to open the relay log './localhost-relay-bin-group_replication_recovery.000001' (relay_log_pos ).
 [ERROR] Could not find target log file mentioned in relay log info in the index file './work_NAT_1-relay-bin-group_replication_recovery.index' during relay log initialization.
 [ERROR] Slave: Failed to initialize the master info structure for channel 'group_replication_recovery'; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
 [ERROR] Failed to create or recover replication info repositories.
 [ERROR] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: 
 [ERROR] /usr/local/mysql/bin/mysqld: Slave failed to initialize relay log info structure from the repository
 [ERROR] Failed to start slave threads for channel ''

Solution:

This error is caused by the slave node not being able to find the location of the relay-log for some reason. The slave needs to be reset.

The above are the details of the common problems and solutions encountered during the construction of MySQL MGR. For more information about MySQL MGR construction, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

How to configure MGR single master and multiple slaves in MySQL 8.0.15
What are the advantages of MySQL MGR?
Detailed explanation of mysql MGR single-master and multi-master mode switching knowledge points
mysql 8.0.18 mgr installation and its switching function
MySQL 8.0.18 uses clone plugin to rebuild MGR implementation
Detailed explanation of MySQL 5.7 MGR single master determination master node method

<<: Web Design Tutorial (1): Steps and Overall Layout

>>: Summary of xhtml block level tags