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: Stopping MySQL database server: mysqld failed! Oct 01 21:27:16 localhost systemd: mysql.service: control process exited, code=exited status=1 Oct 01 21:27:16 localhost systemd: Stopped LSB: Start and stop the mysql database server daemon. Oct 01 21:27:16 localhost systemd: 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.
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
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.
@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.
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.