Mysql – what could cause a thesql process to close (clean)

multi-masterMySQLmysql-5.5

We are using mysql master-master (version 5.5.32) for an API manager called Layer7.
The 2 hosts holding the instances are in the same ESX

From time to time we have mysql doing a clean shutdown for now reason

We check the cronjobs and they are none
We checked the logins to the hosts or the instance during this time and there were none

other than manually shutdown, shutdown from a script or app or shutdown due to a kill what could cause clean shutdown in a master-master config

here is the log of the host where the instance stopped

Thank you

Regards

150605 18:59:32 [Note] /usr/sbin/mysqld: Normal shutdown

150605 18:59:32 [Note] Event Scheduler: Purging the queue. 0 events
150605 18:59:32 [Note] Error reading relay log event: slave SQL thread was killed
150605 18:59:32 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
150605 18:59:32 [Note] Slave I/O thread killed while reading event
150605 18:59:32 [Note] Slave I/O thread exiting, read up to log 'ssgbin-log.000351', position 91210654
150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360878  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360872  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360871  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360864  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360860  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360859  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360858  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360857  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360855  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360854  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360853  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360851  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360849  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360848  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360847  user: 'gateway'

150605 18:59:34 [Warning] /usr/sbin/mysqld: Forcing close of thread 21360846  user: 'gateway'

150605 18:59:36  InnoDB: Starting shutdown...
150605 18:59:36  InnoDB: Waiting for 76 pages to be flushed
150605 18:59:37  InnoDB: Shutdown completed; log sequence number 5077358548454
150605 18:59:37 [Note] /usr/sbin/mysqld: Shutdown complete

150605 18:59:37 mysqld_safe mysqld from pid file /var/lib/mysql/mtllnl7.mtl.cbc.ca.pid ended
150605 18:59:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150605 18:59:38 [Note] Plugin 'FEDERATED' is disabled.
150605 18:59:38 [Note] Plugin 'partition' is disabled.
150605 18:59:38 InnoDB: The InnoDB memory heap is disabled
150605 18:59:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150605 18:59:38 InnoDB: Compressed tables use zlib 1.2.3
150605 18:59:38 InnoDB: Using Linux native AIO
150605 18:59:38 InnoDB: Initializing buffer pool, size = 512.0M
150605 18:59:38 InnoDB: Completed initialization of buffer pool
150605 18:59:38 InnoDB: highest supported file format is Barracuda.
150605 18:59:38  InnoDB: Waiting for the background threads to start
150605 18:59:39 InnoDB: 5.5.32 started; log sequence number 5077358548454
150605 18:59:39 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150605 18:59:39 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
150605 18:59:39 [Note] Server socket created on IP: '0.0.0.0'.
150605 18:59:39 [Note] Slave SQL thread initialized, starting replication in log 'ssgbin-log.000351' at position 91210654, relay log '/var/lib/mysql/ssgrelay-bin.000635' position: 854358
150605 18:59:39 [Note] Slave I/O thread: connected to master 'repluser@mtllnl7.mtl.cbc.ca:3307',replication started in log 'ssgbin-log.000351' at position 91210654
150605 18:59:39 [Note] Event Scheduler: Loaded 0 events
150605 18:59:39 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-enterprise-commercial-advanced-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition (Commercial)
150605 19:05:09 [Note] Slave: received end packet from server, apparent master shutdown:
150605 19:05:09 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'ssgbin-log.000351' at position 94453851
150605 19:05:09 [ERROR] Slave I/O: error reconnecting to master 'repluser@mtllnl7.mtl.cbc.ca:3307' - retry-time: 10  retries: 86400, Error_code: 2003
150605 19:05:29 [Note] Slave: connected to master 'repluser@mtllnl7.mtl.cbc.ca:3307',replication resumed in log 'ssgbin-log.000351' at position 94453851

Best Answer

The SHUTDOWN privilege can only be used when running mysqladmin

Apr 22, 2013 : /usr/libexec/mysqld: Normal shutdown, but my team doesn't do that?
Mar 06, 2013 : How to properly kill MySQL?
Feb 28, 2013 : mysql restart won't kill child processes on CentOS

Taking away SHUTDOWN grant will stop users from running mysqladmin shutdown.

Your Actual Problem

The error log you are presenting in the question is a restart of mysqld.

150605 18:59:32 [Note] /usr/sbin/mysqld: Normal shutdown
.
.
.
150605 18:59:36  InnoDB: Starting shutdown...
150605 18:59:36  InnoDB: Waiting for 76 pages to be flushed
150605 18:59:37  InnoDB: Shutdown completed; log sequence number 5077358548454
150605 18:59:37 [Note] /usr/sbin/mysqld: Shutdown complete

150605 18:59:37 mysqld_safe mysqld from pid file /var/lib/mysql/mtllnl7.mtl.cbc.ca.pid ended
150605 18:59:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

mysqladmin cannot start or restart mysqld.

Someone or something is running service mysql restart

First run the history

# history | grep restart

If nothing comes up in conjunction with the restarts you see, you need to locate a cronjob on the DB Server that is performing service mysql restart. In some cases, it might be a cronjob on a remote server that no one remembers.

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

MySQL – How to Fix Replication Event Checksum Verification Failed Error Code 1743

Try resetting the relay logs as follows:

STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000030',master_log_pos=196820914;
START SLAVE;

This will erase all relay logs in the Slave, starting with a fresh one. It should start retrieving binlog events from the Master. If it fails again, then mysql-bin.000030 on the Master may be corrupt. In that event, you would have to set up replication from scratch by doing this:

Stop the DB Application
On the Master, RESET MASTER;
mysqldump the data from the Master
Load the mysqldump on the Slave
On the Slave, run STOP SLAVE;
On the Slave, run CHANGE MASTER TO master_log_file='mysql-bin.000001',master_log_pos=4;
On the Slave, run START SLAVE;

CAVEAT

Make sure the network connection is clear (with no dropped packets) between Master and Slave.