dasini.net – Diary of a MySQL expert

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.

$ mysqld --help --verbose | head -n13
mysqld  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

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 :

$ mysqld --validate-config
$

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 :

$ mysqld --validate-config --fake-option
2019-06-05T15:10:08.653775Z 0 [ERROR] [MY-000068] [Server] unknown option '--fake-option'.
2019-06-05T15:10:08.653822Z 0 [ERROR] [MY-010119] [Server] Aborting

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 :

$ mysqld --defaults-file=/etc/my.cnf --validate-config 
$ 

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 :

$ mysqld --defaults-file=/etc/my.cnf --validate-config  --log_error_verbosity=2
2019-06-05T15:53:42.785422Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2019-06-05T15:53:42.785660Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.

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

So I fixed these warnings :

• Use binlog_expire_logs_seconds instead of expire-logs-days
• Setup the right permissions for my directory secure-file-priv

$ mysqld --defaults-file=/etc/my.cnf --validate-config  --log_error_verbosity=2
2019-06-05T16:04:32.363297Z 0 [ERROR] [MY-000067] [Server] unknown variable 'binlog_expire_logs_second=7200'.
2019-06-05T16:04:32.363369Z 0 [ERROR] [MY-010119] [Server] Aborting

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 :

$ mysqld --defaults-file=/etc/my.cnf --validate-config  --log_error_verbosity=2
$ 

Note that you could also use verbosity 3

$ mysqld --defaults-file=/etc/my.cnf --validate-config  --log_error_verbosity=3
2019-06-05T16:02:03.589770Z 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled.
2019-06-05T16:02:03.590719Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'MyISAM'
2019-06-05T16:02:03.590763Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'CSV'

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

References

• Server Configuration Validation
• validate-config
• log_error_verbosity

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
Astuce, MySQL by Olivier DASINI