Safe MySQL configuration reload? Easy

Inspired with “How to check MySQL Config files“, I wanted to have a better way to check MySQL configuration for errors.

Using approach in that post is great. However:

• When there are no errors, you’d see a long list of MySQL options in the output
• Trying to silence those by redirecting output to /dev/null still emits a startup message to stderr:

2018-06-14 11:52:02 0 [Note] mysqld (mysqld 5.6.39-83.1) starting as process 7713 ..

But why?

The reason to this, is that MySQL service has to do some initialisation work with its plugins in order to be able to show all the possible configuration options.
MySQL service quickly starts and quits.

That extra output wasn’t something I wanted to deal with 🙂

The proper command for checking MySQL configuration and silencing extraneous output would be:

mysqld --help 2> >( grep -v "starting as" 1>&2 ) > /dev/null

Here, we search stderr output stream for startup message and filter it, while redirecting all the helpful options to nowhere.
We are not interested in information about MySQL options, we want only information about configuration errors.

Instead of memorising the above command, it’s convenient to create a simple script in your PATH to quickly check for MySQL configuration errors.

/usr/local/bin/mysqlconfigtest.sh:

#!/bin/bash
set -eo pipefail
mysqld --help 2> >( grep -v "starting as" 1>&2 ) > /dev/null

You can check it by running mysql-config-test.sh. The exit status would be zero if there are no errors.
If there are errors, those will be output to stderr and the exit status would be appropriately not zero.

Safe MySQL restarts in SystemD

One thing I was quite missing from default MySQL distribution is the lack of service reload command.

systemctl reload mysqld

Will yield:

Failed to reload mysqld.service: Job type reload is not applicable for unit mysqld.service.
See system logs and ‘systemctl status mysqld.service’ for details.

It immediately occurred to me that I can use the above configuration check to implement something like a safe “reload” handler for MySQL. That is, apply the new configuration without worrying that it will result in a dead MySQL instance.

MySQL doesn’t support actual runtime configuration reloads. But we can implement our own.

SystemD is great because you can easily override, or even further extend existing services with a few commands. But as I’ve learnt it’s not good to implement reload function in SystemD if the underlying service does not support true reloads.

So we could have implemented ExecReload in systemd, but this is not the right path. Let’s create a simple script instead:

/usr/local/bin/mysqlreload.sh

#!/bin/bash
set -eo pipefail
/usr/local/bin/mysqlconfigtest.sh && (sleep 1; /usr/bin/systemctl restart mysqld)

You’ll notice that I’m using sleep 1. This part is necessary: when configuration has no error, the MySQL instance that starts for checking configuration errors doesn’t immediately quit and might collude with the one you’re restarting.

Now you can play with your production MySQL configuration much safer:

mysqlreload.sh

If you have configuration errors, your running MySQL instance will not be affected in any way. You will be able to check the configuration error lines with:
t
systemctl status mysqld

One could actually make better reload by checking modification time of /etc/my.cnf (and other ones that you use) and compare to MySQL process uptime to avoid restarting the service unnecessarily.