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.
Best Answer
If you are using MySQL as the main database, you may want to consider using a Star Topology via MySQL Replication.
Now, before you say UGHHH, ROFL and OMG to MySQL Replication, hear me out.
A star topology allows you to write to one DB server (called a Distribution Mster [DM]) and send the SQL commands to several DB servers. How do you setup such a DB infrastructure?
Here is the Description
You have 5 DB servers (server A,B,C,D,E)
Server A
Servers B,C,D,E
I have written posts on this before
To keep MySQL Replication in tip top shape