Mysql – Redesigning a multi-instance MySQL server for performance and resiliency

ArchitectureclusteringMySQLndbcluster

I've got a single MySQL server which currently runs N separate MySQL instances on, which are rapidly growing beyond the capabilities of that box.

Is it possible to have an active-active MySQL cluster, with each individual node in the cluster hosting a single instance, but with the capability of having one or more of the instances failing over to another node if the primary hosting node fails?

Example

Current layout

Connection - Server1:{instance port}

Server - Server1

Instance - Instance 1; Instance 2; Instance N

Drive -    LocalDiskA; LocalDiskB; LocalDiskC

Proposed layout

Connection - Connection:{instance port}

Server -   Node1;        Node2;         NodeN

Instance - Instance 1;   Instance 2;    Instance N

Drive -    SanDrive A;   SanDrive B;    SanDrive C

Failover capability

Connection - Connection:{instance port}

Server -   Node1;                    Node2;         XNodeNX (offline)

Instance - Instance 1, Instance N;   Instance 2;

Drive -    SanDrive A, SanDrive C;   SanDrive B;

I've read a few articles on MySQL clustering, and most of what I've seen just refers to being able to load balance a single instance across multiple servers, which unfortunately doesn't meet my requirements (I need to keep identically structured databases separate, as each instance is environment specific).

I know this is possible with SQL Server, but don't know enough about MySQL so any help you can give would be greatly appreciated.

Best Answer

I would recommend that you set up separate clusters for different database instances. You should be able to set up multiple clusters sharing some or all of the hosts, just make sure each cluster has its own ports defined.

Related Question