Easily Validate Configuration Settings in MySQL 8

In past versions of MySQL, there was often an ‘upgrade dance’ that had to be performed in starting up a newly upgraded MySQL instance with the previous version configuration file. In some cases a few deprecated options might no longer be supported in the newer server version, triggering an error and a subsequent shutdown moments after starting. The same thing can happen even outside of upgrade scenarios if a configuration change was made with a mistake or typo in the variable name or value.

As of MySQL 8.0.16 and later, there is now a ‘validate-config’ option to quickly test and validate server configuration options without having to start the server. Once used, if no issues are found with the configuration file, the server will exit with an exit code of zero (0). If a problem is found, the server will exit with an error code of one (1) for the first occurrence of anything that is determined to be invalid.

Validating A Single Option

For example, consider the following server option (old_passwords) which was removed in MySQL 8:

Note the server exited with an error code of one (1) and is showing the error for the invalid option.

Validating A Configuration File

It is also possible to validate an entire my.cnf configuration file to check all options:

Note that the server exited on the first occurrence of an invalid value. Any remaining errors in the configuration file will need to be found after correcting the first error and running the validate-config option again. So in this example, I’ve now removed the ‘old_passwords=1’ option in the configuration file, and I need to run validate-config again to see if there are any other errors remaining:

Indeed, there is yet another option that was removed from MySQL 8 in the configuration file, so after running the validate again (after fixing the first issue), we’ve now identified a second problem. After removing the query_cache_size option from the my.cnf file and running validate-config again, we finally get a clean bill of health:

Change Validation Verbosity

By default, the validate-config option will only report error messages. If you are also interested in seeing any warnings or informational messages, you can change the log_error_verbosity with a value that is greater than one (1):

Now we are seeing warning level messages, and the server is exiting with an error code of zero (0) as there are technically no errors, only warnings. Taking this further, I’ve added the query_cache_size option from above back in the my.cnf file, and if we run the validate again we see both errors and warnings this time, while the server exits with an error code of one (1) as there really was an error:

Older Version Alternative

During my testing of the validate_config feature, a colleague pointed out that there is a way to replicate this validation on older MySQL versions by using a combination of ‘help’ and ‘verbose’ options as below:

Closing Thoughts

While not perfect, the validate-config feature goes a long way towards making upgrades and configuration changes easier to manage. It is now possible to know with some certainty that your configuration files or options are valid prior to restarting the server and finding an issue that ends up keeping your node from starting normally leading to unexpected downtime.

Percona Distribution for MySQL: An Enterprise-Grade MySQL Solution, for Free