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

This article mainly talks about the requirements and restrictions of MySQL group replication. Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the requirements and restrictions of MySQL group replication.

Requirements for group replication:

1) .InnoDB storage engine: data must be stored in a transactional InnoDB storage engine. The transaction is executed in an optimistic manner, and then conflicts are detected before committing. If there is a conflict, some transactions must be rolled back in order to maintain consistency in the group. This means that a transactional storage engine is required. In addition, the InnoDB storage engine provides additional features that better manage and handle conflicts when combined with group replication.

2) .Primary Keys: each table that needs to be copied by a group must explicitly define a primary key. The primary key plays an extremely important role in determining whether a transaction conflicts: using the primary key to accurately identify which rows in the table have been modified in each transaction. (in fact, the host hash is written into a set, and then certifier is used to detect conflicts between transactions.)

3)。 Use IPv4 address: the group communication engine component used by MySQL group replication supports only IPv4. Therefore, you must use IPv4's network.

4)。 Good network performance: the original purpose of group replication design is to deploy in the cluster environment, the nodes in the cluster are very close to each other, so in addition to network latency, network bandwidth will also affect group replication.

Restrictions on group replication:

1) .Replication Event Checksums: group replication cannot use them at this time because of a design flaw in validating replication events. Therefore, you need to set-- binlog-checksum=NONE.

2) .Gap Locks: during the validation phase (certification process), Gap Locks is not considered, so no information about Gap locks can be obtained outside the InnoDB.

Note: unless your application or business requirements depend on REPEATABLE READ (MySQL defaults to this isolation level), it is recommended that you use READ COMMITTED isolation level in group replication. In the READ COMMITTED isolation level, InnoDB basically does not use Gap Locks, which enables the conflict detection that comes with InnoDB to be aligned with the conflict detection of group replication.

3) .Table Locks and Named Locks: table locks and named locks are not considered in the validation phase (certification process) (see get_lock ()).

4)。 SERIALIZABLE isolation level is not supported: in a multi-master model, this isolation level is not supported by default. If this isolation level is set under the multi-master model, the transaction will be rejected.

5)。 Concurrent DDL and DML operations are not supported: simultaneous DDL and DML modifications to the same object on different nodes under a multi-master model are not supported. When you execute an DDL statement on an object on one node, there is a risk that conflicts will be detected if you execute DML to modify the object on other nodes at the same time. (translation note: it is the concurrency of DDL+DML, the concurrency of DDL+DDL is not allowed. This is because there is no DDL transaction in MySQL, so the atomicity of DDL cannot be guaranteed. When DDL and DML operate on an object at the same time, maybe after DDL is modified, DML will not be executed due to the change of object structure, and then rolled back)

6)。 Cascading foreign key constraints are not supported: groups of multi-master models (all nodes are configured with group_replication_single_primary_mode=OFF) do not support multi-level foreign key dependencies, especially cascading foreign key constraints (CASCADING foreign key constraints) defined on the table. This is because cascading operations that perform foreign key constraints under the multi-master model may have undetected conflicts, resulting in data inconsistencies among members of the group. Therefore, we recommend that when using a multi-master model, set group_replication_enforce_update_everywhere_checks=ON on each node to avoid undetected conflicts. There is no such problem in the single master model because there are no concurrent writes, so it is impossible to have undetected conflicts.

7)。 Large transactions can go wrong: if a transaction is so large that the content of the GTID is so large that it cannot be transmitted over the network within 5 seconds, communication between group members will fail. To avoid this problem, you can limit the size of the transaction as much as possible. For example, a LOAD DATA INFILE file is cut into small chunks.

8)。 Deadlocks may occur in the multi-master model: in the multi-master model, SELECT. FOR UPDATE statements can cause deadlocks. This is because lock resources are not shared among members of the group, so such statements may not achieve the desired results.

At this point, I believe you have a deeper understanding of the "requirements and restrictions of MySQL group replication". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.