This blog was published in April of 2021 and updated in April of 2024.
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:
1 2 3 | ./mysqld --old_passwords=1 --validate-config 2021-03-25T17:56:49.932782Z 0 [ERROR] [MY-000067] [Server] unknown variable 'old_passwords=1'. 2021-03-25T17:56:49.932862Z 0 [ERROR] [MY-010119] [Server] Aborting |
Note the server exited with an error code of one (1) and is showing the error for the invalid option.
Related Blog: Key MySQL Performance Tuning Tips to Improve Database Performance
Validating A MySQL Configuration File
It is also possible to validate an entire my.cnf configuration file to check all options:
1 2 3 | ./mysqld --defaults-file=/home/sandbox/my.cnf --validate-config 2021-03-25T18:03:41.938734Z 0 [ERROR] [MY-000067] [Server] unknown variable 'old_passwords=1'. 2021-03-25T18:03:41.938865Z 0 [ERROR] [MY-010119] [Server] Aborting |
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:
1 2 3 | ./mysqld --defaults-file=/home/sandbox/my.cnf --validate-config 2021-03-25T18:08:28.612912Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query_cache_size=41984'. 2021-03-25T18:08:28.612980Z 0 [ERROR] [MY-010119] [Server] Aborting |
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:
1 | ./mysqld --defaults-file=/home/sandbox/my.cnf --validate-config |
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):
1 2 | ./mysqld --defaults-file=/home/sandbox/my.cnf --log-error-verbosity=2 --validate-config 2021-03-25T18:20:01.380727Z 0 [Warning] [MY-000076] [Server] option 'read_only': boolean value 'y' was not recognized. Set to OFF. |
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:
1 2 3 4 | ./mysqld --defaults-file=/home/sandbox/my.cnf --log-error-verbosity=2 --validate-config 2021-03-25T18:23:11.364288Z 0 [Warning] [MY-000076] [Server] option 'read_only': boolean value 'y' was not recognized. Set to OFF. 2021-03-25T18:23:11.372729Z 0 [ERROR] [MY-000067] [Server] unknown variable 'old_passwords=1'. 2021-03-25T18:23:11.372795Z 0 [ERROR] [MY-010119] [Server] Aborting |
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:
1 2 3 4 5 6 7 8 9 10 11 12 | $ mysqld --defaults-file=/tmp/my.cnf --verbose --help 1>/dev/null; echo $? 0 $ echo default_table_encryption=OFF >> /tmp/my.cnf $ mysqld --defaults-file=/tmp/my.cnf --verbose --help 1>/dev/null; echo $? 2021-03-26T08:43:04.987265Z 0 [ERROR] unknown variable 'default_table_encryption=OFF' 2021-03-26T08:43:04.990898Z 0 [ERROR] Aborting 1 $ mysqld --version mysqld Ver 5.7.33-36 for Linux on x86_64 (Percona Server (GPL), Release 36, Revision 7e403c5) |
Further Learning: Upgrade MySQL to 8.0? Yes, but Avoid Disaster!
The Importance of Validate-Config in MySQL Configuration
While not perfect, the validate-config feature goes a long way toward 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.
Our MySQL Performance Tuning eBook is an essential guide covering the critical aspects of MySQL performance optimization. It will help you ensure your databases run smoother, faster, and more reliably.
Download now: Unlock The Full Potential of Your MySQL Database
FAQs
How does a configuration mistake or typo impact the startup of a MySQL server?
A configuration mistake or typo can prevent the MySQL server from starting up correctly. If the error occurs in the my.cnf or my.ini file, the server might fail to start, or it might start with default settings if the error is non-critical.
How does the ‘validate-config’ option work in MySQL 8?
The validate-config option in MySQL 8 checks the configuration file for errors before starting the server. This option helps in identifying issues such as typos, incorrect settings, or incompatible configurations, ensuring they can be addressed before the server attempts to run.
What happens when an invalid configuration setting is detected during validation?
When an invalid configuration setting is detected during validation with the validate-config option, MySQL reports the errors and fails to start. This prevents the server from running with potentially harmful or unstable settings.
How does proper MySQL configuration contribute to the security and stability of database operations?
Proper configuration of MySQL is critical for both security and stability. It ensures that access controls are correctly enforced, resources are optimally allocated, and performance is maximized. Secure configurations prevent unauthorized access and data breaches, while stable settings ensure that the database operates reliably without unexpected downtimes due to misconfigurations.
we can always put loose with all parameters in case if we are not sure whether the specific parameters is supported in the newer version . But yes checking it exclusively can make work easy.