Starting MySQL on-demand on Debian using systemd socket activation

Debian now packages MariaDB, so this is, unfortunately not up-to-date anymore. The basic principles should also apply to MariaDB.

So, my system sometimes takes some time to boot. After systemd-analyze critical-path I saw that mainly my local MySQL server is to blame here.

After researching how to use socket activation with MySQL, I only found IP-based socket activation. So I dug a little bit deeper and now would like to present my way to my solution.

Final Solution at the end of the page

I started out by simply trying to adapt the IP-based socket activation to Unix sockets:

# /etc/systemd/system/proxy-to-mysql.socket
[Socket]
ListenSocket=/var/run/mysqld/mysqld.sock

[Install]
WantedBy=sockets.target

# /etc/mysql/mysql.conf.d/mysql.cnf
# [...]
[mysql_safe]
socket   = /var/run/mysqld/mysqld.real.sock

#[...]
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.real.sock
#[...]

# /etc/systemd/system/proxy-to-mysql.service
[Unit]
Requires=mysql.service
Ater=mysql.service

[Service]
ExecStart=/lib/systemd/systemd-socket-proxyd /var/run/mysqld/mysqld.real.sock
PrivateTmp=no
PrivateNetwork=no

Stopping MySQL and verifying the Unit status:

sudo systemctl stop mysql
sudo systemctl start proxy-to-mysql.socket
systemctl status proxy-to-mysql.{socket,service} mysql
# proxy-to-mysql.socket is active
# proxy-to-mysql.service is inactive
# mysql is inactive

But when I now try to connect to MySQL, it simply hangs… looks like the proxied socket does not answer. Checking the runtime directory /var/run/mysqld, there is no mysqld.real.sock!

mysql
# hangs here...
# ^C
ls /var/run/mysql
# mysqld.sock
# (No mysqld.real.sock!)

Looking into the MySQL server error log /var/log/mysql/error.log, there is an “Permission Denied” error while creating the socket. Checking the permissions of /var/run/mysqld, owner of the directory is root:root:

ls -ld /var/run/mysqld
# total 0
# drwxr-xr-x  2 root  root    60 Jan 19 12:41 .
# drwxr-xr-x 43 root  root  1340 Jan 19 12:46 ..
# srw-rw-rw-  1 root  root     0 Jan 19 12:41 mysqld.sock

Seems like the proxy-to-mysql.socket is not correctly configured. After some more reasearch I found SockerUser and SocketGroup and added them with user and group mysql to the Socket, but that only made the socket itself owned by mysql:mysql, not the directory, so that didn’t help.

I then decided to move the mysqld.real.sock to it’s own directory, /var/run/mysqld.real/mysqld.sock:

# /etc/mysql/mysql.conf.d/mysql.cnf
# [...]
[mysql_safe]
socket   = /var/run/mysqld.real/mysqld.sock

#[...]
[mysqld]
pid-file = /var/run/mysqld.real/mysqld.pid
socket   = /var/run/mysqld.real/mysqld.sock
#[...]

# /etc/systemd/system/proxy-to-mysql.service
[Unit]
Requires=mysql.service
Ater=mysql.service

[Service]
ExecStart=/lib/systemd/systemd-socket-proxyd /var/run/mysqld.real/mysqld.sock
PrivateTmp=no
PrivateNetwork=no

Applying changes…

sudo systemctl daemon-reload
sudo systemctl stop mysql proxy-to-mysql.service
sudo systemctl restart proxy-to-mysql.socket

And another try.

mysql
# Still hangs :(

Same error, again “Permission denied”, this time /var/run is the problem, which again is owned by root:root. Looking into the /lib/systemd/system/mysql.service, I see RuntimeDirectory=mysqld. After some more resarch I found out that this tells systemd to create the /var/run/mysqld directory. I now want to change that to RuntimeDirectory=mysqld.real. Directly changing the service file in /lib is not a good idea, with the next mysql package update my changes would be overwritten. Fortunately there is systemctl edit to the rescue, so I use that to overwrite the RuntimeDirectory:

sudo systemctl edit mysql

[Service]
# We need to clear first, otherwise it would extend the variable to a value of
# "mysqld mysqld.real"
RuntimeDirectory=
RuntimeDirectory=mysqld.real

systemctl edit reloads the daemon for us, so we don’t need to do it manually. Another try, this time directly the mysql service.

sudo systemctl start mysql
# Takes some time, finally fails.

Still “Permission denied”.

Some thinking…

AppArmor!

And yes, there is a /etc/apparmor.d/usr.sbin.mysql profile, which only allows mysql to write to /var/run/mysqld! Again the issue with not being able to directly edit the file due to overwrites by updates. Fortunately there is /etc/apparmor.d/local/ for extensions. :)

