Check the MySQL server startup configuration

June 11, 2019
Caribbean by Olivier DASINI

Since 8.0.16, MySQL Server supports a validate-config option that enables the startup configuration to be checked for problems without running the server in normal operational mode:

  • If no errors are found, the server terminates with an exit code of 0.
  • If an error is found, the server displays a diagnostic message and terminates with an exit code of 1.

validate-config can be used any time, but is particularly useful after an upgrade, to check whether any options previously used with the older server are considered by the upgraded server to be deprecated or obsolete.


First let’s get some information about my MySQL version and configuration.

I’m using MySQL 8.0.16.
The default options configuration are read in the given order from :

  • /etc/my.cnf
  • /etc/mysql/my.cnf
  • /usr/local/mysql/etc/my.cnf
  • ~/.my.cnf


Now let’s check my MySQL server startup configuration :

No error !
No output, everything looks good.
My server will start with this configuration.


If there is an error, the server terminates.
The output is obviously different :


Usually your configuration options are written in your configuration file (in general named my.cnf).
Therefore you can also use validate-config in this context :

Note:

defaults-file, if specified, must be the first option on the command line.


Furthermore you can handle the verbosity using log_error_verbosity :

  • A value of 1 gives you ERROR
  • A value of 2 gives you ERROR & WARNING
  • A value of 3 gives you ERROR, WARNING & INFORMATION (i.e. note)

With a verbosity of 2, in addition to errors, we will be able to display warnings :

Nothing very serious, however it is a best practice to delete warnings, when possible.

So I fixed these warnings :

Oops!!! There is a typo… :-0
I wrote binlog_expire_logs_second instead of binlog_expire_logs_seconds.
(I forgot the final “s”)

In that case, my MySQL server could not start.
Thanks to validate-config !
I can now avoid some unpleasant experience when starting the server 🙂

With the correct spelling I have now no error and no warning :


Note that you could also use verbosity 3


validate-config is convenient and can be very useful.
It may be worthwhile to include it in your upgrade process.


References


Thanks for using MySQL!

Follow me on twitter

Leave a Reply