MySQL multi-instance installation deployment

Unzip the MySQL 5.7 binary package to the specified directory
[Email protected] ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz-c/usr/local/

Create a MySQL soft link
[[Email protected] ~] #mv/usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/usr/local/mysql

Create a MySQL user
[[Email protected] ~] #groupadd-R MySQL
[[email protected] ~]# useradd-r-g mysql-s/bin/false-m MySQL

Create the Mysql-files directory in the MySQL binary package directory [MySQL data import/Export data exclusive directory]
[Email protected] ~]# mkdir-v/usr/local/mysql/mysql-files
mkdir:created directory '/usr/local/mysql/mysql-files '

Create a multi-instance data Catalog
[[email protected] ~]# mkdir-vp/data/mysql_data{1..4}
mkdir:created directory /data‘ <br/>mkdir: created directory /data/mysql_data1 '
mkdir:created directory /data/mysql_data2‘ <br/>mkdir: created directory /data/mysql_data3 '
mkdir:created directory '/data/mysql_data4 '

To modify the user and owning group of the MySQL binary package directory
1 [[Email protected] ~]# chown root.mysql-r/usr/local/mysql

Modify MySQL Multi-instance data directory with data import/export exclusive directory for the owning user and owning group
[Email protected] ~]# chown mysql.mysql-r/usr/local/mysql/mysql-files/data/mysql_data{1..4}

Configuring the MySQL configuration file/etc/my.cnf
[Mysqld_multi]
Mysqld =/usr/local/mysql/bin/mysqld
Mysqladmin =/usr/local/mysql/bin/mysqladmin
Log =/tmp/mysql_multi.log

[Mysqld1]
# Set Data directory [must be different in multiple instances]
DataDir =/data/mysql_data1
# set sock store file name [must be different in multiple instances]
Socket =/tmp/mysql.sock1
# Set listening open ports [must be different in multiple instances]
Port = 3306
# Set up run user
user = MySQL
# Turn off monitoring
Performance_schema = Off
# set InnoDB Cache size
Innodb_buffer_pool_size = 32M
# Set the Listening IP address
Bind_address = 0.0.0.0
# Turn off DNS reverse resolution
Skip-name-resolve = 0 * *

[Mysqld2]
DataDir =/data/mysql_data2
Socket =/tmp/mysql.sock2
Port = 3307
user = MySQL
Performance_schema = Off
Innodb_buffer_pool_size = 32M
Bind_address = 0.0.0.0
Skip-name-resolve = 0

[MYSQLD3]
DataDir =/data/mysql_data3
Socket =/tmp/mysql.sock3
Port = 3308
user = MySQL
Performance_schema = Off
Innodb_buffer_pool_size = 32M
Bind_address = 0.0.0.0
Skip-name-resolve = 0

[Mysqld4]
DataDir =/data/mysql_data4
Socket =/tmp/mysql.sock4
Port = 3309
user = MySQL
Performance_schema = Off
Innodb_buffer_pool_size = 32M
Bind_address = 0.0.0.0
Skip-name-resolve = 0

• Initialize each instance [after initialization completes with a random password in the output log]
  [Email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data1
  [Email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data2
  [Email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data3
  [Email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data4

• Each instance turns on SSL connection
  [Email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data1
  [Email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data2
  [Email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data3
  [Email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data4

* Modify Environment variables
echo "Export path= $PATH:/usr/local/mysql/bin" >>/etc/profile
Source/etc/profile

• Copy the multi-instance script to the service Management directory [/etc/init.d/]
  [Email protected] ~]# Cp/usr/local/mysql/support-files/mysqld_multi.server/etc/init.d/mysqld_multi

• Add Script Execution permissions
  [Email protected] ~]# chmod +x/etc/init.d/mysqld_multi

• Add in service Management
  [Email protected] ~]# chkconfig--add mysqld_multi

5. Start test
   • Check for multiple instance status
     [Email protected] ~]#/etc/init.d/mysqld_multi report
     Reporting MySQL Servers
     MySQL server from Group:mysqld1 are not running
     MySQL server from Group:mysqld2 are not running
     MySQL server from GROUP:MYSQLD3 are not running
     MySQL server from GROUP:MYSQLD4 are not running

• Start Multi-instance
  [[email protected] ~]#/etc/init.d/mysqld_multi start

• View multi-instance status
  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

• viewing instance listening ports
  [Email protected] ~]# NETSTAT-LNTP | grep mysqld
  TCP 0 0 0.0.0.0:3306 0.0.0.0: LISTEN 2673/mysqld
  TCP 0 0 0.0.0.0:3307 0.0.0.0: LISTEN 2676/mysqld
  TCP 0 0 0.0.0.0:3308 0.0.0.0: LISTEN 2679/mysqld
  TCP 0 0 0.0.0.0:3309 0.0.0.0: LISTEN 2682/mysqld

6. Connection test

MySQL multi-instance installation deployment