TimeOut error while starting mysqld deamon

vinaytp · 08-25-2010, 12:59 AM

Dear All,

I am facing problem while starting mysqld deamon on RHEL 5.4

Code:

[zabbix@CDCTGIMCLSB ~]$ sudo /sbin/service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]

If i try to start through mysqld_safe

Code:

[root@CDCTGIMCLSB ~]# /usr/bin/mysqld_safe
Starting mysqld daemon with databases from /data/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
100825 11:26:35  mysqld ended

I tried out stack Trace procedure hereto debug, but no luck so far.

Thanks in advance..

quanta · 08-25-2010, 01:38 AM

- ls -l /data/mysql?
- Post up your config file (/etc/my.cnf) and init script (/etc/init.d/mysqld)

vinaytp · 08-25-2010, 01:54 AM

Quote:

Originally Posted by quanta

- ls -l /data/mysql?
- Post up your config file (/etc/my.cnf) and init script (/etc/init.d/mysqld)

Many Thanks for your reply..

Here is the output of ls -l /data/mysql

Code:

[root@CDCTGIMCLSB ~]# ls -l /data/mysql
total 135356
-rw-rw---- 1 mysql mysql 127926272 Aug 24 11:14 ibdata1
-rw-rw---- 1 mysql mysql   5242880 Aug 25 11:26 ib_logfile0
-rw-rw---- 1 mysql mysql   5242880 Aug 24 11:31 ib_logfile1
drwx------ 2 mysql mysql      4096 Aug 11 12:46 mysql
drwx------ 2 mysql mysql      4096 Aug 11 13:19 test
drwx------ 2 mysql mysql      4096 Aug 11 18:22 zabbix

Here is my /etc/my.cnf configuration file. I have not made any changes in this file

Code:

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Here is my /etc/init.d/mysqld file

Code:

#!/bin/bash
#
# mysqld        This shell script takes care of starting and stopping
#               the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description:  MySQL database server.
# processname: mysqld
# config: /etc/my.cnf
# pidfile: /var/run/mysqld/mysqld.pid

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network


prog="MySQL"

# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
        result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1`
        if [ -z "$result" ]; then
            # not found, use default
            result="$3"
        fi
}

get_mysql_option mysqld datadir "/var/lib/mysql"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid"
mypidfile="$result"

start(){
        touch "$errlogfile"
        chown mysql:mysql "$errlogfile"
        chmod 0640 "$errlogfile"
        [ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
        if [ ! -d "$datadir/mysql" ] ; then
            action $"Initializing MySQL database: " /usr/bin/mysql_install_db --datadir="$datadir" --user=mysql
            ret=$?
            chown -R mysql:mysql "$datadir"
            if [ $ret -ne 0 ] ; then
                return $ret
            fi
        fi
        chown mysql:mysql "$datadir"
        chmod 0755 "$datadir"
        # Pass all the options determined above, to ensure consistent behavior.
        # In many cases mysqld_safe would arrive at the same conclusions anyway
        # but we need to be sure.
        /usr/bin/mysqld_safe   --datadir="$datadir" --socket="$socketfile" \
                --log-error="$errlogfile" --pid-file="$mypidfile" \
                --user=mysql >/dev/null 2>&1 &
        ret=$?
        # Spin for a maximum of N seconds waiting for the server to come up.
        # Rather than assuming we know a valid username, accept an "access
        # denied" response as meaning the server is functioning.
        if [ $ret -eq 0 ]; then
            STARTTIMEOUT=30
            while [ $STARTTIMEOUT -gt 0 ]; do
                RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1` && break
                echo "$RESPONSE" | grep -q "Access denied for user" && break
                sleep 1
                let STARTTIMEOUT=${STARTTIMEOUT}-1
            done
            if [ $STARTTIMEOUT -eq 0 ]; then
                    echo "Timeout error occurred trying to start MySQL Daemon."
                    action $"Starting $prog: " /bin/false
                    ret=1
            else
                    action $"Starting $prog: " /bin/true
            fi
        else
            action $"Starting $prog: " /bin/false
        fi
        [ $ret -eq 0 ] && touch /var/lock/subsys/mysqld
        return $ret
}

