Mysql Group Replication

Mysql Group Replication

Brief and purpose of group replication.

Mention why  it used for  project.

Replication setup

High level diagram of setting up of servers in group replication , indicate how the data can be accessed in  project.

Explain the data flow.

Failover scenarios

Dos and donts in primary and secondary servers.

1.    TXN based Connection – All basic transactions(creation/modification/deletion/search).Temp usage not allowed..DDL scripts are not allowed.

2.    NO-TXN based connection  - SPs executions / Temp table and DDL based query are feasible.

Latency

Configuring Group replication

PreRequisite

1, Synchronous Group replication starts from MySQL 5.7 and later

2, Two Server with same MySQL set up.

3, we can configure Group replication in both MySQL community and Enterprise edition.

4, The two server should be in same network.

5, MySQL ports should be allowed both inbound and out bound in both servers

     Eg : 3306, 24801,24802

Steps to configure

Configuration File

[mysqld]

server configuration

datadir = <full_path_to_data>/data/s1

basedir = <full_path_to_bin>/mysql-5.7/

port=24801

socket=<full_path_to_sock_dir>/s1.sock

These settings configure MySQL server to use the data directory created earlier and which port the server should open and start listening for incoming connections.

Configuration Changes

Server-id=1

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

These settings configure the server to use the unique identifier number 1, to enable global transaction identifiers and to store replication metadata in system tables instead of files. Additionally, it instructs the server to turn on binary logging, use row-based format and disable binary log event checksums.

Group Replication Settings

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address= "192.168.10.14:24901"

loose-group_replication_group_seeds= "192.168.10.14:24901,192.168.10.15: 24901"

loose-group_replication_bootstrap_group= off

Explaination:

 1, Instructs the server that for each transaction it has to collect the write set and encode it as a hash using the       XXHASH64 hashing algorithm. 

 2, Tells the plugin that the group that it is joining, or creating, is named "aaaaaaaa-aaaa-aaaa-aaaa-  aaaaaaaaaaaa". 

3,Instructs the plugin to not start operations automatically when the server starts. 

4, tells the plugin to use the IP address 192.168.10.14 and port 24901 for incoming connections from other members in the group

5,Tell the member who involved  Group replication that will be white listed here.

Creating Replication User

Create a MySQL user with the REPLICATION-SLAVE privilege. This process should not be captured in the binary log to avoid the changes being propagated to other server instances

Eg

SET SQL_LOG_BIN=0;

CREATE USER rpl_user@'%' IDENTIFIED BY 'password';

GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

FLUSH PRIVILEGES;

SET SQL_LOG_BIN=1;

Group replication recovery

The CHANGE MASTER TO statement to configure the server to use the given credentials for the group_replication_recovery replication channel the next time it needs to recover its state from another member.

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR
CHANNEL 'group_replication_recovery';

                  

Group Replication Plugin

Connect to the servers among the Group in Group replication need to install the Group Replication plugin

For windows:

INSTALL PLUGIN group_replication SONAME 'group_replication.DLL';

Execute Show plugins; to check group replication plugin added are not.

Start Group replication

 To start the group, instruct server s1 to bootstrap the group and then start Group Replication. This bootstrap should only be done by a single server, the one that starts the group and only once. This is why the value of the bootstrap configuration option was not saved in the configuration file. If it is saved in the configuration file, upon restart the server automatically bootstraps a second group with the same name. This would result in two distinct groups with the same name. The same reasoning applies to stopping and restarting the plugin with this option set to ON. 

SET GLOBAL group_replication_bootstrap_group=ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group=OFF;

After  the group replication started we can check the Group members and there status by using below query.

 SELECT * FROM performance_schema.replication_group_members;

Procedure for DB BACKUP in Group replication environment

Access credentials

Superreadonly