How to install two mysql instance on same machine

Question

I want to run two MySQL instances on the same machine. The reason behind this is, I will be needing two databases with entirely different configurations.

The specifics of the configuration of the both the DB are as follows,

Fault tolerant, high-intensity IOPS, RAID 10. Highly available, Low-intensity IOPS, RAID 1. This two instance will have separate HA policies.

Currently, I am only installing once and running the first instance using systemdctl whereas I am running the second instance manually using separate cnf files.

So now, how can I install and run two separate instances of MySQL on same machine?

DO I need to create separate instances of the MySQL-server binaries for both instances? And do I need to install MySQL-server RPM for a second time into an alternate folder?

If yes, then how? I also want to understand the implication of using the single binary for running two instances. Does it affect the other instance in any way during recovery process or any other db intensive task?

My mysql version would be 5.6.34

I have gone through this link(https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html#systemd-multiple-mysql-instances), for installation of multiple instances on single machine but this support is enabled from 5.7.7 version whereas I am using 5.6.34.

• Josh Bonello: I don't see a real reason to separate the MySQL instances if they are also allowed to share CPU, Storage tier, and RAM. If you're worried about them impacting each other, OS level issues will still create mutual impact.
• a_vlad: 2 instance good for separate dev/qual, may be in some time prod ... but not for HA reasons. Any hardware/power failure - kill all, any hacker - kill all, any bad loading - kill performance of both ...
• Bhupesh Pant: then what would be your recommendations in that case, but it has to be a single machine. docker is one way but still it is susceptible to all the above errors.
• Bhupesh Pant: @JoshBonello, The two setup will be using different storages unit, one will use RAID10 and other would use RAID1.

Answer 1

You must have separate data trees and my.cnf files and Ports. Your client(s) must pick the instance via the Port number. That's about all you need to know.

You may use different binaries.

You may use VMs or Docker or ... These may confuse the issues of Ports, binaries, etc, since they chop up your machine is ways that sort of give you separate logical machines.

There are several questions on this forum relating to this, and they discuss details for what to do before 5.7.6.

• Bhupesh Pant: I am interested in point 1, using different cnf entries, but that way how would I be able to control the both the service using systemctl/pacemaker?
• Rick James: So the question is really about systemctl and pacemaker.