You are here

Installation guide for the FromDual Performance Monitor for MariaDB and MySQL

Table of Contents

FromDual Performance Monitor as a Service (MaaS)

Important: If you want to install and use the FromDual Performance Monitor as a Service (MaaS) please check the FromDual Performance Monitor as a Service (MaaS) Installation Guide.

Back to ToC

FromDual Performance Monitor prerequisites

For using the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) you have to meet the following prerequisites:

  • A Zabbix Server and the Zabbix Web Interface (v2.0 or newer) must be installed and properly running. The Zabbix Server is only available for Linux/Windows systems. How to install Zabbix Server you can find here under Download and Install Zabbix.
  • A Zabbix Agent must be installed and running (v2.0 or newer), ideally on the database machine.
    Important: The FromDual Performance Monitor for MariaDB and MySQL was only tested on Linux. It may work on other Unixoides but most probably will NOT work on Windows yet. Please let us know if you plan to run fpmmm or fpmmm for MaaS on Windows...

Back to ToC

Installation of fpmmm on CentOS / Redhat / Fedora / Rocky Linux AlmaLinux

centos.png redhat.png rocky_logo.png almalinuxlogo.png oracle_linux.png

Adding FromDual RPM repository

Adding the FromDual RPM repository as described here. Then install fpmmm with the following command:

Installation of fpmmm from FromDual repository on CentOS 9 / Redhat 9 / Rocky Linux 9 / AlmaLinux 9 / Oracle Linux 9

Not tested yet! Please feel free to try with rhel8 package and let us know your results...

Installation of fpmmm from FromDual repository on CentOS 8 / Redhat 8 / Rocky Linux 8 / AlmaLinux 8 / Oracle Linux 8

rpm -Uvh https://repo.zabbix.com/zabbix/6.0/rhel/8/x86_64/zabbix-release-6.0-1.el7.noarch.rpm
dnf clean all
dnf install fpmmm

Testing fpmmm

Installation of fpmmm from FromDual repository on CentOS 7 / Redhat 7

Important: The CentOS 7 default PHP version is too old (5.4). Fpmmm needs PHP 7.0 and newer. How to achieve this is descried here.

rpm -Uvh https://repo.zabbix.com/zabbix/6.0/rhel/7/x86_64/zabbix-release-6.0-1.el7.noarch.rpm
yum clean all
yum check-update
yum install epel-release
yum install fpmmm

cat << _EOF >/etc/php.d/fpmmm.ini
variables_order = "EGPCS"
date.timezone = 'Europe/Zurich'
_EOF

Testing fpmmm

Back to ToC

Installation of fpmmm on Ubuntu

ubuntu.png

Adding FromDual Ubuntu DEB repository

Adding the FromDual DEB/APT repository as described here. Then install fpmmm with the following command:

Installation of fpmmm from FromDual repository on Ubuntu

apt install fpmmm

Testing fpmmm

Installation of fpmmm on Debian

debian.png

Adding FromDual Debian DEB repository

Adding the FromDual DEB/APT repository as described here. Then install fpmmm with the following command:

Installation of fpmmm from FromDual repository on Debian

apt install fpmmm

Testing fpmmm

Back to ToC

Installation of fpmmm from a tarball

If you are installing fpmmm from a tarball some preparation work has to be done before. If you were using DEB or RPM packages directly without a repository the same instructions apply.

The fpmmm can be downloaded from here.

Then proceed as follows:

cd /download
wget --no-check-certificate https://support.fromdual.com/admin/download/fpmmm-2.0.0.tar.gz
cd /opt
tar xf /download/fpmmm-2.0.0.tar.gz
ln -s fpmmm-2.0.0 fpmmm

Then the following packages have to be installed. They may be named differently depending on your distribution: php php-cli php-mysql php-mysqli php-mysqlnd php-process php-posix php-pcntl php-curl zabbix-sender zabbix-agent smartmontools dmidecode sudo epel-release php-pecl-ssh2 php-yaml pigz wget redhat-lsb-core.

Additionaly you have to set some PHP variables and add smartctl to the sudoers file if you want to make use of smartctl:

PHP_INI='/etc/php.d/20-fpmmm.ini'
PHP_INI='/etc/php/7.4/mods-available/fpmmm.ini'
PHP_INI='/etc/php/cli/conf.d/fpmmm.ini'
PHP_INI='/etc/php/conf.d/fpmmm.ini'
PHP_INI='/etc/php.d/fpmmm.ini'

cat << _EOF >${PHP_INI}
variables_order = "EGPCS"
date.timezone = 'Europe/Zurich'
_EOF

phpenmod fpmmm

cat << _EOF >/etc/sudoers.d/fpmmm
root ALL=(root) NOPASSWD: /usr/sbin/smartctl
_EOF

