Restoring MySQL. Problem solution

Hello! I'm Sasha Khrennikov, head of the DevOps unit at KTS.

We recently ran a DevOps challenge where we needed to recover a broken MySQL instance. It was not easy – the seven strongest DevOps masters, to whom we are already sending prize merch, completed it the fastest. Here they are, from left to right:

@ovsjke (Время прохождения: 31m 27s) @mr_hightlook (Время прохождения: 34m 6s) @angapov (Время прохождения: 51m 49s) @iTem86 (Время прохождения: 55m 14s) @Yepcock (Время прохождения: 56m 0s) @Benosa19 (Время прохождения: 58m 51s) @ovss_s (Время прохождения: 59m 38s)

In this article I will analyze the problem and show how it can be solved in two ways.

Table of contents

How to understand that the cluster is broken

First, let's figure out what happened to our cluster.

The first thing we see is pod mysql-0which is stuck in the Terminating state. To understand what happened, let's look at describe. Among other errors, we get the following line:

Handler 'on_pod_delete' failed temporarily: Cluster cannot be restored because there are unreachable pods

It looks like the pod has a stuck finalizer.

To get rid of this misunderstanding, you can safely run kubectl edit or patch, if that’s more common. Just remove these lines:

finalizers: - mysql.oracle.com/membership - kopf.zalando.org/KopfFinalizerMarker

After this we see that the pod starts. But the service still doesn’t want to respond. Let's continue the investigation and look at the MySQL log:

[Note] [MY-010926] [Server] Access denied for user 'mysqladmin'@'10-1-128-45.mysql-operator.mysql-operator.svc.cluster.local' (using password: YES)

Googling the username “mysqladmin”, in the first link we find that this is a service user created by the operator. The password for it is in the mysql-privsecrets secret. More details follow the link.

We extract the password from the secret and, having fallen into the mysql container, we check that the password does not match. It looks like some villain changed it…

kubectl exec -it mysql-0 -c mysql -- /bin/bash bash-5.1$ mysql -u mysqladmin -p Enter password: ERROR 1045 (28000): Access denied for user 'mysqladmin'@'localhost' (using password: YES)

How to restore the cluster? There are two ways.

Option one

We have the root user credentials and they are quite functional. Therefore, a simple and clear way would be to write his account from the mycluster-cluster-secret secret to the mysql-privsecrets secret: both the password and the username.

After which we see the treasured in the mysql-router logs:

metadata_cache WARNING [7f20d83d6640] Failed fetching metadata from metadata server on mysql-0.mysql-instances.default.svc.cluster.local:3306 - No result returned for metadata query
metadata_cache WARNING [7f20d83d6640] Metadata server mysql-0.mysql-instances.default.svc.cluster.local:3306 is not an online GR member - skipping.
metadata_cache INFO [7f20d83d6640] Potential changes detected in cluster after metadata refresh (view_id=0)
metadata_cache INFO [7f20d83d6640] Metadata for cluster 'mysql' has 1 member(s), single-primary:  
metadata_cache INFO [7f20d83d6640]     mysql-0.mysql-instances.default.svc.cluster.local:3306 / 33060 - mode=RW