Shulou(Shulou.com)06/01 Report--

Introduction to 1.MGR

MySQL Group Replication (MGR) is a database highly available and highly extensible solution officially introduced by MySQL in version 5.7.17, which is provided as a plug-in. MGR is based on distributed paxos protocol to achieve group replication and ensure data consistency. Built-in fault detection and automatic selection of master functions, as long as most of the nodes in the cluster are not down, it can continue to work normally. Single master mode and multi-master mode are provided, and multi-master mode supports multi-point writing.

two。 A brief introduction to the principle

Group replication is a technology that can be used to implement fault-tolerant systems. A replication group is a Server cluster that interacts with each other through messaging. The replication group consists of multiple Server members, such as Master1, Master2 and Master3 in the figure below, all of which complete their own transactions independently.

When the client initiates an update transaction, the transaction is executed locally, and after execution is completed, a commit operation to the transaction is initiated. Before it is actually committed, the resulting replication write set needs to be broadcast and copied to other members. If conflict detection is successful, the group decides that the transaction can be committed and other members can apply, otherwise it will be rolled back.

Finally, all members of the group receive the same set of transactions in the same order. Therefore, the members of the group apply the same changes in the same order to ensure strong consistency of data within the group.

3. Usage restrictions 3.1 support only the innodb engine

Why does ​ need to use the innodb engine? In MySQL Group Replication, transactions are executed optimistically, but conflicts are checked when committing. If there are conflicts, transactions will be rolled back on some instances to maintain data consistency among instances. Then, this requires the use of transaction storage engine. Colleague Innodb provides some additional functions to better manage and handle conflicts, so it is recommended that business use inndb storage engine for tables. Similar to the system table mysql.user uses the MyISAM engine, because it is rarely modified and added, and there are very few conflicts.

3.2 Primary key

​ each table that needs to be copied must define an explicit primary key, which should be distinguished from the implicit primary key (for tables using Innodb engine, if no primary key is specified, the first non-empty unique index is selected as the primary key by default, if not, a 6-byte rowid implicit primary key is automatically created). This primary key can start an extremely important role in the event of a conflict, and at the same time, it can effectively improve the efficiency of relay log execution.

3.3 isolation level

The ​ official website recommends using the READ COMMITTED level, unless the application relies on no GAP LOCK in REPLEATABLE READ,RC mode, and it is better to support the conflict detection mechanism of Innodb itself and the internal distributed detection mechanism of group replication to work together. The SERIALIZABLE isolation level is not supported.

3.4 Foreign key

​ does not recommend the use of cascading foreign keys. If the old library has foreign keys and cannot be removed in business and uses multi-master mode, configure group_replication_enforce_update_everywhere_check to force the cascading check of each group member to avoid undetected conflicts caused by cascading operations in multi-master mode.

4. Parameter specification

Because the default configuration is mostly adopted in the early creation of instances, the difference of database parameters among development, testing, production and other environments has a certain impact on the operation of the program. If you create an instance in the future, the parameters will be normalized and the existing instances will be modified later.

The following is a brief explanation of the following changed parameters

Sql_mode removes ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE and other restrictions and adopts a more relaxed model.

Lower_case_table_names is uniformly set to 1, that is, it is not case-sensitive. Some instances have not been changed. Do not uppercase when you build tables and databases.

Character-set-server is set to utf8. Do not use the latin1 character set.

The wait_timeout and interactive_timeout parameters control the duration of idle connections. If the idle time exceeds this parameter, the connection will be disconnected and will be set to 1800s, that is, 30 minutes.

Transaction_isolation transaction isolation level MySQL officially defaults to repeatable readability (repeatable-read). Currently, mysql of single instance and master-slave architecture adopts this level, and MGR cluster will adopt read committed (read-committed) level. Oracle defaults to read submitted.

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.