Automatically restarting Percona XtraDB cluster
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-serviceSo 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