Back to ToC

Testing fpmmm

php --version   # Should be at least 7.0
/opt/fpmmm/bin/fpmmm --version
/opt/fpmmm/bin/fpmmm --config=/etc/fpmmm.conf
1
cat /var/log/fpmmm/fpmmm.log

Back to ToC

Configuration of fpmmm

Copy the Configuration File Template if it is not already at the right location:

cp /opt/fpmmm/tpl/fpmmm.conf.template /etc/fpmmm.conf
chown zabbix: /etc/fpmmm.conf
chmod 600 /etc/fpmmm.conf

Prepare the fpmmm.conf according to your needs. A typical Configuration File for the FromDual Performance Monitor looks as follows:

# Put everything related to fpmmm agent/daemon itself into this section
[self]

# How long fpmmm should sleep when run as daemon
Interval      = 60
MyPidFile     = /run/lock/fpmmm/fpmmm.pid


# Put everything in here which belongs to all instances/machines
[default]

# Type of section:
# instance for a normal MariaDB/MySQL database
# machine for a server
# Type          = instance

# LogLevels are:
# 1 is logging only errors
# 2 is logging errors and warnings (this is the default)
# 3 logs some informations messages as well
# 4 logs everything (for debugging purposes only)

LogLevel      = 2
LogFile       = /var/log/fpmmm/fpmmm.log
# Directory where the cache files should be written to:
CacheFileBase = /var/cache/fpmmm/fpmmm
# Currently supported formats are zabbix, nagios, icinga, centreon and snmp, default is zabbix
# OutputFormat  = zabbix
AgentLockFile = /run/lock/fpmmm/fpmmm.lock

# If you agent is not located in the same time zone as your server
# For US something like -6.0 to our MaaS Server
# For Asia something like +7.0 to our MaaS Server
# TimeShift     = +0.0
TimeZone      = Europe/Zurich

# Zabbix Server IP address
ZabbixServer  = 192.168.1.42

# Can be one of: minimal, nomal, extensive
DataGathering = normal


# Here you could enter the hostname of your machine (uname -n)
[my_machine]          # This MUST match Hostname in Zabbix!

Type          = machine
Modules       = fpmmm server smart


# All MySQL databases here
# Try to avoid section names with spaces!

# Here you could add a random name of your MySQL/MariaDB instance
[my_mysqld]          # This MUST match Hostname in Zabbix!

Type          = instance

Username      = fpmmm_agent
Password      = secret
MysqlHost     = 127.0.0.1
MysqlPort     = 3306

MysqlPort     = 3306
Modules       = process mysql myisam innodb master slave security data user
DbPidFile     = /var/run/mysqld/mysqld.pid

There is typically a default section ([default]) where you can specify all the defaults for all the hosts to monitor (machines and database instances).

For every database instance you want to monitor you have to add an extra section. This allows you to monitor several MariaDB/MySQL databases running on the same machine.

Important: The section name MUST match the Hostname which you have entered in the Zabbix Server!!!

Back to ToC

Create a Monitoring User in your MariaDB/MySQL instance

For security reason it is a good idea to NOT work with the database user root or mysql. This user needs some preveleges to gather information from the database:

SQL> CREATE USER 'fpmmm_agent'@'127.0.0.1' IDENTIFIED BY 'secret';

SQL> GRANT PROCESS ON *.* TO 'fpmmm_agent'@'127.0.0.1';
SQL> GRANT REPLICATION CLIENT ON *.* TO 'fpmmm_agent'@'127.0.0.1';
SQL> GRANT SELECT ON `mysql`.`user` TO 'fpmmm_agent'@'127.0.0.1';
SQL> GRANT REPLICATION SLAVE ON *.* TO 'fpmmm_agent'@'127.0.0.1';

SQL> GRANT SELECT ON performance_schema.events_statements_summary_by_user_by_event_name TO fpmmm_agent@'127.0.0.1';
SQL> GRANT SELECT ON performance_schema.events_statements_current TO fpmmm_agent@'127.0.0.1';
SQL> GRANT SELECT ON performance_schema.setup_instruments TO fpmmm_agent@'127.0.0.1';
SQL> -- Does not work on MariaDB 10.3 (Rocky Linux 8 / Oracle Linux 8)
SQL> GRANT SELECT ON performance_schema.metadata_locks TO fpmmm_agent@'127.0.0.1';
SQL> GRANT SELECT ON performance_schema.threads TO fpmmm_agent@'127.0.0.1';

SQL> -- For the security module
SQL> GRANT SELECT ON mysql.user TO 'fpmmm_agent'@'127.0.0.1';

