韓誥傭痔

Table of Contents

4. Monitor MySQL with 韓誥傭痔 agent 2

4. Monitor MySQL with 韓誥傭痔 agent 2

Introduction

This page walks you through the steps required to start basic monitoring of a MySQL server.

To monitor a MySQL server, there are several approaches: 韓誥傭痔 agent, 韓誥傭痔 agent 2, or the Open Database Connectivity (ODBC) standard. The primary focus of this guide is on monitoring a MySQL server with 韓誥傭痔 agent 2, which is the recommended approach due to its seamless configuration across various setups. However, this page also offers instructions for the other approaches, so feel free to choose the one that best suits your requirements.

Who this guide is for

This guide is designed for new 韓誥傭痔 users and contains the minimum set of steps required to enable basic monitoring of a MySQL server. If you are looking for deep customization options or require more advanced configuration, see the Configuration section of 韓誥傭痔 manual.

Prerequisites

Before proceeding with this guide, you need to download and install 韓誥傭痔 server, 韓誥傭痔 frontend and 韓誥傭痔 agent 2 according to the instructions for your OS.

Based on your setup, some of the steps in this guide may slightly differ. This guide is based on the following setup:

韓誥傭痔 version: 韓誥傭痔 6.4 (installed from packages)
OS distribution: Ubuntu
OS version: 22.04 (Jammy)
韓誥傭痔 components: Server, Frontend, Agent 2
Database: MySQL
Web server: Apache

Creating a MySQL user

To monitor a MySQL server, 韓誥傭痔 requires access to it and its processes. Your MySQL installation already has a user with the required level of access (the user "zabbix" that was created when installing 韓誥傭痔), however, this user has more privileges than necessary for simple monitoring (privileges to DROP databases, DELETE entries from tables, etc.). Therefore, a MySQL user for the purpose of only monitoring the MySQL server needs to be created.

1. Connect to the MySQL client, create a "zbx_monitor" user (replace <password> for the "zbx_monitor" user with a password of your choice), and the necessary privileges to the user:

mysql -u root -p
       # Enter password:
       
       mysql> CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';
       mysql> GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';
       mysql> quit;

Once the user is created, you can move on to the next step.

Configuring 韓誥傭痔 frontend

1. Log in to 韓誥傭痔 frontend.

2. Create a host in 韓誥傭痔 web interface:

In the Name field, enter a host name (e.g., "MySQL server").
In the Templates field, type or select the template "MySQL by 韓誥傭痔 agent 2" that will be linked to the host.
In the Host groups field, type or select a host group (e.g., "Databases").
In the Interfaces field, add an interface of type "Agent" and specify your MySQL server IP address. This guide uses "127.0.0.1" (localhost) for monitoring a MySQL server that is installed on the same machine as 韓誥傭痔 server and 韓誥傭痔 agent 2.

In the Macros tab, switch to Inherited and host macros, look for the following macros and click on Change next to the macro value to update it:
- {$MYSQL.DSN} - set the data source of the MySQL server (the connection string of a named session from the MySQL 韓誥傭痔 agent 2 plugin configuration file). This guide uses the default data source "tcp://localhost:3306" for monitoring a MySQL server that is installed on the same machine as 韓誥傭痔 server and 韓誥傭痔 agent 2.
- {$MYSQL.PASSWORD} - set the password of the previously created MySQL user "zbx_monitor".
- {$MYSQL.USER} - set the name of the previously created MySQL user "zbx_monitor".

3. Click on Add to add the host. This host will represent your MySQL server.

Viewing collected metrics

Congratulations! At this point, 韓誥傭痔 is already monitoring your MySQL server.

To view collected metrics, navigate to the Monitoring ↙ Hosts menu section and click on Dashboards next to the host.

This action will take you to the host dashboard (configured on the template level) with the most important metrics collected from the MySQL server.

Alternatively, from the Monitoring ↙ Hosts menu section, you can click on Latest data to view all the latest collected metrics in a list. Note that the item MySQL: Calculated value of innodb_log_file_size is expected to have no data, as the value will be calculated from data in the last hour.

Setting up problem alerts

韓誥傭痔 can notify you about a problem with your infrastructure using a variety of methods. This guide provides basic configuration steps for sending email alerts.

1. Navigate to User settings ↙ Profile, switch to the Media tab and add your email.

2. Follow the guide for Receiving a problem notification.

Next time, when 韓誥傭痔 detects a problem, you should receive an alert via email.

Testing your configuration

To test your configuration, we can simulate a real problem by updating the host configuration in 韓誥傭痔 frontend.

1. Open your MySQL server host configuration in 韓誥傭痔.

2. Switch to the Macros tab and select Inherited and host macros.

3. Click on Change next to, for example, the previously configured {$MYSQL.USER} macro value and set a different MySQL user name.

4. Click on Update to update the host configuration.

5. In a few moments, 韓誥傭痔 will detect the problem "MySQL: Service is down", because it will not be able to connect to the MySQL server. The problem will appear in Monitoring ↙ Problems.

If alerts are configured, you will also receive the problem notification.

6. Change the macro value back to its previous value to resolve the problem and continue monitoring the MySQL server.

Other approaches to monitoring MySQL

Instead of monitoring a MySQL server with 韓誥傭痔 agent 2, you could also use 韓誥傭痔 agent or the Open Database Connectivity (ODBC) standard. While using 韓誥傭痔 agent 2 is recommended, there might be some setups that do not support 韓誥傭痔 agent 2 or require a custom approach.

