Inside an OpenEverest MySQL Cluster: Galera Health and a Live Failover Test

Part 2 of 2: Reading wsrep status, killing the writer, and proving zero data loss

In Part 1 we built a 3-node K3s cluster, installed OpenEverest, and created a 3-node Percona XtraDB Cluster (PXC) named mysql-vjg. In this post we look inside that cluster.

• Section A explains how to read Galera’s wsrep_* status variables, so you can tell at a glance whether the cluster is healthy.
• Section B is a live failover test. We delete the writer pod while a client inserts a row every second, then watch HAProxy switch writers, the pod rejoin, and all three nodes end up with identical data.

All output shown in this post is real output from a lab cluster whose nodes are named controlplane, worker1 and worker2. Your node names, pod IPs, timestamps and IDs will differ.

Important: The commands use the example cluster name mysql-vjg from Part 1. Replace it with your own cluster name everywhere (find it with kubectl get pxc -n everest). If you created your database in a namespace other than everest, replace -n everest as well.

Before You Start

Where to run: every command runs on the control plane node, where kubectl is configured, unless a step says otherwise. Some commands run inside a temporary client pod; those steps say so explicitly.

Several commands need the MySQL root password. Load it from the Kubernetes Secret into a shell variable:

Section A: Understanding Galera Health

PXC replicates data with the Galera library. Galera reports its state through status variables whose names start with wsrep_, short for write-set replication. A write-set is the bundle of row changes one transaction produces. At commit time, the write-set is sent to every node and checked for conflicts (certification) before the commit succeeds.

Step A1: Take a Status Snapshot

Connect through HAProxy (Part 1, Step 6.5) or into a pod (Part 1, Step 6.8) and run:

SQL

Output. 

Step A2: What Each Value Means

Step A3: The Node State Machine

Every time a node joins the cluster, it moves through these states. wsrep_local_state shows the number, and wsrep_local_state_comment shows the name.

Step A4: IST vs SST: How a Node Catches Up

A node that rejoins after a restart needs the write-sets it missed. There are two ways to get them:

• IST (Incremental State Transfer): a donor node replays only the missing write-sets from its gcache, a ring buffer of recent write-sets. It takes seconds and puts little load on the donor.
• SST (State Snapshot Transfer): a full copy of the dataset, taken with Percona XtraBackup. It can take a long time on large databases, and the donor does extra work while it runs.

The joining node gets an IST if a donor’s gcache still contains every write-set it missed. Otherwise it needs an SST. This is why gcache size matters: a larger gcache lets a node stay away longer and still rejoin with a fast IST. You will see a real IST in Section B.

Step A5: Other Variables Worth Monitoring

These are not part of the snapshot above, but they are the first places to look when a cluster is slow or applications see unexpected errors.

SQL

Step A6: One-Shot Health Check Across All Pods

Instead of logging into each node, this loop runs one query on each of the three PXC pods with kubectl exec and prints the results side by side. How it works:

• for i in 0 1 2 loops over the pod numbers, so mysql-vjg-pxc-$i becomes mysql-vjg-pxc-0, -1 and -2.
• -c pxc selects the database container in each pod.
• — separates kubectl‘s own options from the command to run inside the pod. Without it, kubectl tries to parse -uroot itself and fails with unknown shorthand flag: ‘u’.
• env MYSQL_PWD=”$ROOT_PW” passes the password to the MySQL client as an environment variable, so it does not appear in the command line.
• -Nse runs the SQL without column headers (-N), in silent tab-separated mode (-s).

Output

Every node reports Synced, Primary, a cluster size of 3, and the same wsrep_last_committed value (1057). All three nodes have applied exactly the same write-sets.

Section B: Live Failover Test

In this test, a client inserts one row per second through HAProxy. While it runs, we delete the writer pod, mysql-vjg-pxc-0, and observe what happens. The client runs inside the cluster, so that an SSH tunnel or kubectl port-forward dropping cannot be confused with the failover itself.

Open three SSH sessions to the control plane and use them like this:

Step B1: Start a Temporary MySQL Client Pod (Terminal A)

kubectl run starts a throwaway pod from the percona/percona-server:8.0 image, which includes the mysql client. The options:

• -it attaches your terminal to the pod, and –command — bash starts a shell instead of a MySQL server.
• –rm –restart=Never deletes the pod as soon as you exit the shell.
• –env=”MYSQL_PWD=$ROOT_PW” puts the root password into the pod’s environment, so mysql commands inside the pod do not need -p.

Output

