In this instance, you actually have two choices
CHOICE #1 : Percona XtraDB Cluster
I am currently evaluating it and I think it is brilliantly designed for MultiMaster writes. It can use mysqldump (default), rsync, and xtrabackup (preferred) for initializing new Cluster node. You have total freedom and power. This may be the greatest cliche of all time but WITH GREAT POWER, THEIR MUST ALSO ALWAYS BE GREAT RESPONSIBILITY (19:16 - 19:25 of the Video).
You ultimately become responsible for
- sizing memory requirements and disk configuration for InnoDB
- remembering that DDL/DML on MyISAM is not replicated in the Galera Write Set Replicator Libraries. Since GRANT commands is storage-engine neutral, MyISAM table in the mysql schema is handled with no problem. Any DML against
mysql.user
is not replicated.
- adding provisioning new Cluster Nodes for Reads/Writes
CHOICE #2 : Amazon RDS
Amazon RDS makes MySQL Database Cloud Services a snap. You must spend some time deploying Servers with one of 7 server models. By default, all InnoDB log files are 128M. Here are the only options that are unique to each Server Model:
MODEL max_connections innodb_buffer_pool_size
--------- --------------- -----------------------
t1.micro 34 326107136 ( 311M)
m1-small 125 1179648000 ( 1125M, 1.097G)
m1-large 623 5882511360 ( 5610M, 5.479G)
m1-xlarge 1263 11922309120 (11370M, 11.103G)
m2-xlarge 1441 13605273600 (12975M, 12.671G)
m2-2xlarge 2900 27367833600 (26100M, 25.488G)
m2-4xlarge 5816 54892953600 (52350M, 51.123G)
You are not given SUPER privilege and there is no direct access to my.cnf. In light of this, in order to change my.cnf options for startup, you must first create a MySQL-based DB Parameter Option List and use the RDS CLI (Command Line Interface) to change the desired Options. Then, you must do this to import the new options:
- Create a Custom DB Parameter Group (call it
MySettings
)
- Download RDS CLI and setup a config file with your AWS Credentials
- Execute the following :
./rds-modify-db-parameter-group MySettings --parameters "name=whateveroption,value=whatevervalue,method=immediate"
- Modify using DB Parameter Option List
MySettings
- Restart the MySQL RDS Instance
As for scaling out to data centers, you have the option to create read replicas. Since the default storage engine is InnoDB, making a read replica becomes seamless because data can be sync'd to Slaves without interrupting the Master.
Higher Server Models means you can have more Memory, more IOPs. Don't forget the cliche I mentioned because when it comes to Amazon RDS, with GREAT POWER COMES GREAT MONEY.
Galera (for MySQL, Percona XtraDB Cluster or MariaDB Cluster, they are essentially the same but from different vendors and base mysql version) can work perfectly with 2 machines, and it is a very common setup for substituting standard MySQL replication.
Requiring 3 nodes is not a requirement of Galera, but of any cluster that cares about data consistency over availability. In other words, it is needed in order to provide availability and data integrity in the case of only one node suffering from network partition (causing a "split brain" on the cluster). We need an odd number of nodes and 1 node is not a cluster :-). You probably knew that, but I wanted to clarify this for any people reading this answer.
Given that the only reason to have 3 nodes is for availability in case of a node failure/network failure, the recommended way to setup a 2 node cluster with network split protection is to setup a Galera Arbitrator which is essentially an emulation of a MySQL node, but without storing any local data. You can install that anywhere, although you must be careful because if still may influence the performance of the cluster if it has network latency problems.
Running several instances on the same physical node is useless (docker is not needed, by the way, you can do that by just changing the local configuration), as if you absolutely can only run things on 2 machines, you better disable the automatic shutdown on one of the nodes and manage the failover manually. The 3 limitation is just for data consistency, there is nothing physical or on the protocol requiring a certain number of nodes.
You can read more information about it on the official documentation.
Best Answer
It's funny you asked this question
I answered a similar question back in June : 9 node percona xtradb cluster
As I stated in my answer, PXC does not write scale.
If you need more read slaves, I would suggest the following