Install MySQL

• The database will be deployed on two filesystems
  • One filesystem for database files
  • One filesystem for backups

• 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

• You can now move mysql default folder to its dedicated data filesystem
• See below example

systemctl stop mysqld

mkdir /agentil/mysql # The new DB folder
cp -rf /var/lib/mysql/* /agentil/mysql
mv /var/lib/mysql /var/lib/mysql.backup
chown -R mysql /agentil/mysql
chgrp -R mysql /agentil/mysql
ln -s /agentil/mysql /var/lib/mysql

• 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';

• Pro.Monitor 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'@'%';

Edit /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]"

• 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