High-availability architecture in multi-data centers "Ring Star Database Architecture"

Paste some older content, the article is new write,Technology may be familiar to everyone, for the entry of the brothers reference. Master Pat
Please see the original: Http://www.muduo.net/index.php/u ... space-itemid-318728

Two
High-availability structures in multi-data centers "ring star type"Database Schema "Before we introduce this structure, let's first look atThe content of MySQL replication. In the first edition of Highperformance MySQL, the author describes a database structure such as:


Three MySQL daemon are home slave, all for the home of the master, ring copy, so, then endless.

The master on each loop has its own slave, which solves the problem of MySQL's efficiency and usability.

Unfortunately, Jeremy's imagination is plentiful, but the latest version of MySQL at the time was 4.0, and did not support such a complex MySQL replication structure, the lack of a key link to the solution, what are the key links?

I.
auto_increment Field Conflict issues

Ii.
For example, 4 (MySQL server 3 slave) replicates data from 3 (MySQL server 4, master) and can only replicate data written by the application on 3 (that is, 3 generates Binlog), for 3 (at this time as a MySQL server 2 Slave) data that is replicated in 2 (as the master of MySQL server 3) cannot be copied to server 4.

Iii.
Circular Copy Local generation of data repeat write problem

Is the above three unresolved problems, Jeremy in "High performance MySQL" in the entire chapter content almost into imagination.

I believe that today's MySQL 5The design must have absorbed the idea of Jeremy.

A
Architecture diagram
In combination with the MySQL high availability architecture for single data center and the features of MYSQL5, we have designed the following multi-data center MySQL high-availability structure, the ring star structure.




Two
System Structure Description
As shown, assuming a, B, C, D 4 data centers, each data center has the same application (not limited to Web services), each data center application directly reads and writes local database data according to the scheme mentioned in the MySQL high availability architecture of single data center.
At this point, on the basis of ensuring that the single data center is highly available, we simplify the structure to the following structure:

A, B, C, D is a very concise MySQL replication ring, to meet the normal operation of this replication structure needs to be configured in the following aspects:

I.
Resolves auto_increment field data conflicts by Http://dev.mysql.com/doc/refman/5.1/en/replication-options-master.html#sysvar_auto _increment_increment

Ii.
When the same machine is used as a slave and as master, the contents of the replication can be transferred to the next slave

Iii.
Resolves a data copy from A to B, copy from B to C, copy from C to D, but not copied from D to a

Here is a detailed description of how to solve the above problem:

I.
auto_increment Field Conflict issues

The two system variables, auto_increment_increment and Auto_increment_offset, are designed to meet the masterßàmaster of this MySQL replication pattern, and these two variables are capable of controlling auto_ The behavior of the INCREMENT column avoids conflicts with the value of the Auto_increment column. For more information on this section, refer to: Http://dev.mysql.com/doc/refman/5.1/en/replication-options-master.html#sysvar_auto_increment_ Increment

Ii.
--log-slave-updates

In general, the slave server does not need (and will not) record the "update" that it receives from the master server into Binlog, but this system variable allows MySQL's slave server to record these "updates" to its own binlog. In the case of using this variable, you need to first configure the MySQL "--log-bin" variable.

Iii.
--replicate-same-server-id

By default, this value is set to 0. To avoid infinite cyclic replication in a circular replication structure.

Ok, on the basis of the normal copy structure, after the above three points additional configuration, the MySQL ring copy can work normally. such as a, B, C, D4 mysql circular replication structure, can be inserted in any one MYSQLD service, update data, and in any MYSQLD server, you can find all the data. Of course, there is a need to further address the architecture of a high-availability MySQL database with a ring star:

I.
High availability of the master service for any MySQL cluster (for example, a) (virtual IP address drift via heart-beat, via drift IP)

Ii.
Data connectivity and reliability issues between groups of databases "Specify different standards for business requirements"

A
Transport layer Proxy via UDT gateway

b
Resolve by Leased Line

Iii.
To solve the bandwidth problem of MySQL Binlog transmission, this issue considers the following two workarounds:

A
With--slave_compressed_protocol, protocol compression is used between MySQL master and slave servers to reduce bandwidth requirements:

1.
Slave_compressed_protocol=1

2.
SET @ @global. slave_compressed_protocol=1;

b
Through the development of MySQL differential replication protocol (e.g. Binlog project of the interactive community )

Mysql the Ring star Multi-data center structure can solve the following problems :

I.
To solve the problem of slow response of cross-network operation data, the data update is conducted in local IDC to ensure the dynamic program is fast and timely.

Ii.
Solve data center redundancy problems, if one IDC data center is down, you can switch to another data center at any time.

Of course, this structure is not perfect, while solving some problems, but also brings some other problems:

I.
Data synchronization delay, cross-IDC database synchronization compared to the same network MySQL replication, the network environment more complex, due to binlog transmission problems, easy to bring greater data latency

Ii.
Stability requires robust monitoring and more sophisticated automated alarms, fault handling measures

High-availability architecture for multi-datacenter "ring Star database Schema"