Centos7 configuring MySQL multiple instances

Centos7 To configure MySQL Multiple instances

This article takes MySQL5.7 As a demonstration , Different versions , There may be subtle differences .

1, download MySQL 5.7 Two systems package

website ：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

Click to download

decompression MySQL 5.7 Binary package to the specified directory （ Be careful ： Remember to extract the directory , Avoid subsequent path errors ）

xftp Upload to this path and decompress

cd /usr/local

tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

2, establish MySQL Soft link

ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql

3, establish MySQL user

useradd -r -s /sbin/nologin zzj

Be careful ：zzj User name created for yourself , It can be changed according to the demand

stay MySQL Binary package directory mysql-files Catalog 【 be used for MySQL Data import and export ) 】

mkdir -v /usr/local/mysql/mysql-files

4, Create a multi instance data directory

mkdir -vp /data/mysql_data{1…2}

modify MySQL The user and group of the binary package directory

chown root.zzj -R /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64

modify MySQL Multi instance data directory and Data import / Export the user and group of the dedicated directory

chown root.zzj -R /usr/local/mysql/mysql-files /data/mysql_data{1…2}

5, To configure MySQL The 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]
datadir = /data/mysql_data1
socket = /tmp/mysql.sock1
port = 3306
user = zzj
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

[mysqld2]
datadir = /data/mysql_data2
socket = /tmp/mysql.sock2
port = 3307
user = zzj
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

vi /etc/my.cnf

Be careful ： Save and check the directory after exiting ; As shown in the figure, it is normal

6, Initialize each instance

/usr/local/mysql/bin/mysqld --initialize --user=zzj --basedir=/usr/local/mysql --datadir=/data/mysql_data1

Be careful ： After initialization, it will bring its own random password in the output log , If initialization fails , Log , Check whether there are path errors or permissions .

7, Each instance opens SSL Connect

/usr/local/mysql/bin/mysql_ssl_rsa_setup --user=zzj --basedir=/usr/local/mysql --datadir=/data/mysql_data1

8, Copy the multi instance script to the service management directory

Be careful ： Check whether each directory exists before copying
cd /usr/local/mysql/support-files/

cd /etc/init.d

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

9, Add script execution permissions

chmod +x /etc/init.d/mysqld_multi
Added to the service Service management
chkconfig --add mysqld_multi

10, Start the test

/etc/init.d/mysqld_multi report

yum -y install perl （ If the above error is prompted, proceed to this step ）

If after installation , The execution prompts the following error , You need to add environment variables

Add environment variables
vi /etc/profile

Make the modified profile effective ：source /etc/profile

Start multiple instances
/etc/init.d/mysqld_multi start

If the above command is still not started , Then check the log

Connect the test
/usr/local/mysql/bin/mysql -S /tmp/mysql.sock1 -p‘xxx’

Change Password

11, to grant authorization

send user You can connect to... From any host mysql The server , Execute the following two commands .

12, Test remote connection database

If you cannot connect, check whether the firewall is turned on

running It means on , Then turn off the firewall
systemctl stop firewalld.service
systemctl status firewalld.service