How to Run Multiple MySQL Instances on Same Machine

Last updated on July 30th, 2024 at 07:09 am

MySQL is a popular database used by many organizations. Sometimes database administrators may need to run multiple MySQL instances on single server. For example, you may need to test different MySQL instances for staging, testing and production. In this article, we will look at how to run multiple MySQL instances on same machine, but different ports, with different logging files and configuration parameters.

How to Run Multiple MySQL Instances on Same Machine

Here are the steps to run multiple MySQL instances on same machine.

1. Open MySQL configuration file

Typically, you will find MySQL configuration file at /etc/mysql/my.cnf. Open terminal and run the following command to open MySQL configuration file in text editor.

$ sudo vi /etc/mysql/my.cnf

Also read : How to Insert multiple rows in MySQL

2. Configure multiple MySQL instances

You will find the default MySQL configuration something similar to what is shown below.

[mysqld] 
user = mysql 
pid-file = /var/run/mysqld/mysqld.pid 
socket = /var/run/mysqld/mysqld.sock 
port = 3306 
datadir = /data/mysql/mysql

Copy+Paste the above block of code below it in the same file. Replace [mysqld] with [mysqld1] and change port value to 3307. This will make the new instance mysqld1 run on a different port 3307 with a different process name mysqld1. Similarly, change file names of pid-file, socket and datadir variables by as shown below.

Make sure that you copy the datadir folder for each instance below. Also ensure that user mysql user has access permissions to it. Each instance needs its own data directory.

[mysqld1] 
user = mysql 
pid-file = /var/run/mysqld/mysqld1.pid 
socket = /var/run/mysqld/mysqld1.sock 
port = 3307 
datadir = /data/mysql/mysql1

Now mysqld1 will run as a separate instance than mysqld

Also read : How to Copy data from one table to another

Similarly, you can create more instances as shown below, each with a different name, pid file, socket file, port number and datadir values. They all need to have the same system user so that it can easily create and manage necessary files.

[mysqld2] 
user = mysql 
pid-file = /var/run/mysqld/mysqld2.pid 
socket = /var/run/mysqld/mysqld2.sock 
port = 3308 
datadir = /data/mysql/mysql2 
.... 
[mysqldN] 
user = mysql 
pid-file = /var/run/mysqld/mysqldN.pid 
socket = /var/run/mysqld/mysqldN.sock 
port = N 
datadir = /data/mysql/mysqlN

Make sure that you provide different pid file, socket file and data dir for each instance and ensure that user mysql has required directory permissions because the mysql user should be able to create required files and directories.

Also read : How to Update Column based on Another Column in SQL

3. Manage multiple instances

You can start each instance separately using mysqld command below. Replace mysqlN with your choice of instance (e.g mysql1, mysql2, etc)

mysqld --initialize --user=mysql --datadir=/home/mysql/mysqlN

If for some reason, the above configuration does not work for you, then copy the my.cnf file to /etc/my.cnf location and try again. Sometimes, mysql does not register multiple instances configured at /etc/mysql/my.cnf

$ sudo cp /etc/mysql/my.cnf /etc/my.cnf

Also read : How to Update Multiple Columns in MySQL

If you want to log into any specific instance (e.g mysqld1) from command line, you need to specify its socket file location with -S option. Otherwise, you will be logged into the default MySQL instance.

mysql -u root -p -S /var/run/mysqld/mysqld1.sock

Conclusion

Hopefully, this article will help you configure and manage multiple MySQL instances on same server. The basic idea is to create separate configuration for each desired instance with its own process name, port number, pid file, socket file and datadir attributes. You can use it to easily create 3 instances for your website – development, testing and production – or for some other purpose. Please remember, you need to issue separate commands to run or connect to each instance. You cannot connect to one instance from another instance. Nevertheless, it is a very handy feature to quickly spin up multiple instances without making much changes.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!