Konfigurácia a inštalácia databázy MySQL

V tomto návode použijeme databázu MySQL, pracujeme s verziou MySQL server 8.0.21
https://www.mysql.com/products/community/

Pracujeme pod používateľom “root”

dnf makecache
dnf -y install mysql-server
systemctl enable mysqld
systemctl start mysqld

Pokračujeme základnými nastaveniami:

mysql_secure_installation

Pre zjednodušenie prístupu vytvoríme nasledovný súbor:

vim /root/.my.cnf

S obsahom:

[client]
user=root
password="Heslo-do-mysql"

Heslo-do-mysql = Heslo, ktoré sme zvolili pri procese mysql_secure_installation

Nastavíme oprávnenia:

chmod go-rwx /root/.my.cnf

Pre zobrazenie informácii o verzii zadáme príkaz:

mysqladmin -u root -p version

Je potrebné nastaviť v konfigurácii /etc/my.cnf.d/mysql-server.cnf predvolené časové pásmo: default-time-zone='+00:00'.

vim /etc/my.cnf.d/mysql-server.cnf

#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

default-time-zone='+00:00'

Po zmene a uložení konfigurácie reštartujeme službu:

systemctl restart mysqld

Prístup do príkazového riadku klienta:

mysql

Pre opustenie príkazového riadku klienta zvolíme “exit”.

Vytvorenie databázy a používateľa

Prihlásime sa do príkazového riadku klienta. Spúšťame príkazy pre vytvorenie databázy a používateľa, ktorému nastavíme silné heslo.

mysql

SET GLOBAL default_storage_engine = 'InnoDB';

SET NAMES 'utf8';
SET CHARACTER SET utf8;
CHARSET utf8;
CREATE DATABASE IF NOT EXISTS shibboleth CHARACTER SET=utf8;
CREATE USER 'shibboleth'@'localhost' IDENTIFIED BY 'Silné-heslo-pre-používateľa-shibboleth';
GRANT ALL ON shibboleth.* TO 'shibboleth'@'localhost';
FLUSH PRIVILEGES;

Môžeme skontrolovať pomocou:

mysql> SELECT user FROM mysql.user;
mysql> SHOW DATABASES;

Vytvoríme novú tabuľku v databáze shibboleth:

USE shibboleth;
CREATE TABLE IF NOT EXISTS `shibpid` (
  `localEntity` VARCHAR(255) NOT NULL,
  `peerEntity` VARCHAR(255) NOT NULL,
  `principalName` VARCHAR(255) NOT NULL DEFAULT '',
  `localId` VARCHAR(255) NOT NULL,
  `persistentId` VARCHAR(50) NOT NULL,
  `peerProvidedId` VARCHAR(255) DEFAULT NULL,
  `creationDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deactivationDate` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (localEntity, peerEntity, persistentId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Môžeme skontrolovať pomocou:

mysql> USE shibboleth
mysql> DESCRIBE shibpid;

Výstup by mal zodpovedať:

+------------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field            | Type         | Null | Key | Default           | Extra                                         |
+------------------+--------------+------+-----+-------------------+-----------------------------------------------+
| localEntity      | varchar(255) | NO   | PRI | NULL              |                                               |
| peerEntity       | varchar(255) | NO   | PRI | NULL              |                                               |
| principalName    | varchar(255) | NO   |     |                   |                                               |
| localId          | varchar(255) | NO   |     | NULL              |                                               |
| persistentId     | varchar(50)  | NO   | PRI | NULL              |                                               |
| peerProvidedId   | varchar(255) | YES  |     | NULL              |                                               |
| creationDate     | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| deactivationDate | timestamp    | YES  |     | NULL              |                                               |
+------------------+--------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.01 sec)

V databáze shibboleth ďalej vytvoríme tabuľku “StorageRecords”:

StorageConfiguration - ClientStorageService
https://wiki.shibboleth.net/confluence/display/IDP4/StorageConfiguration#StorageConfiguration-ClientStorageService

USE shibboleth;
CREATE TABLE IF NOT EXISTS `StorageRecords` (
  `context` VARCHAR(255) NOT NULL,
  `id` VARCHAR(255) NOT NULL,
  `expires` BIGINT(20) DEFAULT NULL,
  `value` longtext NOT NULL,
  `version` BIGINT(20) NOT NULL,
  PRIMARY KEY (`context`,`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Výstup by mal zodpovedať:

mysql> DESCRIBE StorageRecords;

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| context | varchar(255) | NO   | PRI | NULL    |       |
| id      | varchar(255) | NO   | PRI | NULL    |       |
| expires | bigint       | YES  |     | NULL    |       |
| value   | longtext     | NO   |     | NULL    |       |
| version | bigint       | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows

MySQL JDBC connector

MySQL Connector/J is the official JDBC driver for MySQL.
MySQL Connector/J 8.0 is compatible with all MySQL versions starting with MySQL 5.6. Additionally,
MySQL Connector/J 8.0 supports the new X DevAPI for development with MySQL Server 8.0.
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-versions.html

Changes in the Connector/J API

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-api-changes.html

MySQL Connector/J nebudeme inštalovať pomocou správcu balíkov “dnf”, stiahneme ho zabalený v archíve.

MySQL Community Downloads: https://dev.mysql.com/downloads/connector/j/

V návode ďalej pracujeme s verziou mysql-connector-java-8.0.22

Vybrali sme: Platform Independent (Architecture Independent), Compressed TAR Archive, (mysql-connector-java-8.0.22.tar.gz)
https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.22.tar.gz

Stiahneme a rozbalíme archív s konektorom:

wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.22.tar.gz
tar -xzf mysql-connector-java-8.0.22.tar.gz

Skopírujeme konektor do adresára Jetty /opt/jetty/lib/ext/ a nastavíme oprávnenia pre “idp”:

cd mysql-connector-java-8.0.22
cp mysql-connector-java-8.0.22.jar /opt/jetty/lib/ext/
chown idp:idp /opt/jetty/lib/ext/mysql-connector-java-8.0.22.jar

Reštartujeme Jetty:

systemctl restart jetty

Môžete pokračovať Konfigurácia IdP