Shulou(Shulou.com)05/31 Report--

How to choose a highly available architecture in MySQL, many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

I. highly available architecture

MySQL Replication

Classic master-slave replication requires multiple steps to be manually configured. For example, user management, backup recovery, configuration replication. MySQL only provides core functions, and the overall architecture is up to the user to decide (in most cases, customization). In this case, the organization or company needs to use different technical components, and the technical expert or DBA needs to devote a lot of work and time to automation.

MySQL InnoDB Cluster

In 2016, MySQL launched MySQL InnoDB Cluster,InnoDB Cluster, which mainly includes MySQL Group Replication (group member change management, network partition control, cluster scope consistency), MySQL Shell (powerful interface, automated integration of all components), MySQL Router (application transparent routing, load balancing, automatic application failover.) and MySQL Clone (automated deployment member, fully integrated into InnoDB Cluster).

MySQL InnoDB Replica Set

In 2020, MySQL launched MySQL InnoDB Replica Set. This feature is based on classic master-slave replication and fully integrates MySQL Shell and MySQL Router.

II. InnoDB Cluster and InnoDB Replica Set

The past and present solutions that are highly available for MySQL are briefly introduced above, and InnoDB Cluster and InnoDB Replica Set are described in more detail below.

MySQL InnoDB Cluster is an integrated product solution of MySQL with high availability and scalability, and its components include MySQL Server, MySQL Shell, MySQL Router and MySQL Group Replication. Its goal is to provide a MySQL integrated product, all components unified development, integration of all components, and full stack testing. In addition, easy to use is also a feature of the product, using a unified client MySQL Shell for cluster orchestration and management.

MySQL Group Replication

MySQL Group Replication is a distributed high-availability MySQL database with fault tolerance, automatic failover, multi-node update, automatic member management, conflict detection / resolution and data loss prevention. It is the realization of the theory of replication database state machine, which can ensure the overall writing order and consistency. Cluster-wide final consistency, if you use MySQL after 8.0.14, you can achieve strong read-write consistency between session and global scope. MySQL version 5.7 will GA this feature and support all MySQL platforms, including Linux, Windows, Solaris, macOS, FreeBSD and so on.

MySQL Group Replication can be used in the following scenarios:

Consistency: zero data loss (RPO=0)

When the primary member of ○ fails, the data is not lost.

○ prevents network partitioning and prevents network partitioning through most online principles.

High availability: automatic failover

○ automatically selects key members

○ automatically controls network partition

Read extension:

○ add / remove nodes on demand

○ uses processes to control processing delays

○ Configurable Conformance level

◉ is finally consistent

◉ complete consistency

Primary primary environment:

○ writes multiple members simultaneously

Sequential write within ◉ group (implementation of XCOM,PAXOS theory)

◉ guarantees consistency

○ has good write performance.

◉ optimistic lock (pick up on workload)

MySQL Router

A schema that transparently accesses the database, providing a transparent routing connection between the application and the back-end MySQL database.

Transparent client connection routing

○ is responsible for equalization.

○ application connection failover

○ configuration is simple

Seamless design provides simple HA client routing

○ routing as part of the application stack

Integrate InnoDB Cluster and InnoDB Replica Set

2 TCP ports for communication between primary and non-primary nodes

MySQL Shell

Provides a database management interface that can perform all MySQL-related tasks.

Support for multiple languages: JavaScript, Python, SQL

Scriptable

Support for document storage and relational models

Open and complete development and management of API

Classic MySQL protocol and X protocol

MySQL Shell is easy to use, and you can use it to build InnoDB Cluster quickly.

Create a cluster

Configure an instance

Add Instanc

Start MySQL Router

Check cluster status

MySQL InnoDB Replica Set

Fully integrate MySQL Router

Easy-to-use MySQL Shell

Configure, add, remove members

Automated deployment member (Clone)

Master-slave replication architecture

Manual switchover and failover by ○

○ Asynchronous read extension

○ 's simple master-slave architecture

○ has no hardware network requirements

◉ provides availability of the primary node

InnoDB Replica Set can automatically deploy new members through cloning, automatically configure users and replication with MySQL Shell, manually configure, add and remove servers, MySQL Router or other agents used by applications, and integrate the load balancing capabilities of MySQL Router. As a result, the tedious steps needed to configure master-slave replication are avoided.

In the past, it was necessary to use additional monitoring tools to check the topology status on each server, and the user was responsible for all the configurations of all components, and each setting was equivalent to customization. Using InnoDB Replica Set, you can view the topology status through MySQL Shell status (), and Shell configures the server, routing, and replication based on best practices to prevent errors.

InnoDB Replica Set is a standard solution supported and guaranteed by the MySQL team. The advantage of this solution is that it is easy to use and can be quickly mastered by beginners.

Third, how to choose a highly available architecture

Finally, I explain how to choose different high-availability architectures.

First of all, be clear about the needs of the business. The higher the availability, the higher the cost. The goal can be defined from the following aspects:

Recovery time objective (RTO)

How long will it take for the service to recover from the failure?

Recovery Point objective (RPO)

The service allows lost data in the event of a failure

Fault type

○ high availability: single server failure, network partition

○ disaster recovery: overall region / network failure

○ human error: operational error, vandalism

Degree

○ 0

○ second

○ score

○ hour

○...

Architecture corresponding to different business requirements

Single region

RTO= hour

RPO= score

One MySQL server

○ backup

○ synchronization log

Single region

RTO= hour

RPO= less than 1 second

One MySQL server

Frequent ○ backups

○ continues to pull binary logs

Single region

RTO= number score

RPO= less than 1 second

MySQL InnoDB Replica Set

Single region

RTO= seconds

RPO=0

MySQL InnoDB Cluster

Multi-region

Regional fault

RTO= number score

RPO= seconds

MySQL InnoDB Cluster

Using ○ with Asynchronous replication

Multi-region

Regional fault

RTO= number score

RPO=0

MySQL InnoDB Cluster cross-region deployment

The consistency level of the two regions of ○ is set to AFTER, or three regions, each with 1-2 members.

The throughput of ○ writes is affected, and write transactions need to be synchronized.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.