MySQL Multi-instance Setup

MySQL Multi-instance

Overview: Running multiple database services on a single physical host

Function: Save operation and maintenance cost, improve hardware utilization

Configuration Step Description
We take mysql-5.7.20 as an example
--Install packages that support multi-instance services

1.1 Decompression Software

[Email protected]~]# tar-zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

1.2– Modifying directory names

[Email protected] mysql-20]# MV mysql-5.7.20-linux-glibc2.12-x86_64//usr/local/mysql

1.3– Modifying the PATH variable

[Email protected] local]# Vim/etc/profile

[Email protected] ~]# echo "Export path=/usr/local/mysql/bin: $PATH" >>/etc/profile

[Email protected] local]# Source/etc/profile

--Modifying the master configuration file

[Email protected] local]# VIM/ETC/MY.CNF

[Mysqld_multi]//enable multi-instance

Mysqld =/usr/local/mysql/bin/mysqld_safe//path of the specified process file

Mysqladmin =/usr/local/mysql/bin/mysqladmin//Specify Management command path

user = root//Specify who is calling the process

[Mysqld1]//instance process name, X denotes instance name, e.g. [MYSQL1]

port=3307//Port number

Datadir=/dataone//Database directory, to be created manually

Socket=/dataone/mysqld.sock//Specify the path and name of the sock file

Log-error=/dataone/mysqld.log//Error log location

Pid-file=/dataone/mysqld.pid//Process PID Number file location

[Mysqld2]//instance process name, X denotes instance name, e.g. [MYSQL1]

port=3308//Port number

Datadir=/datatwo//Database directory, to be created manually

Socket=/datatwo/mysqld.sock//Specify the path and name of the sock file

Log-error=/datatwo/mysqld.log//Error log location

Pid-file=/datatwo/mysqld.pid//Error log location

– Set the settings according to the configuration file

[Email protected] ~]# mkdir-p/dataone

[Email protected] ~]# mkdir-p/datatwo

[[email protected] ~]# useradd MySQL

C[[email protected] ~]# chown mysql:mysql/data*

* File permissions can be changed for security

Chown mysql:mysql/data*

– Initialize the authorization library

[[email protected] bin]#./mysqld--user=mysql--basedir= Software Installation directory--datadir= database directory –initialize//Initialize authorization library

] #mysqld--user=mysql--basedir=/usr/local/mysql--datadir=/dataone--initialize

] #mysqld--user=mysql--basedir=/usr/local/mysql--datadir=/datatwo--initialize

– Start Service

[[email protected] ~]# Mysqld_multi Start instance number//Start instance process

– Client Access

[[Email protected]ost50~]# mysqld_multi Start 1

[[email protected] ~]# Mysqld_multi start 2

[Email protected] ~]# NETSTAT-UTNLP | grep:3308

TCP6 0 0::: 3308:::* LISTEN 1156/mysqld

[Email protected] ~]# NETSTAT-UTNLP | grep:3307

TCP6 0 0::: 3307:::* LISTEN 927/mysqld

[Email protected] ~]#

[Email protected] bin]#/mysqld_multi--user=root--password=

Password Stop instance number//Stop instance process

6 accessing multi-instance services

Connect Instance Service 1

[[Email protected] ~] #mysql-uroot-p ' bxk.5j!pjto# '-s/dataone/mysqld.sock

mysql> ALTER User User () identified by "123456";

Mysql> quit;

]# mysql-uroot-p123456-s/dataone/mysqld.sock

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Performance_schema |

| SYS |

+--------------------+

4 rows in Set (0.00 sec)

Mysql>

Connect Instance Service 2

[[Email protected] ~] #mysql-uroot-p ' Bksaf+xzk0v3 '-s/datatwo/mysqld.sock

mysql> alter User User () identified by "123456";

Query OK, 0 rows Affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Performance_schema |

| SYS |

+--------------------+

4 rows in Set (0.00 sec)

Mysql> quit

Bye

[Email protected] ~]# mysql-uroot-p123456-s/datatwo/mysqld.sock

Stop the instance service from starting

]# mysqld_multi--user=root--password= Password Stop instance number

[[Email protected]~]# NETSTAT-UTNLP | grep:3307

TCP6 0 0::: 3307:::* LISTEN 927/mysqld

[Email protected] ~]#

[Email protected] ~]#

[[Email protected]~]# NETSTAT-UTNLP | grep:3308

TCP6 0 0::: 3308:::* LISTEN 1156/mysqld

[[email protected] ~]# mysqld_multi--user=root--password=123456 stop 1

[Email protected] ~]# NETSTAT-UTNLP | grep:3307

[Email protected] ~]# NETSTAT-UTNLP | grep:3308

TCP6 0 0::: 3308:::* LISTEN 1156/mysqld

[Email protected] ~]#

[Email protected] ~]#

[[Email protected]~]#

[[email protected] ~]# mysqld_multi--user=root--password=123456 stop 2

[[Email protected]~]# NETSTAT-UTNLP | grep:3308

[Email protected]~]# mysql-uroot-p123456-s/datatwo/mysqld.sock

MySQL: [Warning] Using a password on the command line interface can is insecure.

ERROR 2002 (HY000): Can ' t connect to local MySQL server through socket '/datatwo/mysqld.sock ' (2)

MySQL Multi-instance Setup