CENTOS7 installing MySQL 5.7 multi-instance

One, MySQL multi-instance is a server running multiple MySQL service processes, open different service ports, through different sockets to listen to different service ports to provide their own services.

Two, MySQL multiple examples have the following several characteristics:

1, the effective use of server resources: through multi-instance configuration, the server can fully utilize the remaining resources.

2, the resource preemption problem: The problem of resource preemption, when a service instance service is high concurrency or slow query, will consume more memory, CPU, disk IO resources, resulting in other instances of the server service quality degradation.

3, save resources.

Three, pre-installation preparation

1. Server version: CentOS 7

2, the/etc/selinux/config in the SELinux set to: Disabled

3. Download: mysqlhttps://cdn.mysql.com//downloads/mysql-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz

Four, installation and configuration MySQL multi-example

1. Unzip MySQL

A, decompression mysq:tar-zxvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz

B, mobile Mysql:sudo MV Mysql-5.7.19-linux-glibc2.12-x86_64/usr/local/mysql

2. Create MySQL instance users and files

A, sudo useradd mysql-r-s/sbin/nologin

B, sudo mkdir-p/usr/local/mysql/mysql_files//Data Import Export directory

C. Create multi-instance Data directory: sudo mkdir-p/data/mysql_data{1..3}

D, modify the MySQL directory of the genus and users: sudo chown root.mysql-r/usr/local/mysql

E, modify the owner and genus of the dedicated directory: sudo chown mysql.mysql-r/usr/local/mysql/mysql_files/data/mysql_data{1..3}

3, configure the MySQL configuration file/etc/my.cnf, the content is as follows:

[Mysqld_multi]

Mysqld =/usr/local/mysql/bin/mysqld

Mysqladmin =/usr/local/mysql/bin/mysqladmin

Log =/tmp/mysql_multi.log

[Mysqld1]

DataDir =/DATA/MYSQL_DATA1//Set Data directory

Socket =/TMP/MYSQL.SOCK1//Set sock file storage path

Port = 3306//Set listening open port

user = MySQL//set up running users

Performance_schema = off//off monitoring

Innodb_buffer_pool_size = 32M//set InnoDB cache size

Bind_address = 0.0.0.0//Setting the Listening IP address

Skip-name-resolve = 0//Turn off DNS reverse resolution

[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

4, initialization of each instance: After initialization will be generated in the log password, remember to save, a will be used.

A, sudo/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data1

B,/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data2

C,/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data3

　　

5. Turn on SSL connection for each instance

A, Sudo/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_ Data1

B, Sudo/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_ Data2

C, Sudo/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_ Data3

　　

6. Copy the multi-instance script to the service management directory

A, sudo cp/usr/local/mysql/support-files/mysqld_multi.server/etc/init.d/mysqld_multi

7. Give script permission to execute: sudo chmod +x/etc/init.d/mysqld_multi

8. Join service Management: sudo chkconfig–add mysqld_multi

9. Add Environment variables

A. Add content to the last side of the file: sudo vim/etc/profile

# # SETUP MYSQL ' S PATH

Export Mysql_home=/usr/local/mysql

Export Path=${mysql_home}/bin: $PATH

B. Make the modified configuration file effective: Source/etc/profile

Five, test example

1. View multiple instance states: Mysqld_multi Report

2. Start each instance: sudo mysqld_multi start

3. View the listening port of the instance: sudo ss-tulpn|grep mysqld

4. Connection Example 1:

A, sudo mysql-s/tmp/mysql.sock1-p ' akgl?lkjl8l2 '

b, go in and change the root password: set password=123456;

C, make the amendment effective: flush privileges;

5, the other two instances of the operation method and example 1 are consistent, no longer repeat.

PS : This article is referenced from http://www.linuxidc.com/Linux/2017-07/145343.htm

CENTOS7 installing MySQL 5.7 multi-instance