Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Thursday, 12 July 2018

Run Multiple Instance of MySQL on CentOS 7 - Unix/Linux


In this article , I will explain you why we exactly need to install multiple instances of MySQL (the well-known mysqld process) running in the same server concurrently in a transparent way, instead of having them executed in separate servers/containers/virtual machines, is not very common. MySQL provides a tool to facilitate the execution of multiple instances called “mysqld_multi”.

For some particular requirement like we have to create a Central Backup Server for 4 different MySQL Servers. To achieve this requirement we can think about Multi-Instance MySQL Server.

I will show you how to Install 4 instances of MySQL on our Backup Server 172.11.2.232

Before start the configuration, we are assuming that you have already installed MYSQL by default with port 3306 and binary files in /usr

Pre Installed MySQL Port – “3306” (default)
Pre Installed my.cnf – “/etc/my.cnf”
Pre Installed Base Directory – “/usr”
Pre Installed Data Directory – “/var/lib/mysql”
Root user for MySQL – “dbuser/root”

Architecture Diagram for 4-Instances:


Precautions:-

First of all stop the pre installed mysqld service
[root@NMNMYSQLTEST01 alok]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
Modify the default my.cnf as Group My.cnf

New “my.cnf” will looks like as below...
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# or Contact us on Here!

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = dbuser
password   = XXXXXXX

[mysqld]
user   = mysql
datadir  = /var/lib/mysql
socket  = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
port  = 3306
skip_name_resolve
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0

# Recommended in standard MySQL setup
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld1]
user  = mysql
pid-file = /var/run/mysqld/mysqld1.pid
socket  = /var/lib/mysql1/mysqld1.sock
port  = 3307
datadir  = /var/lib/mysql1
skip_name_resolve


[mysqld2]
user            = mysql
pid-file        = /var/run/mysqld/mysqld2.pid
socket          = /var/lib/mysql2/mysqld2.sock
port            = 3308
datadir         = /var/lib/mysql2
skip_name_resolve

[mysqld3]
user            = mysql
pid-file        = /var/run/mysqld/mysqld3.pid
socket          = /var/lib/mysql3/mysqld3.sock
port            = 3309
datadir         = /var/lib/mysql3
skip_name_resolve

[mysqld4]
user            = mysql
pid-file        = /var/run/mysqld/mysqld4.pid
socket          = /var/lib/mysql4/mysqld4.sock
port            = 3310
datadir         = /var/lib/mysql4
skip_name_resolve

#[mysqld_safe]
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
Installation Steps for multiple instances:

  • 1 > Create Data directory for all instances.

[root@NMNMYSQLTEST01 alok]# cd /var/lib/
[root@NMNMYSQLTEST01 lib]# mkdir mysql1
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql1
 
[root@NMNMYSQLTEST01 lib]# mkdir mysql2
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql2
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql2
 
[root@NMNMYSQLTEST01 lib]# mkdir mysql3
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql3
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql3
 
[root@NMNMYSQLTEST01 lib]# mkdir mysql3
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql3
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql3
 
[root@NMNMYSQLTEST01 lib]# mkdir mysql4
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql4
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql4

  • 2 > Modify new my.cnf as shown above.
  • 3 > Create data directory by copy preinstalled default datadir.
[root@NMNMYSQLTEST01 alok]# cp /var/lib/mysql/* /var/lib/mysql1/
[root@NMNMYSQLTEST01 alok]# cp /var/lib/mysql/* /var/lib/mysql2/
[root@NMNMYSQLTEST01 alok]# cp /var/lib/mysql/* /var/lib/mysql3/
[root@NMNMYSQLTEST01 alok]# cp /var/lib/mysql/* /var/lib/mysql4/


OR
Create data directory using “mysql_install_db”
[root@NMNMYSQLTEST01 alok]# mysql_install_db --user=mysql --datadir=/var/lib/mysql1
[root@NMNMYSQLTEST01 alok]# mysql_install_db --user=mysql --datadir=/var/lib/mysql2
[root@NMNMYSQLTEST01 alok]# mysql_install_db --user=mysql --datadir=/var/lib/mysql3
[root@NMNMYSQLTEST01 alok]# mysql_install_db --user=mysql --datadir=/var/lib/mysql4

This will create default data directory with empty root user. If you create datadir using this command then you will have to further troubleshoot in login (need to create root/dbuser later to use the instance) 
Suggest: Best practice to copy any existing data directory.

  • 4 > Change the Ownership 
[root@NMNMYSQLTEST01 alok]# chown –R mysql:mysql /var/lib/mysql1
[root@NMNMYSQLTEST01 alok]# chown –R mysql:mysql /var/lib/mysql2
[root@NMNMYSQLTEST01 alok]# chown –R mysql:mysql /var/lib/mysql3
[root@NMNMYSQLTEST01 alok]# chown –R mysql:mysql /var/lib/mysql4

  • 5 > Start all/any particular Instances
[root@NMNMYSQLTEST01 alok]# mysqld_multi start
[root@NMNMYSQLTEST01 alok]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
OR
[root@NMNMYSQLTEST01 alok]# mysqld_multi start 1,2,4
[root@NMNMYSQLTEST01 alok]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is running

  • 6 > Connect any instance on command line as below.
mysql -u dbuser -p --port=3307 --socket=/var/lib/mysql1/mysqld1.sock
mysql -u dbuser -p --port=3308 --socket=/var/lib/mysql2/mysqld2.sock
mysql -u dbuser -p --port=3309 --socket=/var/lib/mysql3/mysqld3.sock
mysql -u dbuser -p --port=3310 --socket=/var/lib/mysql4/mysqld4.sock

  • 7 > To stop any Instance you have use mysqladmin. Need to shutdown privileges in mysqladmin user.

Mysql> GRANT SHUTDOWN ON *.* TO 'dbuser'@'localhost' IDENTIFIED BY 'XXXXXXXX';

  • 8 > Stop any instance using below command.

mysqladmin -u dbuser -p -P=3306 -S /var/lib/mysql1/mysqld1.sock shutdown
mysqladmin -u dbuser -p -P=3306 -S /var/lib/mysql2/mysqld2.sock shutdown
mysqladmin -u dbuser -p -P=3306 -S /var/lib/mysql3/mysqld3.sock shutdown
mysqladmin -u dbuser -p -P=3306 -S /var/lib/mysql4/mysqld4.sock shutdown


Now 4 multiple instances has been installed successfully.

[root@NMNMYSQLTEST01 alok]# netstat -ntpl | grep mysqld
tcp6       0      0 :::3306           :::*              LISTEN      20926/mysqld
tcp6       0      0 :::3307           :::*              LISTEN      21315/mysqld
tcp6       0      0 :::3308           :::*              LISTEN      22348/mysqld
tcp6       0      0 :::3309           :::*              LISTEN      19325/mysqld
tcp6       0      0 :::3310           :::*              LISTEN      19326/mysqld

Pre installed default port 3306 which can be stopped by “Service mysqld stop”
Manual Installed Instances are 3307, 3308, 3309, 3310
Base directory for all instances are same as 3306
basedir=/usr
bindir=/usr/bin



Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...