The prompt changes to [mysql@mysql-client /]$: you are now inside the pod. The warning is a reminder that anything typed here ends up in the pod’s logs. The password is also visible in the pod spec, which is acceptable for a short-lived lab pod but not for production.

Step B2: Create a Test Table (Terminal A, inside the pod)

mysql-vjg-haproxy is the Kubernetes DNS name of the HAProxy Service. It resolves only inside the cluster, which is why these commands run from the client pod. The table records which node served each insert (served_by) and when (ts, with millisecond precision).

Step B3: Start the Write Loop (Terminal A, inside the pod)

Every second, the loop opens a new connection through HAProxy, inserts one row, and prints the time, the node that served the request, and the ID it generated. –connect-timeout=2 stops a failed connection from hanging the loop, and 2>&1 makes any error message appear in the output.

Leave it running. Before the failover, every line shows mysql-vjg-pxc-0, the current writer.

Step B4: Watch the PXC Pods (Terminal B)

-w (watch) keeps the command running and prints a new line every time a pod changes state. -o wide adds the pod IP and the node it runs on, and grep –line-buffered pxc keeps only PXC pods while printing each line immediately.

Step B5: Delete the Writer Pod (Terminal C)

Make sure Terminals A and B are both running, then delete the writer. kubectl delete pod sends the pod a SIGTERM, which simulates a planned restart or node maintenance. The StatefulSet that owns the pod immediately creates a replacement with the same name.

Output

Step B6: What the Application Saw (Terminal A)

Here is the write loop output around the failover, trimmed (… marks removed lines):

Output (trimmed, annotations added)

Zero failed writes

The write at 09:07:40 went to pxc-0, and the next one, one second later, went to pxc-2. Not a single insert failed. Three things made this possible:

• Graceful shutdown. SIGTERM let mysqld leave the Galera cluster cleanly instead of crashing.
• Health-checked routing. HAProxy checks every node’s Galera state and stopped sending new connections to pxc-0 as soon as it began shutting down.
• Short connections. Each loop iteration opens a new connection, so no connection was open on pxc-0 when it went away.

Important: HAProxy fails over routing, not sessions. Connections that are open on the stopped node are closed, and transactions that were not yet committed are rolled back. Applications need reconnect-and-retry logic. Connection pools that check connections before use handle this for you. Step B11 shows this in action.

The auto-increment step follows cluster membership

Look at the IDs. Galera sets auto_increment_increment to the current cluster size and gives each node a different auto_increment_offset (the wsrep_auto_increment_control setting). Two nodes can then never generate the same ID, even when both accept writes. As a side effect, the IDs show cluster membership changing in real time:

Gaps in auto-increment IDs are normal in Galera. Applications should never assume IDs are consecutive.

Step B7: What Kubernetes Did (Terminal B)

Output (columns and duplicate lines trimmed)

Reading it from top to bottom:

• The three PXC pods run on three different nodes. OpenEverest uses pod anti-affinity so that losing one node never takes out two database replicas.
• Terminating to Completed (about 14 seconds): mysqld shut down gracefully and the container exited cleanly.
• Pending to Init to Running: the StatefulSet created a new pod with the same name on the same node. The local-path volume (Part 1, Step 4.3) lives on that node’s disk, so the pod has to come back there to reattach its data.
• Running 0/1 to 1/1 (about 30 seconds): the container was running but did not pass its readiness check until mysqld had rejoined the cluster and reached Synced. The pod also got a new IP (10.42.2.17), which is why applications must use Service names and never pod IPs.

Step B8: How pxc-0 Rejoined: IST or SST? (Terminal C)

First confirm the node is back in the Synced state:

Output

Then search the pod’s logs for state-transfer messages. –all-containers covers the case where the error log is collected by a separate logging container.

Output (trimmed)

The node rejoined with an IST: it received the 27 write-sets it missed (sequence numbers 341 to 367) in about 0.2 seconds. This was possible because the pod reattached its existing volume, so it only lacked the writes from the few seconds it was down, and those were still in the donor’s gcache. If the volume had been lost, the node would have needed a full SST.

Put together with the auto-increment table, the timeline is: pxc-0 rejoined the Galera group at about 09:08:16 (cluster size back to 3), received the IST at 09:08:21 to 09:08:22, reached Synced, and then passed its Kubernetes readiness check.

Step B9: Where Did the Writes Go? (Terminal A)

Stop the loop with Ctrl+C, then summarize which node accepted the writes:

Output

Two things stand out:

