CODEWITHSUNDEEP
mysql
Warrior
Warrior

Reputation: 39384

Run multiple MySQL server on a single machine

Can we run multiple MySQL servers on a single machine?

Thanks.

Upvotes: 21

Views: 31272

Answers (5)

Aathil Mohamed
Aathil Mohamed

Reputation: 11

Yes definitely, Create multiple configuration files with different ports. This is the best resource to understand:

Video Tutorial: MySQL Multiple Instances

Reference article: Click here

Upvotes: 1

Sarfraz Shaikh
Sarfraz Shaikh

Reputation: 552

For Windows, if the version of mysql server is different then using MYSQL Installer download and install the different versions of the MYSQL server.

MYSQL installer window

Select Reconfigure for each MYSQL server and configure the PORT differently. Complete the configuration steps by clicking next until it is finished

Port configuration window

Upvotes: 1

jumping_monkey
jumping_monkey

Reputation: 7829

My steps on Windows 10:

  1. Copy C:\ProgramData\MySQL\MySQL Server 8.0\my.ini to C:\ProgramData\MySQL\MySQL Server 8.0\my1.ini
  2. Open my1.ini and modify:
    • port=3307(under Client and Server Section)
    • datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data1
    • report_port=3307
  3. Copy C:\ProgramData\MySQL\MySQL Server 8.0\Data to C:\ProgramData\MySQL\MySQL Server 8.0\Data1
  4. Run on cmd prompt: (With Administrator privileges if necessary)
    C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqld --install MySQL80-1 --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my1.ini"

If all went well, you will see:
Service successfully installed.

  1. Win+R
    Type services.msc, find the service name MySQL80-1, right-click on it and click Start.

If all went well, you will see the Status change to Running.
If it did not go well, open xxx.err file found in C:\ProgramData\MySQL\MySQL Server 8.0\Data1 to check why.


If you do not want the service anymore:

  • Stop it
  • Delete it on the cmd prompt using sc delete MySQL80-1 where MySQL80-1 is your service name.

Upvotes: 7

Jeremy Stanley
Jeremy Stanley

Reputation: 5926

Yes, you just need to run them on separate ports and point them at different lib directories for their data.

Here's a good reference: http://dev.mysql.com/doc/refman/5.1/en/mutiple-servers.html

(If you want to use this for testing, I suggest checking out MySQL Sandbox which is now replaced by dbdeployer)

Upvotes: 23

Mari Criss
Mari Criss

Reputation: 111

There are various methods to run multiple instances of mysql (on different ports) on the same machine. Here I have used the same binary and used a separate configuration file (with separate port, pid, socket and data directory). We need to create new directories for our datadir and log folder (if used). Also we need to assign proper permissions on those folders:

# mkdir /var/lib/mysql2
  # chown -R mysql.mysql /var/lib/mysql2/
  # mkdir /var/log/mysql2
  # chown -R mysql.mysql /var/log/mysql2

Next we need a separate configuration file same as a default mysql configuration file. So start by copying the existing one and changing the needed values.

 # cp /etc/my.cnf /etc/my2.cnf

(or change the path appropriately for your configuration file is in a different place).

Next, we need to edit our new configuration file with different mysql port (default to 3306), the pid and socket than the default ones, and also point the data and log folders to the ones created before.

# cd /etc
  # sed -i ‘s/3306/3307/g’ my2.cnf
  # sed -i ‘s/mysqld.sock/mysqld2.sock/g’ my2.cnf
  # sed -i ‘s/mysqld.pid/mysqld2.pid/g’ my2.cnf
  # sed -i ‘s/var\/lib\/mysql/var\/lib\/mysql2/g’ my2.cnf
  # sed -i ‘s/var\/log\/mysql/var\/log\/mysql2/g’ my2.cnf

Finally we need to initialize the default dbs:

# mysql_install_db –user=mysql –datadir=/var/lib/mysql2/

Finally we can start our new mysql instance with:

# mysqld_safe – -defaults-file=/etc/my2.cnf &

We can connect to our new instance using:

# mysql -S /var/run/mysqld/mysqld2.sock

or

# mysql -h 127.0.0.1 -P 3307

and if we no longer need it, stop it with:

# mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown

Ref Site : https://linuxinpakistan.com/start-multiple-instances-mysql-machine

Upvotes: 11

Related Questions