MYSQL on Ubuntu 15.10

I haven’t researched whether this has changed, in 15.10, or whether it has been this way since ubuntu switched to systemd, which is probably the case.

Under systemd, ubuntu no longer uses the /etc/init.d/mysql script, but instead uses a systemd unit in /lib/systemd/system/mysql.service which invokes /usr/bin/mysqld_safe to start and stop mysqld.

I have had a lot of trouble with this, and had to do a lot of debugging to figure out what is going on. Probably I would not have had trouble if I were not trying to port over a running mysql installation manually, i.e. if I just installed mysql-server and proceeded to create new databases, new entries in mysql etc.

One issue is that a mysql install creates a file in /etc/mysql called debian.cnf which contains a user/password for user debian-sys-maint with a generated password, and this is put into the mysql users table, to enable various operations to be performed by mysqladmin using these credentials.

The first problem was that when I copied over the mysql table from the previous installation, I was copying in the old password for debian-sys-main, which didn’t match the debian.cnf file which was installed when I did the apt-get install mysql-server. So I had to read the debian.cnf file, extract the password and change the password in the mysql table.

A second problem was that the debian.cnf file was owned by root with 0600 perms, but that seems to me to create a problem (at least for me), because mysqld_safe is running under user mysql. Maybe if I had installed everything as some genius wanted me to all the /etc/mysql stuff would have been owned by mysql, but in my case it was all owned by root (and I think that I captured the virgin /etc/mysql directory before I did anything and I swear it is all owned by root too). I don’t know how they expect this to work. Anyway, mysqld_safe running under user mysql launches the mysqld daemon and monitors it. When systemctl stop mysql tries to stop mysqld it traps, and stops mysqld daemon with mysqladmin shutdown, using the debian.cnf credentials. If mysqladmin can’t read the debian.cnf credentials, then it can’t communicate with the server.

Another issue that I never figured out involves the logging, and shutting down mysql with systemctl.

When systemd tries to shut down mysql, it (presumably) just issues a kill which is trapped by mysqld_safe. Mysqld_safe traps the kill signal, and uses mysqladmin to shut down the daemon. If it works right, the daemon will remove the pid file and quit. Mysqld safe will detect the pid file is gone, and then it will exit itself. If the pid file is not gone, mysqld_safe things the daemon has crashed, and restarts it.

It isn’t working for me. The daemon seems to return something that confuses mysqld_safe. I don’t know what happens exactly, but mysqld_safe fails to stop the daemon (or at least the pid file never gets removed). It logs “[61B blob data]” followed by an error “lost connection to MySQL server at ‘reading initial communication packet’, system error: 104’” and proceeds as if the daemon has crashed, which is to say it kills the mysqld daemon and starts a new one. Meanwhile systemd is sitting there waiting for mysqld_safe to stop, which it never does.

There is a file in /etc/mysql/mysqld.conf.d/mysqld_safe_syslog.cnf which tells mysqld_safe where to log – and tells it to log to syslog, i.e. to the journal. Which is fine. So it launches the mysqld daemon as a pipe into logger. If I change this to not log to the journal, by setting it to skip-syslog instead of syslog, then it doesn’t launch the logger process, and the daemon is launched sending its output to /var/log/mysql/hostname like it always used to. When I do this, the daemon seems to stop ok and I don’t get the error above.

I don’t know why. I don’t much care on Cinnamon. I just want the damn thing to stop when I tell it to. So if I have to log to the old log location instead of the journal, I can live with that.