Install MySQL

Prepare

The database will be deployed on two filesystems

One filesystem for database files

One filesystem for backups

Install

Download the adapted yum package: https://dev.mysql.com/downloads/repo/yum/

Make sure you select the one adapted to your Linux version (7/8)

Update yum repo to use the package

yum localinstall mysql80-community-release-el7-1.noarch.rpm
yum install mysql-community-server
systemctl enable mysqld
systemctl start mysqld

Having problems installing MySQL ? See troubleshooting section down below

Move database

Skip this step if you do not need to use a specific data filesystem

You can now move mysql default folder to its dedicated data filesystem

See below example

systemctl stop mysqld
mkdir /opt/mysql-data # The new DB folder
cp -a /var/lib/mysql/* /opt/mysql-data
mv /var/lib/mysql /var/lib/mysql.backup
chown -R mysql:mysql /opt/mysql-data
chgrp -R mysql /opt/mysql-data
ln -s /opt/mysql-data /var/lib/mysql
systemctl start mysqld

Start Database

Start the service

service mysqld start
or
systemctl start mysqld

Initialize root password:

mysqld --initialize

Root temporary password is printed in /var/log/mysqld.log file

Use it for your first log in

mysql -u root -p

Then change it

ALTER USER root@'localhost' IDENTIFIED BY 'xxxxxxxxx';

Create application databases manually

Those operations are no longer required, the tenant creation
wizard available within the cockpit admin portal will automate this.

Redpeaks cockpit will need at least 2 databases:

A MAIN database for global settings (You can call it MAIN by default)

One database per tenant. A tenant database will hold all the information of a particular tenant: Configuration, alarms, metrics and metadata

Example for MAIN database:

mysql --user=root -p
CREATE DATABASE MAIN;
USE MAIN;
CREATE USER 'superadmin'@'%' identified by 'XXXXXXXX';
GRANT ALL PRIVILEGES ON MAIN.* TO 'superadmin'@'%';

Example for TENANT database:

mysql --user=root -p
CREATE DATABASE TENANT;
USE TENANT;
CREATE USER 'tenantadmin'@'%' identified by 'XXXXXXXX';
GRANT ALL PRIVILEGES ON TENANT.* TO 'tenantadmin'@'%';

Configure Database global settings

Edit /etc/my.cnf or /etc/mysql/my.cnf and add below parameter

Replication logs

If replication is not configured, you can turn off the creation of binlogs

Add the below setting [mysqld] section

disable-log-bin

Sort max size : You need to increase the max size of sort buffer:

Add the below setting [mysqld] section

sort_buffer_size = 15M

Set password for mysqldump:

[mysqldump]
password="[SECRET]"

Configure backups

Backups will be triggered by using cron once a day at 10:00pm and keep the last 7 days

First create backup user (use same password than set in the my.cnf file for mysqldump):

CREATE USER 'backup'@'localhost' IDENTIFIED BY '[SECRET]';
GRANT SELECT, PROCESS ON *.* TO 'backup'@'localhost';

Create the file do_backups.sh in the dedicated backups filesystem:

#!/bin/sh
mysqldump -u root --single-transaction --databases [DB_NAME] > /[DB_FS]/[DB_NAME]_`date +%Y-%m-%d`.sql
gzip /[DB_FS]/[DB_NAME]_`date +%Y-%m-%d`.sql
find /pmlogs/mysql/backups -name "*" -mtime +7 -delete

Duplicate mysqldump and gzip command for each DB to backup

Do not install the script in the same folder than backup archive, or it will be removed by the cleanup script

Edit cron crontab -e

0 22 * * * /[SCRIPTS]/do_backups.sh

Troubleshooting

Installation

Sometimes even after having installed the rpm, yum won't find the mysql-community-server module

The reason can be that it is somehow hidden by an existing mysql module stream

You can disable it following below instructions:

yum module disable mysql

Check the module is now disabled

yum module list mysql

Output should no longer show [e] next to mysql X.X

Ensure the MySQL Yum Repository is correctly configured for your distribution (Ex: RHEL 8)

rpm -qa | grep mysql80-community

Expected output example: mysql80-community-release-el8-5.noarch

If not present, reinstall the correct repository package, by example:

wget https://dev.mysql.com/get/mysql80-community-release-el8-5.noarch.rpm
yum localinstall mysql80-community-release-el8-5.noarch.rpm

Refresh repository metadata and install the package

yum makecache
yum install mysql-community-server