How To : Setup MySQL Group Replication

MySQL Group Replication is a MySQL Server plugin that enables you to create elastic, highly-available, fault-tolerant replication topologies.

Groups can operate in a single-primary mode with automatic primary election, where only one server accepts updates at a time. Alternatively, for more advanced users, groups can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently.

There is a built-in group membership service that keeps the view of the group consistent and available for all servers at any given point in time. Servers can leave and join the group and the view is updated accordingly. Sometimes servers can leave the group unexpectedly, in which case the failure detection mechanism detects this and notifies the group that the view has changed. This is all automatic.

As i prepare to roll out a 6 Node Group Replication in production, we are currently in the process of setting up our UAT environment.
It will be running 3 Node cluster.


My Environment

Host :
192.168.56.101	lnxmygrp01
192.168.56.102	lnxmygrp02
192.168.56.103	lnxmygrp03

Mount Points : 
[admin@lnxmygrp01 ~]$ df -h | grep mysql
/dev/sdc                 2.0G  6.0M  1.8G   1% /mysql_config
/dev/sdf                  50G   53M   47G   1% /mysql_backup
/dev/sdd                  30G   45M   28G   1% /mysql_binlog
/dev/sde                  20G   45M   19G   1% /mysql_tranlog
/dev/sdb                  50G   53M   47G   1% /mysql_data
[admin@lnxmygrp01 ~]$

MySQL version :
[admin@lnxmygrp01 ~]$ mysqld --version
mysqld  Ver 5.7.17-enterprise-commercial-advanced for Linux on x86_64 (MySQL Enterprise Server - Advanced Edition (Commercial))
[admin@lnxmygrp01 ~]$

Before we initialize our MySQL, lets standardize our my.cnf

note : do the same in all other nodes
[admin@lnxmygrp01 mysql_config]$ sudo rm -f /etc/my.cnf
[admin@lnxmygrp01 etc]$ sudo ln -s /mysql_config/my.cnf /etc/my.cnf
[admin@lnxmygrp01 mysql_config]$

What is the content of my.cnf

[client]
port                                         = 3306
socket                                       = /mysql_config/mysql.sock

[mysql]
no_auto_rehash
max_allowed_packet                           = 16M
prompt                                       = '\u@\h [\d]> '
default_character_set                        = utf8

[mysqldump]
max_allowed_packet                           = 16M

[mysqld_safe]
open_files_limit                             = 8192
user                                         = mysql
log-error                                    = /mysql_tranlog/mysql_error.log

[mysqld]
# Connection and Thread variables
port                                         = 3306

#Datafile location
datadir                                      = /mysql_data
socket                                       = /mysql_config/mysql.sock
tmpdir                                       = /tmp

# SSL options
#ssl-ca = ca.pem
#ssl-cert = server-cert.pem
#ssl-key = server-key.pem

max_allowed_packet                           = 16M
default_storage_engine                       = InnoDB
character_set_server                         = utf8
lower_case_table_names                       = 1
max_connections                              = 505
max_user_connections                         = 500
thread_cache_size                            = 505

# Query Cache
query_cache_type                             = 1
query_cache_size                             = 32M

# Session variables
sort_buffer_size                             = 2M
tmp_table_size                               = 32M

# Other buffers and caches
table_definition_cache                       = 1400
table_open_cache                             = 2000
table_open_cache_instances                   = 16

# MySQL error log
log_error                                    = /mysql_tranlog/mysql_error.log

log_timestamps                      	     = SYSTEM
log_error_verbosity                                 = 2

# Slow Query Log
slow_query_log_file                          = /mysql_tranlog/mysql_slow.log
slow_query_log                               = 0
log_queries_not_using_indexes                = 0
long_query_time                              = 0.5
min_examined_row_limit                       = 100

# General Query Log
general_log_file                             = /mysql_tranlog/mysql_general.log
general_log                                  = 0

# Binary logging and Replication
server_id                                    = 1
log_bin                                      = /mysql_binlog/binlog
binlog_cache_size                            = 1M
binlog_stmt_cache_size                       = 1M
max_binlog_size                              = 128M
sync_binlog                                  = 0
expire_logs_days                             = 5
binlog_format                                = ROW

# Security variables
local_infile                                 = 0
sql_mode                                     = TRADITIONAL,ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER   # Be careful changing this afterwards
allow-suspicious-udfs                        = false
local_infile                                 = 0
skip-grant-tables                            = false
skip_symbolic_links                          = YES
log-error_verbosity                          = 2
log-raw                                      = OFF

