run multiple mysql instances

How to Run Multiple MySQL Instances on Same Machine

Sometimes you 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.

$ 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.

user = mysql 
pid-file = /var/run/mysqld/ 
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 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.

user = mysql 
pid-file = /var/run/mysqld/ 
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

user = mysql 
pid-file = /var/run/mysqld/ 
socket = /var/run/mysqld/mysqld2.sock 
port = 3308 
datadir = /data/mysql/mysql2 
user = mysql 
pid-file = /var/run/mysqld/ 
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

Hopefully, this article will help you configure and manage multiple MySQL instances on same server.

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


About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!