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

Best practices for database high availability scenarios for MySQL MGR+ Consul

Background note:

Based on the fact that there are many MySQL database single point failures, the traditional MHA,PXC and other schemes can be realized by VIP or DNS switching, and the strong data consistency based on the database is considered. The MGR cluster is adopted, and the consul service registration discovery is used to realize the application side to access the MGR cluster through dynamic DNS, so that the database is highly available and switched automatically.

Introduction to MGR

MySQL Group Replication (MGR) is a database solution with high availability and high expansion introduced by MySQL officially in version 5.7.17. It is provided as a plug-in to achieve the ultimate consistency of distributed data. The characteristics of MGR are summarized as follows:

High consistency: group replication based on distributed paxos protocol to ensure data consistency

High fault tolerance: automatic detection mechanism, as long as not most of the nodes are down, can continue to work, built-in anti-brain fissure protection mechanism

High scalability: the addition and removal of nodes automatically updates the group member information, and after the new node is added, the incremental data is automatically synchronized from other nodes until it is consistent with the data of other nodes.

High flexibility: provides single-master mode and multi-master mode. Single-master mode can automatically select master after the main database is down. All writes are carried out on the master node, and multi-master mode supports multi-node writes.

The principle of MGR:

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 communication layer provides guarantee mechanisms such as atomic message (atomic message) and completely ordered information exchange.

Multi-master update based on replication protocol is realized.

A replication group consists of multiple server members, and each server member in the group can perform transactions independently. However, all read-write (RW) transactions are committed only after conflict detection is successful. Read-only (RO) transactions do not need to be detected in conflict and can be committed immediately. In short, for any RW transaction, the commit operation is not one-way determined by the originating server, but by the group. Specifically, on the originating server, when the transaction is ready to commit, the server broadcasts the write value (the changed row) and the corresponding write set (the unique identifier of the updated row). A global order is then established for the transaction. Ultimately, this means that all server members receive the same set of transactions in the same order. Therefore, all server members apply the same changes in the same order to ensure consistency within the group. Group replication is a share-nothing replication scenario in which each server member has its own complete copy of the data.

Limitations of MGR:

Only InnodDB storage engine tables are supported, and each table must have a primary key ID, which is used for conflict detection of wirte set.

The GTID feature must be enabled and the binlog log format must be row mode

Currently, a MGR cluster supports up to 9 nodes.

The save point feature of external key is not supported, and global constraint detection and partial rollback cannot be done.

Binary log does not support binlog event checksum

Introduction to sonsul

Microservice architecture is a very important module, which provides service registration, service discovery and so on. The commonly used service discovery modules are zookeeper, enreka, etcd, cunsul and so on.

Cousul is a distributed, highly available, horizontally developable intermediate key with the following features:

1. Service discovery: provide service registration and discovery through dns or http APIs

2. Health checking: comes with health check, which can provide transfer in case of service failure.

3. Key/value storage: can store dynamically configured system and provide http interface

4. Multi-datacenter: multiple data centers can be supported

Environment description:

10.88.128.163 consul server currently deploys only one server and can deploy cluster mode

10.88.6.251 mysql server mnode1 、 consul client

10.88.6.252 mysql server mnode2 、 consul client

10.88.6.253 mysql server mnode3 、 consul client

System version: centos 7.4

Mysql version:5.7.25

Consul version:1.2.3

Construction of MGR cluster environment

The installation of mysql is described in detail here (if necessary, I will put the automated installation script on the blog)

All three mysql server hosts install the mysql service and initialize the password

Modify the configuration file to ensure that the configuration of the three mysql server is the same (serverid and IP are inconsistent)

Vim / etc/my.cnf

# # gtid configuration

Server_id = 1 # # ensure that the serverid of the three hosts is inconsistent. Here, it is configured as 1meme 11111.

Gtid_mode=ON

Enforce_gtid_consistency=ON

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

Binlog_checksum=NONE

Log_slave_updates=ON

Log_bin=binlog

Binlog_format=ROW

Innodb_buffer_pool_instances=4

Innodb_buffer_pool_size=1G

Innodb_flush_log_at_trx_commit=2

Sync_binlog=0

# for parallel apply binlog

Slave-parallel-type=LOGICAL_CLOCK

Slave-parallel-workers=4

Slave_preserve_commit_order=on

# # mgr configuration

Transaction_write_set_extraction=XXHASH64

# # it means that the replication group you join or create is named 8053c671-0622-11e8-a300-525400b9c5e8, and you can specify it yourself.

Loose-group_replication_group_name= "8053c671-0622-11e8-a300-525400b9c5e8"

# set not to start group replication automatically when server starts

Loose-group_replication_start_on_boot=off

# set the port for group replication and ensure that its cluster can be accessed normally

Loose-group_replication_local_address= "10.88.6.251purl 33091"

# when joining a group, you should connect to these servers for configuration

Loose-group_replication_group_seeds= "10.88.6.251VOR 33091Magnet 10.88.6.252VOR 33091JI 10.88.6.253JULING 33091"

Loose-group_replication_bootstrap_group= off

Loose-group_replication_single_primary_mode=ON # # enable single main mode

# turn off multi-master mode

Loose-group_replication_enforce_update_everywhere_checks=FALSE

# configure cluster whitelist

