How to install multiple MySQL instances on the same server

MySQL is an open source relational database management system that is widely used in data management of various applications. In actual use, it is sometimes necessary to install multiple MySQL instances at the same time to support different applications or scenarios. This article will introduce how to install multiple MySQL instances on the same server.

1. Preparations before installation

Before installing multiple MySQL instances, you need to carry out the following preparations:

1. Prepare multiple MySQL installation packages , you can download the version suitable for your own environment from the MySQL official website: https://dev.mysql.com/downloads/
2. Prepare multiple MySQL data directories, and you can create different directories to support different MySQL Instances, such as: /data/mysql1, /data/mysql2, etc.
3. For each MySQL instance, configure an independent MySQL user, which has the corresponding MySQL installation path and data directory permissions.

2. Install multiple MySQL instances based on binary packages

The following are the steps to install multiple MySQL instances based on binary packages:

1. Unzip MySQL Install the package and rename it

After decompressing multiple MySQL installation packages, you can rename them to different names for easy differentiation, such as: mysql1, mysql2, etc.

$ tar -zxvf mysql-8.0.22-linux-glibc2.12-x86_64.tar.gz
$ mv mysql-8.0.22-linux-glibc2.12-x86_64 mysql1
$ tar -zxvf mysql-8.0.22-linux-glibc2.12-x86_64.tar.gz
$ mv mysql-8.0.22-linux-glibc2.12-x86_64 mysql2

2. Initialization data Directory

Each MySQL instance requires an independent data directory, which needs to be initialized using the mysql_install_db command. The following are the commands to initialize mysql1 and mysql2 instances:

$ cd mysql1
$ bin/mysqld --initialize --user=mysql --basedir=/share/opt/mysql1 --datadir=/data /mysql1
$ cd ../mysql2
$ bin/mysqld --initialize --user=mysql --basedir=/share/opt/mysql2 --datadir=/data/mysql2

3. Install MySQL service

For each MySQL instance, you need to use the mysqld_safe command to install the MySQL service. The following is the command to install mysql1 and mysql2 instances:

$ cp support-files /mysql.server /etc/init.d/mysql1
$ cp support-files/mysql.server /etc/init.d/mysql2
$ chmod x /etc/init.d/mysql1
$ chmod x /etc/init.d/mysql2
$ chkconfig --add mysql1
$ chkconfig --add mysql2
$ service mysql1 start
$ service mysql2 start

4. Configure MySQL instance

For each MySQL instance, an independent configuration file my.cnf is required. You can create a my.cnf file in the MySQL installation directory. The following is an example of the my.cnf file. ：

[mysqld]
datadir=/data/mysql1
socket=/share/tmp/mysql1.sock
port = 3306
user=mysql
log-error =/data/mysql1/mysql.err
pid-file=/share/tmp/mysql1.pid

The following is the configuration of the mysql2 instance

[mysqld2]
datadir= /data/mysql2
socket=/share/tmp/mysql2.sock
port = 3307
user=mysql
log-error=/data/mysql2/mysql.err
pid-file =/share/tmp/mysql2.pid

5. Configure MySQL user

Use the following command to create a MySQL user and grant the corresponding directory permissions:

$ useradd -r -g mysql -s /bin/false mysql1
$ useradd -r -g mysql -s /bin/false mysql2
$ chown -R mysql:mysql /data/mysql1
$ chown - R mysql:mysql /data/mysql2
$ chown -R mysql:mysql /share/opt/mysql1
$ chown -R mysql:mysql /share/opt/mysql2

3. Based on Docker Container installation of multiple MySQL instances

1. Prepare the Docker environment

You need to install the Docker environment on the server first and pull the Docker image of MySQL. The following is to pull the latest version MySQL mirroring command:

$ docker pull mysql/mysql-server:latest

2. Start the MySQL container

You can start multiple MySQL containers To meet the needs of multiple instances, different instances can be distinguished by specifying different ports, data volumes, and configuration files. The following is an example command to start two different MySQL instances:

$ docker run --name=mysql1 -d \
-e MYSQL_ROOT_PASSWORD=your_password \
-p 3306:3306 \
-v /data/mysql1:/var/lib/mysql \
-v /share/opt/mysql1:/etc/mysql \
mysql/mysql-server:latest
$ docker run -- name=mysql2 -d \
-e MYSQL_ROOT_PASSWORD=your_password \
-p 3307:3306 \
-v /data/mysql2:/var/lib/mysql \
-v /share/opt /mysql2:/etc/mysql \
mysql/mysql-server:latest

3. Configuring MySQL instance

You can create different instances in different data volumes Configuration file my.cnf, and then mount it to different containers. The following is an example of my.cnf:

The following is the configuration of the mysql1 instance

[mysqld]
datadir = /var/lib/mysql
socket = /var/run/ mysqld/mysqld.sock

Note: This setting only takes effect for MySQL 5.7 or earlier versions

symbolic-links = 0

Customized MySQL configuration

[mysqld]
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
user = mysql
log-error = / var/lib/mysql/mysql.err
pid-file = /var/run/mysqld/mysqld.pid

The following is the configuration of the mysql2 instance

[mysqld2]
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
symbolic-links = 0

Customized MySQL configuration

[mysqld2]
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
port = 3307
user = mysql
log-error = /var/lib/mysql/mysql.err
pid-file = /var/run/mysqld/mysqld.pid

4. Use multiple A MySQL instance

After configuring multiple MySQL instances, you can use multiple MySQL instances by connecting to the corresponding port, using the corresponding database instance name and configuring the corresponding user:

mysql -h localhost -u username1 -p -P 3306 -D database1
mysql -h localhost -u username2 -p -P 3307 -D database2

Summary

Introduction to this article Describes how to install multiple MySQL instances on the same server, including installation based on binary packages and installation based on Docker containers. By rationally using multiple MySQL instances, different applications and scenarios can be better supported, and the performance and reliability of the database can be improved.

The above is the detailed content of How to install multiple MySQL instances on the same server. For more information, please follow other related articles on the PHP Chinese website!