SoFunction
Updated on 2025-04-14

Detailed explanation of configuration examples for installing multiple mysql databases under Centos

In actual development and operation and maintenance scenarios, sometimes we need to run multiple MySQL database instances on the same server. This article will explain in detail how to install and configure multiple MySQL database instances in a CentOS system.

1. Environmental preparation

Operating system: CentOS

MySQL version: 5.

2. Install MySQL

First, make sure your system has MySQL installed. If not installed, you can use the following command to install:

sudo yum update -y
sudo yum install -y mysql-server

After the installation is complete, start the MySQL service and set up the power-on self-start:

sudo systemctl start mysqld
sudo systemctl enable mysqld

3. Create multiple MySQL instances

3.1 Create a directory structure

For management convenience, we create independent data and log directories for each MySQL instance. Suppose we want to create two instances: mysql1​ and ​​mysql2​.

sudo mkdir -p /var/lib/mysql1
sudo mkdir -p /var/lib/mysql2
sudo chown -R mysql:mysql /var/lib/mysql1
sudo chown -R mysql:mysql /var/lib/mysql2

3.2 Initialize the data directory

Use the mysqld command to initialize the data directory for each instance:

sudo mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql1
sudo mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql2

3.3 Configuration File

Create an independent configuration file for each instance. For example, /etc//​​ and /etc//​​.

[mysqld]
datadir=/var/lib/mysql1
socket=/var/lib/mysql1/
port=3306
server-id=1

[mysqld]
datadir=/var/lib/mysql2
socket=/var/lib/mysql2/
port=3307
server-id=2

3.4 Create system service files

Create an independent systemd service file for each instance. For example, /etc/systemd/system/[email protected]​​.

[Unit]
Description=MySQL Server Instance %i
After=
 
[Service]
User=mysql
Group=mysql
ExecStart=/usr/sbin/mysqld --defaults-file=/etc//mysql% --datadir=/var/lib/mysql%i
Restart=on-failure
PrivateTmp=true
 
[Install]
WantedBy=

3.5 Reload systemd configuration

sudo systemctl daemon-reload

3.6 Start and enable the service

sudo systemctl start mysqld@1
sudo systemctl start mysqld@2
sudo systemctl enable mysqld@1
sudo systemctl enable mysqld@2

4. Verify the installation

You can verify that the two instances are running normally through the following command:

sudo systemctl status mysqld@1
sudo systemctl status mysqld@2

You can also connect to these two instances through the MySQL client:

mysql -u root -p -S /var/lib/mysql1/
mysql -u root -p -S /var/lib/mysql2/

5. Things to note

  • Make sure that the ports of each instance do not conflict.
  • Make sure that the configuration file path for each instance is correct.
  • If remote access is required, make sure the firewall rules allow the corresponding ports.

6. Method supplement

This is useful for multi-tenant scenarios in test, development, or production environments. Installing and configuring multiple MySQL DB instances in CentOS is a relatively complex process, but this can be achieved by planning configuration files and ports reasonably. The following is a specific step and sample code for installing two MySQL instances (for example: mysqld1​​ and ​​mysqld2​) on CentOS.

Step 1: Install MySQL

First, make sure your system has MySQL installed. If not installed, you can install it through the following command:

sudo yum install -y mysql-server

Step 2: Create a data directory

Create a separate data directory for each MySQL instance and set the correct permissions:

sudo mkdir -p /var/lib/mysql1 /var/lib/mysql2
sudo chown -R mysql:mysql /var/lib/mysql1 /var/lib/mysql2

Step 3: Configure MySQL instance

Create a separate configuration file for each MySQL instance. Suppose we will use /etc//​ and /etc//​​.

[mysqld1]
datadir=/var/lib/mysql1
socket=/var/lib/mysql1/
port=3306
pid-file=/var/run/mysqld/

[mysqld2]
datadir=/var/lib/mysql2
socket=/var/lib/mysql2/
port=3307
pid-file=/var/run/mysqld/

Step 4: Initialize the database

Initialize the data directory for each MySQL instance:

sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql1
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql2

Step 5: Create a system service file

Creates a system service file for each MySQL instance. Suppose we will use /etc/systemd/system/​ and /etc/systemd/system/​​.

[Unit]
Description=MySQL Server Instance 1
After=
 
[Service]
User=mysql
Group=mysql
ExecStart=/usr/sbin/mysqld --defaults-file=/etc//
ExecReload=/bin/kill -HUP $MAINPID
ExecStop=/bin/kill -SIGTERM $MAINPID
Restart=on-failure
 
[Install]
WantedBy=

[Unit]
Description=MySQL Server Instance 2
After=
 
[Service]
User=mysql
Group=mysql
ExecStart=/usr/sbin/mysqld --defaults-file=/etc//
ExecReload=/bin/kill -HUP $MAINPID
ExecStop=/bin/kill -SIGTERM $MAINPID
Restart=on-failure
 
[Install]
WantedBy=

Step 6: Start and enable the service

Start and enable the services of these two MySQL instances:

sudo systemctl daemon-reload
sudo systemctl start mysqld1
sudo systemctl start mysqld2
sudo systemctl enable mysqld1
sudo systemctl enable mysqld2

Step 7: Verify the installation

Verify that both MySQL instances are running properly:

sudo systemctl status mysqld1
sudo systemctl status mysqld2

You can connect to each instance via the following command:

mysql -u root -p -S /var/lib/mysql1/
mysql -u root -p -S /var/lib/mysql2/

Or connect via port:

mysql -u root -p -h 127.0.0.1 -P 3306
mysql -u root -p -h 127.0.0.1 -P 3307

