MySQL database introduction: detailed explanation of multi-instance configuration method

1. What is multi-instance
2. Preparation for installing multiple instances
3. Install MYSQL multiple instances
4. Start multiple instances and log in

This article uses examples to explain how to configure multiple instances of the MySQL database. Share with you for your reference, the details are as follows:

The related basic command operations are introduced above: MySQL Database Basics: Getting Started with Basic Commands

All operations are based on a single instance. MySQL multiple instances are also very practical in actual production environments because they must be mastered.

1. What is multi-instance

Multiple instances means opening multiple different service ports (the default is 3306) on a server and running multiple MySQL service processes. These service processes provide their own services by listening to different service ports through different sockets. All instances share a set of MYSQL installation programs, but each uses different configuration files, startup programs, and data files, and are logically relatively independent.

The main function of multiple instances is to make full use of existing server hardware resources to provide data services for different services. However, if the concurrency of an instance is relatively high, it will also affect the performance of other instances.

2. Preparation for installing multiple instances

You need to install MySQL before installation, but you only need to proceed to make install (compile and install). If you use the free installation program, you only need to unzip the software package. Today's environment is to install the MySQL main program through the free installation package (other installations can refer to the previous installation process for self-test)

System environment

[root@centos6 ~]# cat /etc/redhat-release 
CentOS release 6.5 (Final)
[root@centos6 ~]# uname -r
2.6.32-431.el6.x86_64

Installer

mysql-5.5.52-linux2.6-x86_64.tar.gz

First download the software to your local computer

wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.52-linux2.6-x86_64.tar.gz

Create an installation user

[root@centos6 ~]#groupadd mysql
[root@centos6 ~]#useradd mysql -s /sbin/nologin -g mysql -M
[root@centos6 ~]#tail -1 /etc/passwd
mysql:x:500:500::/home/mysql:/sbin/nologin

Create a data directory for multiple instances

[root@centos6 tools]# mkdir -p /data/{3306,3307}
[root@centos6 tools]# tree /data/
/data/
+-- 3306
+-- 3307
2 directories, 0 files

3. Install MYSQL multiple instances

Next, install multiple instances of MySQL.

Decompression software

[root@centos6 tools]# ll mysql-5.5.52-linux2.6-x86_64.tar.gz 
-rw-r--r--. 1 root root 185855000 Aug 26 21:38 mysql-5.5.52-linux2.6-x86_64.tar.gz
[root@centos6 tools]# tar zxf mysql-5.5.52-linux2.6-x86_64.tar.gz

Copy the configuration file

