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.