plugin-load                                  = audit_log.so
audit_log_connection_policy                  = 'ALL'
audit_log_exclude_accounts                   = NULL
audit_log_policy                             = 'ALL'
audit_log_statement_policy                   = 'ALL'
audit_log_strategy                           = 'SEMISYNCHRONOUS'
audit_log                                    = 'FORCE_PLUS_PERMANENT'

plugin-load                                  = validate_password.so
validate_password                            = FORCE_PLUS_PERMANENT
validate_password_length                     = 14
validate_password_mixed_case_count           = 1
validate_password_number_count               = 1
validate_password_special_char_count         = 1
validate_password_policy                     = MEDIUM

# MyISAM variables
key_buffer_size                              = 8M                    # Set to 25 - 33 % of RAM if you still use MyISAM
myisam_recover_options                       = 'BACKUP,FORCE'

# MEMORY variables
max_heap_table_size                          = 64M

# InnoDB variables
innodb_strict_mode                           = ON
innodb_file_format_check                     = 1
innodb_buffer_pool_size                      = 512M                 # Go up to 80% of your available RAM
innodb_buffer_pool_instances                 = 8                    # Bigger if huge InnoDB Buffer Pool or high concurrency
innodb_file_per_table                        = 1                    # Is the recommended way nowadays
innodb_flush_log_at_trx_commit               = 2                    # 1 for durability, 0 or 2 for performance
innodb_log_buffer_size                       = 8M                   # Bigger if innodb_flush_log_at_trx_commit = 0
innodb_log_file_size                         = 256M                 # Bigger means more write throughput but longer recovery time

# prevent use of non-transactional storage engines
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE"

#####################################################################################################
# Group replication
#####################################################################################################
# replication and binlog related options
binlog-row-image                    = MINIMAL
binlog-rows-query-log-events        = ON
log-bin-trust-function-creators     = TRUE
expire-logs-days                    = 90
max-binlog-size                     = 1G
relay-log-recovery                  = ON
slave-parallel-type                 = LOGICAL_CLOCK
slave-preserve-commit-order         = ON
slave-parallel-workers              = 8
slave-rows-search-algorithms        = 'INDEX_SCAN,HASH_SCAN'
slave-type-conversions              = ALL_NON_LOSSY
sync-master-info                    = 1000
sync-relay-log                      = 1000

# General replication settings
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 	     				= /mysql_binlog/binlog
binlog_format 		        		= ROW
plugin-load                         		= group_replication.so
group_replication                   		= FORCE_PLUS_PERMANENT
transaction_write_set_extraction 		= XXHASH64
loose-group_replication_bootstrap_group 	= OFF
loose-group_replication_start_on_boot 		= ON
loose-group_replication_ssl_mode 		= REQUIRED
loose-group_replication_recovery_use_ssl 	= 1

# Shared replication group configuration
loose-group_replication_group_name 		= "af35aeee-66ac-4b38-9b79-9964c5ebe251"
loose-group_replication_ip_whitelist 		= "192.168.56.101,192.168.56.102,192.168.56.103"
loose-group_replication_group_seeds 		= "192.168.56.101:33061,192.168.56.102:33061,192.168.56.103:33061"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
loose-group_replication_single_primary_mode 			= ON
loose-group_replication_enforce_update_everywhere_checks 	= OFF

# Host specific replication configuration <-- update below based on other node information.
server_id 				= 1
bind-address 				= "192.168.56.101"
report_host 				= "192.168.56.101"
loose-group_replication_local_address 	= "192.168.56.101:33061"
relay_log				= /mysql_binlog/lnxmygrp01-relay-bin

Now let’s initialize mysql in all servers.

[admin@lnxmygrp01 mysql_tranlog]$ sudo mysqld --initialize --user=mysql --datadir /mysql_data

[admin@lnxmygrp01 mysql_tranlog]$ sudo service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
[admin@lnxmygrp01 mysql_tranlog]$

[admin@lnxmygrp01 mysql_tranlog]$ ps -ef | grep mysql
mysql    30645     1  2 13:01 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
admin    30828  5148  0 13:01 pts/0    00:00:00 grep --color=auto mysql
[admin@lnxmygrp01 mysql_tranlog]$

[admin@lnxmygrp01 mysql_tranlog]$ cat /mysql_tranlog/mysql_error.log | grep root
2017-04-21T12:59:40.355416-05:00 1 [Note] A temporary password is generated for root@localhost: XVLdv.+t+1Ha
[admin@lnxmygrp01 mysql_tranlog]$

Now lets do the post install steps. perform to all nodes.

[admin@lnxmygrp01 mysql_tranlog]$ mysql_secure_installation
Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No:
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
[admin@lnxmygrp01 mysql_tranlog]$

