Automatically restarting Percona XtraDB cluster

2023-02-01T10:05:00-08:00

I've been experimenting with Percona XtraDB cluster, and found that by default it requires manual intervention to restart the cluster from an all-nodes-down state when the nodes were gracefully shutdown. The docs talk about identifying which node has safe_to_bootstrap: 1 in it's /var/lib/mysql/grastate.dat file, and on that node starting the mysql@boostrap service instead of just plain mysql.

Looking at a file and acting on what's found seems like something that could be automated, so here's my take for an Ubuntu 22.04 setup:

On each node (yay Ansible!) I added this script as /usr/local/sbin/choose-mysql-service.sh

#!/bin/bash

GRASTATE="/var/lib/mysql/grastate.dat"

service="mysql"

# Start a different service if grastate.dat is present
# with safe_to_bootstrap: 1
#
if [ -f $GRASTATE ]; then
    if grep --quiet "^safe_to_bootstrap: 1" $GRASTATE; then
        service="mysql@bootstrap"
    fi
fi

echo "Starting $service"
systemctl start $service

Then I added a one-shot systemd unit to execute at boot time, as /etc/systemd/system/choose-mysql-service.service

[Unit]
Description=Choose MySQL service
After=network.target

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/choose-mysql-service.sh
RemainAfterExit=true

[Install]
WantedBy=multi-user.target

And the disabled the default mysql service and enabled my new unit with:

systemctl daemon-reload
systemctl disable mysql
systemctl enable choose-mysql-service

So now when the OS boots, instead of just blindly trying to start mysql, it looks at the grastate.dat and if it has safe_to_bootstrap: 1 it starts mysql@bootstrap instead - or otherwise falls back to the default of starting mysql

I also shared this on the Percona Forum, look for feedback there

mysqldump: Got error: 1049: Unknown database when using LOCK TABLES

2022-12-22T14:56:00-08:00

Ran in to a confusing error message today:

mysqldump: Got error: 1049: Unknown database 'mydb' when using LOCK TABLES

I did have a database named mydb and then deleted it the other day. Later when doing some scripted mysqldumps of other databases, I got that message. Why in the world was it trying to lock a database I knew to be gone?

Turns out one of my other dbs was referencing the deleted db in a VIEW. Hope that helps

Fun with ones and zeros - mysql

Automatically restarting Percona XtraDB cluster

mysqldump: Got error: 1049: Unknown database when using LOCK TABLES