# /etc/apparmor.d/local/usr.sbin.mysqld
/var/run/mysqld.real/mysqld.pid rw,
/var/run/mysqld.real/mysqld.sock rw,
/run/mysqld.real/mysqld.pid rw,
/run/mysqld.real/mysqld.sock rw,

(/var/run is a symlink to /run).

Another try!

sudo systemctl start mysql

I now realize how long it takes to start the mysql service. Looking into the error log, there is no error, mysqld seems to be running fine. Checking via mysql --socket /var/run/mysqld.real/mysqld.sock verifes: I can connect to mysql through that socket! :)

So I got to htop to kill the systemctl start and see a child process mysqladmin ping. Trying that command myself, it does not work. Some thinking… Ah, yes! there is an After=mysql in the proxy-to-mysqld.service, so the systemd-socket-proxyd won’t start until the mysql.service is running, which means /var/run/mysqld/mysqld.sock is still dead.

But why is this mysqladmin ping running anyway? I remember that there was a process between the mysqladmin ping and the systemctl start mysql, a mysql-systemd-start post. And that one I remember I saw in the mysql.service. Looking into the mysql.service again, there is ExecStartPost=/usr/share/mysql/mysql-systemd-start post. Looking into the file the post case executes mysqladmin ping in a loop, I guess to ensure mysqld is really up.

The Problem here is that by default this uses the /var/run/mysqld/mysqld.sock, which is still dead while the command runs. Adjusting the command directly in the file again is not a good idea, so I copied it to /etc/mysql, added --socket /var/run/mysqld.real/mysqld.sock and changed the ExecPostStart to use that file.

# /etc/mysql/mysql-systemd-start, copied from
# /usr/share/mysql/mysql-systemd-start
# [...]
pinger () {
  server_up=false
  for i in $(seq 1 30); do
    sleep 1
    if mysqladmin -S /run/mysqld.real/mysqld.sock ping >/dev/null 2>&1; then
# [...]

sudo systemctl edit mysql

[Service]
# Clear var so we don't extend it
ExecPostStart=
ExecPostStart=/etc/mysql/mysql-systemd-start post

And another try!

sudo systemctl stop mysql proxy-to-mysql.{socket,service}
sudo systemctl start proxy-to-mysql.socket
sudo systemctl start mysql
# Success, works!
sudo systemctl stop mysql
mysql
# mysql> 
# Yeah! Also works!

Finally!

Now just one problem remains: The mysql.service link in /etc/systemd/system/multi-user.target/mysql.service, which would result in mysql still starting on boot. I can remove this link, but it will come back, as there is WantedBy=multi-user.target in the Install section of the mysql.service. Unfortunately this cannot be overwritten using a systemctl edit mysql override config. One has to copy the full service file to /etc/systemd/system and change the values there. But, okay, I can live with that for the moment. systemd still helps one with that: sudo systemctl edit --full mysql.service. I just removed the [Install] section.

Summary

All the changed and create files are summarized below.

You can find the created files also in this Git repo: https://git.joinout.de/mysql-socket-activation

Proxy Socket and Service

/etc/systemd/system/proxy-to-mysql.socket

[Socket]
ListenSocket=/var/run/mysqld/mysqld.sock

[Install]
WantedBy=sockets.target

/etc/systemd/system/proxy-to-mysql.service

[Unit]
Requires=mysql.service
After=mysql.service

[Service]
ExecStart=/lib/systemd/systemd-socket-proxyd /var/run/mysqld/mysqld.real.sock
PrivateTmp=no
PrivateNetwork=no

MySQL Server configuration

/etc/mysql/mysql.conf.d/mysql.cnf

# [...]
[mysql_safe]
socket   = /var/run/mysqld.real/mysqld.sock

#[...]
[mysqld]
pid-file = /var/run/mysqld.real/mysqld.pid
socket   = /var/run/mysqld.real/mysqld.sock
#[...]

/etc/apparmor.d/local/usr.sbin.mysqld

/var/run/mysqld.real/mysqld.pid rw,
/var/run/mysqld.real/mysqld.sock rw,
/run/mysqld.real/mysqld.pid rw,
/run/mysqld.real/mysqld.sock rw,

MySQL Service adjustments

/etc/mysql/mysql-systemd-start
(Copied from /usr/share/mysql/mysql-systemd-start and added --socket /var/run/mysqld.real/mysqld.sock to the mysqladmin command)

# [...]
pinger () {
  server_up=false
  for i in $(seq 1 30); do
    sleep 1
    if mysqladmin --socket /run/mysqld.real/mysqld.sock ping >/dev/null 2>&1; then
# [...]

/etc/systemd/system/mysql.service.d/override.conf

[Service]
ExecStartPost=
ExecStartPost=/etc/mysql/mysql-systemd-start post
RuntimeDirectory=
RuntimeDirectory=mysqld.real

/etc/systemd/system/mysql.service

sudo systemctl edit --full mysql.service
# remove the [Install] section