MySQL multi-instance configuration what is the application instance scenario?

What this article shares with you is what the application instance scenario of MySQL multi-instance configuration is like. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

MySQL multi-instance overview of what is multi-instance:

Run multiple database services on a server, and these service processes provide their own services by listening to different service ports through different socket

Why configure multiple instances

Save operation and maintenance costs

Improve hardware utilization

Multi-instance application scenario

Companies that are cash-strapped

Concurrent access is not a very big business.

Configure multiple instances

1. Install softwar

Specific operations:

Extract the software, change the directory name, modify the PATH path

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

Next, install the software that provides multi-instance services

The version demonstrated below is MySQL5.7.36, which uses binary installation. For the rest of the installation, please see the installation method of MySQL.

Groupadd-r-g 306 mysql useradd-g 306-r-u 306 mysqltar-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

two。 Configuration file

Main configuration file / etc/my.cnf

Each instance should be independent: database directory, port

Socket file, pid file, error log file

Vim / etc/ my.cnf [mysqld _ multi] # start multi-instance mysqld = / usr/local/mysql/bin/mysqld_safe # specify process file path mysqladmin = / usr/local/mysql/bin/mysqladmin # specify administrative command path user = root # specify process user # pass = MySQL@123 # can not specify a password, and the password [mysqld3306] # instance process name will also appear during initialization 3306 is the service number socket = / tmp/mysql.sock1 # specifies the path and name of the sock file port = 3306 # designated port pid-file = / data/3306/mysqld.pid # process file pid number file by location datadir = / data/3306 # database directory It is best to create log-error = / data/3306/mysqld.log # error log location [mysqld3307] socket = / tmp/mysql.sock2port = 3307pid-file = / data/3307/mysqld.piddatadir = / data/3307log-error = / data/3307/mysqld.logmkdir-p / data/330 {6.. 7} chown-R mysql:mysql / data/330 {6. 7}

Socket file: pass parameters through socket file when you access your own database service

3. Start the service

Manage multiple instances

Start the service

Mysqld_multi start instance # start the service

Out of Service

Mysqld_multi-- user=root-- password= password stop instance number

/ usr/local/mysql/bin/mysqld_multi start 3306 # initialization for the first time # you can also use this command to initialize / usr/local/mysql/bin/mysqld-- initialize-- user=mysql-- basedir=/usr/local/mysql-- datadir=/data/3307# after initialization with this command, you still need to enter an absolute path using the startup service / usr/local/mysql/bin/mysqld_multi start 3307. You can write the environment variable vim / etc/export PATH=$PATH:/usr/local/mysql/bin/source / etc/profile.d/mysql.sh# and then you can write the short instruction mysqld_multi start 3307

4. View service status

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

5. Connect to the service using the initial password

Client access

Local connection

Connect using the initial password

Modify the local login password

# mysql-uroot-p 'initial password'-S sock file # mysql > alter user root@ "localhost" identified by "New password"; # connecting to the first database instance test / usr/local/mysql/bin/mysql-uroot-pairing'- S / tmp/mysql.sock1show databases;alter user root@ "localhost" identified by "123456"; show databases;exit# connecting to the second database instance testing mysql-uroot-pairing'- S / tmp/mysql.sock2show databases Alter user root@ "localhost" identified by "123456"; show databases;exit

Log in and verify again with the new password

6. Stop multiple instances

Mysqld_multi-- user=root-- password=123456 stop 3306#mysqld_multi-- user=root-- password=123456 stop will stop the instance ss-nultp where the database user has a root password of 123456 if the instance number is not written. | grep 3306

The above is what the MySQL multi-instance configuration application instance scenario looks like. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.