MySQL connect to server at ‘localhost’ failed at shutdown

debianMySQL

I am unable to cleanly shut down mysql. After 10 minutes, shutdown times out and kills the process. I believe this started happening after upgrading from 5.5 to 5.6 on debian jessie.

systemctl stop mysql.service

Jan 20 09:59:32 vesuvius mysqld_safe[19606]: #007/usr/bin/mysqladmin: connect to server at 'localhost' failed
Jan 20 09:59:32 vesuvius mysqld_safe[19606]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Jan 20 09:59:32 vesuvius mysqld_safe[19606]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Jan 20 09:59:32 vesuvius mysqld_safe[19606]: 170120 09:59:32 mysqld_safe Number of processes running now: 1
Jan 20 09:59:32 vesuvius mysqld_safe: Number of processes running now: 1
Jan 20 09:59:32 vesuvius mysqld_safe[19606]: 170120 09:59:32 mysqld_safe mysqld process hanging, pid 19996 - killed
Jan 20 09:59:32 vesuvius mysqld_safe: mysqld process hanging, pid 19996 - killed
Jan 20 09:59:32 vesuvius mysqld_safe[19606]: 170120 09:59:32 mysqld_safe mysqld restarted
Jan 20 09:59:32 vesuvius mysqld_safe: mysqld restarted
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 0 [Note] /usr/sbin/mysqld (mysqld 5.6.30-1~bpo8+1) starting as process 20052 ...
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
Jan 20 09:59:32 vesuvius mysqld:
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)
Jan 20 09:59:32 vesuvius mysqld:
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] Plugin 'FEDERATED' is disabled.
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: Using atomics to ref count buffer pool pages
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: The InnoDB memory heap is disabled
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: Memory barrier is not used
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: Compressed tables use zlib 1.2.8
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: Using Linux native AIO
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: Not using CPU crc32 instructions
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: Initializing buffer pool, size = 128.0M
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: Completed initialization of buffer pool
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
Jan 20 09:59:32 vesuvius mysqld: 2017-01-20 09:59:32 20052 [Note] InnoDB: Retrying to lock the first data file
Jan 20 09:59:33 vesuvius mysqld: 2017-01-20 09:59:33 20052 [Note] InnoDB: Highest supported file format is Barracuda.
Jan 20 09:59:33 vesuvius mysqld: 2017-01-20 09:59:33 20052 [Note] InnoDB: The log sequence numbers 14002731 and 14002731 in ibdata files do not match the log sequence number 14002741 in the ib_logfiles!
Jan 20 09:59:33 vesuvius mysqld: 2017-01-20 09:59:33 20052 [Note] InnoDB: Database was not shutdown normally!
Jan 20 09:59:33 vesuvius mysqld: 2017-01-20 09:59:33 20052 [Note] InnoDB: Starting crash recovery.
Jan 20 09:59:33 vesuvius mysqld: 2017-01-20 09:59:33 20052 [Note] InnoDB: Reading tablespace information from the .ibd files...
Jan 20 09:59:34 vesuvius mysqld: 2017-01-20 09:59:34 20052 [Note] InnoDB: Restoring possible half-written data pages
Jan 20 09:59:34 vesuvius mysqld: 2017-01-20 09:59:34 20052 [Note] InnoDB: from the doublewrite buffer...
Jan 20 09:59:34 vesuvius mysqld: InnoDB: Last MySQL binlog file position 0 1302, file name mysql-bin.003235
Jan 20 09:59:34 vesuvius mysqld: 2017-01-20 09:59:34 20052 [Note] InnoDB: 128 rollback segment(s) are active.
Jan 20 09:59:34 vesuvius mysqld: 2017-01-20 09:59:34 20052 [Note] InnoDB: Waiting for purge to start
Jan 20 09:59:34 vesuvius mysqld: 2017-01-20 09:59:34 20052 [Note] InnoDB: 5.6.30 started; log sequence number 14002741
Jan 20 09:59:34 vesuvius mysqld: 2017-01-20 09:59:34 20052 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
Jan 20 09:59:34 vesuvius mysqld: 2017-01-20 09:59:34 20052 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
Jan 20 09:59:34 vesuvius mysqld: 2017-01-20 09:59:34 20052 [Note] Server socket created on IP: '127.0.0.1'.
Jan 20 09:59:34 vesuvius mysqld: 2017-01-20 09:59:34 20052 [Note] Event Scheduler: Loaded 0 events
Jan 20 09:59:34 vesuvius mysqld: 2017-01-20 09:59:34 20052 [Note] /usr/sbin/mysqld: ready for connections.
Jan 20 09:59:34 vesuvius mysqld: Version: '5.6.30-1~bpo8+1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
Jan 20 10:09:32 vesuvius systemd[1]: mysql.service stop-sigterm timed out. Killing.
Jan 20 10:09:32 vesuvius systemd[1]: mysql.service: main process exited, code=killed, status=9/KILL
Jan 20 10:09:32 vesuvius systemd[1]: Unit mysql.service entered failed state.

/var/run/mysqld/mysqld/sock exists and is writeable by the mysql user

Best Answer

Found solution at https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=798080

The issue seems to be that mysqld_safe is not supposed to be used alongside systemd. mysqld_safe keeps mysqld alive, duplicating systemd's work and interfering with its own mechanism to restart the server.

Able to resolve as follows:

> mysqladmin shutdown //shuts down mysql cleanly since systemd isn't working

Edit /lib/systemd/system/mysql.service and set ExecStart

ExecStart=/usr/sbin/mysqld

Then restart systemd and start mysql

> systemctl daemon-reload
> systemctl start mysql
Related Topic