Running Multiple Instances of MySql in ubuntu 16.04

Hi,

In this post we will discuss about having multiple instances of mysql on a server machine.

I am trying  to establish the objective here using MySql-5.7.25 on Ubuntu, you can do the same with other version of MySql as well

If you do not have one, you can refer an other article of mine to install MySql Server, the link to it is given below

MySql Server Installation in Ubuntu

1. Disabling mysqld in AppArmor 

The first and foremost is disabling mysqld using AppArmor tool, Ubuntu is shipped with this tool by default and if not you can install the same using the following web page as reference...

Installation of AppArmor

Create a link to "usr.sbin.mysqld" in disable folder as follows...

Move to folder "/etc/apparmor.d/disable" and provide the command below to have mysql daemon disabled at the start up

> sudo ln -s ../usr.sbin.mysqld usr.sbin.mysqld

RESTART YOUR MACHINE

2. Creating Directories and Files

The second step is to create required directories for each instances of mysql, the following commands would help to create those directories for you.

I have chosen "/opt" directory as my location to put the directories in but, you can choose to have your convenient location to create directories...

The below commands would ask to provide the password before the execution, provide the one associated with the account.

Creating directories for Instance One

> sudo mkdir -p /opt/mysql/one/data

> sudo mkdir -p /opt/mysql/one/log

> sudo touch /opt/mysql/one/log/mysql.log

> sudo chown -R mysql:mysql /opt/mysql/one

> sudo /usr/sbin/mysqld --initialize --explicit_defaults_for_timestamp --user=mysql --basedir=/opt/mysql/one --datadir=/opt/mysql/one/data

Creating directories for Instance Two

> sudo mkdir -p /opt/mysql/two/data

> sudo mkdir -p /opt/mysql/two/log

> sudo touch /opt/mysql/two/log/mysql.log

> sudo chown -R mysql:mysql /opt/mysql/two

> sudo /usr/sbin/mysqld --initialize --explicit_defaults_for_timestamp --user=mysql --basedir=/opt/mysql/two --datadir=/opt/mysql/two/data

Creating directories for Instance Three

> sudo mkdir -p /opt/mysql/three/data

> sudo mkdir -p /opt/mysql/three/log

> sudo touch /opt/mysql/three/log/mysql.log

> sudo chown -R mysql:mysql /opt/mysql/three

> sudo /usr/sbin/mysqld --initialize --explicit_defaults_for_timestamp --user=mysql --basedir=/opt/mysql/three --datadir=/opt/mysql/three/data

3. Editing my.cnf file (/etc/mysql/my.cnf)

The third step is to update the file called "my.cnf" file which you can locate under the folder "/etc/mysql"

The following are the details to be provided for each instance of MySql in "my.cnf", these entries are called group entries where each entry corresponds to an instance of mysql

[mysqld_multi]

mysqld	=	/usr/bin/mysqld_safe
mysqladmin	=	/usr/bin/mysqladmin
user	=	multi_admin
password	=	password
log	=	/var/log/mysqld_multi.log

[mysqld1]
skip-grant-tables

user	=	mysql
port	=	3306
datadir	=	/opt/mysql/one/data
socket	=	/opt/mysql/one/one-mysql.sock
pid-file	=	/opt/mysql/one/one-mysql.pid
log-error	=	/opt/mysql/one/log/mysql.log

[mysqld2]
skip-grant-tables

user	=	mysql
port	=	4406
datadir	=	/opt/mysql/two/data
socket	=	/opt/mysql/two/two-mysql.sock
pid-file	=	/opt/mysql/two/two-mysql.pid
log-error	=	/opt/mysql/two/log/mysql.log

[mysqld3]
skip-grant-tables

user	=	mysql
port	=	5506
datadir	=	/opt/mysql/three/data
socket	=	/opt/mysql/three/three-mysql.sock
pid-file	=	/opt/mysql/three/three-mysql.pid
log-error	=	/opt/mysql/three/log/mysql.log

Start the Instances

After the configuration update is finished at "my.cnf", the immediate next step is to start the instance to check its function.

The command given below would help you to achieve starting an instance, if you are successful please perform the same actions for other instances as well...

NOTE: do not forget to change the socket file parameter...

> sudo mysqld_multi start

> mysql -u root -S /opt/mysql/default/default-mysql.sock -p

mysql> use mysql

mysql> UPDATE user SET authentication_string=PASSWORD('admin') WHERE User='root';

mysql> FLUSH PRIVILEGES;

4. Granting SHUTDOWN privilege to multi_admin USER

This step discuss granting shutdown privileges to multi_admin user, to provide the same kill the running mysql instances using "ps-aux | grep mysql"

remove "skip-grant-tables" parameter from every group in my.cnf file

now your my.cnf file should look something as below

[mysqld_multi]

mysqld	=	/usr/bin/mysqld_safe
mysqladmin	=	/usr/bin/mysqladmin
user	=	multi_admin
password	=	password
log	=	/var/log/mysqld_multi.log

[mysqld1]

user	=	mysql
port	=	3306
datadir	=	/opt/mysql/one/data
socket	=	/opt/mysql/one/one-mysql.sock
pid-file	=	/opt/mysql/one/one-mysql.pid
log-error	=	/opt/mysql/one/log/mysql.log

[mysqld2]

user	=	mysql
port	=	4406
datadir	=	/opt/mysql/two/data
socket	=	/opt/mysql/two/two-mysql.sock
pid-file	=	/opt/mysql/two/two-mysql.pid
log-error	=	/opt/mysql/two/log/mysql.log

[mysqld3]

user	=	mysql
port	=	5506
datadir	=	/opt/mysql/three/data
socket	=	/opt/mysql/three/three-mysql.sock
pid-file	=	/opt/mysql/three/three-mysql.pid
log-error	=	/opt/mysql/threelog/mysql.log

5. Start an instance

After removing the "skip-grant-tables" parameter from the group in "my.cnf" each instance of MySql will be identified by numbers based  on the position they appear in the configuration file.

To start the first instance, issue the following command

> sudo mysqld_multi start 1

login as root using the following command on instance 1 to check whether the connection establishment is fine.

> mysql -u root -S /opt/mysql/default/default-mysql.sock -p

provide the password of the root user

We can conclude the connection is fine, the the terminal window appears with mysql prompt.

6. Reset the password of root

This is final step and also an optional step to have, If you want to reset the root users password you can perform resetting the password using the following commands.

mysql> SET PASSWORD = PASSWORD('admin');

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY '<password-provided-in-my.cnf-for-multi_admin>';