stop(){
        MYSQLPID=`cat "$mypidfile"  2>/dev/null `
        if [ -n "$MYSQLPID" ]; then
            /bin/kill "$MYSQLPID" >/dev/null 2>&1
            ret=$?
            if [ $ret -eq 0 ]; then
                STOPTIMEOUT=60
                while [ $STOPTIMEOUT -gt 0 ]; do
                    /bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
                    sleep 1
                    let STOPTIMEOUT=${STOPTIMEOUT}-1
                done
                if [ $STOPTIMEOUT -eq 0 ]; then
                    echo "Timeout error occurred trying to stop MySQL Daemon."
                    ret=1
                    action $"Stopping $prog: " /bin/false
                else
                    rm -f /var/lock/subsys/mysqld
                    rm -f "$socketfile"
                    action $"Stopping $prog: " /bin/true
                fi
            else
                action $"Stopping $prog: " /bin/false
            fi
        else
            ret=1
            action $"Stopping $prog: " /bin/false
        fi
        return $ret
}

restart(){
    stop
    start
}

condrestart(){
    [ -e /var/lock/subsys/mysqld ] && restart || :
}

# See how we were called.
case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  status)
    status mysqld
    ;;
  restart)
    restart
    ;;
  condrestart)
    condrestart
    ;;
  *)
    echo $"Usage: $0 {start|stop|status|condrestart|restart}"
    exit 1
esac

exit $?

Awaiting for your response..

quanta · 08-25-2010, 02:19 AM

Change the datadir in init script correlative with which you set in the config file:

Code:

get_mysql_option mysqld datadir "/var/lib/mysql"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql.sock"
socketfile="$result"

or make a symlink:

Code:

ln -s /data/mysql /var/lib/mysql

if no datadir of another MySQL instance set to /var/lib/mysql.

vinaytp · 08-25-2010, 02:34 AM

Thanks again for your response quanta..

I changed the path to datadir to /data/mysql as you suggested.

Code:

get_mysql_option mysqld datadir "/data/mysql"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql.sock"
socketfile="$result"

But still I am getting Time Out Error..

Hope that should not be the problem, because I was able to start the deamon before with same configuration. Suddenly when I tried to restart the deamon I am experiencing this.

quanta · 08-25-2010, 02:50 AM

Check the socketfile.

vinaytp · 08-25-2010, 03:17 AM

Quote:

Originally Posted by quanta

Check the socketfile.

Actually this mysql is in OS level cluster. Two node high available cluster, Now I shifted cluster to other node.

Now my data directory got disappeared in node A and appearing in node B.

So mysql service is getting started at node A which was not starting before and a socket file got created under /var/lib/mysql/mysql.sock.

Hope there should be some problem in mysql cofiguration. How mysql deamon can start without the presence of /data/mysql directory ?

quanta · 08-25-2010, 05:36 AM

Change:

Code:

get_mysql_option mysqld socket "$datadir/mysql.sock"
socketfile="$result"

to

Code:

socketfile="/var/lib/mysql/mysql.sock"

and try again.

vinaytp · 08-25-2010, 05:54 AM

Quote:

Code:

socketfile="/var/lib/mysql/mysql.sock"

and try again.

Thanks for your efforts quanta,

I tested this as well, but no luck so far.

By the way, we do not have to change this because this init script will take the datadir, socketfile parameters from /etc/my.cnf file.

If you can observe the init script, there is a function called get_mysql_option(), under whihch my_print_defaults command collects the parameters defined in /etc/my.cnf file and that will be considered. If parameters are not defined in /etc/my.cnf file, then as you have suggested, default parameteres will be considered.

Here is the output of /usr/bin/my_print_defaults mysqld command with is under get_mysql_option() function.

Code:

[root@CDCTGIMCLSA ~]# sudo /usr/bin/my_print_defaults mysqld
--datadir=/data/mysql
--socket=/var/lib/mysql/mysql.sock
--user=mysql
--old_passwords=1

Also I have tested the mysqld_safe command which is being executed in init script manually which starts and ends up ubruptly wihtout any usefull information in logs. Here is the sample result of mysqld_safe command

Code:

[root@CDCTGIMCLSA ~]# sudo /usr/bin/mysqld_safe --datadir=/data/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql
Starting mysqld daemon with databases from /data/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
100825 16:12:45  mysqld ended

Could you please advice me, What might be the problem. Also /data/mysql directory is owned by mysql user and group.

Awaiting your response.

quanta · 08-25-2010, 10:01 AM

Sorry for my wrongs. I suggest you open 2 consoles, one for starting mysqld_safe while monitoring the log file (/var/log/mysqld.log) on the other.