====== 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

{{..:installguide:pasted:20250313-101331.png?width=500}}

  * 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