3

I Get the following error when adding the second instance to a MYSQL Innodb Cluster

ERROR: Cannot add instance 'XXX' to the cluster because it has the same server ID of a member of the cluster. Please change the server ID of the instance to add: all members must have a unique server ID.
Cluster.addInstance: Access denied for user 'XXX'@'XXX' (using password: YES) (RuntimeError)

However if I check the two serverid's I can confirm that they are different

Server 1

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+
1 row in set (0.00 sec)

Server 2

show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

Please advise?

3

Did you restart the server after changing the server-id? Despite server-id being dynamic, there is the following caveat on the manual:

If the server ID is set to 0, binary logging takes place, but a master with a server ID of 0 refuses any connections from slaves, and a slave with a server ID of 0 refuses to connect to a master. Note that although you can change the server ID dynamically to a nonzero value, doing so does not enable replication to start immediately. You must change the server ID and then restart the server to initialize the replication slave.

I am giving a wild guess that may be your issue, because it is a very common problem that people suffer once when setting up replication.

Edit: Re "Interestingly mysql is ignoring the server_id in the my.cnf file and choosing its own."

Then you have a configuration problem in which your server id is not properly read, please use --print-defaults to debug which files and options are being applied live, and check the paths for correct permissions, file existence and avoid duplicate config definitions/unintended includes.

  • I tried your suggestion. But still the same thing. Interestingly mysql is ignoring the server_id in the my.cnf file and choosing its own. However both are unique. Still get the same problem. – Wize Nov 28 '19 at 8:14
  • By the way after the error is an access denied but the server can login via mysql to each other. – Wize Nov 28 '19 at 8:17
  • That's valuable information but that's not the cause of this particular issue. Please see my reply below. – Miguel Araújo Jan 7 at 13:12
0

Are you using different accounts to administer the cluster? i.e. did you use an account to create the cluster and are you using a different one to add the instance to it? Or is it the same account with a different password?

If that's the case, then that's the reason you're hitting this issue. The account used to administer the cluster must have the same credentials on all cluster members.

The recommendation is to create a clusterAdmin account, using dba.configureInstance(). More info in the docs.

The current error message is misleading and confusing. This will be improved soon and the documentation will also be improved to outline this requirement.

Cheers, Miguel

  • The documentation was already updated: dev.mysql.com/doc/refman/8.0/en/… Tip The clusterAdmin user name and password must be the same on all instances that belong to a cluster. And also in dev.mysql.com/doc/refman/8.0/en/…: Important Each sandbox instance uses the root user and password. This is not recommended in production, see Configuring Production Instances for information about the clusterAdmin and clusterAdminPassword options. – Miguel Araújo Jan 8 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.