The key difference between 韓誥傭痔 agent and ODBC lies in the data collection method - 韓誥傭痔 agent is installed directly on the MySQL server and collects data using its built-in functionality, while ODBC relies on an ODBC driver to establish a connection to the MySQL server and retrieve data using SQL queries.

Although many of the configuration steps are similar to monitoring a MySQL server with 韓誥傭痔 agent 2, there are some significant differences - you need to configure 韓誥傭痔 agent or ODBC to be able to monitor a MySQL server. The following instructions walk you through these differences.

Monitor MySQL with 韓誥傭痔 agent

To monitor a MySQL server with 韓誥傭痔 agent, you need to download and install 韓誥傭痔 server, 韓誥傭痔 frontend and 韓誥傭痔 agent according to the instructions for your OS.

Once you have successfully installed the required 韓誥傭痔 components, you need to create a MySQL user as described in the Creating a MySQL user section.

After you have created the MySQL user, you need to configure 韓誥傭痔 agent to be able to establish a connection with the MySQL server and monitor it. This includes configuring multiple user parameters for executing custom agent checks, as well as providing 韓誥傭痔 agent with the necessary credentials for connecting to the MySQL server as the previously created "zbx_monitor" user.

Configuring 韓誥傭痔 agent

1. Navigate to the 韓誥傭痔 agent additional configurations directory.

cd /usr/local/etc/zabbix/zabbix_agentd.d

The 韓誥傭痔 agent additional configurations directory should be located in the same directory as your 韓誥傭痔 agent configuration file (zabbix_agentd.conf). Depending on your OS and 韓誥傭痔 installation, this directory can have a different location than specified in this guide. For default locations, check the Include parameter in the 韓誥傭痔 agent configuration file.

Instead of defining all of the necessary user parameters for monitoring the MySQL server in the 韓誥傭痔 agent configuration file, these parameters will be defined in a separate file in the additional configurations directory.

2. Create a template_db_mysql.conf file in the 韓誥傭痔 agent additional configurations directory.

vi template_db_mysql.conf

3. Copy the contents from the file (located in the 韓誥傭痔 repository) to the template_db_mysql.conf file you created, and save.

4. Restart 韓誥傭痔 agent to update its configuration.

systemctl restart zabbix-agent

Once you have configured 韓誥傭痔 agent user parameters, you can move on to configure the credentials that will allow 韓誥傭痔 agent to access the MySQL server.

5. Navigate to the 韓誥傭痔 agent home directory (if it does not exist on your system, you need to create it; default: /var/lib/zabbix).

cd /var/lib/zabbix

6. Create a .my.cnf file in the 韓誥傭痔 agent home directory.

vi .my.cnf

7. Copy the following contents to the .my.cnf file (replace <password> with the password of the "zbx_monitor" user).

[client]
       user='zbx_monitor'
       password='<password>'

Configuring 韓誥傭痔 frontend and testing your configuration

To configure 韓誥傭痔 frontend, follow the instructions in the Configuring 韓誥傭痔 frontend section with the following adjustments:

In the Templates field, type or select the template "MySQL by 韓誥傭痔 agent" that will be linked to the host.
Configuring Macros is not required.

Once you have configured 韓誥傭痔 frontend, you can view collected metrics and set up problem alerts.

To test your configuration, follow the instructions in the Testing your configuration section with the following adjustments:

In the Inherited and host macros section of the MySQL server host configuration, click on Change next to the {$MYSQL.PORT} macro value and set a different port (e.g., "6033").

Monitor MySQL with ODBC

To monitor a MySQL server with ODBC, you need to download and install 韓誥傭痔 server and 韓誥傭痔 frontend.

Once you have successfully installed the required 韓誥傭痔 components, you need to create a MySQL user as described in the Creating a MySQL user section.

After you have created the MySQL user, you need to setup ODBC. This includes installing one of the most commonly used open source ODBC API implementations - - and a unixODBC driver, as well as editing the ODBC driver configuration file.

Configuring ODBC

1. Install unixODBC. The suggested way of installing unixODBC is to use the Linux operating system default package repositories.

apt install unixodbc

2. Install the MariaDB unixODBC database driver. Although you have a MySQL database, the MariaDB unixODBC driver is used for compatibility issues.

apt install odbc-mariadb

3. Check the location of the ODBC configuration files odbcinst.ini and odbc.ini.

odbcinst -j

The result of executing this command should be similar to the following.

unixODBC 2.3.9
       DRIVERS............: /etc/odbcinst.ini
       SYSTEM DATA SOURCES: /etc/odbc.ini
       FILE DATA SOURCES..: /etc/ODBCDataSources
       ...

4. To configure the ODBC driver for monitoring a MySQL database, you need the driver name, which is located in the odbcinst.ini file. In the following odbcinst.ini file example, the driver name is "MariaDB Unicode".

[MariaDB Unicode]
       Driver=libmaodbc.so
       Description=MariaDB Connector/ODBC(Unicode)
       Threading=0
       UsageCount=1

5. Copy the following contents to the odbc.ini file (replace <password> with the password of the "zbx_monitor" user). This guide uses "127.0.0.1" (localhost) as the MySQL server address for monitoring a MySQL server that is installed on the same machine as the ODBC driver. Note the data source name (DSN) "test", which will be required when configuring 韓誥傭痔 frontend.

[test]
       Driver=MariaDB Unicode
       Server=127.0.0.1
       User=zbx_monitor
       Password=<password>
       Port=3306
       Database=zabbix