Java & Linux : Fishing in the Desert: September 2020

https://github.com/auntaru/vagrantfiles
https://www.youtube.com/watch?v=EVb_YhO3c7U
https://raw.githubusercontent.com/auntaru/vagrantfiles/master/MySQL-iNNoDB-CLuSTeR-pacemaker-RouTeR/provision.sh
https://github.com/auntaru/vagrantfiles/blob/master/MySQL-iNNoDB-CLuSTeR-pacemaker-RouTeR/innodb-cluster-status.js
https://github.com/auntaru/vagrantfiles/blob/master/MySQL-iNNoDB-CLuSTeR-pacemaker-RouTeR/Vagrantfile

GoaL of this article :
MySQL Router shouldn't be the single point of failure between application and a MySQL CLuSTeR

On all Nodes, Install Pacemaker and configure some settings.
If [HighAvailability] repository is not in your CentOS 8, run [# dnf upgrade centos-repos] first.

The first step is to install pacemaker on all the machines we will use for our “MySQL Router Cluster”:

# # enable [HighAvailability] repo and install (it's disabled by default)
[root@hyperv-primary-cos8 ~]# dnf --enablerepo=HighAvailability -y install pacemaker pcs

# Now we need to start the pcsd service and enable it at boot (on all machines):
[root@hyperv-primary-cos8 ~]# systemctl enable --now pcsd

Setup authentication : operation is again executed on all machines part of the MySQL Router Cluster:
# # set cluster admin password
[root@hyperv-primary-cos8 ~]# echo MyStrongPassw0rd | passwd --stdin hacluster

And on one of the nodes, we can now execute the following command:

On CentOS7 :

[root@mysql1 ~]# pcs cluster auth mysql1 mysql2 mysql3 -u hacluster -p MyStrongPassw0rd --force
mysql1: Authorized
mysql2: Authorized
mysql3: Authorized

On CentOS8 :
On a Node, Configure basic Cluster settings.
# authorize among nodes
[root@hyperv-primary-cos8 ~]# pcs host auth mysql01 mysql02 mysql03 -u hacluster -p MyStrongPassw0rd
mysql02: Authorized
mysql01: Authorized
mysql03: Authorized
[root@hyperv-primary-cos8 ~]#

# configure / create pacemaker-corosync cluster :
[root@hyperv-primary-cos8 ~]# pcs cluster setup routercluster mysql01 mysql02 mysql03
No addresses specified for host 'mysql01', using 'mysql01'
No addresses specified for host 'mysql02', using 'mysql02'
No addresses specified for host 'mysql03', using 'mysql03'
Destroying cluster on hosts: 'mysql01', 'mysql02', 'mysql03'...
mysql02: Successfully destroyed cluster
mysql03: Successfully destroyed cluster
mysql01: Successfully destroyed cluster
Requesting remove 'pcsd settings' from 'mysql01', 'mysql02', 'mysql03'
mysql01: successful removal of the file 'pcsd settings'
mysql02: successful removal of the file 'pcsd settings'
mysql03: successful removal of the file 'pcsd settings'
Sending 'corosync authkey', 'pacemaker authkey' to 'mysql01', 'mysql02', 'mysql03'
mysql01: successful distribution of the file 'corosync authkey'
mysql01: successful distribution of the file 'pacemaker authkey'
mysql02: successful distribution of the file 'corosync authkey'
mysql02: successful distribution of the file 'pacemaker authkey'
mysql03: successful distribution of the file 'corosync authkey'
mysql03: successful distribution of the file 'pacemaker authkey'
Sending 'corosync.conf' to 'mysql01', 'mysql02', 'mysql03'
mysql01: successful distribution of the file 'corosync.conf'
mysql02: successful distribution of the file 'corosync.conf'
mysql03: successful distribution of the file 'corosync.conf'
Cluster has been successfully set up.
[root@hyperv-primary-cos8 ~]#

We can now start the cluster (running the following command on one node):
[root@hyperv-primary-cos8 ~]# pcs cluster start --all
mysql02: Starting Cluster...
mysql01: Starting Cluster...
mysql03: Starting Cluster...
[root@hyperv-primary-cos8 ~]#

[root@hyperv-primary-cos8 ~]# pcs cluster status
Cluster Status:
Cluster Summary:
* Stack: corosync
* Current DC: mysql01 (version 2.0.3-5.el8_2.1-4b1f869f0f) - partition with quorum
* Last updated: Wed Sep 16 18:34:28 2020
* Last change: Wed Sep 16 18:19:10 2020 by hacluster via crmd on mysql01
* 3 nodes configured
* 0 resource instances configured
Node List:
* Online: [ mysql01 mysql02 mysql03 ]

PCSD Status:
mysql01: Online
mysql02: Online
mysql03: Online
[root@hyperv-primary-cos8 ~]#

Now we can set some properties to our cluster:
[root@hyperv-primary-cos8 ~]# pcs property set stonith-enabled=false
[root@hyperv-primary-cos8 ~]# pcs property set no-quorum-policy=ignore
[root@hyperv-primary-cos8 ~]# pcs resource defaults migration-threshold=1

Adding resources

VIP

The first resource we will use is the VIP (Virtual IP) that the application will use to connect to MySQL Router:

[root@hyperv-primary-cos8 ~]# ip a

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

valid_lft forever preferred_lft forever

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000

link/ether 00:15:5d:38:01:07 brd ff:ff:ff:ff:ff:ff

inet 192.168.0.22/24 brd 192.168.0.255 scope global noprefixroute eth0

valid_lft forever preferred_lft forever

inet6 fc00::1:122/64 scope global noprefixroute

valid_lft forever preferred_lft forever

inet6 fe80::77b3:112:420b:3363/64 scope link noprefixroute

valid_lft forever preferred_lft forever

[root@hyperv-primary-cos8 ~]#

[root@hyperv-primary-cos8 ~]# pcs resource create Router_VIP ocf:heartbeat:IPaddr2 \

> ip=192.168.0.11 cidr_netmask=24 op monitor interval=5s

[root@hyperv-primary-cos8 ~]# ip a

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

valid_lft forever preferred_lft forever

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000

link/ether 00:15:5d:38:01:07 brd ff:ff:ff:ff:ff:ff

inet 192.168.0.22/24 brd 192.168.0.255 scope global noprefixroute eth0

valid_lft forever preferred_lft forever

inet 192.168.0.11/24 brd 192.168.0.255 scope global secondary eth0

valid_lft forever preferred_lft forever

inet6 fc00::1:122/64 scope global noprefixroute

valid_lft forever preferred_lft forever

inet6 fe80::77b3:112:420b:3363/64 scope link noprefixroute

valid_lft forever preferred_lft forever

[root@hyperv-primary-cos8 ~]#

yum install -y mysql-router

[root@hyperv-primary-cos8 ~]# pcs resource create mysqlrouter systemd:mysqlrouter clone

pcs resource list | grep -i router
pcs resource list | grep -i mysql

[root@hyperv-primary-cos8 ~]# pcs resource list | grep -i postgres
ocf:heartbeat:pgsql - Manages a PostgreSQL database instance
[root@hyperv-primary-cos8 ~]#

Before adding the MySQL Router as resource, we need to be sure that the router is installed and bootstrapped on each nodes. As reminder, this is how to proceed:

MySQL-RouteR : BooTsTraP

password="Cluster^123"
mysql -e "create user 'mysqlrouter' identified by '$password'"

[root@hyperv-proxy-cos8 ~]# echo $password | mysqlrouter --bootstrap mycluster@mysql01 --user=mysqlrouter

mysql --host=192.168.0.22 --port=3306 --user=mycluster -p"Cluster^123"

Now we can add the resource that will manage MySQL Router in our pacemaker cluster. We will use systemd.

You can verify if systemd service for mysqlrouter is available using the following command:

[root@mysql1 ~]# pcs resource list | grep router
service:mysqlrouter - systemd unit file for mysqlrouter
systemd:mysqlrouter - systemd unit file for mysqlrouter

OK, so let’s add the resource and let’s it run on all nodes (clone), no need to start mysqlrouter only if the running one has issue:

[root@hyperv-primary-cos8 ~]# pcs resource create mysqlrouter systemd:mysqlrouter clone

Let’s verify if the resource is now added:

[root@hyperv-primary-cos8 ~]# crm_mon -1

Cluster Summary:

* Stack: corosync

* Current DC: mysql01 (version 2.0.3-5.el8_2.1-4b1f869f0f) - partition with quorum

* Last updated: Wed Sep 16 19:00:31 2020

* Last change: Wed Sep 16 19:00:27 2020 by root via cibadmin on mysql01

* 3 nodes configured

* 4 resource instances configured

Node List:

* Online: [ mysql01 mysql02 mysql03 ]

Active Resources:

* Router_VIP (ocf::heartbeat:IPaddr2): Started mysql01

* Clone Set: mysqlrouter-clone [mysqlrouter]:

* Started: [ mysql01 mysql02 mysql03 ]

[root@hyperv-primary-cos8 ~]#

Now we need to tell the cluster that the VIP needs to stand where a router instance also runs:

[root@hyperv-primary-cos8 ~]# pcs constraint colocation add Router_VIP \

> with mysqlrouter-clone score=INFINITY

[root@hyperv-primary-cos8 ~]#

Now we will kill mysqlrouter process on mysql01 and see what the cluster does:

https://lefred.be/content/mysql-router-ha-with-pacemaker/
https://www.server-world.info/en/note?os=CentOS_8&p=pacemaker&f=1
https://raw.githubusercontent.com/behemius/gcp01/master/node1_install.sh
https://raw.githubusercontent.com/behemius/gcp01/master/router_install.sh

[root@hyperv-primary-cos8 ~]#

[root@hyperv-primary-cos8 ~]# mysqlsh --sql -e "select * from performance_schema.replication_group_members\G" --user=mycluster -p"Cluster^123"

WARNING: Using a password on the command line interface can be insecure.

*************************** 1. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 3d17fb2b-f143-11ea-9c6b-00155d380107

MEMBER_HOST: hyperv-primary-cos8

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

MEMBER_ROLE: PRIMARY

MEMBER_VERSION: 8.0.17

*************************** 2. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 6a766bf2-f184-11ea-a7ad-00155d380109

MEMBER_HOST: hyperv-proxy-cos8

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

MEMBER_ROLE: SECONDARY

MEMBER_VERSION: 8.0.17

*************************** 3. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: ef479c0a-f183-11ea-b35a-00155d380108

MEMBER_HOST: hyperv-standby-cos8

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

MEMBER_ROLE: SECONDARY

MEMBER_VERSION: 8.0.17

[root@hyperv-primary-cos8 ~]#

[root@hyperv-primary-cos8 ~]# mysqlsh --sql -e "SELECT @@global.read_only, @@global.super_read_only, @@global.group_replication_group_seeds\G" --user=mycluster -p"Cluster^123"

WARNING: Using a password on the command line interface can be insecure.

*************************** 1. row ***************************

@@global.read_only: 0

@@global.super_read_only: 0

@@global.group_replication_group_seeds: 192.168.0.22:33061,192.168.0.24:33061,192.168.0.28:33061,hyperv-proxy-cos8:33061

[root@hyperv-primary-cos8 ~]#

[root@hyperv-primary-cos8 ~]# cat ./innodb-cluster-status.js

try {

shell.connect('mycluster@hyperv-primary-cos8', "Cluster^123");

print(dba.getCluster().status());

print(session.runSql("SHOW DATABASES;"));

} catch(e) {

print('\nThe InnoDB cluster could not be created.\n\nError: ' +

+ e.message + '\n');

}

[root@hyperv-primary-cos8 ~]#

[root@hyperv-primary-cos8 ~]# mysqlsh -f innodb-cluster-status.js

{

"clusterName": "mycluster",

"defaultReplicaSet": {

"name": "default",

"primary": "hyperv-primary-cos8:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"hyperv-primary-cos8:3306": {

"address": "hyperv-primary-cos8:3306",

"mode": "R/W",

"readReplicas": {},

"replicationLag": null,

"role": "HA",

"status": "ONLINE",

"version": "8.0.17"

"hyperv-proxy-cos8:3306": {

"address": "hyperv-proxy-cos8:3306",

"mode": "R/O",

"readReplicas": {},

"replicationLag": null,

"role": "HA",

"status": "ONLINE",

"version": "8.0.17"

"hyperv-standby-cos8:3306": {

"address": "hyperv-standby-cos8:3306",

"mode": "R/O",

"readReplicas": {},

"replicationLag": null,

"role": "HA",

"status": "ONLINE",

"version": "8.0.17"

}

"topologyMode": "Single-Primary"

"groupInformationSourceMember": "hyperv-primary-cos8:3306"

}<SqlResult>[root@hyperv-primary-cos8 ~]#

https://lefred.be/content/mysql-router-ha-with-pacemaker

https://lefred.be/content/mysql-innodb-cluster-is-the-router-a-single-point-of-failure/

https://www.server-world.info/en/note?os=CentOS_8&p=pacemaker&f=1

Clusters_from_Scratch.pdf

Java & Linux : Fishing in the Desert

Tuesday, September 22, 2020

MySQL 8 Router - High Availability - with Pacemaker on CentOS 8

Popular Posts