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

 
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 ~]#

[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 ~]#

[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

How to generate self-signed SSL certificates

 Generate a root private key (rootCA.key):

openssl genrsa -out rootCA.key 2048

Generate a self-singed root certificate (rootCA.pem):

openssl req -x509 -new -nodes -key rootCA.key -days 1024 -out rootCA.pem

Create a private key for the final certificate (dovecot.key):

openssl genrsa -out dovecot.key 2048

Create a certificate sign request (dovecot.csr):

openssl req -new -key dovecot.key -out dovecot.csr

Create a certificate based on the root CA certificate and the root private key (dovecot.crt):

openssl x509 -req -in dovecot.csr -CA rootCA.pem -CAkey rootCA.key -CAcreateserial -out dovecot.crt -days 500

Copy the private key and the certificate to the /etc/dovecot/private/ directory

cp dovecot.crt /etc/dovecot/private/dovecot.crt

# cp dovecot.key /etc/dovecot/private/dovecot.key

Set the required permissions on the file:

chmod 400 /etc/dovecot/private/dovecot.crt

# chmod 400 /etc/dovecot/private/dovecot.key

Update paths to the key and the certificate in the Dovecot configuration file /etc/dovecot/dovecot.conf:

ssl_cert = < /etc/dovecot/private/dovecot.crt

ssl_key =  </etc/dovecot/private/dovecot.key

Restart the Dovecot service to apply the changes:

service dovecot restart

Systemd - systemctl - Services Management

systemctl list-units
This will show you a list of all of the units that systemd currently has active on the system.

systemctl list-units --all
This will show any unit that systemd loaded or attempted to load, regardless of its current state on the system. Some units become inactive after running, and some units that systemd attempted to load may have not been found on disk.

systemctl list-units --all --state=inactive
systemctl list-units --type=service

systemctl list-unit-files
Units are representations of resources that systemd knows about. Since systemd has not necessarily read all of the unit definitions in this view, it only presents information about the files themselves. The output has two columns: the unit file and the state.

systemctl cat atd.service
systemctl list-dependencies sshd.service
systemctl show sshd.service

sudo systemctl edit nginx.service
This will be a blank file that can be used to override or add directives to the unit definition. A directory will be created within the /etc/systemd/system directory which contains the name of the unit with .d appended. For instance, for the nginx.service, a directory called nginx.service.d will be created.

https://www.digitalocean.com/community/tutorials/how-to-use-systemctl-to-manage-systemd-services-and-units

AWS EC2 : Dockerize Java App & Deploy Container

https://youtu.be/ToqoJ8YAkRg

Dockerize app and deploy in EC2 instance
Push the docker image to Docker Hub public repository

Steps:
1. Setting up docker engine

sudo yum update -y

sudo amazon-linux-extras install docker

sudo service docker start

sudo usermod -a -G docker ec2-user


2. Dockerize your application

-- Dockerfile
FROM java:8
WORKDIR /
ADD myapp.jar myapp.jar
COPY application.properties application.properties
EXPOSE 5000
CMD java -jar myapp.jar -Dspring.config.location=application.properties

3. Building docker image of application:

docker build -t myapp .


4. Tagging image

docker tag myapp nirajtechi/myapp


5. Overriding port while launching docker container

docker run -p 8080:5000 nirajtechi/myapp


6. Push image to repository (Docker Hub)
docker push nirajtechi/myapp

7. Pull image from repository (Docker Hub)
docker pull nirajtechi/myapp

Java Microservices : Quarkus vs. Spring Boot

Nowadays, with the Microservice Architecture, perhaps it does not make sense anymore, nor any advantage, build something multi-platform (interpreted) for something that will always run on the same place and platform (the Docker Container — Linux environment). Portability is now less relevant (maybe more than ever), those extra level of abstraction is not important.

Having said that, let's check a simple and raw comparison between two alternatives to generate Microservices in Java: the very well-known Spring Boot and the not so very well-know (yet) Quarkus.

https://dzone.com/articles/microservices-quarkus-vs-spring-boot

https://thorntail.io/posts/thorntail-community-announcement-on-quarkus/

Thorntail Community Announcement on Quarkus

The team will continue contributing to SmallRye and Eclipse MicroProfile, while also shifting work towards Quarkus in the future . . . 

https://thorntail.io/

Thorntail has announced end of life. Everything else on this site is now outdated. We recommend using Quarkus or WildFly.

Thorntail offers an innovative approach to packaging and running Java EE applications by packaging them with just enough of the server runtime to "java -jar" your application. It's MicroProfile compatible, too. And, it's all much, much cooler than that ...

https://wildfly.org/news/2020/06/18/Bootable-jar-Wildfly-20/

Getting started

In order to help you start using the org.wildfly.plugins:wildfly-jar-maven-plugin Maven plugin, we have defined a set of examples that cover common use-cases.

To retrieve the examples:

git clone -b 2.0.0.Alpha4 http://github.com/wildfly-extras/wildfly-jar-maven-plugin

cd wildfly-jar-maven-plugin/examples

A good example to start with is the jaxrs example. To build and run the jaxrs example:

cd jaxrs

mvn package

java -jar target/jaxrs-wildfly.jar

The plugin documentation (currently an index.html file to download) can be found here. It contains a comprehensive list of the options you can use to fine tune the Maven build and create a bootable JAR.

Be sure to read the examples/README that contains required information to run the examples in an OpenShift context.

Using Kubernetes to Deploy PostgreSQL

PostgreSQL kubectl YAML

Config Maps :

===============================

File: postgres-configmap.yaml

---------------------------

apiVersion: v1

kind: ConfigMap

metadata:

  name: postgres-config

  labels:

    app: postgres

data:

  POSTGRES_DB: postgresdb

  POSTGRES_USER: postgresadmin

  POSTGRES_PASSWORD: admin123

Create Postgres config maps resource

---------------------------

$ kubectl create -f postgres-configmap.yaml 

configmap "postgres-config" created

Persistent Storage Volume

===============================

File: postgres-storage.yaml

---------------------------

kind: PersistentVolume

apiVersion: v1

metadata:

  name: postgres-pv-volume

  labels:

    type: local

    app: postgres

spec:

  storageClassName: manual

  capacity:

    storage: 5Gi

  accessModes:

    - ReadWriteMany

  hostPath:

    path: "/mnt/data"

---

kind: PersistentVolumeClaim

apiVersion: v1

metadata:

  name: postgres-pv-claim

  labels:

    app: postgres

spec:

  storageClassName: manual

  accessModes:

    - ReadWriteMany

  resources:

    requests:

      storage: 5Gi

Create storage related deployments

---------------------------

$ kubectl create -f postgres-storage.yaml 

persistentvolume "postgres-pv-volume" created

persistentvolumeclaim "postgres-pv-claim" created

PostgreSQL Deployment

===============================

File: postgres-deployment.yaml

--------------------------------

apiVersion: extensions/v1beta1

kind: Deployment

metadata:

  name: postgres

spec:

  replicas: 1

  template:

    metadata:

      labels:

        app: postgres

    spec:

      containers:

        - name: postgres

          image: postgres:10.4

          imagePullPolicy: "IfNotPresent"

          ports:

            - containerPort: 5432

          envFrom:

            - configMapRef:

                name: postgres-config

          volumeMounts:

            - mountPath: /var/lib/postgresql/data

              name: postgredb

      volumes:

        - name: postgredb

          persistentVolumeClaim:

            claimName: postgres-pv-claim

Create Postgres deployment

--------------------------------

$ kubectl create -f postgres-deployment.yaml 

deployment "postgres" created

PostgreSQL Service

===============================

File: postgres-service.yaml

--------------------------------

apiVersion: v1

kind: Service

metadata:

  name: postgres

  labels:

    app: postgres

spec:

  type: NodePort

  ports:

   - port: 5432

  selector:

   app: postgres

Create Postgres Service

$ kubectl create -f postgres-service.yaml 

service "postgres" created

Connect to PostgreSQL

$ kubectl get svc postgres

NAME       TYPE       CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE

postgres   NodePort   10.107.71.253   <none>        5432:31070/TCP   5m

psql -h localhost -U postgresadmin1 --password -p 31070 postgresdb

Delete PostgreSQL Deployments

# kubectl delete service postgres 

# kubectl delete deployment postgres

# kubectl delete configmap postgres-config

# kubectl delete persistentvolumeclaim postgres-pv-claim

# kubectl delete persistentvolume postgres-pv-volume

Other Kubernetes usefuL Links : 

Running Galera Cluster on Kubernetes

https://severalnines.com/database-blog/running-galera-cluster-kubernetes

Example: Deploying WordPress and MySQL with Persistent Volumes

https://kubernetes.io/docs/tutorials/stateful-application/mysql-wordpress-persistent-volume/

MicroK8s, Part 3: How To Deploy a Pod in Kubernetes

https://virtualizationreview.com/articles/2019/02/01/microk8s-part-3-how-to-deploy-a-pod-in-kubernetes.aspx

Using Kubernetes to Deploy PostgreSQL

https://severalnines.com/database-blog/using-kubernetes-deploy-postgresql

Setting up PostgreSQL Database on Kubernetes

https://medium.com/@suyashmohan/setting-up-postgresql-database-on-kubernetes-24a2a192e962

Getting started with Docker and Kubernetes: a beginners guide

https://www.educative.io/blog/docker-kubernetes-beginners-guide

https://github.com/vfarcic/k8s-specs/blob/master/pod/db.yml

MariaDB in the penguin container of ChromeOS

I've got a Chromebook ; Installed MariaDB in penguin container :

apt install mysql-server
apt install mysql-workbench
systemctl status mariadb

https://discuss.linuxcontainers.org/t/using-lxd-on-your-chromebook/3823

https://www.maketecheasier.com/run-ubuntu-container-chrome-os/