MySQL Series II Multi-Instance Configuration

[root@centos7 ~]# mkdir -p /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data,bin}
[root@centos7 ~]# chown -R mysql:mysql /mysqldb/

[root@centos7 ~]# cd /app/mysql/
[root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3306/data/ --user=mysql --basedir=/app/mysql/ 
[root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3307/data/ --user=mysql --basedir=/app/mysql/
[root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3308/data/ --user=mysql --basedir=/app/mysql/

[root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3306/etc/my.cnf
[root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3307/etc/my.cnf
[root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3308/etc/my.cnf
[root@centos7 mysqldb]# cd /mysqldb/
[root@centos7 mysqldb]# vim 3306/etc/my.cnf
[mysqld]
port = 3306
datadir = /mysqldb/3306/data
socket = /mysqldb/3306/socket/mysql.sock
[root@centos7 mysqldb]# vim 3307/etc/my.cnf #Change according to the above configuration example [root@centos7 mysqldb]# vim 3308/etc/my.cnf

[root@centos7 ~]# cat mysqld #Script example#!/bin/bash

port=3306 #needs to be changed to the port number of the current instancemysql_user="root"
mysql_pwd=""
cmd_path="/app/mysql/bin" #bin in the installation directory
mysql_basedir="/mysqldb" #The directory where the instance database file is locatedmysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
    else
      printf "MySQL is running...\n"
      exit
    fi
}


function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}


function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
[root@centos7 ~]# cp mysqld /mysqldb/3306/bin/
[root@centos7 ~]# cp mysqld /mysqldb/3307/bin/
[root@centos7 ~]# cp mysqld /mysqldb/3308/bin/
[root@centos7 ~]# vim /mysqldb/3306/bin/mysqld
port=3306
[root@centos7 ~]# vim /mysqldb/3307/bin/mysqld
port=3307
[root@centos7 ~]# vim /mysqldb/3308/bin/mysqld
port=3308

[root@centos7 ~]# chmod 700 /mysqldb/3306/bin/mysqld 
[root@centos7 ~]# chmod 700 /mysqldb/3307/bin/mysqld  
[root@centos7 ~]# chmod 700 /mysqldb/3308/bin/mysqld 

[root@centos7 ~]# service mysqld stop #Ensure that the original service is stopped and release port 3306 [root@centos7 ~]# /mysqldb/3306/bin/mysqld start #Start the service [root@centos7 ~]# /mysqldb/3307/bin/mysqld start
[root@centos7 ~]# /mysqldb/3308/bin/mysqld start
[root@centos7 ~]# ss -tnl #If you see that the ports listened by the three instances are all open, it means that the service is started normally LISTEN 0 80 :::3306 :::*
LISTEN 0 80 :::3307 :::*
LISTEN 0 80 :::3308 :::*

[root@centos7 ~]# mysql -S /mysqldb/3306/socket/mysql.sock #Use -S to specify the socket file Server version: 10.2.15-MariaDB-log Source distribution
MariaDB [(none)]> show variables like '%port'; #Check if the port is 3306
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| extra_port | 0 |
| large_files_support | ON |
| port | 3306 |
| report_port | 3306 |
+---------------------+-------+
4 rows in set (0.00 sec)

[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock #Reconnect and test 3307 and 3308
Server version: 10.2.15-MariaDB-log Source distribution
MariaDB [(none)]> show variables like '%port';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| extra_port | 0 |
| large_files_support | ON |
| port | 3307 |
| report_port | 3307 |
+---------------------+-------+
4 rows in set (0.00 sec)

[root@centos7 ~]# mysql -S /mysqldb/3308/socket/mysql.sock
Server version: 10.2.15-MariaDB-log Source distribution
MariaDB [(none)]> show variables like '%port';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| extra_port | 0 |
| large_files_support | ON |
| port | 3308 |
| report_port | 3308 |
+---------------------+-------+
4 rows in set (0.00 sec)

[root@centos7 ~]# /mysqldb/3306/bin/mysqld stop

[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock 
Server version: 10.2.15-MariaDB-log Source distribution

MariaDB [(none)]> update mysql.user set password=PASSWORD("your_password") where user='root';
Query OK, 4 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *9E72259BA9214F692A85B240647C4D95B0F2E08B |
| root | centos7 | *9E72259BA9214F692A85B240647C4D95B0F2E08B |
| root | 127.0.0.1 | *9E72259BA9214F692A85B240647C4D95B0F2E08B |
| root | ::1 | *9E72259BA9214F692A85B240647C4D95B0F2E08B |
| | localhost | |
| | centos7 | |
+------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)

[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock -uroot -p'your_password' #Specify the password and log in again OK~