Validate in all nodes if group replication plugin is loaded.

root@localhost [(none)]> show plugins;
+----------------------------+----------+--------------------+----------------------+-------------+
| Name                       | Status   | Type               | Library              | License     |
+----------------------------+----------+--------------------+----------------------+-------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | PROPRIETARY |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | PROPRIETARY |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL                 | PROPRIETARY |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL                 | PROPRIETARY |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | PROPRIETARY |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL                 | PROPRIETARY |
.
.
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | PROPRIETARY |
+----------------------------+----------+--------------------+----------------------+-------------+
45 rows in set (0.00 sec)

root@localhost [(none)]>

Create replication user, perform in all nodes.

root@localhost [(none)]> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.01 sec)

root@localhost [(none)]> CREATE USER 'rpl_user'@'192.168.56.10%' IDENTIFIED BY 'rpl_pass' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'rpl_user'@'192.168.56.10%';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]>
oot@localhost [(none)]> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.01 sec)

root@localhost [(none)]> CREATE USER 'rpl_user'@'192.168.56.10%' IDENTIFIED BY 'rpl_pass' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'rpl_user'@'192.168.56.10%';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]>
root@localhost [(none)]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

root@localhost [(none)]>

On Node 1 : lets bootstrap the first node.

dbadmin@localhost [(none)]> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

dbadmin@localhost [(none)]> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.49 sec)

dbadmin@localhost [(none)]> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

dbadmin@localhost [(none)]>

+--------------------------------------+----------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_TYPE |
+--------------------------------------+----------------+-------------+--------------+-------------+
| a1c5e25e-2715-11e7-bbe4-0800273fb9a2 | 192.168.56.103 |        3306 | ONLINE       | PRIMARY     |
+--------------------------------------+----------------+-------------+--------------+-------------+
1 rows in set (0.01 sec)

Now, Lets start the group replication in other nodes.

root@lnxmygrp02 [(none)]> START GROUP_REPLICATION;
Query OK, 0 rows affected (8.24 sec)
root@lnxmygrp02 [(none)]>

root@lnxmygrp03 [(none)]> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.58 sec)
root@lnxmygrp03 [(none)]>

+--------------------------------------+----------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_TYPE |
+--------------------------------------+----------------+-------------+--------------+-------------+
| a1c5e25e-2715-11e7-bbe4-0800273fb9a2 | 192.168.56.103 |        3306 | ONLINE       | PRIMARY     |
| a1e3e0e0-2715-11e7-b2cd-0800274048c8 | 192.168.56.101 |        3306 | ONLINE       | SECONDARY   |
| a293fead-2715-11e7-bdf9-080027d1779e | 192.168.56.102 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+----------------+-------------+--------------+-------------+
3 rows in set (0.01 sec)
root@lnxmygrp01 [(none)]>

If you encounter below error during the first time joining to the cluster.

2017-04-22T00:43:52.170798-05:00 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: a293fead-2715-11e7-bdf9-080027d1779e:1-3 > Group transactions: a1e3e0e0-2715-11e7-b2cd-0800274048c8:1-3,
af35aeee-66ac-4b38-9b79-9964c5ebe251:1'
2017-04-22T00:43:52.170932-05:00 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

root@lnxmygrp02 [(none)]> SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected (0.00 sec)

root@lnxmygrp02 [(none)]> START GROUP_REPLICATION;
Query OK, 0 rows affected (8.24 sec)

root@lnxmygrp02 [(none)]> SET GLOBAL group_replication_allow_local_disjoint_gtids_join=OFF;
Query OK, 0 rows affected (0.00 sec)
root@lnxmygrp02 [(none)]> 

root@lnxmygrp03 [(none)]> SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected (0.00 sec)

root@lnxmygrp03 [(none)]> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.58 sec)

root@lnxmygrp03 [(none)]> SET GLOBAL group_replication_allow_local_disjoint_gtids_join=OFF;
Query OK, 0 rows affected (0.00 sec)
root@lnxmygrp03 [(none)]>

+--------------------------------------+----------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_TYPE |
+--------------------------------------+----------------+-------------+--------------+-------------+
| a1e3e0e0-2715-11e7-b2cd-0800274048c8 | 192.168.56.101 |        3306 | ONLINE       | PRIMARY     |
| a1c5e25e-2715-11e7-bbe4-0800273fb9a2 | 192.168.56.103 |        3306 | ONLINE       | SECONDARY   |
| a293fead-2715-11e7-bdf9-080027d1779e | 192.168.56.102 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+----------------+-------------+--------------+-------------+

Now our new cluster is up. it’s time to configure our router.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s