How to check MySQL Config files

check MySQL Config filesProbably most of MySQL users sometimes had a situation when they would do changes in MySQL config file without restarting the server (maybe matching SET GLOBAL command to do it in run time).  This could be because it is misspelled or because the given version does not support such option, the server when would refuse to start, either on operating system restart or recovering from MySQL Server crash. In any case, it is quite nasty.

Check MySQL Config files

Unlike Apache MySQL service control script does not have “configtest” option so there is no straight way to check MySQL config file for errors. However, you can do it by running mysqld –help option:

This does not do complete check, i.e. it is possible to allocate 80GB to buffer pool instead of 8G you intended, but at least it checks options for names which is good sanity check.

I would encourage you to run such check after you made changes to your config file for sanity purposes.

Share this post

Comments (8)

  • walrus

    I believe the proper way is to submit a feature request on and ask them to add special options, say, –check_config to mysqld

    February 14, 2008 at 6:28 am
  • peter

    It would,

    Though I honestly do not expect little things as these to be given a lot of priority 🙂

    February 14, 2008 at 6:42 am
  • peterwang

    hi, what’s your mysql version?
    it doesn’t work for me.
    /usr/libexec/mysqld Ver 4.1.18 for pc-linux-gnu on i686 (Source distribution)

    February 18, 2008 at 1:22 am
  • peter

    Hm. I think it worked for MySQL 4.1
    I tested it with MySQL 5.0

    February 18, 2008 at 6:40 am
  • sv

    Yup. I think the problem might be with your configuration. I use Mysql version 5.0.
    and I get following message, I am not use of the warning message. Can you help

    090922 2:30:57 [Warning] option ‘max_join_size’: unsigned value 18446744073709551615 adjusted to 4294967295
    090922 2:30:57 [Warning] option ‘max_join_size’: unsigned value 18446744073709551615 adjusted to 4294967295

    /usr/libexec/mysqld Ver 5.0.67 for redhat-linux-gnu on i386 (Source distribution)
    Copyright (C) 2000 MySQL AB, by Monty and others
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL license

    Starts the MySQL database server

    Usage: /usr/libexec/mysqld [OPTIONS]

    For more help options (several pages), use mysqld –verbose –help

    September 20, 2009 at 8:21 am
  • rajeev

    Copy the following code into “mysql_test.php” and click save. (Make sure to replace the MYSQL_PASS constant with the MySQL Password you specified during the MySQL installation).


    # Define MySQL Settings
    define("MYSQL_HOST", "localhost");
    define("MYSQL_USER", "root");
    define("MYSQL_PASS", "password");
    define("MYSQL_DB", "test");

    $conn = mysql_connect("".MYSQL_HOST."", "".MYSQL_USER."", "".MYSQL_PASS."") or die(mysql_error());
    mysql_select_db("".MYSQL_DB."",$conn) or die(mysql_error());

    $sql = "SELECT * FROM test";
    $res = mysql_query($sql);

    while ($field = mysql_fetch_array($res))
    $id = $field['id'];
    $name = $field['name'];

    echo 'ID: ' . $field['id'] . '’;
    echo ‘Name: ‘ . $field[‘name’] . ”;

    Open up Internet Explorer and type in “http://localhost/mysql_test.php”. If the “mysql_test.php” page returns something similiar to:

    ID: 1
    Name: John

    after run on broser:

    Fatal error: Call to undefined function mysql_connect() in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\mysql_test.php on line 9

    why given error in line no-9
    plz help

    December 27, 2011 at 11:25 pm
  • gorenje

    Once thing that happened to me was that the file was deleted even though nothing happened to the server. This was an annoyance since it caused nagios to go critical because the pid was missing.

    I assume that /etc/init.d/mysql would have had a fit had I tried to use it (i.e. server running but not pid file for the server).

    Version I was using: Ver 5.1.56 for pc-linux-gnu on x86_64

    January 18, 2012 at 12:54 am
  • Iusico Aliona

    $ which mysqld

    $ /usr/sbin/mysqld –verbose –help | grep -A 1 “Default options”
    Default options are read from the following files in the given order:
    /etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

    June 27, 2016 at 3:11 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.