Multiple MySQL Instances Using systemd upgrade-safe/restart-safe?

Question

We run MySQL 5.7.36 under Debian Buster with several instances.

For this we use the feature of Systemd described at https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html to work with argv[0]. ("@" - If the executable path is prefixed with "@", the second specified token will be passed as "argv[0]" to the executed process (instead of the actual filename), followed by the further arguments specified. - See: https://manpages.debian.org/testing/systemd/systemd.service.5.en.html).

/etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld@replica01]
datadir=/var/lib/mysql-replica01
socket=/var/lib/mysql-replica01/mysql.sock
port=3307
log-error=/var/log/mysql/replica01.log

[mysqld@replica02]
datadir=/var/lib/mysql-replica02
socket=/var/lib/mysql-replica02/mysql.sock
port=3308
log-error=/var/log/mysql/replica02.log

Manage the instances:

systemctl start mysql@replica01
systemctl start mysql@replica02

Enable autostart:

systemctl enable mysql@replica01
systemctl enable mysql@replica02

We have deactivated the default autostart of the generic MySQL service:

systemctl disable mysql

However, when upgrading the mysql-server via package management, we have the problem that we always have to stop each instance manually first, as described in the documentation:

On Debian platforms, the packaging scripts for MySQL uninstallation cannot currently handle mysqld@ instances. Before removing or upgrading the package, you must stop any extra instances manually first.

Let us now check the autostart options of the services "systemctl list-unit-files | grep mysql":

mysql.service                                     disabled
[email protected]                                    indirect

If we now perform an upgrade with mysql.service disabled, no attempt is made to stop or restart the service during the package upgrade.

The idea would be to create a wrapper to override the normal mysql.service via "sudo systemctl edit mysql", which triggers the individual instances mysql@replica01 and mysql@replica02 for start, stop, restart and resolves the upgrade problem.

Is this possible and does anyone have an example?

Answer 1

I had the same trouble with MariaDB (MDEV-23321).

Debian Systemd packaging isn't conducive to packaging independent template instance systemd services. But, it did give an ugly workaround in the realm of what you where considering.

I suggest in your systemctl edit mysql.service

[Unit]
PartOf=mysql@replica01
PartOf=mysql@replica01

[Service]
Type=oneshot
ExecStart=
ExecStart=/usr/bin/systemctl disable mysql.service

ExecStart= (blank) is needed tor reset the list.

This should be just enough to trigger the restart and keep the mysql.service from starting a new instance, no fail, and not look misleading in the list-instances.

ref: dh_installsystemd code

Answer 2

Thanks to danblack's answer for putting me on the right track. Based on the documentation and code, I have now built the following design, which seems to work:

systemctl edit mysql

[Unit]
Description=MySQL Wrapper
[email protected]
[email protected]

[Service]
Restart=no
Type=oneshot
ExecStartPre=
RemainAfterExit=true
ExecStart=
ExecStart=/bin/true

For each instance: systemctl edit mysql@replica01 && systemctl edit mysql@replica02

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