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:

[mysqld1]

user = mysql pid-file = /var/run/mysqld/mysqld1.pid socket = /var/run/mysqld/mysqld1.sock port = 3307 basedir = /usr datadir = /var/lib/mysqld-instances/1 tmpdir = /tmp lc-messages-dir = /usr/share/mysql log-error = /var/log/mysqld-instances/1/error.log skip-external-locking character-set-server = utf8mb4 collation-server = utf8mb4_general_ci bind-address = 127.0.0.1 key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size = 16M expire_logs_days = 10 max_binlog_size = 100M # this is only for the mysqld standalone daemon

[mysqld0]

# # * Basic Settings #

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:

    [mysqld_multi]

    mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = multi_admin password = my_password

    [mysqld2]

    socket = /tmp/mysql.sock2 port = 3307 pid-file = /var/lib/mysql2/hostname.pid2 datadir = /var/lib/mysql2 language = /usr/share/mysql/mysql/english user = unix_user1

    [mysqld3]

    mysqld = /path/to/mysqld_safe ledir = /path/to/mysqld-binary/ mysqladmin = /path/to/mysqladmin socket = /tmp/mysql.sock3 port = 3308 pid-file = /var/lib/mysql3/hostname.pid3 datadir = /var/lib/mysql3 language = /usr/share/mysql/mysql/swedish user = unix_user2

    [mysqld4]

    socket = /tmp/mysql.sock4 port = 3309 pid-file = /var/lib/mysql4/hostname.pid4 datadir = /var/lib/mysql4 language = /usr/share/mysql/mysql/estonia user = unix_user3

    [mysqld6]

    socket = /tmp/mysql.sock6 port = 3311 pid-file = /var/lib/mysql6/hostname.pid6 datadir = /var/lib/mysql6 language = /usr/share/mysql/mysql/japanese user = unix_user4

    Grant command by connecting to the sock file

    Sources

    • https://www.percona.com/blog/2014/08/26/mysqld_multi-how-to-run-multiple-instances-of-mysql/
    • http://www.ducea.com/2009/01/19/running-multiple-instances-of-mysql-on-the-same-machine/
    • https://dev.mysql.com/doc/refman/5.7/en/multiple-servers.html
    • http://www.mysqlab.net/knowledge/kb/detail/topic/installation/id/4926
    • https://teknoteknik.wordpress.com/2010/07/29/how-to-run-multiple-instances-of-mysql-server-on-a-single-linux-server/
    • http://lasanthals.blogspot.com.es/2012/09/running-multiple-instances-of-mysql-on.html
    • https://dev.mysql.com/doc/refman/8.0/en/mysqld-multi.html
    • https://askubuntu.com/questions/932713/what-is-the-difference-between-chmod-x-and-chmod-755
    • https://askubuntu.com/questions/335242/how-to-install-an-init-d-script
    • https://raspberrypi.stackexchange.com/questions/13358/insserv-warning-script-mathkernel-missing-lsb-tags-and-overrides?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa
    • https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa
    • https://askubuntu.com/questions/790384/mysql-how-to-log-into-root-from-a-user-account-after-upgade-to-5-7?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa