Raivo Laanemets. Software consultant.

MySQL 5.5 in Debian Jessie does not stop


Recently I set up a fresh machine using Debian Jessie (8.0). The installation included a MySQL 5.5 server package. At some point I had to edit the MySQL configuration and restart the server daemon to reload the new configuration. The configuration change enabled listening on a TCP port but all attempts to connect the port failed while testing it.

I have had issues with init scripts before and inability to correctly restart a process is a pretty common failure. This was the first thing I decided to look at. My gut feeling was correct: checking out MySQL pids before and after service mysql restart showed that processes were not restarted at all. MySQL pids can be checked using the command:

ps -Af | grep mysql

Attempt to stop MySQL with command service mysql stop gave same result: processes just kept running. The command itself output no errors or warnings. Googling for the issue revealed very little: there is only one reported issue that more-less matches the similar environment. Although it is for Ubuntu 15.04 and MySQL 5.6.

There are lots of other issues relating to MySQL not starting. Many of them are related to 3rd party tools and file permission errors. I tried to "fix" some of the file permissions but it did not help. Further debugging through systemd showed very little information:

# journalctl -u mysql.service

Stopping LSB: Start and stop the mysql database server daemon...
Oct 01 21:27:16 localhost mysql[3643]: Stopping MySQL database server: mysqld failed!
Oct 01 21:27:16 localhost systemd[1]: mysql.service: control process exited, code=exited status=1
Oct 01 21:27:16 localhost systemd[1]: Stopped LSB: Start and stop the mysql database server daemon.
Oct 01 21:27:16 localhost systemd[1]: Unit mysql.service entered failed state.

My concern from this log was line mysql.service: control process exited, code=exited status=1. It should have more output, shouldn't it? Digging through the systemd configuration and unit files revealed horror: MySQL in Debian has no proper unit file. It's still using a good old init script, combining relative undebuggability of systemd with unreliability of init scripts. This is also confirmed by this bug report.

Part of the issue is also on MySQL daemon that does not block before it can actually accept client connections. This means that a simple service unit file does not work as dependant services would start before MySQL is ready. This has been explained in this article and fixed in MySQL 5.7. Installing MySQL 5.7 requires custom repos.

Solutions

There are 3 solutions:

  • Debian's MariaDB is also missing the unit file. Installing the package might help or not.
  • Installing MySQL 5.7 from custom repositories.
  • Fixing the stop issue with 5.5.

I got the last solution working by forcefully stopping mysql processes (mysqld_safe and mysqld), purging the installation, reinstalling and reconfiguring it. This can be done with these commands (it leaves data intact):

Stopping processes (repeat untill dead):

killall -9 mysqld_safe
killall -9 mysqld

Reinstalling:

apt-get --purge remove mysql-server-5.5
apt-get --reinstall install mysql-server-5.5
dpkg-reconfigure mysql-server-5.5

Somehow it started to correctly respond to stop/restart requests after this.

Update 2016-05-03

@jouir has pointed out possible issues with the Debian MySQL start scripts.

I have also stumbled on another machine with these issues. This time the behaviour was clearly caused by the bad debian-sys-maint user password. Maintenance users and password hashes are in the file /etc/mysql/debian.cnf in the following format:

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = <censored>
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = <censored>
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

The maintenance user's password hash can be queried in MySQL with a query:

SELECT password FROM mysql.user WHERE user = 'debian-sys-maint';

And can be updated using the query:

SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('<censored>');

The password must match the one in the /etc/mysql/debian.cnf file. Unmatched passwords are usually a result of a MySQL restore that overwrites the database contents, including user tables, but does not update the configuration file.

Security issue

The Debian-specific configuration file is also related to at least one security vulnerability which might indicate that this setup is not very good. I understand that Debian needs it to automatically install certain packages that modify the database contents. However, if this is not needed, it might be the best idea to use some custom solution to start/stop database, either by modifying the init script or by using an external process manager.


Comments

Me at 2016-02-17
Don't know if it's the same problem, but in my case also service start, stop, restart didn't work. The problem was, that I removed original /var/lib/mysql directory and I copied my old database directory from another server. Doing this, i removed the "debian-sys-maint" used, which is needed for "service stop" etc. So you just have to add this user to mysql, set a password, and put this password into /etc/mysql/debian.cnf All is working fine then.

Raivo at 2016-03-18
@Me, the problem might actually been from wrong maintenance user password. The database as a whole was copied from backup but not any configuration files.

jouir at 2016-04-30
I had the same problem for a few days now on the same environment (MySQL 5.5, Debian 8.4, restored datadir from backups) and I found something weird. We don't like to have a second root user on our databases, even if it's only accessible from localhost so we remove the 'debian-sys-maint'@'localhost' user and create one with less privileges (SELECT, RELOAD, SHUTDOWN). This user is able to ping and shutdown from mysqladmin. We override the debian credentials with ours and it used to work on squeeze and wheezy. We also change the /etc/mysql/debian-start script to skip some checks at start and only exit 0. We want our service to start. We don't want Debian to CHECK our tables at start, try to upgrade, etc and say the service is not started but mysqld says it's started. I discovered that the debian-start script must echo something no matter what otherwise the init script exit 1, even if the debian-start exit 0. So I added a "echo OK" in this script and it's now fixed. I'm now able to stop and start on jessie.

Raivo at 2016-05-03
@jouir, I had another system with a similar issue. Debugged and documented the debian-sys-maint password mismatch and correction in the article update section. Also suggested removal of the user and running MySQL by other means.

Email is not displayed anywhere.
URLs (max 3) starting with http:// or https:// can be used. Use @Name to mention someone.