Description:
When creating a windows InnoDB cluster it will fail when trying to add the 2nd (or more) instance.
The reason for the failure is the installer uses port 33061 which is not opened in the firewall by the installer.
The installer does open ports 3306 and 33060
as a follow up I am finding the cluster wont allow the Instances to join once the fix below is applied.
each Instance/VM reports it is a "standalone instance". More testing will be done once a new installer is released.
How to repeat:
Instance 1 on VM 1.
Server only
Install C++ 2019 execute
Next / Execute
Next / Next
(All normal so far, now for the cluster)
Select INNODB CLuster
Select Create a new INNODB Cluster
Next
Type in a password x2
Select the HOST address as the IP = 192.168.0.167
(everything else is as default)
Next
default / next
default / next
type in root password x2
Next
default / Next
Execute
takes about a 6 minutes / finish / next / Finish
All done
Instance 2 on VM 2.
Server only
Install C++ 2019 execute
Next / Execute
Next / Next
(All normal so far and same as above, now for the cluster)
Select INNODB CLuster
Select Add Local ....
Next
Seed Instance = 192.168.0.167
port : 3306
Cluster admin username : ic
password x1
host address = 192.168.0.172
server ID: 2
Test Connection = Green Tick
Next
default / next
default / Next
Root password x 2
Next
default / Next
Execute
within a few minutes it gets to the last step and then just sits there.
in the log this is the last entry, everything prior to this all is OK as well.
Instance configuration is compatible with InnoDB cluster
The instance '192.168.0.172:3306' is valid for InnoDB cluster usage.
SUCCESS: The instance configuration has been prepared for InnoDB cluster usage.
Ended configuration step: Preparing MySQL Server for InnoDB cluster usage
Beginning configuration step: Adding local MySQL Server to production InnoDB cluster (check the Log tab for details)
Flushing binary log and resetting master...
Binary log flushed and master reset successfully.
Adding local MySQL Server to production InnoDB cluster...
Using the following parameters:
Cluster Admin Username: ic
Host Name of Cluster Instance: 192.168.0.167
Port of Cluster Instance: 3306
Host Name of Local Instance: 192.168.0.172
Port of Local Instance: 3306
Add Local Instance to MySQL InnoDB Cluster
=========================================
Adding the instance '192.168.0.172:3306'
to the InnoDB cluster running on '192.168.0.167:3306'
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster ...
Validating instance at 192.168.0.172:3306...
This instance reports its own address as 192.168.0.172
Instance configuration is suitable.
after 5 minutes you get a message "... adding local MySQL server to production InnoDB cluster is taking longer than expected. It is advised not to cancel the configuration and wait."
The log has not changed from the above.
after about 15 minutes more you get a lot of errors all about port 33061
ERROR: Group Replication plugin failed to start. Server error log contains the following errors:
2020-04-06T02:43:51.127112Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:44:01.139401Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:44:11.154479Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:44:21.155966Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:44:31.171804Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:44:40.766392Z 18 [ERROR] [MY-011640] [Repl] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2020-04-06T02:44:40.768694Z 18 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member is already leaving or joining a group.'
2020-04-06T02:44:41.172243Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:44:51.172381Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:45:01.172474Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:45:11.188140Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:45:21.188803Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:45:21.192224Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061'
2020-04-06T02:45:22.026618Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2020-04-06T02:45:37.357903Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
2020-04-06T02:45:47.373434Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.0.167:33061 on local port: 33061.'
I noticed in the firewall port 33060 is open but not 33061, so I opened it as well on both instances.
I then tried to add Instance 2 again, which failed with many errors, which might be for someone else to work out.
deleted both VM's and started again but this time manually opening port 33061 before starting the installation of MySQL.
BOOM !!!!
It works.
thanks
Damon
Suggested fix:
manually open port 33061 prior to running the MySQL Community Installer or MySQL WEB Community Installer.
Not sure if the installer should be using port 33060 or 33061 but I am sure you will.