The above steps and sample code show how to install and configure multiple MySQL instances on CentOS. Hope this information is helpful to you! If you have any questions, feel free to ask.

Installing and configuring multiple MySQL DB instances in CentOS can provide a separate database environment for different applications or projects. The following is a detailed step-by-step guide, including necessary commands and configuration file modifications to help you set up multiple MySQL instances on CentOS.

1. Install MySQL

First, make sure that MySQL is already installed on your system. If not, you can install it with the following command:

sudo yum update -y
sudo yum install -y mysql-server

2. Create multiple MySQL configuration files

MySQL uses configuration files to define the behavior of each instance. We will create a separate configuration file for each instance. Suppose we want to create two instances: mysql1​ and ​​mysql2​.

Create a configuration directory

sudo mkdir /etc/mysql
sudo mkdir /etc/mysql/mysql1
sudo mkdir /etc/mysql/mysql2

Create a configuration file

Edit the /etc/mysql/mysql1/​​ file:

[mysqld]
datadir=/var/lib/mysql1
socket=/var/lib/mysql1/
port=3306
pid-file=/var/run/mysqld/
log-error=/var/log/mysql1/

Edit the /etc/mysql/mysql2/​ file:

[mysqld]
datadir=/var/lib/mysql2
socket=/var/lib/mysql2/
port=3307
pid-file=/var/run/mysqld/
log-error=/var/log/mysql2/

3. Create data directory and log directory

Create data directories and log directories for each instance and set appropriate permissions:

sudo mkdir -p /var/lib/mysql1
sudo mkdir -p /var/lib/mysql2
sudo mkdir -p /var/log/mysql1
sudo mkdir -p /var/log/mysql2
sudo chown -R mysql:mysql /var/lib/mysql1
sudo chown -R mysql:mysql /var/lib/mysql2
sudo chown -R mysql:mysql /var/log/mysql1
sudo chown -R mysql:mysql /var/log/mysql2

4. Initialize the database

Use the mysql_install_db​ tool to initialize the data directory for each instance. Please note that starting from MySQL 5.7, it is recommended to use the ​​mysqld --initialize​ command:

sudo mysqld --initialize --user=mysql --basedir=/usr --datadir=/var/lib/mysql1
sudo mysqld --initialize --user=mysql --basedir=/usr --datadir=/var/lib/mysql2

5. Create a startup script

Create a startup script for each instance. These scripts will be used to start, stop and manage each instance.

Create ​​​/etc//mysql1​ script

sudo nano /etc//mysql1

The content is as follows:

#!/bin/bash
# chkconfig: 345 64 36
# description: MySQL Server 1
 
. /etc//functions
 
MYSQLD=/usr/sbin/mysqld
MYSQLD_OPTS="--defaults-file=/etc/mysql/mysql1/"
PIDFILE=/var/run/mysqld/
 
start() {
    echo -n "Starting MySQL 1: "
    daemon $MYSQLD $MYSQLD_OPTS
    RETVAL=$?
    echo
    [ $RETVAL -eq 0 ] && touch /var/lock/subsys/mysql1
    return $RETVAL
}
 
stop() {
    echo -n "Shutting down MySQL 1: "
    killproc -p $PIDFILE $MYSQLD
    RETVAL=$?
    echo
    [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/mysql1
    return $RETVAL
}
 
case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    restart)
        stop
        start
        ;;
    *)
        echo "Usage: $0 {start|stop|restart}"
        exit 1
esac
 
exit $?

Create /etc//mysql2 script

sudo nano /etc//mysql2

The content is as follows:

#!/bin/bash
# chkconfig: 345 65 35
# description: MySQL Server 2
 
. /etc//functions
 
MYSQLD=/usr/sbin/mysqld
MYSQLD_OPTS="--defaults-file=/etc/mysql/mysql2/"
PIDFILE=/var/run/mysqld/
 
start() {
    echo -n "Starting MySQL 2: "
    daemon $MYSQLD $MYSQLD_OPTS
    RETVAL=$?
    echo
    [ $RETVAL -eq 0 ] && touch /var/lock/subsys/mysql2
    return $RETVAL
}
 
stop() {
    echo -n "Shutting down MySQL 2: "
    killproc -p $PIDFILE $MYSQLD
    RETVAL=$?
    echo
    [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/mysql2
    return $RETVAL
}
 
case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    restart)
        stop
        start
        ;;
    *)
        echo "Usage: $0 {start|stop|restart}"
        exit 1
esac
 
exit $?

6. Set startup script permissions

Make the startup script executable:

sudo chmod +x /etc//mysql1
sudo chmod +x /etc//mysql2

7. Register for Services

Register the service to the system startup item:

sudo chkconfig --add mysql1
sudo chkconfig --add mysql2
sudo chkconfig --level 345 mysql1 on
sudo chkconfig --level 345 mysql2 on

8. Start a MySQL instance

Start two MySQL instances:

sudo service mysql1 start
sudo service mysql2 start

9. Verify the example

Verify that each instance is running properly:

sudo netstat -tuln | grep 3306
sudo netstat -tuln | grep 3307

10. Connect to the instance

You can use the mysql client to connect to each instance:

mysql -u root -p -S /var/lib/mysql1/
mysql -u root -p -S /var/lib/mysql2/

Through the above steps, you can successfully configure and run multiple MySQL instances on CentOS. Each instance has its own configuration file, data directory and log directory, which does not interfere with each other.

The above is a detailed explanation of the configuration examples for installing multiple mysql databases under Centos. For more information about installing multiple mysqls in Centos, please pay attention to my other related articles!