Percona-server time out on /etc/init.d/mysql start

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

I know this is a bit old, but its 30/4/2015 and we had the same problem with Percona 5.6.

The /etc/init.d/mysql 'start' function has the following (starting ln:112):

#wait 10sec before start checking if pid file created or server is dead
if [ $dead_check_counter -lt 10 ]; then
   dead_check_counter=$(( dead_check_counter + 1 ))
else
  if mysqld_status check_dead warn; then break; fi
fi

Our server normally requires around 40-70 seconds to start (init of an innodb buffer pool of 97GB takes about 35 seconds alone), so naturally 'start' reports it 'dead' since the socket file is not created yet.

I've just increased the "10" value and that worked fine for me.

Cheers