Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Every time I start mysql, using /etc/init.d/mysql start or service mysql start, it always times out.

 * Starting MySQL (Percona Server) database server mysqld                   [fail] 

However, I can get into mysql. Just wanted to know if there is a problem with the install because it happens all the time, not a one off error.

mysql-error.log shows:

121214 11:25:56 mysqld_safe Starting mysqld daemon with databases from /data/mysql/
121214 11:25:56 [Note] Plugin 'FEDERATED' is disabled.
121214 11:25:56 InnoDB: The InnoDB memory heap is disabled
121214 11:25:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121214 11:25:56 InnoDB: Compressed tables use zlib 1.2.3
121214 11:25:56 InnoDB: Using Linux native AIO
121214 11:25:56 InnoDB: Initializing buffer pool, size = 14.0G
121214 11:25:58 InnoDB: Completed initialization of buffer pool
121214 11:26:01  InnoDB: Waiting for the background threads to start
121214 11:26:02 Percona XtraDB (http://www.percona.com) 1.1.8-rel29.2 started; log sequence number 9333955393950
121214 11:26:02 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
121214 11:26:02 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
121214 11:26:02 [Note] Server socket created on IP: '0.0.0.0'.
121214 11:26:02 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.005163' at position 624540946, relay log '/data/mysql/mysql-relay-bin.000043' position: 624541092
121214 11:26:02 [Note] Slave I/O thread: connected to master 'repl@10.8.25.111:3306',replication started in log 'mysql-bin.005180' at position 823447620
121214 11:26:02 [Note] Event Scheduler: Loaded 0 events
121214 11:26:02 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.28-29.2-log'  socket: '/data/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release 29.2
share|improve this question
    
What OS are you on? I've seen this on Debian/Ubuntu when the /etc/mysql/debian.cnf file contained the wrong information. Also, on a very large database, the timeout in the init script was too short for InnoDB to initialize. –  gertvdijk Dec 14 '12 at 19:49
    
Its an Ubuntu 12.04 server which is running as a slave, using the my.cnf copied from the master. The database is about 750 gb , not sure if that is very large. –  ks_1010 Dec 14 '12 at 19:57
    
In that case, also copy over the debian.cnf from the master, as I assume you copied over the whole database from the master of some time. That is used to check for a running MySQL/Percona server. See the init file at line 27 and 77. –  gertvdijk Dec 14 '12 at 20:08
    
+1 for bringing out this often-overlooked condition –  RolandoMySQLDBA Dec 14 '12 at 20:31
    
@gertvdijk the master in a Centos server, could that be a problem, the replication is also affected. –  ks_1010 Dec 14 '12 at 20:58
show 7 more comments

3 Answers 3

up vote 2 down vote accepted

The display of the error log looks quite normal

Socket file was created MySQL Replication started just fine /usr/sbin/mysqld: ready for connections. appears

Especailly since you see /usr/sbin/mysqld: ready for connections., you should be able to connect to mysql which you just stated that you can.

Your mysqld process is fine.

The error may be coming from /etc/init.d/mysql but not before mysqld has done everything else it needed to do.

If you look inside /etc/init.d/mysql there are two lines

[root@***** init.d]$ cat mysql | grep -n "&" | grep "pid-file"
313:      $manager --user=$user --pid-file=$pid_file >/dev/null 2>&1 &
327:      $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

If /etc/init.d/mysql timed out, it definitely occurred after these two lines.

Here is the code that checks for the pid file

wait_for_pid () {
  verb="$1"
  manager_pid="$2"  # process ID of the program operating on the pid-file
  i=0
  avoid_race_condition="by checking again"
  while test $i -ne $service_startup_timeout ; do

    case "$verb" in
      'created')
        # wait for a PID-file to pop into existence.
        test -s $pid_file && i='' && break
        ;;
      'removed')
        # wait for this PID-file to disappear
        test ! -s $pid_file && i='' && break
        ;;
      *)
        echo "wait_for_pid () usage: wait_for_pid created|removed manager_pid"
        exit 1
        ;;
    esac

    # if manager isn't running, then pid-file will never be updated
    if test -n "$manager_pid"; then
      if kill -0 "$manager_pid" 2>/dev/null; then
        :  # the manager still runs
      else
        # The manager may have exited between the last pid-file check and now.
        if test -n "$avoid_race_condition"; then
          avoid_race_condition=""
          continue  # Check again.
        fi

        # there's nothing that will affect the file.
        log_failure_msg "Manager of pid-file quit without updating file."
        return 1  # not waiting any more.
      fi
    fi

    echo $echo_n ".$echo_c"
    i=`expr $i + 1`
    sleep 1
  done

  if test -z "$i" ; then
    log_success_msg
    return 0
  else
    log_failure_msg
    return 1
  fi
}

wait_for_pid() is called after the launch of mysqld_safe

  # Give extra arguments to mysqld with the my.cnf file. This script
  # may be overwritten at next upgrade.
  pid_file=$server_pid_file
  $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &
  wait_for_pid created $!; return_value=$?

In the worst case, mysqld is running without a pid file. In light of this, service mysql stop and /etc/init.d/mysql stop may not work correctly as it checks for the pid file to know the process id of mysqld.

Without a pid file, the proper way to shutdown mysqld is

# mysqladmin -uroot -h127.0.0.1 --protocol=tcp -p shutdown

CAVEAT

This is not that local of a phenomenon. I have seen this occur with standard MySQL binaries as well.

share|improve this answer
add comment

It appears that the init script checks for a running MySQL/Percona server on Ubuntu by using mysqladmin --defaults-file=/etc/mysql/debian.cnf ping (see line 27 and 77 of /etc/init.d/mysql of the in this case percona-server-server-5.5 package version 5.5.28-rel29.2-360.precise). This works in a default new installation, but when copying over data files in setting up replication from another machine, the user configured in debian.cnf does not match anymore.

As a result, the mysqladmin command will fail and the init script will report the service failed, yet it just runs fine as you can see in the logs.

One solution is to re-create the MySQL user the init scripts expect. On the master, just add the user like this (seems to have all privileges by default?!):

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES,
CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, INDEX, INSERT,
LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE,
SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE
ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD 
*replacethiswithaknownhash' WITH GRANT OPTION;

and create/modify the /etc/mysql/debian.cnf file:

[client]
host     = localhost
user     = debian-sys-maint
password = yourpass
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = yourpass
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

You may need to wait to have the replication process catched up and then try to restart.

share|improve this answer
    
Yes, mysqladmin unable to connect to just started mysqld is one cause why the script seem to fail. Happened to me during upgrade to Debian Wheezy when mysqladmin was not yet upgraded. –  Marki555 Jan 6 at 18:44
add comment

Had exactly the same problem on debian. The server actually starts but the init script says it fails:

/etc/mysql/debian.cnf has the mysql socket set at /var/run/mysqld/mysqld.sock

whereas the my.cnf has it likely set to

/var/lib/mysql/mysql.sock

Make sure that the socket and pid path in my.cnf are pointing to what's set in

/etc/mysql/debian.cnf

Also make sure to point to mysqld not mysql

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.