Considerations for MySQL multi-IDC deployment

Currently, no database product is dedicated to cross-IDC optimization. Theoretically, the best solution adopted by the industry is based on the Paxos protocol (currently only implemented by google, google f1), today we only discuss considerations for MySQL in this regard:

The following problems are basically designed for cross-IDC scenarios:

1. MySQL Data Synchronization across multiple IDCs for Data Consistency

2. High Availability between multiple IDCs

3. Multi-Point Data Writing for multiple IDCs

4. O & M monitoring

Data synchronization between multiple IDCs in MySQL:

1. MySQL replication is asynchronous (for semi-sync 5.5, it is still "Asynchronous"). MySQL synchronization depends on many factors, such as the synchronous network environment and hardware configuration, whether the SQL statement is efficient and the size of the data volume.

When the data volume is large, the master and slave enable the slave-compressed-protocol = 1 compression mode, and the network environment uses a leased line. You can use MySQL of the blackhole storage engine as the relay server to implement cascade replication. It is best to control it within three layers to reduce synchronization latency and reduce the data volume transmitted between data centers. About SQL statements, there should not be too many indexes, which affects the insertion speed. Pay attention to the update and deletion issues. If you use the row mode for replication, the data volume is a problem. We recommend that you use the mixed Mode for replication. Applications with high real-time requirements can be read from the master database during the first read. You can also give a wait time on the application end to mitigate the impact of synchronization latency on users.

2. High Availability issues in multiple IDCs.

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

When the dual-master mode is used, the Disaster Tolerance mode is obvious, and each master node is connected to a server Load balancer cluster. However, the disadvantages of Multi-IDC deployment are also obvious. Only two IDCs can be used.

3. Multi-point writing

Dual-master replication and loop replication can achieve multi-point writing through the federated engine. In this case, you need to consider auto-increment for multi-point writing. You can solve this problem by using auto_increment_increment = N (increment value) and auto_increment_offset = N (initial value. Update loss problem. Each master only writes local data, and the slave has all the data. At this time, the data appears in a certain order. Using message queue, data is also written into the message queue when written to the local database (I am involved)

4. O & M monitoring problems

The monitoring system plays an important role in the entire maintenance process. To prevent the impact of the network environment between multiple IDCs, it is best to adopt distributed monitoring, and each IDCs can work independently. For details, see zabbix and ganglia (two open-source enterprise-level monitoring systems)