Standard Operating Procedure (SOP)

For

MySQL Multiple Instance

1. Introduction

In This Standard Operating Procedure (SOP), we get to know how to install MySQL multiple instances in
Linux. To provide architecture view of application and step by step execution guidelines to team of a
system in case of disruption, to facilitate recovery within the stipulated timeframes to ensure business
continuity with minimal impact on business operation.

2.Application Architecture

Operating System Windows / Linux

Technology OpensourceDB
Database MySQL

Environment PROD/PRE-PROD/UAT/DR

3. Start Criteria

Bi-directional communication should be in place. Check telnet from primary server to secondary server
and vice versa.

Confidential Softcopy: 901711884.docx Page 1 of 8

4. prerequisites

 Sufficient system resources: Each MySQL instance requires its own set of system resources,
including CPU, memory, and disk space. Make sure your server has enough resources to
accommodate multiple instances without impacting performance.
 Separate MySQL installations: Each MySQL instance must have its own installation directory. This
means you'll need to install MySQL multiple times, specifying different installation paths for each
instance.
 Unique configuration files: Each MySQL instance should have its own configuration file. You can
create separate configuration files by duplicating the default configuration file (typically named
my.cnf or my.ini) and modifying the necessary parameters, such as port number, data directory,
and log files.
 Distinct port numbers: To avoid conflicts, each MySQL instance must be assigned a unique port
number. By default, MySQL uses port 3306, so you'll need to specify a different port number for
each instance in their respective configuration files.
 Separate data directories: Each MySQL instance should have its own data directory to store the
database files. When setting up each instance, specify a unique data directory path in the
configuration file.
 Different service names: If you're running MySQL as a service, you'll need to assign different
service names for each instance. This ensures that the instances can be started, stopped, and
managed independently.
 Grant appropriate user privileges: Ensure that the user accounts used to access each MySQL
instance have appropriate privileges and access rights to their respective databases.

5. Process Flow

 To install MySQL multiple instances on Linux, First, we need to download packages from download
section of MySQL website.
https://dev.mysql.com/downloads/mysql/
 Under MySQL community server, select operating system as Linux, and OS version as preferred.
 Then click on download.
 Copy the link address and using wget command download it in Linux.
 We need to extract the tar file.
 Create the necessary directories, give permission and create symbolic link.
 MySQL directory given permission of mysql.
 Then, create a configuration file for each instance and configure them.
 We need to Initialize each instance of the MySQL files in bin.
 Create a symbolic link and update environment variables. (without specifying the full path to the
MySQL binary directory (/usr/local/mysql/bin). Instead, you can simply use mysqld as the cmd)
 Give permission of mysql because some of the file permissions newly generated by mysql during
initialization are root, so it is best to reauthorize the datadir path to mysql before starting.

Confidential Softcopy: 901711884.docx Page 2 of 8

  Once Initialized, Get temporary password.
 Once this is done. We need to up MySQL sevices for each instance.
 After MySQL services up and running, we need to login to all Mysql instance one by one.
 After this, we need to reset the temporary password that we have got earlier.

6.Technical Flow

To install mysql on linux, first we need to download packages from download section of mysql website.
https://dev.mysql.com/downloads/mysql/

Copy the link address and using wget command download it in linux.

We need to extract the tar file.

Create the necessary directories, MySQL directory given permission of mysql and create symbolic link.

Create a configuration file for each instance.

Confidential Softcopy: 901711884.docx Page 3 of 8

We need to Initialize the mysql multiple instance files in bin as below.

Create a symbolic link and update environment variables.

# Add the following information at the end of the file(/etc/profile)

-export PATH=$PATH:/usr/local/mysql/bin

# Make environment variables take effect

-source /etc/profile

Confidential Softcopy: 901711884.docx Page 4 of 8

Again MySQL directory given permission of mysql and initialize. (The nohup command ensures that the
process continues running even if the terminal session ends)

Once Initialized, Get the temporary password.

After this, we need to reset the temporary password for each instance.

After reset MySQL DB now service is up and running.

Confidential Softcopy: 901711884.docx Page 5 of 8

To shutdown an instance

Confidential Softcopy: 901711884.docx Page 6 of 8

7. Query to Be Used

Below queries needs to be performed for any activities:

-Wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz
- tar -zxvf mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz -C /usr/local
- cd /usr/local
- ln -s /usr/local/mysql-5.7.42-linux-glibc2.12-x86_64/ mysql
- mkdir -p /mysql/{3307,3308}/data
- chown -R mysql: mysql /mysql
- cd /mysql/3307
- vi my.cnf
-/usr/local/mysql/bin/mysqld --defaults-file=/mysql/3307/my.cnf --initialize --basedir=/usr/local/mysql --
datadir=/mysql/3307/data
-cd ..
-cd /mysql/3308
-vi my.cnf
-/usr/local/mysql/bin/mysqld --defaults-file=/mysql/3308/my.cnf --initialize --basedir=/usr/local/mysql --
datadir=/mysql/3308/data
-cd data
-ln -s /usr/local/mysql/bin/mysqld /usr/bin
-cd
-vi /etc/profile
# Add the following information at the end of the file(/etc/profile)
-export PATH=$PATH:/usr/local/mysql/bin
# Make environment variables take effect
-source /etc/profile
-chown -R mysql: mysql /mysql
-nohup /usr/local/mysql/bin/mysqld --defaults-file=/mysql/3307/my.cnf --user=mysql &
-nohup /usr/local/mysql/bin/mysqld --defaults-file=/mysql/3308/my.cnf --user=mysql &
-ps -ef | grep mysql
-grep 'temporary password' /mysql/3307/data/mysql07.log
-grep 'temporary password' /mysql/3308/data/mysql08.log
- /usr/local/mysql/bin/mysql -S /tmp/mysql_3307.sock -p
-alter user ‘root’@’localhost’ identified by ‘Mysql’@’123’;
-\q
- /usr/local/mysql/bin/mysql -S /tmp/mysql_3308.sock -p
-alter user ‘root’@’localhost’ identified by ‘Mysql’@’123’;

Confidential Softcopy: 901711884.docx Page 7 of 8

-\q
- cd /usr/local/mysql/bin
- ./bin/mysql -S /tmp/mysql_3307.sock -pMysql@123
-\s
- cd /usr/local/mysql/bin
- ./bin/mysql -S /tmp/mysql_3308.sock -pMysql@123
-\s
-\q
- ./mysqladmin -u root -p -P3307 --socket=/tmp/mysql_3307.sock shutdown
-ps -ef | grep mysql

8.Known Error Logs

N.A

9. References
https://dev.mysql.com/downloads/mysql/

10.Made By

11.Escalation Matrix

Escalation Matrix
Manager Mr. Krishnanand Tiwari krishnanand.tiwari@cloverinfotech.com
Team
Lead Mr.Sumit Kajwe sumitc.kajwe@cloverinfotech.com
Techno- MySQ Mon- Postgr-
logy L goDB eSQL DB2

Hynda Amit.singh@cloverinfotech.com
Amit Mayur Subham Mayur.tawde@cloverinfotech.com
vi
Subham.dash@cloverinfotech.com
Pranjal.sawant@cloverinfotech.com
Pranjal Dhruv Vaibhav Dhruv.gujrati@cloverinfotech.com
Vaibhav.margaje@cloverinfotech.com

Confidential Softcopy: 901711884.docx Page 8 of 8