Adventures in Linux hosting: Multiple MySQL instances

Reading up on mySQL, I realized that rather than using Docker or VMs, the simplest thing to do is run multiple mySQL instances from the default installation.  Because the new machines are Ubuntu Linux, this can be controlled from systemd, the daemon that controls services.

There is also documentation on mysql.com about using Docker and there are some concerns with containers in general and Docker in particular:

Docker containers are in principle ephemeral, and any data or configuration are expected to be lost if the container is deleted or corrupted (see discussions here). Docker volumes, however, provides a mechanism to persist data created inside a Docker container.

and it’s also not clear to me whether there are issues related to security and mysql root password storage. These are probably features for development environments, but we want a more production-like setup for the public wikis.

So, let’s try the multiple instances via systemd first and see if that works.

mysql service

On ubuntu, it seems that the service is mysql (not mysqld) and systemd controls it via

sudo service mysql stop|start|etc

mySQL configuration

mySQL configuration files are often located in obscure places, and when I was installing from a distribution on Macs, the my.cnf files were sometimes hard to find. We can ask the mysql daemon where it’s looking for configuration files:

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 

Mysql will look for these and the later option values override anything set in the earlier file. Additional options files can be added using include directives:

It is possible to use !include directives in option files to include other option files and !includedir to search specific directories for option files. For example, to include the /home/mydir/myopt.cnf file, use the following directive:

!include /home/mydir/myopt.cnf

To search the /home/mydir directory and read option files found there, use this directive:

!includedir /home/mydir

MySQL makes no guarantee about the order in which option files in the directory will be read.

In the default LAMP installation,

  • there is no /etc/my.cnf.
  • /etc/mysql/my.cnf is a symlink to /etc/alternatives/my.cnf. That file only has a pair of include directives
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

The 2 directories contain 4 .cnf files:

conf.d:
 total 8.0K
 -rw-r--r-- 1 root root 8 Jan 21 2017 mysql.cnf
 -rw-r--r-- 1 root root 55 Jan 21 2017 mysqldump.cnf

mysql.conf.d:
 total 8.0K
 -rw-r--r-- 1 root root 3.0K Feb 3 2017 mysqld.cnf
 -rw-r--r-- 1 root root 21 Feb 3 2017 mysqld_safe_syslog.cnf

The /etc/mysql/conf.d/mysql.cnf file is empty. The /etc/mysql/mysql.conf.d/mysqld.cnf file seems to be the one with the relevant options. Following the documentation, I added stanzas for replica servers on different ports to the latter and restarted the mysql service… and nothing happens, as far as I can tell. systemd does not recognize the replica services at all.

Doing lots of googling, I’m not sure what is missing, but others seem to have the same problem.