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.
Best Answer
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.