Results 1 to 6 of 6
  1. #1

    mySQL -- Is there a way to verify if /etc/my.cnf values are being used?

    Greetings:

    Is there a way to verify if /etc/my.cnf values are being used by mySQL?

    Is there a way to double check if the values in /etc/my.cnf for max connections and the like are being read "as is" from /etc/my.cnf?

    Meaning, if you set max connections to 500, without creating 500 connections can you check if mySQL believes it can go up to 500 vs. 100?

    Thank you.
    ---
    Peter M. Abraham
    LinkedIn Profile

  2. #2
    Join Date
    Apr 2004
    Location
    SF Bay Area
    Posts
    877

    Re: mySQL -- Is there a way to verify if /etc/my.cnf values are being used?

    Originally posted by dynamicnet
    Is there a way to double check if the values in /etc/my.cnf for max connections and the like are being read "as is" from /etc/my.cnf?
    ./mysqld --help
    or
    ./mysqld --verbose --help

    ...will show you what your current startup params are.

    Hopefully they'll have a V$PARAMETER view like Oracle one of these days. 5.x?
    Last edited by fastdeploy; 07-11-2005 at 03:34 PM.

  3. #3
    Join Date
    Apr 2005
    Location
    Sweden
    Posts
    241
    Or, using the "mysql" text client, the command "show variables" will give you a complete list.

  4. #4
    Greetings:

    The "mysqld --help" did the trick.

    The sad part is that it appears that /etc/my.cnf (or if I put them in /var/lib/mysql/my.cnf) the configuration file is not being read; and the system is using the defaults.

    Here is /etc/my.cnf

    [myqld]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    skip-locking
    set-variable = key_buffer = 128M
    set-variable = join_buffer = 1M
    set-variable = record_buffer = 1M
    set-variable = sort_buffer = 2M
    set-variable = table_cache = 1500
    set-variable = max_connections = 500
    set-variable = max_connect_errors = 10
    set-variable = thread_cache_size = 128
    set-variable = max_allowed_packet = 5M
    set-variable = connect_timeout = 15
    set-variable = query_cache_limit = 1M
    set-variable = query_cache_size = 32M
    set-variable = query_cache_type = 1
    set-variable = thread_concurrency = 2
    log-slow-queries = /var/log/mysql_slow_query_log
    set-variable = long_query_time = 5

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates

    [isamchk]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M

    [myisamchk]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout


    Here is the start up script for mysql:

    #!/bin/sh
    # Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB
    # This file is public domain and comes with NO WARRANTY of any kind

    # MySQL daemon start/stop script.

    # Usually this is put in /etc/init.d (at least on machines SYSV R4 based
    # systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
    # When this is done the mysql server will be started when the machine is
    # started and shut down when the systems goes down.

    # Comments to support chkconfig on RedHat Linux
    # chkconfig: 2345 90 20
    # description: A very fast and reliable SQL database engine.

    # Comments to support LSB init script conventions
    ### BEGIN INIT INFO
    # Provides: mysql
    # Required-Start: $local_fs $network $remote_fs
    # Required-Stop: $local_fs $network $remote_fs
    # Default-Start: 2 3 4 5
    # Default-Stop: 0 1 6
    # Short-Description: start and stop MySQL
    # Description: MySQL is a very fast and reliable SQL database engine.
    ### END INIT INFO

    # If you install MySQL on some other places than /, then you
    # have to do one of the following things for this script to work:
    #
    # - Run this script from within the MySQL installation directory
    # - Create a /etc/my.cnf file with the following information:
    # [mysqld]
    # basedir=<path-to-mysql-installation-directory>
    # - Add the above to any other configuration file (for example ~/.my.ini)
    # and copy my_print_defaults to /usr/bin
    # - Add the path to the mysql-installation-directory to the basedir variable
    # below.
    #
    # If you want to affect other MySQL variables, you should make your changes
    # in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.

    basedir=

    # The following variables are only set for letting mysql.server find things.

    # Set some defaults
    datadir=/var/lib/mysql
    pid_file=
    if test -z "$basedir"
    then
    basedir=/
    bindir=/usr/bin
    else
    bindir="$basedir/bin"
    fi

    PATH=/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin
    export PATH

    mode=$1 # start or stop

    case `echo "testing\c"`,`echo -n testing` in
    *c*,-n*) echo_n= echo_c= ;;
    *c*,*) echo_n=-n echo_c= ;;
    *) echo_n= echo_c='\c' ;;
    esac

    parse_arguments() {
    for arg do
    case "$arg" in
    --basedir=*) basedir=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
    --datadir=*) datadir=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
    --pid-file=*) pid_file=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
    esac
    done
    }

    # Get arguments from the my.cnf file,
    # groups [mysqld] [mysql_server] and [mysql.server]
    if test -x ./bin/my_print_defaults
    then
    print_defaults="./bin/my_print_defaults"
    elif test -x $bindir/my_print_defaults
    then
    print_defaults="$bindir/my_print_defaults"
    elif test -x $bindir/mysql_print_defaults
    then
    print_defaults="$bindir/mysql_print_defaults"
    else
    # Try to find basedir in /etc/my.cnf
    conf=/etc/my.cnf
    print_defaults=
    if test -r $conf
    then
    subpat='^[^=]*basedir[^=]*=\(.*\)$'
    dirs=`sed -e "/$subpat/!d" -e 's//\1/' $conf`
    for d in $dirs
    do
    d=`echo $d | sed -e 's/[ ]//g'`
    if test -x "$d/bin/my_print_defaults"
    then
    print_defaults="$d/bin/my_print_defaults"
    break
    fi
    if test -x "$d/bin/mysql_print_defaults"
    then
    print_defaults="$d/bin/mysql_print_defaults"
    break
    fi
    done
    fi

    # Hope it's in the PATH ... but I doubt it
    test -z "$print_defaults" && print_defaults="my_print_defaults"
    fi

    #
    # Test if someone changed datadir; In this case we should also read the
    # default arguments from this directory
    #

    extra_args=""
    if test "$datadir" != "/var/lib/mysql"
    then
    extra_args="-e $datadir/my.cnf"
    fi

    parse_arguments `$print_defaults $extra_args mysqld mysql_server mysql.server`

    #
    # Set pid file if not given
    #
    if test -z "$pid_file"
    then
    pid_file=$datadir/`/bin/hostname`.pid
    else
    case "$pid_file" in
    /* ) ;;
    * ) pid_file="$datadir/$pid_file" ;;
    esac
    fi

    # Safeguard (relative paths, core dumps..)
    cd $basedir

    case "$mode" in
    'start')
    # Start daemon

    if test -x $bindir/mysqld_safe
    then
    # Give extra arguments to mysqld with the my.cnf file. This script may
    # be overwritten at next upgrade.
    $bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file >/dev/null 2>&1 &
    # Make lock for RedHat / SuSE
    if test -w /var/lock/subsys
    then
    touch /var/lock/subsys/mysql
    fi
    else
    echo "Can't execute $bindir/mysqld_safe from dir $basedir"
    fi
    ;;

    'stop')
    # Stop daemon. We use a signal here to avoid having to know the
    # root password.
    if test -s "$pid_file"
    then
    mysqld_pid=`cat $pid_file`
    echo "Killing mysqld with pid $mysqld_pid"
    kill $mysqld_pid
    # mysqld should remove the pid_file when it exits, so wait for it.

    sleep 1
    while [ -s $pid_file -a "$flags" != aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ]
    do
    [ -z "$flags" ] && echo $echo_n "Wait for mysqld to exit$echo_c" || echo $echo_n ".$echo_c"
    flags=a$flags
    sleep 1
    done
    if [ -s $pid_file ]
    then echo " gave up waiting!"
    elif [ -n "$flags" ]
    then echo " done"
    fi
    # delete lock for RedHat / SuSE
    if test -f /var/lock/subsys/mysql
    then
    rm -f /var/lock/subsys/mysql
    fi
    else
    echo "No mysqld pid file found. Looked for $pid_file."
    fi
    ;;

    'restart')
    # Stop the service and regardless of whether it was
    # running or not, start it again.
    $0 stop
    $0 start
    ;;

    *)
    # usage
    echo "Usage: $0 start|stop|restart"
    exit 1
    ;;
    esac


    Thoughts on why /etc/my.cnf or /var/lib/mysql/my.cnf (same file for each) are being ignored?

    Thank you.

  5. #5
    Join Date
    Dec 2002
    Location
    Egypt
    Posts
    151
    Can you try editing your /etc/my.cnf and delete all instances of "set-variable ="

    Don't forget to backup it first and restart your mysql server.
    knowledge is Power , Spread it.
    www.e-tutankhamun.com
    [email protected]
    AIM:AhmedFouad0 , yahooID:xor2004

  6. #6
    Greetings:

    Done on both /etc/my.cnf and /var/lib/mysql/my.cnf; restarted mysql and I still get the system defaults.

    Thoughts?

    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •