Home / server / Questions / 368204
Accepted
pQd
Asked: 2012-03-10 15:29:17 +0800 CST

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

  • 772

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?

mysql debian

2 Answers

  1. Best Answer

    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'
    • 12

  2. 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
    • 3