How check your MySQL configuration for syntax errors

Introduction

When configuring services, it's important to try to validate your changes before applying them to the environments that they will impact. This is especially true for changes made to essential services like MySQL databases.

While many changes need to be checked manually for semantic correctness (making sure the configuration means what you intend it to mean), often you can automate checks for syntactic correctness (making sure the changes are in an expected format). This provides a limited, but extremely helpful check that can catch spelling mistakes, incorrect punctuation, and malformed configuration options that would otherwise prevent the server from starting correctly when attempting to incorporate new changes.

In this short guide, we'll cover how to validate MySQL server configuration files using the built-in options. This can help you validate that the edits you perform won't interfere with MySQL when you restart the service.

Checking MySQL server configuration files

The mysqld binary used to run the MySQL server includes many options that you might not use on a regular basis. One of these is the --validate-config flag.

The --validate-config flag causes the mysqld binary to parse its configuration file and then exit. You can run the validation check by typing:

mysqld --validate-config

If no problems are found, the program will exit successfully with no output instead of attempting to start the MySQL server. You can verify that the process exited successfully by checking its exit code:

echo $?

0

If any errors are found, MySQL will abort the process as it would in an actual startup scenario and output information about the file and line where the problem occurred. You can add syntax errors to your MySQL configuration file to trigger this process see the output. For instance, you can

echo "hello there" | sudo tee --append /etc/mysql/my.cnf

Now when you check the configuration again, the command outputs the offending line that we added to the configuration:

mysqld: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 23.
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

The exit code also verifies that an error occurred:

echo $?

1

You can use sed to remove the line we added to the MySQL configuration file. Validate the configuration file again to ensure that the removal was successful:

sudo sed --in-place '/hello there/d' /etc/mysql/my.cnf
mysqld --validate-config

The server configuration file is now verified to by syntactically correct again.

Conclusion

In this guide, we covered how to use the mysqld binary with the --validate-config flag to validate the MySQL server's configuration files for errors. While this cannot be used to detect all errors, it goes a long way towards preventing typos, incorrect configuration grammar, and invalid options. It is a good idea to always use the --validate-config flag before restarting your server to ensure that your new configuration is valid before stopping your database service.