Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to create a second instance of MySQL – Querychat

It is likely that at some point you needed to launch a second instance of the MySQL database on the same machine. In this article, we will see how to do it. 

However, we are not going to install MySQL again. The idea is to use the resources of the installation already done, but we will have two database servers.

The benefits of having multiple MySQL instances

Why would we use multiple instances of MySQL? 

Having two instances of MySQL has a specific usage for different project requirements. A very common scenario is to have multiple environments, for purposes of Development and Testing. This allows us to make all necessary changes and tests before transferring the work to another environment.

It can also be used as a backup system or have two different workspaces in both databases as an additional security measure.

Setting the installation directory for the new instance of MySQL

First, we need to know the location of the existing MySQL installation. Then, we also have to specify the new location of both the program files and data files.

In Windows, something similar to C:\Program Files\ MySQL\ would be the typical directory. In our examples, we will assume that MySQL is installed on a certain path of the file system. From now on, it will be path/MySQL/ so that it works for everyone, even for Linux users.

Creating a new instance of MySQL

When creating the second instance, we have two options:

  • Clean Instance: Empty context, except for the system catalog and initial users such as ‘root’@’localhost’
  • Duplicate Instance: Copy the current state of an existing instance. We also copy the MySQL data folder. This is particularly useful to create snapshots of a database at a specific state.

In the following examples, we will be creating a clean instance.

Download MySQL installer

If you do not have the installer you can download it from the official site at http://dev.mysql.com/downloads/mysql/.

Download the Community Server edition. Locate the appropriate version for your operating system, preferably compressed files without installer.

Official site to download MySQL installer

Suppose the compressed folder is called mysql-version-X.zip. We are only interested in the mysql-version-X/data/ folder.

MySQL data folder

Copy and paste files in path/MySQL/

We have to be careful in this step. The directory path/MySQL/data must exist because of the original installation and we DO NOT want to overwrite what we had in there.

The original MySQL folder should look like this before copying the new instance files:

MySQL folder before making the new instance

Let’s rename as instance2 the installer’s data folder and paste it. After pasting the instance2 folder, the MySQL folder it should look like this:

MySQL folder with 2 instances

Configuring the MySQL Options File for the new instance

MySQL has the ability to read some startup parameters. These parameters must be specified in a file called my.ini in Windows (path/MySQL/my.ini) or my.cnf in Linux.

We are going to generate a copy of that file and name it path/MySQL/mysettings2.ini.

We need to update the communications port and the data route entries in this file. Additionally, we can modify several entries and options within the Options File, depending on what we need.

Within the Options File (path/MySQL/mysettings2.ini), look for the following entries, and modify the following sections:

[client]

port=3307

 

[mysqld]

port=3307

datadir="path/MySQL/instance2/"

Since the usual port of MySQL is 3306, it is common practice to use the next number for the new port number, 3307. As for the data path, remember that “path” refers to the path on your computer/server.

Configure the Startup and Shutdown of the new instance as a Windows service

In addition to setting up the files, we also register the new instance as a Windows service, to facilitate startup and shutdown. For this, we execute the following instructions in the command line terminal. 

If you do have the routes defined in the PATH system variable, you can execute the commands anywhere. But, If you do not have it, you will have to go to the MySQL executable folder, which is path/MySQL/bin/:

cd path/MySQL/bin/

mysqld --install MySQLInstance2 --defaults-file="path/MySQL/mysettings2.ini"

With this we get MySQLInstance2 to appear as an available service. To start the service manually we execute the following command in the command line terminal:

net start MySQLInstance2

Conversely, to stop the service, we execute the following:

net stop MySQLInstance2

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc