MySQL client goes down after using setPrimaryInstance() in InnoDB clustering, but the cluster remains up with all nodes functioning

Question

So, I have a cluster of 3 nodes, each in a different data center located in the same city. My architecture is a single primary one where one node (let's call it Node 1) is primary and the other two are secondary nodes.

So, I noticed that the MySQL client is down. I tried to restart it but it failed. When I checked the logs, I saw an error that said: InnoDB: Unable to lock ./ibdata1, error: 11.

At this point, trying to resolve the issue, the only thing that worked for me is when I used lsof -i:3306 and killed the process that was associated with mysqld.

As I expected, killing the mysqld process forced my cluster to switch its primary node from Node 1 to Node 3. Then I used mysqlsh to manage my cluster and used the setPrimaryInstance() method to reswitch from Node 3 back to Node 1, as my primary node.

After doing so, the MySQL client in Node 1 stopped working again and it crashed and can't start again. I have to go through the same process (i.e. kill the process associated with it) for it to work again, which will again switch the primary node.

So, my question is: Is there any way to keep Node 1 as the primary node while MySQL client still works? And why does switching the primary instance make MySQL client go down anyway? I can't understand why it happens.

P.S. I also have mysql router installed on my system. And I am using MySQL version 8.0.25.

Answer 1

The daemon is called mysqld. This is normally "started" once after booting, probably automatically.

The commandline client is called mysql; this is not a 'service' and does not need sudo.

"Cluster" implies multiple servers. That implies that you must connect to a specific machine (or through a proxy). Running mysql without a -h asks to connect to the instance on the same machine, that is, through a "socket".

If that is not sufficient to figure out what to fix, then please list the machines involved, stating which MySQL component is on which server. Include the client(s), such as mysql, MySQL Shell, and the "automatic scripts".