SQL> -- MariaDB only
SQL> INSTALL PLUGIN metadata_lock_info SONAME 'metadata_lock_info.so';

SQL> -- MariaDB 10.5 and newer
SQL> GRANT SELECT ON mysql.global_priv TO fpmmm_agent@'127.0.0.1';
SQL> GRANT REPLICATION MASTER ADMIN ON *.* TO 'fpmmm_agent'@'127.0.0.1';
SQL> GRANT SLAVE MONITOR ON *.* TO 'fpmmm_agent'@'127.0.0.1';

Back to ToC

Adding the fpmmm Temlates to Zabbix

Choose the templates you need. The template fpmmm is mandatory. All other templates are optional but you have to load them if you want to monitor some specific storage engines or features.
If you do not want to think about install ALL templates. A typical good choice of templates for the machine (host) is: fpmmm and server and for the a normal MariaDB/MySQL database itself: process, mysql, myisam and innodb.
If you have a Master/Slave Replication running you should add also the Master and the Slave templates.

Load the Templates (tpl/templates_*/*.xml) into the Zabbix Monitor (leave all the checkboxes as they are): Configuration → Templates → Import → Select the module → Import.

There should not be any conflict with other rules because a different name space was used for the FromDual modules.

Back to ToC

Creating Zabbix Host Groups

We found, that it is a good idea to create a separate Host Group for your MariaDB/MySQL databases. But you are free to have even a more granular separation or to have just one Host Group.

We miss-use these Host Groups for separating our Databases and our Machines. So the name could be a bit miss-leading how we used it for our purposes. We do not only monitor Hosts but also Databases/Instances.

Adding a Zabbix Host Group:

Configuration → Host Groups → Create host group → then enter a group name for example as follows: Database Server and MariaDB/MySQL

Zoom

Back to ToC

Creating Hosts on your Zabbix Server

A Host in Zabbix terminology is a device you want to monitor. This can be either a Machine or a MariaDB/MySQL Database Instance (mysqld) or something else.

For every Database Instance you want to monitor you have to create 2 Hosts, one for the Machine and one for the Database Instance: Configuration → Hosts → Creating host.

Then enter a UNIQUE Name for this Host (= machine or database). Be very careful with choosing the Name (in other places also called Hostname). The Name is the identifier for authenticating the Zabbix Agent to the Zabbix Server and is the marker where the data are stored. If the names do not match, the data cannot be sent!

Make sure that Host name matches exactly the hostname specified in fpmmm.conf (my_machine and my_mysqld).

Add this Host to the right Group, add the IP address and Link at least the Template tpl/templates_*/Template_FromDual.MySQL.fpmmm from the Templates tab to the machine host (my_machine) (and possibly some others you have chosen above) and the other templates to the Database Host (my_mysqld). Then save the changes.

Example for the Server:

...

Example for the Database:

...

Zoom

Then check again if your fpmmm data reaches your Zabbix Server: Monitoring → Latest data → fpmmm and check the time stamp of Last check for example.

Back to ToC

Enable the Host for monitoring on Zabbix Server

When you have done all the steps above you can enable the monitored Database Server by clicking on its Not monitored status. In the availability you should see if a certain host is monitored and if not why...

Zoom

The most common reasons why an agent is not monitored are:

  • Hostname on Zabbix Server and Section in FromDual Performance Monitor Agent configuration file do NOT match.
  • Agent is down or not reachable (firewall, etc.).

To see if data is sent and what the actual values are you can go to:

Monitoring → Latest data → then filter by group and host:

mpm_inst_guide04.png

Regularly running the fpmmm Agent

There are 2 different possibilities to run fpmmm regularly.
First by the Zabbix Agent:

Put the following line either into its own file (/etc/zabbix/conf.d/fpmmm.conf) or add it to the Zabbix Agent configuartion file (/etc/zabbix/zabbix_agentd.conf).

#
# /etc/zabbix/zabbix_agentd.conf
#

UserParameter=FromDual.MySQL.check,/opt/fpmmm/bin/fpmmm --config=/etc/fpmmm.conf

Restart the Zabbix agent afterwards: systemctl restart zabbix-agentd.

or second by the crontab:

* * * * * /opt/fpmmm/bin/fpmmm --config=/etc/fpmmm.conf >/dev/null

Back to ToC

Special requirements for specific modules

Special requirements for the DRBD module

If the FromDual Performance Monitor Agent is running under a different user than root not all information will be displayed correctly because of missing privileges.

You either have to run the FromDual Performance Monitor Agent under root (security!) or grant the appropriate rights to the user the Agent is running.

Back to ToC

For more information look also into our Forum or our Bug database.

Please let us know if there are some errors in this FromDual Performance Monitor Installation Guide or if something is not described clear enough!