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:
1 | /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.
my.cnf is just an ini file. I’d write a script that uses some standard ini parser plus custom validation of keys/values.
Doing tricks like this can and will cause problems; you and the other consultants are not consistently at 100% attention.
You already maintain your own collection of branches, if this is a problem for you why don’t you implement something like –configcheck or similar? I guess this’s something which will get picked up by upstream quite fast if it’s a worthwhile feature.
Don’t know how bad the MySQL architecture is though and how hard it would be to implement such a feature in an authoritative but nondestructive way.
best regards,
Michael
Clarification: syntax isn’t the only thing I’m checking. I’m also checking that options exist (in THIS version of the server), values are acceptable, etc. The server itself has to say whether the file is valid or not — no external source is trustworthy.
Ideally I want a way to check every server, not just those customers who are running our builds. If there isn’t one, then I will suggest this as a feature request either in our builds or upstream.
As to worthwhile changes getting picked up upstream, history does not agree with you 🙂
Strictly talking about checking the syntax of the my.cnf file…
/usr/sbin/mysqld –verbose –help 1>/dev/null
No output means no syntax errors
If you want to be on the safer side (paranoic)…
su -l nobody -s /bin/sh -c “/usr/sbin/mysqld –verbose –help 1>/dev/null”
(I used “nobody”, but can be any unprivileged user)
And following the original article example, as an unprivileged user:
su -l nobody -s /bin/sh -c “/usr/sbin/mysqld –help –verbose 1>/dev/null”
Unless i’m mistaken, another neat thing to have would be to check the “old” config file against the current values in the server, in case someone has changed a global variable without reflecting it in the config file. those changed could be lost upon restart. maybe a 3-way comparison between the old config, the new config, and some mysqladmin variables, to make sure you’re only changing what you want to change ?
Yes, we have a tool for that in Maatkit, called mk-config-diff. It is not quite complete yet, but it has served me well quite a few times.
Cool, thanks ! i’ll check it out (no pun intended 🙂 )
I actually wouldn’t do any of these. It’s a standard, and the intended audience are developers implementing the standard. Those people want the standard to be very, very precise. (For examples, I turn to Jim Melton’s books such as “Understanding the new SQL.”)
The one thing I would do is create a publicly accessible Test Compatibility Kit (TCK) that people can run against their chosen database. Vendors such as Oracle have claimed for years that they comply with SQL standards. If there was a public TCK, we could publish the results and shame them into action.
why not deploy your configs with chef or puppet and stop worrying about it?
Question:
Can a mysql server run without a my.cnf file? I have tested it, seems like it can, wondering if someone can verify that.
Yes, it certainly can. It’s just a convenience. It’s so convenient that it’s necessary, though!
You can also use mk-variable-advisor as a safe alternative.
While these cool “tricks” are always nice, I too wish that mysqld had a –check-config option that would truly just validate the config and report any/all problems/errors, then exit assuming that a mysqld was already running and not to interrupt it or that one should be running on “this server” using the –defaults-file I gave it. Yes –help method can work, but I don’t know if that’s supposed to verify that directories exist, that the mysql database is installed, and other similar items are appropriately placed. There’s nothing worse for a DBA than to make a change to a production instance’s config only to realize that someone else had placed an error in the file earlier that was waiting for a start to cause a crash (i.e. changing the datadir to some inappropriate value – even if by accident).
BTW: –defaults-file=… –help … doesn’t do what you suggested it might do for me. I would have expected mysql to gripe about the bogus=bogus line but it didn’t. I agree completely – I want an option to mysql that will validate a complete configuration for likely issues. See below.
% cat bogus.conf
[mysqld]
bogus=bogus
% /usr/sbin/mysqld –defaults-file=bogus.conf –help
/usr/sbin/mysqld Ver 5.5.25-enterprise-commercial-advanced for Linux on x86_64 (MySQL Enterprise Server – Advanced Edition (Commercial))
Copyright (c) 2000, 2011, 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: /usr/sbin/mysqld [OPTIONS]
For more help options (several pages), use mysqld –verbose –help.
%
It didn’t work for me when I tried adding bogus lines or even removing characters from existing directives.