MySQL multi-instance configuration application scenario

MySQL multiple instances

Multi-Instance Overview

What is multi-instance?
Why configure multiple instances?
Application scenarios of multiple instances
Configuring multiple instances

MySQL multiple instances

Multi-Instance Overview

What is multi-instance?

On one server, multiple database services are running. These service processes provide their own services by listening to different service ports through different sockets.

Why configure multiple instances?

Save operation and maintenance costs
Improve hardware utilization

Application scenarios of multiple instances

Companies with tight funds;
Concurrent access is not a particularly large business;

Configuring multiple instances

1. Install the software

Specific operations:

Unzip the software, modify the directory name, and modify the PATH path

If you are running multiple instances on a machine with an existing database service, first check whether the database service version supports multiple instances. If not, you need to stop the service and install a database version that supports multiple instances.

Next, install the software that provides multi-instance services

The version demonstrated below is MySQL 5.7.36, which is installed using binary. For other installation methods, see MySQL installation methods.

groupadd -r -g 306 mysql 
useradd -g 306 -r -u 306 mysql
tar -zxvf mysql-5.7.36-linux-glibc2.12-x86-64.tar.gz 
mv /root/mysql-5.7.36-linux-glibc2.12-x86_64 /usr/local/mysql

2. Configuration File

Main configuration file /etc/my.cnf

Each instance must have independent: database directory, port
socket file, pid file, error log file

vim /etc/my.cnf
[mysqld_multi] #Start multiple instancesmysqld = /usr/local/mysql/bin/mysqld_safe #Specify the process file pathmysqladmin = /usr/local/mysql/bin/mysqladmin #Specify the management command pathuser = root #Specify the process user#pass = MySQL@123 #You don't need to specify a password. It will appear during initialization[mysqld3306] #Instance process name, 3306 is the service numbersocket = /tmp/mysql.sock1 #Specify the path and name of the sock fileport = 3306 #Specify the portpid-file = /data/3306/mysqld.pid #Process file pid number file by locationdatadir = /data/3306 #Database directory, it is best to create it in advancelog-error = /data/3306/mysqld.log #Error log location[mysqld3307]
socket = /tmp/mysql.sock2
port = 3307
pid-file = /data/3307/mysqld.pid
datadir = /data/3307
log-error = /data/3307/mysqld.log

mkdir -p /data/330{6..7}
chown -R mysql:mysql /data/330{6,7}

Socket file: When you access your own database service, you pass parameters through the socket file

3. Start the service

Managing multiple instances

Start the service

mysqld_multi start instance number#Start the service

Stop service

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

/usr/local/mysql/bin/mysqld_multi start 3306 #First start initialization #You can also use this command to initialize /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307
#After initializing with this command, you still need to start the service /usr/local/mysql/bin/mysqld_multi start 3307
Entering the absolute path is too troublesome, you can write it into the environment variable vim /etc/
export PATH=$PATH:/usr/local/mysql/bin/

source /etc/profile.d/mysql.sh
#Then you can write the short command mysqld_multi start 3307

insert image description here

4. Check service status

ss -nultp | grep 3306
ss -nultp | grep 3307

insert image description here

5. Use the initial password to connect to the service

Client Access

Local connection

Connect using the initial password

Change the local login password

#mysql -uroot -p'initial password' -S sock file#mysql>alter user root@"localhost" identified by "new password";
#Connect to the first database instance test /usr/local/mysql/bin/mysql -uroot -p'' -S /tmp/mysql.sock1
show databases;
alter user root@"localhost" identified by "123456";
show databases;
exit
#Connect to the second database instance to test mysql -uroot -p'' -S /tmp/mysql.sock2
show databases;
alter user root@"localhost" identified by "123456";
show databases;
exit

insert image description here

6. Stop multiple instances

mysqld_multi --user=root --password=123456 stop 3306
#mysqld_multi --user=root --password=123456 stop If you do not write the instance number, the instance with the database user root and password 123456 will be stopped ss -nultp | grep 3306

insert image description here

This is the end of this article about the configuration and application scenarios of MySQL multi-instances. For more relevant MySQL multi-instance content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

MySQL multi-instance installation boot auto-start service configuration process
MySQL multi-instance deployment and installation guide under Linux
MySQL Series II Multi-Instance Configuration
MySQL database introduction: detailed explanation of multi-instance configuration method
MySQL multi-instance configuration solution

<<: Keep-alive multi-level routing cache problem in Vue

>>: Circular progress bar implemented with CSS