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.
About Baron Schwartz
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.