• Writes moved back to pxc-0. The last write on pxc-2 was at 09:08:35, about 13 seconds after pxc-0 finished its IST. From then on, pxc-0 served every write. HAProxy prefers pxc-0 as the writer and switches back to it once it is healthy again. The write loop kept running past 09:08:21, which is why the totals go on until 09:21.
• The totals include an earlier practice run. pxc-2‘s first write at 09:05:51 comes from a previous failover test, so the counts cover both runs.

Step B10: Verify That All Nodes Hold Identical Data (Terminal C)

The final check: query each PXC pod directly, bypassing HAProxy, and compare the row count, the highest ID, and a checksum of the whole table. CHECKSUM TABLE computes a hash over every row, so even one different value on one node would change it.

Output

All three nodes have 1007 rows and the same checksum, including pxc-0, which was deleted in the middle of the test. Galera replication is virtually synchronous: a transaction commits only after every node has received and certified its write-set. So an acknowledged write cannot be lost when a single node fails.

Also note that MAX(id) is 2960 for only 1007 rows. That is the auto-increment step at work: with 3 nodes, IDs advance by 3, so the highest ID is roughly three times the row count.

Step B11 (Optional): What Happens to a Running Query?

The write loop opened a new connection every second, so no connection was open when the writer stopped. What happens to a query that is running at that moment? In the client pod, start a query that sleeps for 20 minutes:

While it is sleeping, delete the pod it printed (the current writer) from Terminal C:

The query ended right away, without an error:

Output

SLEEP() returns 0 when it sleeps the full time and 1 when it is interrupted. Here is what happened:

• kubectl delete pod sends a normal stop signal (SIGTERM), so MySQL shuts down gracefully.
• During a graceful shutdown, MySQL first interrupts every running query and sends the client a result. That is why SLEEP returned 1.
• Then MySQL closes all connections. Our client had no more statements to run, so it exited normally and no error was shown.

The connection was still closed. You see an error if you try to keep using the same connection. To see this, open an interactive session and start a transaction:

mysql -h mysql-vjg-haproxy -uroot

SQL

Delete the pod that @@hostname printed from Terminal C, wait about 20 seconds, then run COMMIT; in the same session. The client should report an error such as ERROR 2013 (HY000): Lost connection to MySQL server during query. The client may then reconnect to the new writer by itself, but the open transaction is gone. The row was not saved, which you can check with:

SQL

The lesson: a failover never gives you corrupt or half-written data, but open connections and transactions are lost. Applications must reconnect and retry the whole transaction.

Step B12 (Optional): Take a Whole Server Offline

So far we deleted one pod. Now we take a whole server out of service, as you would for maintenance such as an OS update or a reboot.

kubectl drain stops all the pods on a server and blocks new pods from starting there. The server keeps running and its data stays on disk. It stays blocked until you run kubectl uncordon.

Where to run: all commands in this step run on the control plane node. kubectl acts on the worker node through the Kubernetes API, so you don’t need to log in to the worker.

B12.1:  Pick a node

Check which PXC pod runs on which node:

We drain worker2. It runs mysql-vjg-pxc-0, the current writer, so this tests losing a server and a writer failover at the same time.

B12.2: Drain the node

Result: 

 –ignore-daemonsets: Some helper pods, such as the ones that handle networking, must run on every server. They can’t move to another server, so drain skips them and leaves them running.

–delete-emptydir-data: Some pods keep temporary files that are deleted when the pod stops. This option tells drain that it’s OK to delete those files. Your database data is safe, because it’s stored on a separate disk volume that drain doesn’t touch. 

B12.3: Check the database pod

mysql-vjg-pxc-0 is Pending and has no node. Its data is on a local-path volume on worker2’s disk, and worker2 is blocked, so Kubernetes has nowhere to start it. It waits.

The database keeps working on the other two nodes. Two out of three is still a majority, so the cluster keeps quorum.

B12.4: Bring the node back

The waiting pod starts right away on worker2 and reattaches its data:

0/1 Running means MySQL has started but is still rejoining the cluster and catching up. When it shows 1/1, it’s Synced and back in service.

Key Takeaways

• A healthy PXC node shows Synced (state 4), Primary, the expected cluster size, and the same wsrep_last_committed value as its peers.
• HAProxy sends writes to one node to avoid certification conflicts, and moved writes to a healthy node within one second when that node was deleted. It moved them back once the original writer had recovered.
• Applications that open short connections saw zero failed writes. A running query is interrupted cleanly, but open connections are closed and uncommitted transactions are lost, so applications must reconnect and retry.
• A restarted pod reattaches its own volume and catches up with a fast IST from the gcache. Losing the volume means a full SST.
• With local-path storage, a database pod is tied to its node. Replicated storage removes that limitation.
• After all of this, every node held byte-for-byte identical data.