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

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

  * 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'@'%';

===== Configure Database global settings =====

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

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