Is it a good idea to split MySQL DB into two servers I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate:

I would like to split the MySQL DB server into two separate servers as follows:


Server A (Master) would receive the statistic data for computation.  
Server B (Master) would keep

Aggregated (pre-calculated data) the Master on-demand
application
user data. 



The relation b/w servers would be Master-Master.


Server A -> Server B : sending aggregated (calculated) data  
Server B -> Server A : sending new application and user data so that aggregation will also be performed for newly tracked applications. 


If my reasoning is correct, can you shed some light on how I might accurately do this?
OPTION #1 : Use Master/Master

You could use Master/Master only under one condition: If you write to a DB on ServerA, DO NOT ISSUE WRITES TO ServerB, and vice versa. In this way, you split writes cleanly. Splitting writes to the same DB in Master/Master can be a little clumsy if you depend on looking up rows by IDs that have the AUTO_INCREMENT property. If you look up rows by unique keys that never change from server to server, such as Social Security Number, Driver License, a HashKey and so forth, the splitting writes to the same DB between two Masters in Master/Master is fine.

OPTION #2 : Use Slave Servers

@DTest already described this, so I will add nothing additional to his suggestion (He get's a +1 for it).

OPTION #3 : Use MyISAM in the Slave Servers

When using read-only slaves that are not being used as a master for other servers, you should do two things to the data in that MySQL Instance


Convert all tables to MyISAM
Run ALTER TABLE tblname ROW_FORMAT=Fixed on all MyISAM tables


This should add 20% increase in speed for reads.

OPTION #4 : Use MySQL 5.5 for Semisynchronous Replication

Before MySQL 5.5, when a Master has Multiple Slaves, this is how an SQL statament is processed among the slaves (Example will be a Master with two slaves):

SQL Statement is


Executed in Master
Recorded in Master's Binary Logs
Passed to Slave1 Relay Logs
Executed in Master in Slave1
Passed to Slave2 Relay Logs
Executed in Master in Slave2


In MySQL 5.5, when a Master has Multiple Slaves, this is how an SQL statament is processed among the slaves (Example will be a Master with two slaves) using Semisynchronous Replication:

SQL Statement is


Executed in Master
Recorded in Master's Binary Logs
Passed to Slave1 Relay Logs
Acknowledged to Master of Receipt By Slave1
Passed to Slave2 Relay Logs
Acknowledged to Master of Receipt By Slave2


Replication is a little more robust and at least 1 Slave is More Closely Sync'd to the Master.