How to syntax-check your my.cnf file

How to syntax-check your my.cnf file

PREVIOUS POST
NEXT POST

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:

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.

PREVIOUS POST
NEXT POST

Share this post

Comments (15)

  • Alexey Kupershtokh Reply

    my.cnf is just an ini file. I’d write a script that uses some standard ini parser plus custom validation of keys/values.

    February 17, 2011 at 9:33 am
  • Michael Renner Reply

    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

    February 17, 2011 at 10:19 am
  • Baron Schwartz Reply

    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 🙂

    February 17, 2011 at 10:40 am
  • Alfredo Sosa Reply

    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”

    February 17, 2011 at 10:40 am
  • Romain GUINOT Reply

    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 ?

    February 17, 2011 at 3:45 pm
  • Baron Schwartz Reply

    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.

    February 17, 2011 at 4:17 pm
  • Romain GUINOT Reply

    Cool, thanks ! i’ll check it out (no pun intended 🙂 )

    February 18, 2011 at 12:26 am
  • Lazer Epilasyon Adana Reply

    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.

    February 18, 2011 at 6:55 am
  • snackyfufu Reply

    why not deploy your configs with chef or puppet and stop worrying about it?

    February 21, 2011 at 8:17 pm
  • honeybee Reply

    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.

    August 22, 2011 at 4:02 pm
  • Baron Schwartz Reply

    Yes, it certainly can. It’s just a convenience. It’s so convenient that it’s necessary, though!

    August 22, 2011 at 5:50 pm
  • Jonathan Valerio Reply

    You can also use mk-variable-advisor as a safe alternative.

    January 18, 2012 at 1:52 am
  • KB Reply

    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).

    October 8, 2013 at 4:43 pm
  • KB Reply

    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.
    %

    October 9, 2013 at 1:13 pm
  • Luis Reply

    It didn’t work for me when I tried adding bogus lines or even removing characters from existing directives.

    December 16, 2015 at 7:52 pm

Leave a Reply