Loose-group_replication_ip_whitelist= "10.88.6.0 Compact 24"

Mnode1:

Log in to mysql

Create a mysql account and execute the following command to create an account and close binglog

Mysql > SET SQL_LOG_BIN=0

Query OK, 0 rows affected (0.00 sec)

Mysql > GRANT REPLICATION SLAVE ON *. * TO rpl_user@'%' IDENTIFIED BY 'pan@345'

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > SET SQL_LOG_BIN=1

Query OK, 0 rows affected (0.00 sec)

Mysql > CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='pan@345' FOR CHANNEL 'group_replication_recovery'

Mysql > INSTALL PLUGIN group_replication SONAME 'group_replication.so'

Query OK, 0 rows affected (0.16 sec)

# this boot should be done independently by a single sever, and the server starts the group only once. This is why the value of the boot configuration option is not saved in the configuration file. If you save it in the configuration file, server will automatically boot the second with the same name on reboot

A group. This will result in two different groups with the same name mysql > SET GLOBAL group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec)

Mysql > START GROUP_REPLICATION

Query OK, 0 rows affected (1.78 sec)

Mysql > SET GLOBAL group_replication_bootstrap_group=OFF

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT * FROM performance_schema.replication_group_members\ G

CHANNEL_NAME: group_replication_applier

MEMBER_ID: a7495a32-398b-11e9-bec1-080027857522

MEMBER_HOST: mnode1

MEMBER_PORT: 3309

MEMBER_STATE: ONLINE

Server mnode2 、 server mnode3

Mysql > SET SQL_LOG_BIN=0

Query OK, 0 rows affected (0.00 sec)

Mysql > GRANT REPLICATION SLAVE ON *. * TO rpl_user@'%' IDENTIFIED BY 'Workhard@345'

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > SET SQL_LOG_BIN=1

Query OK, 0 rows affected (0.00 sec)

Mysql > CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='pan@345' FOR CHANNEL 'group_replication_recovery'

Mysql > INSTALL PLUGIN group_replication SONAME 'group_replication.so'

Query OK, 0 rows affected (0.16 sec)

Set global group_replication_allow_local_disjoint_gtids_join=ON

Query OK, 0 rows affected (0.00 sec) mysql > START GROUP_REPLICATION

Query OK, 0 rows affected (44 sec 88) mysql > SELECT * FROM performance_schema.replication_group_members\ G

* * 1. Row *

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 3c68303c-391b-11e9-b5b5-08002788ba6b

MEMBER_HOST: mnode3

MEMBER_PORT: 3309

MEMBER_STATE: ONLINE

* 2. Row * *

CHANNEL_NAME: group_replication_applier

MEMBER_ID: a7495a32-398b-11e9-bec1-080027857522

MEMBER_HOST: mnode1

MEMBER_PORT: 3309

MEMBER_STATE: ONLINE

* 3. Row * *

CHANNEL_NAME: group_replication_applier

MEMBER_ID: b8ff481f-39d6-11e9-9208-0800278c8292

MEMBER_HOST: mnode2

MEMBER_PORT: 3309

MEMBER_STATE: ONLINE

3 rows in set (0.00 sec)

So far, the MGR cluster has been set up, and the whole process of joining its members can be observed while starting the cluster.

Say a few points for attention:

1. Ensure the interconnection of ports in the cluster.

2. Make sure that performance_schema = 1, otherwise the members cannot be found in the library.

Check MGR cluster

On primary, create libraries, tables, and add, delete, modify and query information, and other nodes in the cluster will take corresponding actions to confirm that the cluster can provide services normally.

Set up consul to register its mysql-primary and mysql-slave with service discovery

Consul-server:10.88.128.163

Consul-client:10.88.6.251 、 10.88.6.252 、 10.88.6.253

Consul install so easy

Download the corresponding version on the official website: https://www.consul.io/downloads.html, and then copy consul to / usr/local/bin/ after decompression.

Install and run on four machines

Mkdir-pv / etc/consul.d/ & & mkdir-pv / data/consul/

Mkdir-pv / data/consul/shell

Write a configuration file on consul server 10.88.128.163

Vim / etc/consul.d/server.json

{

"data_dir": "/ data/consul"

"datacenter": "dc1"

"log_level": "INFO"

"server": true

"advertise_addr": "10.88.128.163"

"bootstrap_expect": 3

"bind_addr": "10.88.128.163"

"client_addr": "10.88.128.163"

"ui": true

}

Write configuration files on consul client 10.88.6.251,10.88.6.252,10.88.6.253, and modify the upper bind_addr of the three servers to respond to IP.

Vim cat / etc/consul.d/client.json

{

"data_dir": "/ data/consul"

"enable_script_checks": true

"bind_addr": "10.88.6.252"

"retry_join": ["10.88.128.163"]

"retry_interval": "30s"

"rejoin_after_leave": true

"start_join": ["10.88.128.163"]

}

Start consul server on 10.88.128.163

Nohup consul agent-config-dir=/etc/consul.d > / data/consul/consul.log &

Start consul client at 10.88.6.251, 10.88.6.252, 10.88.6.253

Nohup consul agent-config-dir=/etc/consul.d > / data/consul/consul.log &

Observe the log log of consul server. Three client are automatically registered on consul.

View consul members

Visit consulserver's web page http://10.88.128.163:8500/ui/

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.