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: 
    [mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld-instances/mysqld_multi.log
user       = multi_admin
password   = password

[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
    • #!/bin/sh
### BEGIN INIT INFO
# Provides:          mysqld_multi
# Required-Start:
# Required-Stop:
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: Example initscript
# Description:       This script takes care of mysql multi
### END INIT INFO

#put your MySql root pass here with the --password=
#example: pass="--password=ChangeThisPassword"
pass="--password=ChangeThisPassword"

mysqld_start() {
echo "Starting mysqld..."
mysqld_multi start $* $pass
}

mysqld_stop() {
echo "Stopping mysqld..."
mysqld_multi stop $* $pass
}

mysqld_restart() {
mysqld_stop $*
sleep 1
mysqld_status $*
sleep 1
mysqld_start $*
sleep 1
mysqld_status $*
}
mysqld_which() {
b=`grep "\[mysqld[0-9][0-9]*\]" /etc/my.cnf | sed 's/[^A-Za-z0-9]//g' | wc -l`
echo "The following $b instances are configured:"
grep "\[mysqld[0-9][0-9]*\]" /etc/my.cnf | sed 's/[^A-Za-z0-9]//g'
}

mysqld_status() {
mysqld_multi report $*
}

#Set these variables so mysqld finds the right information
export PATH=/usr/bin:/usr/sbin:$PATH
option=$1
shift

case "$option" in
'start') mysqld_start $*;;
'stop') mysqld_stop $*;;
'restart') mysqld_restart $*;;
'which') mysqld_which $*;;
'status')
mysqld_status $* ;;
*)
echo "Usage: $0 [start|stop|restart|status|which]"
echo "Optional info: "
echo " This uses mysql_multi, which allows control of individual mysqld "
echo " instances. Do this by specifying a list of numbers following the"
echo " command (start/stop/etc.). For example:"
echo " $0 stop 1,3"
echo " $0 stop 1-3"
echo " $0 stop 1"
echo " $0 stop 1-3,5"
echo
echo " do $0 which to list the mysql instances that are configured"
esac
    • 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:

    # This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf
#
# SOME IMPORTANT NOTES FOLLOW:
#
# 1.COMMON USER
#
#   Make sure that the MySQL user, who is stopping the mysqld services, has
#   the same password to all MySQL servers being accessed by mysqld_multi.
#   This user needs to have the 'Shutdown_priv' -privilege, but for security
#   reasons should have no other privileges. It is advised that you create a
#   common 'multi_admin' user for all MySQL servers being controlled by
#   mysqld_multi. Here is an example how to do it:
#
#   GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password'
#
#   You will need to apply the above to all MySQL servers that are being
#   controlled by mysqld_multi. 'multi_admin' will shutdown the servers
#   using 'mysqladmin' -binary, when 'mysqld_multi stop' is being called.
#
# 2.PID-FILE
#
#   If you are using mysqld_safe to start mysqld, make sure that every
#   MySQL server has a separate pid-file. In order to use mysqld_safe
#   via mysqld_multi, you need to use two options:
#
#   mysqld=/path/to/mysqld_safe
#   ledir=/path/to/mysqld-binary/
#
#   ledir (library executable directory), is an option that only mysqld_safe
#   accepts, so you will get an error if you try to pass it to mysqld directly.
#   For this reason you might want to use the above options within [mysqld#]
#   group directly.
#
# 3.DATA DIRECTORY
#
#   It is NOT advised to run many MySQL servers within the same data directory.
#   You can do so, but please make sure to understand and deal with the
#   underlying caveats. In short they are:
#   - Speed penalty
#   - Risk of table/data corruption
#   - Data synchronising problems between the running servers
#   - Heavily media (disk) bound
#   - Relies on the system (external) file locking
#   - Is not applicable with all table types. (Such as InnoDB)
#     Trying so will end up with undesirable results.
#
# 4.TCP/IP Port
#
#   Every server requires one and it must be unique.
#
# 5.[mysqld#] Groups
#
#   In the example below the first and the fifth mysqld group was
#   intentionally left out. You may have 'gaps' in the config file. This
#   gives you more flexibility.
#
# 6.MySQL Server User
#
#   You can pass the user=... option inside [mysqld#] groups. This
#   can be very handy in some cases, but then you need to run mysqld_multi
#   as UNIX root.
#
# 7.A Start-up Manage Script for mysqld_multi
#
#   In the recent MySQL distributions you can find a file called
#   mysqld_multi.server.sh. It is a wrapper for mysqld_multi. This can
#   be used to start and stop multiple servers during boot and shutdown.
#
#   You can place the file in /etc/init.d/mysqld_multi.server.sh and
#   make the needed symbolic links to it from various run levels
#   (as per Linux/Unix standard). You may even replace the
#   /etc/init.d/mysql.server script with it.
#
#   Before using, you must create a my.cnf file either in /usr/my.cnf
#   or /root/.my.cnf and add the [mysqld_multi] and [mysqld#] groups.
#
#   The script can be found from support-files/mysqld_multi.server.sh
#   in MySQL distribution. (Verify the script before using)
#

    [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

    shell> mysql -u root -S /tmp/mysql.sock -p
Enter password:
mysql> CREATE USER 'multi_admin'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost';

    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
    Tags