29 Apr, 2018

Run multiple MySQL/MariaDB instances on Ubuntu server

We are using MariaDB on an Ubuntu server powered by Webmin and Virtualmin.

The MariaDB server has been installed after removing the default MySQL installation that comes with Virtualmin.

Configuration

Please note that your other instances will always be identified by positive integers (e.g. 1, 2, 3, 4, 5 etc).

  • mysql -uroot -ppassword
    Replace password with your password
  • GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'password';
    Replace password with the password you want to use for multi
  • FLUSH PRIVILEGES;
  • exit;
  • service mysql stop
  • nano /etc/mysql/my.cnf
    This could be an actual file or as it is in my case, a symlink to /etc/mysql/mariadb.cnf. If the [mysqld] group is not in this file, you are supposed to go though each of the included files until you find it.
    Hint: it might be in /etc/mysql/mariadb.conf.d/50-server.cnf
  • Replace [mysqld]  with:

    The configuration file contains more information about each of these fields.
  • Ctrl+X and Y

The folders

  • sudo su
    Switch to the root user, for running the commands with ease – this is not recommended on production servers.
  • mkdir /var/lib/mysqld-instances
  • mkdir -p /var/lib/mysqld-instances/1/mysql
  • mkdir /var/log/mysqld-instances
  • mkdir /var/log/mysqld-instances/1/
  • cp -r /var/lib/mysql/mysql/ /var/lib/mysqld-instances/1/mysql
  • chown -R mysql.mysql /var/lib/mysqld-instances
  • chown -R mysql.mysql /var/log/mysqld-instances

Initiate and test

  • mysqld_multi start
  • mysqld_multi report
  • Test:
    mysql -h localhost -P 3307

Autostart

  • cd /etc/init.d/
  • nano mysql_multi
  • chmod 755 mysqld_multi
  • chown root:root mysqld_multi
  • update-rc.d mysqld_multi defaults
  • update-rc.d mysqld_multi enable
  • reboot
    To see if the command is executed properly.

More info

Configuration examples

mysqld_multi --example will return:

Grant command by connecting to the sock file

Sources