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

Posted on

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
mysql@.service                                    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 :

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

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
Wants=mysql@replica01.service
Wants=mysql@replica02.service

[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

Leave a Reply

Your email address will not be published. Required fields are marked *