Considerations for MySQL Multi-IDC deployment

At present, there is no database products specifically for the cross-IDC optimization, in theory by the industry that the best solution is based on the Paxos protocol (temporarily only Google made the implementation, Google F1), today only discusses MySQL in this respect to note:

For a cross-IDC scenario, the following issues are basically designed:

1, MySQL more IDC data synchronization, data consistency

2, multiple IDC between the high availability

3. Multiple-point write problem of multi-IDC

4, Operation and maintenance monitoring

For MySQL multiple IDC data synchronization problem:

1, MySQL replication is asynchronous (for 5.5 of the half synchronous, or belong to "asynchronous"), MySQL synchronization depends on many factors, the synchronization of the network environment, hardware configuration, SQL statements are efficient, as well as the size of the data volume.

In the case of large amount of data in the master and slave to enable Slave-compressed-protocol = 1 compression mode, network environment using a dedicated line. You can use the Blackhole storage engine MySQL as the relay server to achieve cascade replication, this best control within three layers, reduce synchronization latency, reduce the amount of data transferred between the room. About SQL statements, not too many indexes, affecting the insertion speed, attention to update and delete problems, if the row mode for replication, data volume is a problem, it is recommended to use the mixed mode for replication. Applications that require high real-time performance can be read from the main library for the first time. Or wait time is given on the application side to mitigate the effect of synchronization delay on the user.

2, for many IDC in the high availability problem.

This column more highlights: http://www.bianceng.cn/database/MySQL/

For the use of Relay server, you can use two servers, each relay below is a slave cluster. When a problem occurs, you can access only another slave cluster.

For the use of dual-main mode, the disaster-tolerant mode is more obvious, each master under a slave cluster. But for many IDC deployment, the shortcomings are also obvious, can only use two IDC.

3. Multi-point Write problem

Dual-master replication, loop replication, can be achieved by federated engine multiple-point write. This time for multiple-point write need to consider the problem of auto_increment_increment=n, through the (increment value, and N is the number of nodes) and auto_increment_offset=1,2,3 ... N (initial value) to resolve. Update missing issue. Each master writes only local data, slave owns all the data, and the data comes in a certain order. With Message Queuing, data is written to the local database as well as to the message queue (I am involved)

4, operation and maintenance monitoring problems

The monitoring system plays a very important role in the whole maintenance process. To prevent the impact of the network environment between multiple IDC, it is best to use distributed monitoring, and each IDC monitoring system can work independently, can refer to Zabbix,ganglia (two open source enterprise level monitoring System)