How to Run Multiple MySQL Instances on One Server

Running multiple MySQL instances on a single physical server is an efficient way to isolate databases, test different configurations, or maximize hardware utilization. This article provides a step-by-step guide on how to configure, initialize, and run multiple independent MySQL instances on one machine by configuring unique ports, data directories, and configuration files.

1. Create Separate Data Directories

Each MySQL instance must have its own dedicated directory to store database files. Running multiple instances pointing to the same data directory will cause immediate data corruption. Create a new directory and grant the appropriate ownership permissions to the MySQL user.

mkdir -p /var/lib/mysql2
chown -R mysql:mysql /var/lib/mysql2
chmod 750 /var/lib/mysql2

2. Allocate Unique Ports and Sockets

Every MySQL instance requires a unique network port, Unix socket file, and PID (Process ID) file to prevent conflicts. While the default instance runs on port 3306, subsequent instances should be assigned different ports (e.g., 3307, 3308) and distinct socket paths.

Default Instance: Port 3306, Socket /var/run/mysqld/mysqld.sock
Second Instance: Port 3307, Socket /var/run/mysqld/mysqld2.sock

3. Create a Dedicated Configuration File

Create a new configuration file (such as /etc/mysql/my2.cnf) for the second instance. This file must explicitly define the unique paths, port, and socket configurations.

[mysqld]
user            = mysql
port            = 3307
datadir         = /var/lib/mysql2
socket          = /var/run/mysqld/mysqld2.sock
pid-file        = /var/run/mysqld/mysqld2.pid
log_error       = /var/log/mysql/error2.log
bind-address    = 127.0.0.1

Ensure the log directory exists and has the correct permissions:

touch /var/log/mysql/error2.log
chown mysql:mysql /var/log/mysql/error2.log

4. Initialize the New Data Directory

Before starting the new instance, you must initialize the MySQL system tables in the new data directory. Use the mysqld command while referencing your newly created configuration file.

mysqld --defaults-file=/etc/mysql/my2.cnf --initialize --user=mysql

During this initialization process, a temporary root password will be generated. Locate this password in the error log you defined:

grep 'temporary password' /var/log/mysql/error2.log

5. Start the Second MySQL Instance

Start the new instance by pointing directly to its custom configuration file using mysqld_safe.

mysqld_safe --defaults-file=/etc/mysql/my2.cnf &

For long-term management, you can create a custom systemd service file (e.g., /etc/systemd/system/mysql2.service) to allow standard management commands like systemctl start mysql2.

6. Connect and Secure the Instance

To connect to the newly created instance, you must explicitly specify the unique port or socket file in your connection command.

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

Once logged in using the temporary password, immediately change the root password to secure the instance:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewSecurePassword';
FLUSH PRIVILEGES;