[root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/my-small.cnf /data/3306/my.cnf

[root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/mysql.server /data/3306/mysql

[root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/my-small.cnf /data/3307/my.cnf

[root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/mysql.server /data/3307/mysql

For a standard installation path, copy the free installation package to the application directory

[root@centos6 tools]# mv mysql-5.5.52-linux2.6-x86_64 /application/mysql

[root@centos6 tools]# ll /application/mysql
total 72
drwxr-xr-x. 2 root root 4096 Dec 9 17:15 bin
-rw-r--r--. 1 7161 31415 17987 Aug 26 19:24 COPYING
drwxr-xr-x. 3 root root 4096 Dec 9 17:15 data
drwxr-xr-x. 2 root root 4096 Dec 9 17:15 docs
drwxr-xr-x. 3 root root 4096 Dec 9 17:15 include
-rw-r--r--. 1 7161 31415 301 Aug 26 19:24 INSTALL-BINARY
drwxr-xr-x. 3 root root 4096 Dec 9 17:15 lib
drwxr-xr-x. 4 root root 4096 Dec 9 17:15 man
drwxr-xr-x. 10 root root 4096 Dec 9 17:15 mysql-test
-rw-r--r--. 1 7161 31415 2496 Aug 26 19:24 README
drwxr-xr-x. 2 root root 4096 Dec 9 17:15 scripts
drwxr-xr-x. 27 root root 4096 Dec 9 17:15 share
drwxr-xr-x. 4 root root 4096 Dec 9 17:15 sql-bench
drwxr-xr-x. 2 root root 4096 Dec 9 17:15 support-files

Modify the configuration file and startup file

Because it is a multi-instance, the parameters need to be modified. The modified configuration file is as follows: Configuration file my.cnf

[client]
port = 3307
socket = /data/3307/mysql.sock

[mysql]
no-auto-rehash

[mysqld] user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /application/mysql
datadir = /data/3307/data
#log_long_format
#log-error = /data/3307/error.log
#log-slow-queries = /data/3307/slow.log
pid-file = /data/3307/mysql.pid
server-id = 3 

[mysqld_safe]
log-error=/data/3307/mysql3307.err
pid-file=/data/3307/mysqld.pid

Start the program file mysql

[root@backup 3307]# cat mysql
#!/bin/sh
init port=3307
mysql_user="root"
mysql_pwd="migongge"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup
function_start_mysql() {
if [ ! -e "$mysql_sock" ];then
 printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
 printf "MySQL is running...\n"
exit
fi
}
#stop function
function_stop_mysql() {
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql() {
 printf "Restarting MySQL...\n"
 function_stop_mysql
 sleep 2
 function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

For other configurations, please refer to the configuration file for modification.

Multiple instance initialization operations

[root@centos6 3306]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
Installing MySQL system tables...
161209 18:02:17 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
161209 18:02:17 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52-log) started as process 3336 ...
OK
Filling help tables...
161209 18:02:17 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
161209 18:02:17 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52-log) started as process 3343 ...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/application/mysql/bin/mysqladmin -u root password 'new-password'
/application/mysql/bin/mysqladmin -u root -h centos6 password 'new-password'
Alternatively you can run:
/application/mysql/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
It is strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /application/mysql ; /application/mysql/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /application/mysql/mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/

After successful initialization, a data directory data and some files will be generated in the data directory

[root@centos6 3306]# ll /data/3306/data/
total 1136
drwx------. 2 mysql root 4096 Dec 9 18:02 mysql
-rw-rw----. 1 mysql mysql 27693 Dec 9 18:02 mysql-bin.000001
-rw-rw----. 1 mysql mysql 1114546 Dec 9 18:02 mysql-bin.000002
-rw-rw----. 1 mysql mysql 38 Dec 9 18:02 mysql-bin.index
drwx------. 2 mysql mysql 4096 Dec 9 18:02 performance_schema
drwx------. 2 mysql root 4096 Dec 9 18:02 test

Please refer to the above operation to initialize another instance. The operation process will not be introduced one by one.

[root@centos6 3307]# ll /data/3307/data/
total 1136
drwx------. 2 mysql root 4096 Dec 9 18:40 mysql
-rw-rw----. 1 mysql mysql 27693 Dec 9 18:40 mysql-bin.000001
-rw-rw----. 1 mysql mysql 1114546 Dec 9 18:40 mysql-bin.000002
-rw-rw----. 1 mysql mysql 38 Dec 9 18:40 mysql-bin.index
drwx------. 2 mysql mysql 4096 Dec 9 18:40 performance_schema
drwx------. 2 mysql root 4096 Dec 9 18:40 test

4. Start multiple instances and log in

Start the service

[root@backup 3307]# /data/3306/mysql start
Starting MySQL...
[root@backup 3307]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 19986 mysql 10u IPv4 90967 0t0 TCP *:mysql (LISTEN)
[root@backup 3307]# /data/3307/mysql
start Starting MySQL...
[root@backup 3307]# lsof -i :3307
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 21648 mysql 11u IPv4 92899 0t0 TCP *:opsession-prxy (LISTEN)

Check the port

[root@backup 3307]# netstat -lntup|grep mysql
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 21648/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 19986/mysqld

Log in to a multi-instance database

[root@backup ~]# mysql -S /data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.51-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database data3306;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|data3306|
|mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> quit
Bye

[root@backup ~]# mysql -S /data/3307/mysql.sock
Welcome to the MySQL monitor.
Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.51 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.05 sec)

Successfully logged in and created a database in the 3306 instance, but no data was created on the 3307 instance, indicating that the two instances are independent

Note: If you need to add another instance, the basic configuration steps are the same as above. You only need to modify the port number and data directory path in the configuration file and startup program file accordingly. Finally, you can add the multi-instance database startup command to the boot-up automatic startup.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

In-depth analysis based on MySQL multi-instance installation
MySQL multi-instance configuration solution
MySQL tutorial on how to deploy multiple instances on a single machine using mysqld_multi
MySQL multi-instance installation boot auto-start service configuration process

<<: Summary of 10 common HBase operation and maintenance tools

>>: Understanding and application scenarios of enumeration types in TypeScript

How to configure Linux CentOS to run scripts regularly

MySQL database introduction: detailed explanation of multi-instance configuration method

Table of contents

1. What is multi-instance

2. Preparation for installing multiple instances

3. Install MYSQL multiple instances

4. Start multiple instances and log in

How to configure Linux CentOS to run scripts regularly

Explain TypeScript mapped types and better literal type inference

How to use firewall iptables strategy to forward ports on Linux servers

How to implement responsiveness in Vue source code learning

Detailed explanation of the pitfalls of add_header in nginx configuration tutorial

Pure CSS to solve the ceiling and bottom suction in H5 layout implementation steps

Summary of the benefits of deploying MySQL delayed slaves

Detailed steps for remote deployment of MySQL database on Linux

HTML Tutorial: HTML horizontal line segment

HTML table tag tutorial (20): row background color attribute BGCOLOR

Recommend

WeChat applet example of using functions directly in {{ }}

Detailed explanation of custom events of Vue components

How to query whether the mysql table is locked

Detailed explanation of Mysql transaction isolation level read commit

How to do a good refactoring is not only refactoring the code but also refactoring life

Summary of the characteristics of SQL mode in MySQL

Detailed explanation of Vue's props configuration

jQuery implements the function of adding and deleting employee information

MySQL table name case selection

Summary of methods for creating, listing, and deleting Docker containers on Linux

HTML page header code is completely clear

Detailed tutorial for installing the unzipped version of mysql5.7.28 winx64 on windows

Detailed explanation of MySQL/Java server support for emoji and problem solving

Docker containers communicate directly through routing to achieve network communication

jQuery plugin to implement minesweeper game (1)