What is the 'Debian way' of installing multiple MySQL instances on a single server?

Do you know any 'Debian way' of setting up multiple MySQL instances on a single server? The server would receive data replicated from multiple remote databases.

I could use scripts like mysqlsandbox but I prefer to stick to Debian packages and would like to be able to upgrade the setup without much complications in the future. Another solution is mysqlmanager - it works with MySQL 5.1 but it's deprecated and not released anymore with 5.5.

So what's the 'best practice' of running multiple MySQL instances on a single Debian server?

I believe it's as simple as running mysqld_multi and setting up your my.cnf correctly. mysqld_multi is a part of the mysql distribution - not a packaged frill.

There is a patch to make /usr/share/mysql/mysqld_multi.server appropriate for use in /etc/init.d and another patch to make mysqld_multi use files in /etc/mysql/conf.d.

You can initialize MySQL data directories for new instances with the mysql_install_db command like this:

mysql_install_db --datadir=/var/lib/mysql2

Don't forget to change the root password of the newly created instance:

mysqladmin --port 3307 --user=root password 'new-password'

On Debian 8, you can use Systemd mechanism: no more need of mysqld_multi.

NOTE: I use MariaDB version! Not sure if it works with 'classic' MySQL package.

From /lib/systemd/system/[email protected]:

Multi instance version of mariadb. For if you run mutiple verions at once. Also used for mariadb@bootstrap to bootstrap Galera.

create config file /etc/mysql/conf.d/my{instancename}.cnf

start as systemctl start mariadb@{instancename}.server

So, create a file /etc/mysql/conf.d/myserver2.cnf, and specify in it new pid/socket/datadir files and network port:

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld-server2.pid
socket      = /var/run/mysqld/mysqld-server2.sock
port        = 3307
basedir     = /usr
datadir     = /var/lib/mysql-server2
tmpdir      = /tmp

EDIT: be carefull that first MySQL instance do not read this config file, with !includedir /etc/mysql/conf.d/* at the bottom of /etc/mysql/my.cnf. If it is the case, replace the !includedir with a !include of each config file OTHER THAN myserver2.cnf:

#!includedir /etc/mysql/conf.d/*
!include /etc/mysql/conf.d/conf1.cnf
!include /etc/mysql/conf.d/confX.cnf

The MySQL official doc indicate you have to name [mysqld] as [mysqld@server2] (https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html#systemd-multiple-mysql-instances) but this does not work with MariaDB. So just let [mysqld].

Before starting the new daemon, don't forget to create the datadir and needed files :

mkdir /var/lib/mysql-server2
chown mysql:mysql /var/lib/mysql-server2
mysql_install_db --datadir=/var/lib/mysql-server2

Also reload systemd daemon config:

systemctl daemon-reload

And if you want to start this daemon at boot:

systemctl enable mariadb@server2

To start it:

service mariadb@server2 start