How to syntax-check your my.cnf fileBaron Schwartz
For a long time I’ve used a little trick to check whether there are syntax errors in a server’s my.cnf file. I do this when I need to shut down and restart the server, and I’ve either made changes to the file, or I’m worried that someone else has done so. I don’t want to have extra downtime because of a syntax error.
The trick is to examine the currently running MySQL server’s command-line from ps -eaf | grep mysqld, and then copy those options into something like the following:
/usr/sbin/mysqld <options> --help --verbose
However, this requires care. First, it should be run as a user who doesn’t have write privileges to the database directory, so it can’t actually mess with the server’s data if something goes wrong. Second, you need to specify a non-default socket and pid-file location. If you run the command as a privileged user, it will actually remove the pid file from the running server, and that can break init scripts.
Because of the above risks, I am extremely careful with this technique, and I have always wanted a better way. In fact, I only recently discovered the gotcha with the pid file. Perhaps readers can suggest something safer but still effective in the comments.