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.
[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 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
[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
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!
