# Database initialization

How to install and initialize database

# Initialize database on your server

<p class="callout info">The purpose of this tutorial is to show how to initialize a database required for Soffid IAM installation.</p>

## 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.

<p class="callout info">You can find the [default option file locations on Linux, Unix, Mac or Windows following this link](https://mariadb.com/kb/en/configuring-mariadb-with-option-files/). </p>

<div data-hasbody="true" data-macro-name="code" id="bkmrk-%5Bmysqld%5D-max_allowed"><div><div><div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td class="code"><div><div>`[mysqld]`</div><div>`max_allowed_packet=128M`</div></div></td></tr></tbody></table>

</div></div></div></div><p class="callout success">If the version of MariaDB is 10.1.38, or newer, the recommended value for max\_allowed\_packet<span lang="en"> </span> is 512M</p>

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.

<div data-hasbody="true" data-macro-name="code" id="bkmrk-%5Bmysqld%5D-innodb_log_"><div><div><div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td class="code"><div><div>`[mysqld]`</div><div>`innodb_log_file_size=256M`</div></div></td></tr></tbody></table>

</div></div></div></div>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:

<div data-hasbody="true" data-macro-name="code" id="bkmrk-%5Bmysqld%5D-character-s"><div><div><div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td class="code"><div><div>`[mysqld]`</div><div>`character-set-server  = Latin1`</div><div>`collation-server      = Latin1_general_ci`</div></div></td></tr></tbody></table>

</div></div></div></div>Alternatively, if UTF character set is required, write the following settings:

<div data-hasbody="true" data-macro-name="code" id="bkmrk-%5Bmysqld%5D-character-s-0"><div><div><div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td class="code"><div><div>`[mysqld]`</div><div>`character-set-server  = utf8mb4`</div><div>`collation-server      = utf8mb4_general_ci`</div><div>`innodb_large_prefix   = ``1`</div><div>`innodb_file_format    = Barracuda`</div><div>`innodb_file_per_table = ``1`</div></div></td></tr></tbody></table>

</div></div></div></div><p class="callout info">Following [this link](https://bookstack.soffid.com/books/installation/page/creating-a-multimaster-mariadb-replica-2b4) you will find the steps to set up a two nodes database cluster.</p>

## 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.

```shell
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:

```shell
ALTER DATABASE [database_name] SET READ_COMMITTED_SNAPSHOT ON
```

# Initialize database using Docker

<p class="callout info">The purpose of this tutorial is to show how to initialize a database **MariaDB** required for Soffid IAM installation using Docker.</p>

## Prerequisites

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

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

```shell
sudo docker network create -d bridge NETWORKNAME
```

<p class="callout warning">For the correct installation of Soffid it is recommended not to use the underline character \_ in the network name.</p>

## 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:

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

Second, you can check the deployed containers:

```shell
sudo docker ps
```

Then, you must connect to the created container:

```shell
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:

```western
mysql -u root -p
```

Create database and grant permissions:

```shell
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.

<div data-hasbody="true" data-macro-name="code" id="bkmrk-%5Bmysqld%5D-max_allowed"><div><div><div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td class="code"><div><div>`[mysqld]`</div><div>`max_allowed_packet=128M`</div></div></td></tr></tbody></table>

</div></div></div></div><p class="callout success">If the version of MariaDB is 10.1.38, or newer, the recommended value for max\_allowed\_packet<span lang="en"> </span> is 512M</p>

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.

<div data-hasbody="true" data-macro-name="code" id="bkmrk-%5Bmysqld%5D-innodb_log_"><div><div><div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td class="code"><div><div>`[mysqld]`</div><div>`innodb_log_file_size=256M`</div></div></td></tr></tbody></table>

</div></div></div></div>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:

<div data-hasbody="true" data-macro-name="code" id="bkmrk-%5Bmysqld%5D-character-s"><div><div><div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td class="code"><div><div>`[mysqld]`</div><div>`character-set-server  = Latin1`</div><div>`collation-server      = Latin1_general_ci`</div></div></td></tr></tbody></table>

</div></div></div></div>Alternatively, if UTF character set is required, write the following settings:

<div data-hasbody="true" data-macro-name="code" id="bkmrk-%5Bmysqld%5D-character-s-0"><div><div><div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td class="code"><div><div>`[mysqld]`</div><div>`character-set-server  = utf8mb4`</div><div>`collation-server      = utf8mb4_general_ci`</div><div>`innodb_large_prefix   = ``1`</div><div>`innodb_file_format    = Barracuda`</div><div>`innodb_file_per_table = ``1`</div></div></td></tr></tbody></table>

</div></div></div></div>Following [this link ](https://confluence.soffid.com/display/SOF/Soffid+3%3A+Creating+a+multi-master+MariaDB+cluster) you will find the steps to setup a two nodes database cluster.

### Video Tutorial

#### MariaDB initialization using Docker

<iframe allowfullscreen="allowfullscreen" height="314" src="https://www.youtube.com/embed/mDJeSRbrn7w" width="560"></iframe>

# Initialize database on Kubernetes

<p class="callout info">The purpose of this tutorial is to show how to initialize a **MariaDB** database required for Soffid IAM installation on Kubernetes.</p>

## 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.

```YAML
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
```

<p class="callout warning">Path "/home/ulocal/kubernetes-disk3" must be exists.</p>

Then you must define the MariaDB deployment:

```YAML
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:

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

### Video Tutorial

#### MariaDB initialization in Kubernetes

<iframe allowfullscreen="allowfullscreen" height="314" src="//www.youtube.com/embed/_F6p8JlurXs?rel=0" width="560"></iframe>

# 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.

<div id="bkmrk-node-1-action-node-2"><table border="1" class="confluenceTable tablesorter tablesorter-default" role="grid" style="width: 769px; height: 4015px;"><thead><tr class="tablesorter-headerRow" role="row" style="height: 29px;"><th aria-disabled="false" aria-label="Node 1 action: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="0" role="columnheader" scope="col" style="height: 29px; width: 416px;" tabindex="0"><div>Node 1 action</div></th><th aria-disabled="false" aria-label="Node 2 action: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="1" role="columnheader" scope="col" style="height: 29px; width: 404px;" tabindex="0"><div>Node 2 action</div></th></tr></thead><tbody aria-live="polite" aria-relevant="all"><tr role="row" style="height: 29px;"><td class="confluenceTd" style="height: 29px; width: 416px;">**Create and setup a Maria DB in node 1.**</td><td class="confluenceTd" style="height: 29px; width: 404px;">  
</td></tr><tr role="row" style="height: 211px;"><td class="confluenceTd" style="height: 211px; width: 416px;">Configure Maria DB to generate binary log files. Add the following lines to /etc/mysql/my.cnf:

<span style="color: #000080;">*server-id = **1** log-bin binlog-format=row expire\_logs\_days = 15 max\_binlog\_size = 1000M replicate-ignore-table = soffid.SC\_SEQUENCE slave-skip-errors = 1032,1053,1062*</span>

</td><td class="confluenceTd" style="height: 211px; width: 404px;">  
</td></tr><tr role="row" style="height: 77px;"><td class="confluenceTd" style="height: 77px; width: 416px;">Restart MariaDB:

```
service mysql restart
```

</td><td class="confluenceTd" style="height: 77px; width: 404px;"></td></tr><tr role="row" style="height: 29px;"><td class="confluenceTd" style="height: 29px; width: 416px;">  
</td><td class="confluenceTd" style="height: 29px; width: 404px;">**Create and setup a Maria DB in node 2.**</td></tr><tr role="row" style="height: 211px;"><td class="confluenceTd" style="height: 211px; width: 416px;">  
</td><td class="confluenceTd" style="height: 211px; width: 404px;">Configure Maria DB to generate binary log files. Add the following lines to /etc/mysql/my.conf:

<span style="color: #000080;">*server-id = **2** log-bin binlog-format=row expire\_logs\_days = 15 max\_binlog\_size = 1000M replicate-ignore-table = soffid.SC\_SEQUENCE slave-skip-errors = 1032,1053,1062*</span>

</td></tr><tr><td style="width: 416px;">  
</td><td style="width: 404px;">Restart MariaDB:

```
service mysql restart
```

</td></tr><tr role="row" style="height: 77px;"><td class="confluenceTd" style="height: 77px; width: 416px;">Dump current database contents:

<span style="color: #333399;">*mysqldump soffid -u soffid -p &gt;soffid.data*</span>

</td><td class="confluenceTd" style="height: 77px; width: 404px;">Load current database contents

<span style="color: #333399;">*mysql -u soffid -p &lt; soffid.data*</span>

</td></tr><tr role="row" style="height: 123px;"><td class="confluenceTd" style="height: 123px; width: 416px;">  
</td><td class="confluenceTd" style="height: 123px; width: 404px;">Create a user for node 1 to fetch data from node 2. From mysql, execute:

<span style="color: #000080;">*grant replication slave on \*.\* to [replication\_user@](mailto:replication_user@logpmgid02v.toolfactory.net)&lt;NODE1-IP&gt;*</span>

<span style="color: #000080;">*set password for [replication\_user@1](mailto:replication_user@192.168.2.171)&lt;NODE1-IP&gt; = password('&lt;NODE1-PASS&gt;')*</span>

</td></tr><tr role="row" style="height: 123px;"><td class="confluenceTd" style="height: 123px; width: 416px;">Create a user for node 2 to fetch data from node 1. From mysql, execute:

<span style="color: #000080;">*grant replication slave on \*.\* to [replication\_user@](mailto:replication_user@logpmgid02v.toolfactory.net)&lt;NODE2-IP&gt;*</span>

<span style="color: #000080;">*set password for [replication\_user@1](mailto:replication_user@192.168.2.171)&lt;NODE2-IP&gt; = password('&lt;NODE2-PASS&gt;')*</span>

</td><td class="confluenceTd align-left" style="height: 123px; width: 404px; padding-left: 40px;">  
</td></tr><tr role="row" style="height: 199px; padding-left: 40px;"><td class="confluenceTd" style="height: 199px; width: 416px; padding-left: 40px;">Query current binary log position:

MariaDB \[(none)\]&gt; *<span style="color: #000080;">show master status</span>;*

The result should look like this:

<div><table class="confluenceTable tablesorter tablesorter-default" role="grid" style="height: 75px; width: 362px;"><thead><tr class="tablesorter-headerRow" role="row" style="height: 30px;"><th aria-disabled="false" aria-label="File: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="0" role="columnheader" scope="col" style="width: 88px; height: 30px;" tabindex="0"><div>File</div></th><th aria-disabled="false" aria-label="Position: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="1" role="columnheader" scope="col" style="width: 69px; height: 30px;" tabindex="0"><div>Position</div></th><th aria-disabled="false" aria-label="Binlog_Do_DB: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="2" role="columnheader" scope="col" style="width: 109px; height: 30px;" tabindex="0"><div>Binlog_Do_DB</div></th><th aria-disabled="false" aria-label="Binlog_Ignore_DB: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="3" role="columnheader" scope="col" style="width: 96px; height: 30px;" tabindex="0"><div>Binlog_Ignore_DB</div></th></tr></thead><tbody aria-live="polite" aria-relevant="all"><tr role="row" style="height: 45px;"><td class="confluenceTd" style="width: 88px; height: 45px;">mysqld-bin.000030</td><td class="confluenceTd" style="width: 69px; height: 45px;">68175</td><td class="confluenceTd" style="width: 109px; height: 45px;">  
</td><td class="confluenceTd" style="width: 96px; height: 45px;">  
</td></tr></tbody></table>

</div>The got values will be used on node 2 to start replica process.

</td><td class="confluenceTd" style="height: 199px; width: 404px;">  
</td></tr><tr role="row" style="height: 255px;"><td class="confluenceTd" style="height: 255px; width: 416px;">  
</td><td class="confluenceTd" style="height: 255px; width: 404px;">Start replication from node 1 to node 2. From mysql, execute the following sentence, replacing proper values:

<span style="color: #000080;">*CHANGE MASTER TO MASTER\_HOST='&lt;NODE1-IP&gt;', MASTER\_USER='replication\_user', MASTER\_PASSWORD='&lt;NODE2-PASS&gt;', MASTER\_PORT=3306, MASTER\_LOG\_FILE='&lt;NODE1-FILE&gt;' , /\*\* i.e. mysql-bin.000030 \*\*/ MASTER\_LOG\_POS=&lt;NODE1-POSITION&gt;, /\*\* i.e. 68175 \*\*/ MASTER\_CONNECT\_RETRY=10;*</span>

</td></tr><tr role="row" style="height: 145px;"><td class="confluenceTd" style="height: 145px; width: 416px;">  
</td><td class="confluenceTd" style="height: 145px; width: 404px;">Verify replica is working right, by executing

<span style="color: #000080;">*SHOW SLAVE STATUS \\G*</span>

Check following lines:

Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
Seconds\_Behind\_Master: 0

</td></tr><tr role="row" style="height: 199px;"><td class="confluenceTd" style="height: 199px; width: 416px;">  
</td><td class="confluenceTd" style="height: 199px; width: 404px;">Query current binary log position:

MariaDB \[(none)\]&gt; *show master status;*

The result should look like this:

<div><table class="confluenceTable tablesorter tablesorter-default" role="grid" style="width: 373px;"><thead><tr class="tablesorter-headerRow" role="row"><th aria-disabled="false" aria-label="File: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="0" role="columnheader" scope="col" style="width: 88px;" tabindex="0"><div>File</div></th><th aria-disabled="false" aria-label="Position: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="1" role="columnheader" scope="col" style="width: 69px;" tabindex="0"><div>Position</div></th><th aria-disabled="false" aria-label="Binlog_Do_DB: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="2" role="columnheader" scope="col" style="width: 109px;" tabindex="0"><div>Binlog_Do_DB</div></th><th aria-disabled="false" aria-label="Binlog_Ignore_DB: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="3" role="columnheader" scope="col" style="width: 107px;" tabindex="0"><div>Binlog_Ignore_DB</div></th></tr></thead><tbody aria-live="polite" aria-relevant="all"><tr role="row"><td class="confluenceTd" style="width: 88px;">mysqld-bin.000060</td><td class="confluenceTd" style="width: 69px;">98325</td><td class="confluenceTd" style="width: 109px;">  
</td><td class="confluenceTd" style="width: 107px;">  
</td></tr></tbody></table>

</div>The got values will be used on node 1 to start replica process.

</td></tr><tr role="row" style="height: 255px;"><td class="confluenceTd" style="height: 255px; width: 416px;">Now, start replication from node 2 to node 1. From mysql, execute the following sentence, replacing proper values:

<span style="color: #000080;">*CHANGE MASTER TO MASTER\_HOST='&lt;NODE2-IP&gt;', MASTER\_USER='replication\_user', MASTER\_PASSWORD='&lt;NODE1-PASS&gt;', MASTER\_PORT=3306, MASTER\_LOG\_FILE='&lt;NODE2-FILE&gt;', /\*\* i.e. mysql-bin.000060 \*\*/ MASTER\_LOG\_POS=&lt;NODE2-POSITION&gt;, /\*\* i.e. 98325 \*\*/ MASTER\_CONNECT\_RETRY=10;*</span>

</td><td class="confluenceTd" style="height: 255px; width: 404px;">  
</td></tr><tr role="row" style="height: 145px;"><td class="confluenceTd" style="height: 145px; width: 416px;">Verify replica is working right, by executing

<span style="color: #000080;">*SHOW SLAVE STATUS \\G*</span>

Check following lines:

Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
Seconds\_Behind\_Master: 0

</td><td class="confluenceTd" style="height: 145px; width: 404px;"></td></tr><tr role="row" style="height: 189px;"><td class="confluenceTd" style="height: 189px; width: 416px;">Now, create and start SC\_SEQUENCE table in node 1. This sequence will generate values 1, 11, 21, 31, 41, and so on:

<span style="color: #000080;">*CREATE TABLE `SC\_SEQUENCE` (*</span>  
<span style="color: #000080;">*`SEQ\_NEXT` bigint(20) NOT NULL,*</span>  
<span style="color: #000080;">*`SEQ\_CACHE` bigint(20) NOT NULL,*</span>  
<span style="color: #000080;">*`SEQ\_INCREMENT` bigint(20) NOT NULL*</span>

<span style="color: #000080;">*);*</span>

<span style="color: #000080;">*INSERT INTO SC\_SEQUENCE VALUES (1, 100, 10);*</span>

</td><td class="confluenceTd" style="height: 189px; width: 404px;">  
</td></tr><tr role="row" style="height: 189px;"><td class="confluenceTd" style="height: 189px; width: 416px;">  
</td><td class="confluenceTd" style="height: 189px; width: 404px;">Now, create and start SC\_SEQUENCE table in node 2. This sequence will generate values 2, 12, 22, 32, 42, and so on::

<span style="color: #000080;">*CREATE TABLE `SC\_SEQUENCE` (*</span>  
<span style="color: #000080;">*`SEQ\_NEXT` bigint(20) NOT NULL,*</span>  
<span style="color: #000080;">*`SEQ\_CACHE` bigint(20) NOT NULL,*</span>  
<span style="color: #000080;">*`SEQ\_INCREMENT` bigint(20) NOT NULL*</span>

<span style="color: #000080;">*);*</span>

<span style="color: #000080;">*INSERT INTO SC\_SEQUENCE VALUES (2, 100, 10);*</span>

</td></tr></tbody></table>

</div>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.

<div id="bkmrk-node-1-node-2-instal"><table class="confluenceTable tablesorter tablesorter-default stickyTableHeaders" role="grid"><thead class="tableFloatingHeaderOriginal"><tr class="tablesorter-headerRow" role="row"><th aria-disabled="false" aria-label="Node 1: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="0" role="columnheader" scope="col" tabindex="0"><div>Node 1</div></th><th aria-disabled="false" aria-label="Node 2: No sort applied, activate to apply an ascending sort" aria-sort="none" class="confluenceTh tablesorter-header sortableHeader tablesorter-headerUnSorted" data-column="1" role="columnheader" scope="col" tabindex="0"><div>Node 2</div></th></tr></thead><thead class="tableFloatingHeader"></thead><tbody aria-live="polite" aria-relevant="all"><tr role="row"><td class="confluenceTd">Install Corosync and Pacemaker. It is recommended to use **apt** or **yum** because these programs will handle dependencies for you, making the process much easier.</td><td class="confluenceTd">Install Corosync and Pacemaker.</td></tr><tr role="row"><td class="confluenceTd">Cluster nodes need a key in order to authenticate the packages sent between them by corosync.

*sudo corosync-keygen*

Once the key has been generated, copy it to the other nodes:

*sudo scp /etc/corosync/authkey &lt;user&gt;@&lt;other-cluster-node&gt;:/home/&lt;user&gt;*</td><td class="confluenceTd">  
</td></tr><tr role="row"><td class="confluenceTd">  
</td><td class="confluenceTd">Once the key has been copied, move the copied key from the */home/&lt;user&gt;* route to */etc/corosync/authkey*</td></tr><tr role="row"><td class="confluenceTd">Now we need to tell Corosync which IP to use to communicate with other nodes in the cluster.  
Open */etc/corosync/corosync.conf* and edit the bindnetaddr field. Set the right IP and save the file.  
We need to do this in every node in the cluster, although you can use the same file if you set the right name in your hosts file.</td><td class="confluenceTd">  
</td></tr><tr role="row"><td class="confluenceTd">  
</td><td class="confluenceTd">Configure Corosync with the right IP binding as done in node 1.</td></tr><tr role="row"><td class="confluenceTd">Configure the */etc/default/corosync* file to enable Corosync changing START to yes "START=yes".  
Then we can start Corosync using *sudo service corosync start*.</td><td class="confluenceTd">  
</td></tr><tr role="row"><td class="confluenceTd">  
</td><td class="confluenceTd">Enable Corosync and start it as in node 1.</td></tr><tr role="row"><td class="confluenceTd">Allow the nodes a few seconds to start, then you can monitor the cluster nodes using sudo crm\_mon. The result should be similar to this:

*============*  
*Last updated: Mon Mar 31 14:05:23 2015*  
*Stack: corosync*  
*Current DC: yourDC - partition with quorum*  
*Version: 1.x.x-yourversion*  
*2 Nodes configured, 2 expected votes*  
*0 Resources configured.*  
*============*

*Online: \[ node1 node2 \]*

</td><td class="confluenceTd">  
</td></tr><tr role="row"><td class="confluenceTd">  
</td><td class="confluenceTd">Check the nodes with sudo crm\_mon</td></tr><tr role="row"><td class="confluenceTd">Corosync is ready, now we will tell Pacemaker which resources we want it to handle in HA. These will be the database and a virtual IP (VIP) we will use to address the cluster.

Add the VIP to the node, and then use this to create the resource:  
*sudo crm configure primitive FAILOVER-ADDR [ocf:heartbeat:IPaddr2](http://ocfheartbeatipaddr2/) params ip="your.virtual.IP" nic="your.network.device" op monitor interval="10s" meta is-managed="true"*

You can check the result using *sudo crm status*, which should look something like:

*Last updated: Wed Jan 18 10:21:12 2017 Last change: Tue Jan 17 13:08:25 2017 by hacluster via crmd on nodename*  
*Stack: corosync*  
*Current DC: nodename(version 1.1.14-70404b0) - partition with quorum*  
*2 nodes and 2 resources configured*

*Online: \[ node1 node2 \]*

*Full list of resources:*

*Resource Group: my\_cluster*  
*FAILOVER-ADDR (ocf::heartbeat:IPaddr2): Started node2*

</td><td class="confluenceTd">  
</td></tr><tr role="row"><td class="confluenceTd">Now we will add the database. We will use:

*sudo crm configure primitive FAILOVER-MARIADB lsb::mysql op monitor interval=15s*

</td><td class="confluenceTd">  
</td></tr><tr role="row"><td class="confluenceTd">  
</td><td class="confluenceTd">  
</td></tr></tbody></table>

</div>