Case 1: configure MySQL multiple instances
1.1 problem

On the host 192.168.4.56, configure the first MySQL instance
 Instance name mysql1, port 3307
 Database directory / data3307, PID file mysql1.pid
 Error log mysql1.err
 On the host 192.168.4.56, configure the second MySQL instance
 Instance name mysql2, port 3308
 Database directory / data3308, PID file mysql2.pid
 Error log mysql2.err

Step 1: configure multiple instances (operation above 192.168.4.56)

What is multi instance:

Running multiple database services on one physical host can save operation and maintenance costs and improve hardware utilization

1) Decompress the software and modify the directory name

[root@mysql ~]# cd mysql/
[root@mysql mysql]# ls
mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@mysql mysql]# tar -xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@mysql mysql]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql

2) Adjust PATH variable

[root@mysql mysql]# echo  "export  PATH=/usr/local/mysql/bin:$PATH" \ 
 >> /etc/profile
[root@mysql mysql]# source /etc/profile
[root@mysql mysql]# echo $PATH
/usr/local/mysql/bin:/usr/local/mycat/bin:/usr/local/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin

3) Edit the main configuration file / etc/my.cnf

Each instance should have its own database directory, listening port number, instance name and independent sock file

[mysqld_multi]        //Enable multiple instances
mysqld = /usr/local/mysql/bin/mysqld_safe        //Specify process file path
mysqladmin = /usr/local/mysql/bin/mysqladmin    //Specify management command path
user = root        //Specify process user
[mysqld1]        //Instance process name
port=3307        //Port number
datadir=/data3307        //Database directory, to create manually
socket=/data3307/mysqld.sock        //Specify the path and name of the sock file
pid-file=/data3307/mysql1.pid        //Process pid number file location
log-error=/data3307/mysql1.err        //Error log location 
[mysqld2]
port=3308
datadir=/data3308
socket=/data3308/mysqld.sock
pid-file=/data3308/mysql2.pid
log-error=/data3308/mysql2.err 

4) Create database directory

[root@mysql mysql]# mkdir -p /data3307
[root@mysql mysql]# mkdir -p /data3308

5) Create the owner and group mysql for the process to run

[root@mysql mysql]# useradd mysql
[root@mysql mysql]# chown  mysql:mysql  /data*

6) Initialize authorization Library

[root@mysql mysql]# mysqld --user=mysql --basedir=/usr/local/mysql 
--datadir=/data3307 --initialize
...
2018-09-26T07:07:33.443378Z 1 [Note] A temporary password is generated for root@localhost: 7L?Vi!dGKmgu        //Initialization password for root login
[root@mysql mysql]# mysqld --user=mysql --basedir=/usr/local/mysql
 --datadir=/data3308 --initialize
...
2018-09-26T07:08:07.770289Z 1 [Note] A temporary password is generated for root@localhost: kC)BbyUp1a-b        //Initialization password for root login

7) Start multiple instances

[root@mysql mysql]# Mysqld_multi start 1 / / 1 is the instance number
[root@mysql mysql]# mysqld_multi  start 2

8) View port

 [root@mysql mysql]# netstat -utnlp  | grep :3307
tcp6       0      0 :::3307                 :::*                    LISTEN      21009/mysqld        
 [root@mysql mysql]# netstat -utnlp  | grep :3308
tcp6       0      0 :::3308                 :::*                    LISTEN      21177/mysqld        
[root@mysql mysql]# ps -C mysqld
  PID TTY          TIME CMD
21009 pts/1    00:00:00 mysqld
21177 pts/1    00:00:00 mysqld

9) Access multiple instances

Log in to multi instance 1 with initialization password

[root@mysql mysql]# mysql -u root -p'7L?Vi!dGKmgu' -S /data3307/mysqld.sock
mysql> alter user root@"localhost"  identified by '123456';        //Change Password
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

Log in to multi instance 2 with initialization password

[root@mysql bin]# mysql -u root -p'kC)BbyUp1a-b' -S /data3307/mysqld.sock
mysql> alter user root@"localhost"  identified by '123456';        //Change Password
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

10) Create Library

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

11) Stop started instance service

mysqld_multi --user=root --password = password stop instance number

[root@mysql mysql]#  mysqld_multi  --user=root  --password=123456 stop 1
[root@mysql mysql]# Netstat - utnlp | grep: 3307 / / check that there is no port
[root@mysql mysql]#  mysqld_multi  --user=root  --password=123456 stop 2
[root@mysql mysql]# Netstat - utnlp | grep: 3308 / / check that there is no port
[root@mysql mysql]# mysql -uroot   -p123456   -S    /data3307/mysqld.sock 
//connection denied
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data3307/mysqld.sock' (2)