Share various high-availability technologies in MYSQL (derived from jiangchengyao) and mysql jiangchengyao

Share various high-availability technologies in MYSQL (derived from jiangchengyao) and mysql jiangchengyao
Share various high-availability technologies in MYSQL (from Jiangcheng yaoda NIU)

Images and materials from MYSQL Daniel Jiang chengyao (author of MYSQL technology insider)

Jiang chengyao: Technical Manager of Netease Hangzhou research institute leads the development of INNOSQL

Comparison of mysql High Availability Technologies

Database Reliability refers to data reliability.

Database Availability refers to the availability of database services.

Reliable dataFor example, ICBC cannot lose data

Available Services: The server cannot go down.

 

 

 

Use various high availability technologies of MYSQL to meet the following high availability requirements

To reach 99.9%: MYSQL replication technology

To reach 99.99%: use mysql ndb cluster and Virtualization Technology

To reach 99.999%: GEO-REPLICATION and NDB cluster technology using the shared-nothing Architecture

What is Gluster Geo-replication?

Gluster Geo-replication (geo-replication) is a remote disaster recovery technology,

It is mainly used to back up a storage in a cluster almost instantly (near real-time) to a remote data center through the Internet (wan ).

 

Downtime allowed for various high availability levels

DRBD: Raid 1 of a Network Disk

Solution 1: MYSQL master-slave replication (single-active)

The voting mechanism is complicated.

MySQL does not provide a replication failover solution. The MHA script is required for automatic failover.

Multiple slave databases are supported, and reports and backups can be made from the slave database.

 

Solution 2: Dual-master (single-active), failover is simpler than single-Master

Similarly, MMM scripts are required for automatic failover.

The disadvantage is that a master crashes, and the slave below him also fails.

Solution 3: Dual-master SAN storage (single-active)

 

This architecture is the same as solution 2, except that data does not need to be synchronized between two masters because they use shared disks.

This solution isRich SolutionsNo matter which master fails, other slave does not fail, but SAN storage is too expensive ..

Companies like China Unicom in the communication industry are useful

If a master crashes, the following slave will not.

Note: failover does not push data and data is not pre-loaded into the memory. After switching, the storage performance will be affected for a period of time.

 

Solution 4: DRBDDual Master ConfigurationDRBD(Single-active)

The only difference between the architecture and the solution is that SAN is not used, but insteadLocal disk

Because the disk data is copied in real time, the performance will be affected.

DRBD is called"Diaosi SAN"

Poor man's SAN: a POOR SAN

 

 

 

Solution 5: NDB CLUSTER

 

Few companies use NDB clusters in China, and some banks seem to be useful.

NDB clusters do not need to rely on third-party components. All of them use official components to ensure data consistency.

If a data node fails, other data nodes can still provide services.

Management Nodes need to be redundant to prevent failures

Disadvantages: Management and configuration are complicated, and some SQL statements such as join statements need to be avoided.

 

Solution 6: third-party tunsten Software

Written in java, not built-in MYSQL

It is also MYSQL database replication, but it is not made using the MYSQL built-in components.

Not only supports MYSQL database replication but also heterogeneous database replication, but also supports Heterogeneous Database Replication. For example, copying MYSQL to ORACLE

Solution 7: Netease's INNOSQL

Similar to the high security mode of SQLSERVER Images

The High Safety mode (synchronous mode) does not have a witness Server
The transaction of the database in Principle,Mirror confirmation is required immediately to complete. In this case, the data of Mirror and Principle is synchronized.
However, the performance may be affected because all transactions require the confirmation of mirror.

Difference: innosql slave can be read, and image slave (slave database) cannot be read

Ensures data will not be lost and high data reliability

 

MySQL and later versions support this mode.

 

 

Summary

Each solution has different features and different configuration and application scenarios.

Some tend to be low-cost, some tend to be high-cost, some tend to be data reliability, and some tend to be Database Availability.

Each solution has its own advantages and disadvantages. DBA should select a high-availability solution based on the business situation of the company.

 

More references:

Read/write Splitting: Amoeba

Set up the Amoeba series in MySQL under Ubuntu10 (article index)

Cluster Technology: Database Cluster Technology

How Gluster Geo-replication works

 

If anything is wrong, you are welcome to make a brick o

What is Mysql used?

Like ms SQL Server and DB2 and marshal and Access, they are the databases you use when building dynamic websites. They are free of charge.

MYSQL statement to list the total number of entries with the same ID value

If only the column id is used:
Select char_id from table name goup by char_id having sum (money) = 20

If all the data entries are displayed
Select * from table name where char_id in (
Select char_id from table name goup by char_id having sum (money) = 20
)

---------- Supplement --------
What you wrote is different from what I wrote,
My group by char_id. Do you want to check whether the value is 20 or greater than or equal to 20?