User Tools

Site Tools


products:cockpit.old:1.0:installguide:installmysql

Install MySQL

Prepare

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

Install

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
/home/clients/8c48b436badcd3a0bdaaba8c59a54bf1/wiki-web/data/pages/products/cockpit.old/1.0/installguide/installmysql.txt · Last modified: 2024/05/01 18:39 (external edit)