I am attempting a preliminary stab at a PXC design in support of a
web-based application. A Drupal framework is used to generate over 120
websites for various units within my client’s organization. The
organization has two data centers, one on each coast of the U.S. The
semi-static data for each website resides in a mostly read-only MySQL
database on a public-facing VM in a DMZ which is firewalled from
the rest of the organization’s network. Databases are backed up every
day using mysqldump locally. There is no replication per se, but what
amounts to duplicates of the VMs are pushed from the primary data center
to the secondary every day. Unit administrators periodically modify the
data for their websites.
Increasing numbers of organization units are requesting websites, with
as many as a dozen going live in a single month, but averaging about one
per week. The expectation is that eventually over 300 websites, and,
hence, 300 separate MySQL databases, will be required.
What is the best way to use PXC to provide both HA and failover for
this environment? Should I contemplate placing 3 distinct sets of
databases (A-G/H-O/P-Z, for example) in three separate clusters,
each of three nodes, at the primary data center? And should I also
consider expanding the clusters to the other data center instead of
trying to figure out how to replicate to the secondary site? Or can I
run straight MySQL replication from a node of each cluster on the
primary to a slave on complementary clusters at the secondary site?
Any insight would be appreciated.