If you’re a DBA, one of your “easiest” tasks is to stop/start MySQL during a maintenance window, but even that could lead to unwanted scenarios if you modify some dynamic parameters at some point in your instance.

Here’s a brief story of how this could happen, to make it clearer:

You’re a DBA managing a few MySQL servers. The application using one of them starts having issues on a Friday night, right before it’s time to leave; after a quick check, you notice the app is requesting more connections, and the hotfix is to up max connections; you change them dynamically, the fire is off, and so are you. Let your future self worry about finding the root cause and fixing it properly the following Monday.

 

But life happens; Monday is here with new challenges, and you already forgot about the connections issue… A few months later, a restart for MySQL is required, and surprise, surprise, right after the restart, the issue is back ‘unexpectedly’; now you have to troubleshoot and lose time wondering what happened and fixing it.

Side note: This is not advice on how to fix things; the story only introduces stuff that can happen when restarting MySQL and how to prevent it. This could also be a tale of why doing that kind of “administration” is bad.

Introduction of pt-config-diff and the situation

As you might know, the Percona Toolkit is a collection of advanced command-line tools used to perform various MySQL, MongoDB, and system tasks that are too difficult or complex to perform manually.

One of those tools is pt-config-diff; this tool can show differences within MySQL configuration files and server variables.

You can use it to compare two config files (i.e., you want to ensure your new server has the same settings as the old one) or one config file and a running MySQL instance (this is what we’ll use here).

Below are the commands and output that lead to the different values in the story.

First, let’s see the connection settings in the configuration file.

And before the dynamic change, MySQL is running with the same value.

That Friday night, the value was increased to 500:

By then, the settings in the configuration file and the runtime value for max_connections are different, and the dynamic changes will be lost after a MySQL reboot.

Side note: In case you missed it, with MySQL 8, you can now persist dynamic changes as well to avoid manual modification to the cnf file. See more in this blog post: Using MySQL 8 Persisted System Variables.

How to check dynamic changes

When it’s the moment to reboot MySQL, you can use pt-config-diff to validate if the runtime settings are the same as in the configuration file.

Using the same example, here’s the output:

Now, you know the difference between the config file and your runtime settings and can properly prepare for it accordingly (Modify the value in your config file or SET PERSIST).

Let’s modify the configuration file manually:

And re-checking with the same command as above, now there’s no output (Meaning no differences were found).

Things to be aware of

The tool will only compare the values for parameters present in both sources. 

Using the same example. if the max_connections parameter is not present in the configuration file (MySQL was running with the default value), pt-config-diff won’t show the value in the output.

Example:

The parameter is not present in the configuration file.

And MySQL is running with the default value:

Modifying the value dynamically (using the same commands as in the previous example) and running pt-config-diff, the output is empty.

As mentioned above, this happens because there’s nothing to compare to; the configuration file doesn’t have a value for the max_connections parameter.

Another thing to keep in mind is if you have more than one config file (Using !include or !includedir, or in more than one of the default places MySQL looks for config files), you can’t compare all of them at once.

Ensure you compare all the required config files if that’s your case.

Final thoughts

First, take this one as a reminder always to persist your changes, either manually adding them to your configuration files or using SET PERSIST (Note: This command will create/use mysqld-auto.cnf inside the datadir).

With that being said, even after checking with pt-config-diff, there could be some differences that could be lost after a reboot, but using this tool can help you prevent unwanted surprises and highlight possible issues before a restart.

As always, even when performing the “simplest” task, it’s recommended to have some pre-checks to ensure the activity goes smoothly.

Also, if you identified yourself with the story at the beginning of this article and feel you could use a hand, remember you can always rely on our Managed Services offering, where you can have a team of DBAs managing the instances for you.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

 

Try Percona Distribution for MySQL today!

Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments