Database initialization

• Initialize database on your server
• Initialize database using Docker
• Initialize database on Kubernetes
• Creating a multimaster MariaDB replica
• Configuring database cluster

Initialize database on your server

The purpose of this tutorial is to show how to initialize a database required for Soffid IAM installation.

Prerequisites

First of all, you sould install a database required in the Soffid IAM installation.

The supported databases are:

• MySQL
• MariaDB
• PostgreSQL
• Oracle 
• Microsoft SqlServer

MySQL/MariaDB

In order to configure MySQL database you need access to the database administration tool (mysql) with superuser permissions using a TCP/IP connection. If needed, please create a user for the Soffid installation. If you don't have such a user, or don't know its password, please access MySQL as root, execute the mysql tool and create the user with grant command (where ADMIN_USER is the user to be used during the installation process to create the soffid repository database and ADMIN_PASSWORD is the required password).

create database soffid;
use soffid;
grant all privileges on *.* to ADMIN_USER@localhost identified by 'ADMIN_PASSWORD' with grant option;

In addition, in order to be able to manage big files, like process definitions or software add-ons, we have to modify the max_allowed_packet parameter on MySQL. This parameter is commonly located on the /etc/mysql/my.cnf file.

You can find the default option file locations on Linux, Unix, Mac or Windows following this link. 

If the version of MariaDB is 10.1.38, or newer, the recommended value for max_allowed_packet  is 512M

Note: in the case, we will obtain the next 'The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.' error when trying to upload an addon, we may update the default value of this mysql/mariadb parameter. This parameter is commonly allocated on the /etc/mysql/my.cnf file.

If you are installing on a Ubuntu 18.04 server, the default character set is set to utf8mb4. Using this character set can cause problems, as many index sizes will exceed the maximum key size of 767 bytes. To prevent this problem, change the following settings:

Alternatively, if UTF character set is required, write the following  settings:

Following this link you will find the steps to set up a two nodes database cluster.

Oracle

A new database instance should be created. Optionally two tablespaces should be created (SOFFID_DATA and SOFFID_INDEX) to separate soffid tables and indexes.

CREATE TABLESPACE SOFFID_DATA DATAFILE '/app/oracle/oradata/project/soffid_data.dbf' SIZE 200M EXTENT MANAGEMENT LOCAL AUTOALLOCATE

To create the tablespace is necessary to provide the full path name, its size and MANAGEMENT AUTOALLOCATE option. The autoallocate option is needed because the tables are not sized by database creation scripts. Also, the Oracle Listener must have a TCP/IP port accepting connections.

Microsoft SQLServer

You must enable the SQL Server Browser Service at startup and the authentication method have to be set to “SQL Server and Windows Authentication mode”.

In addition, you must ensure that 'READ_COMMITTED_SNAPSHOT" parameter is enabled, you can do so with the following command: 

ALTER DATABASE [database_name] SET READ_COMMITTED_SNAPSHOT ON

Initialize database using Docker

The purpose of this tutorial is to show how to initialize a database MariaDB required for Soffid IAM installation using Docker.

Prerequisites

1. Install docker ( https://docs.docker.com/install/ )

2. Create a docker network, that network allows you to connect containers to the same bridge network to communicate:

sudo docker network create -d bridge NETWORKNAME

For the correct installation of Soffid it is recommended not to use the underline character _ in the network name.

sudo docker exec -i -t ID_CONTAINER  /bin/bashMySQL/MariaDB

First step will be initialize MariaDB with Docker, in this case we attach the container to an exist network:

sudo docker run -d --name mariadb-service  --network=NETWORKNAME -e "MYSQL_ROOT_PASSWORD=ADMIN_PASSWORD" mariadb

Second, you can check the deployed containers:

sudo docker ps

Then, you must connect to the created container:

sudo docker exec -i -t mariadb-service  /bin/bash

In order to configure MySQL database you need access to the database administration tool (mysql) with superuser permissions using a TCP/IP connection. If needed, please create a user for the Soffid installation. If you don't have such a user, or don't know its password, please access MySQL as root, execute the mysql tool and create the user with grant command (where ADMIN_USER is the user to be used during the installation process to create the soffid repository database and ADMIN_PASSWORD is the required password).

Coonect to MySQL:

mysql -u root -p

Create database and grant permissions:

create database soffid;
use soffid;
grant all privileges on *.* to ADMIN_USER@'%' identified by 'ADMIN_PASSWORD' with grant option;

In addition, in order to be able to manage big files, like process definition or software addons, we have to modify max_allowed_packet parameter on MySQL. This parameter is commonly allocated on the /etc/mysql/my.cnf file.

If the version of MariaDB is 10.1.38, or newer, the recommended value for max_allowed_packet  is 512M

Note: in the case we will obtain the next 'The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.' error trying to upload an addon, we may update the default value of this mysql/mariadb parameter. This parameter is commonly allocated on the /etc/mysql/my.cnf file.

If you are installing on a Ubuntu 18.04 server, default character set is set to utf8mb4. Using this character set can cause problems, as many index sizes will exceed maximum key size of 767 bytes. To prevent this problem, change following settings:

Alternatively, if UTF character set is required, write the following  settings:

Following this link  you will find the steps to setup a two nodes database cluster.

Video Tutorial

MariaDB initialization using Docker

Initialize database on Kubernetes

The purpose of this tutorial is to show how to initialize a MariaDB database required for Soffid IAM installation on Kubernetes.

MySQL/MariaDB

To initialize MariaDB on Kubernetes first of all you must create a Persistent Volume. Storage in the cluster will be provisioned using Storage Classes.

apiVersion: v1
kind: PersistentVolume
metadata:
  name: local-pv3
spec:
  capacity:
    storage: 10Gi
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Retain
  storageClassName: local-storage
  local:
    path: /home/ulocal/kubernetes-disk3
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - soffid123
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mariadb-claim3
spec:
  storageClassName: local-storage
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 5Gi

Path "/home/ulocal/kubernetes-disk3" must be exists.

Then you must define the MariaDB deployment:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mariadb3
  labels:
    app: soffid
    instance: "Soffid-3"
    type: database
spec:
  strategy:
    rollingUpdate:
      maxSurge: 0
      maxUnavailable: 1
    type: RollingUpdate
  replicas: 1
  selector:
    matchLabels:
      app: soffid
      instance: "Soffid-3"
      type: database
  template:
    metadata:
      labels:
        app: soffid
        instance: "Soffid-3"
        type: database
    spec:
      restartPolicy: Always
      containers:
        - name: mariadb3
          image: mariadb
          resources:
            limits:
              memory: 2Gi
            requests:
              memory: 400Mi
          args:
            - "--max-allowed-packet=175M"
            - "--innodb-log-file-size=256M"
            - "--character-set-server=utf8"
            - "--collation-server=utf8_bin"
            - "--net-read-timeout=3600"
            - "--net-write-timeout=3600"
            - "--innodb-buffer-pool-size=100M"
          ports:
            - containerPort: 3306
              name: db-port
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mariadb
                  key: root_password
            - name: MYSQL_USER
              valueFrom:
                secretKeyRef:
                  name: mariadb
                  key: username
            - name: MYSQL_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mariadb
                  key: password
            - name: MYSQL_DATABASE
              value: soffid
          volumeMounts:
          - name: mysql-persistent-storage3
            mountPath: /var/lib/mysql
          
      volumes:
        - name: mysql-persistent-storage3
          persistentVolumeClaim:
            claimName: mariadb-claim3
---
apiVersion: v1
kind: Service
metadata:
  name: mariadb3-service
  namespace: default
spec:
  clusterIP: None
  ports:
  - name: mariadb
    port: 3306
    protocol: TCP
    targetPort: 3306
  selector:
    app: soffid
    instance: "Soffid-3"
    type: database
  type: ClusterIP

Finally you must create resources in a cluster:

kubectl apply -f mariadb-pv-file.yaml
kubectl apply -f mariadb-deployment-file.yaml

Video Tutorial

MariaDB initialization in Kubernetes

Creating a multimaster MariaDB replica

This topic will cover the process to create a two node Maria DB cluster. The cluster will be configured to allow Soffid console to use either database node, which in turn will replicate data changes to the other one.

service mysql restart

service mysql restart

Now, configure the Console to use the following jdbc URL:

jdbc:mariadb:sequential://mariadb-host-1,mariadb-host-2/soffid

Configuring database cluster

Once the database replica is setup, it's important to guarantee transactianality rules. To achive it, one database instance must be acting as the master and other as the slave.

Using corosync and pacemaker, you can configure a floating IP address that will mark which one is the active one at each moment.