yumupgrades for production use, this is the repository for you.
Active subscription is required.
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/nullstill emits a startup message to
2018-06-14 11:52:02 0 [Note] mysqld (mysqld 5.6.39-83.1) starting as process 7713 ..
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.
#!/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
systemctl reload mysqld
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:
#!/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:
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:
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.