[comment]: # aside:5 [comment]: # ({13a65fe6-b4128477}) # Monitor MySQL with Zabbix agent 2 [comment]: # ({/13a65fe6-b4128477}) [comment]: # ({ad3dab41-2185307b}) #### 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: Zabbix agent, Zabbix agent 2, or the Open Database Connectivity (ODBC) standard. The primary focus of this guide is on monitoring a MySQL server with Zabbix 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](#other-approaches-to-monitor-mysql), so feel free to choose the one that best suits your requirements. [comment]: # ({/ad3dab41-2185307b}) [comment]: # ({0cf7feb5-831aae47}) **Who this guide is for** This guide is designed for new Zabbix 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](/manual/config) section of Zabbix manual. [comment]: # ({/0cf7feb5-831aae47}) [comment]: # ({519621df-2fd1df9d}) **Prerequisites** Before proceeding with this guide, you need to [download and install](https://www.zabbix.com/download) Zabbix server, Zabbix frontend and Zabbix agent 2 according to the instructions for your OS. Depending on your environment, some steps in this guide may slightly differ. This guide is based on an environment running Ubuntu. [comment]: # ({/519621df-2fd1df9d}) [comment]: # ({8ae78277-ae0ef261}) #### Create MySQL user To monitor a MySQL server, Zabbix 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 Zabbix), 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 ** for the "zbx_monitor" user with a password of your choice), and [GRANT](https://dev.mysql.com/doc/refman/8.0/en/grant.html) the necessary privileges to the user: mysql -u root -p # Enter password: mysql> CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY ''; 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. [comment]: # ({/8ae78277-ae0ef261}) [comment]: # ({7d9d17aa-e6607c21}) #### Configure Zabbix frontend 1\. Log into Zabbix frontend. 2\. [Create a host](/manual/config/hosts/host) in Zabbix web interface: - In the *Host name* field, enter a host name (e.g., "MySQL server"). - In the *Templates* field, type or select the template "MySQL by Zabbix agent 2" that will be [linked](/manual/config/templates/linking) 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 Zabbix server and Zabbix agent 2. ![](../../../assets/en/manual/guides/mysql_host.png){width="600"} - 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](/manual/appendix/config/zabbix_agent2_plugins/mysql_plugin#parameters) from the MySQL Zabbix 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 Zabbix server and Zabbix agent 2. - {$MYSQL.PASSWORD} - set the password of the previously [created MySQL user](#create-mysql-user) "zbx_monitor". - {$MYSQL.USER} - set the name of the previously [created MySQL user](#create-mysql-user) "zbx_monitor". ![](../../../assets/en/manual/guides/mysql_macros.png){width="600"} 3\. Click on *Add* to add the host. This host will represent your MySQL server. [comment]: # ({/7d9d17aa-e6607c21}) [comment]: # ({bdcc4c70-339fa505}) #### View collected metrics Congratulations! At this point, Zabbix is already monitoring your MySQL server. To view collected metrics, navigate to the [*Monitoring → Hosts*](/manual/web_interface/frontend_sections/monitoring/hosts) menu section and click on *Dashboards* next to the host. ![](../../../assets/en/manual/guides/mysql_hosts.png){width="600"} This action will take you to the host dashboard (configured on the template level) with the most important metrics collected from the MySQL server. ![](../../../assets/en/manual/guides/mysql_dashboard.png){width="600"} Alternatively, from the [*Monitoring → Hosts*](/manual/web_interface/frontend_sections/monitoring/hosts) menu section, you can click on [*Latest data*](/manual/web_interface/frontend_sections/monitoring/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. ![](../../../assets/en/manual/guides/mysql_latestdata.png){width="600"} [comment]: # ({/bdcc4c70-339fa505}) [comment]: # ({eca01285-91c440fc}) #### Set up problem alerts Zabbix 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*](/manual/web_interface/user_profile), switch to the *Media* tab and [add your email](/manual/quickstart/basic_config/login#adding-user). ![](../../../assets/en/manual/quickstart/new_media.png){width="600"} 2\. Follow the guide for [Receiving a problem notification](/manual/quickstart/basic_config/notification). Next time, when Zabbix detects a problem, you should receive an alert via email. [comment]: # ({/eca01285-91c440fc}) [comment]: # ({77c86049-3e5fc0d5}) #### Test your configuration To test your configuration, we can simulate a real problem by updating the host configuration in Zabbix frontend. 1\. Open your MySQL server host configuration in Zabbix. 2\. Switch to the *Macros* tab and select *Inherited and host macros*. 3\. Click on *Change* next to, for example, the [previously configured](#configure-zabbix-frontend) {$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, Zabbix 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*](/manual/web_interface/frontend_sections/monitoring/problems). ![](../../../assets/en/manual/guides/mysql_problem.png){width="600"} If alerts are [configured](#set-up-problem-alerts), 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. [comment]: # ({/77c86049-3e5fc0d5}) [comment]: # ({599a98b0-69f193e9}) #### Other approaches to monitor MySQL Instead of monitoring a MySQL server with Zabbix agent 2, you could also use Zabbix agent or the Open Database Connectivity (ODBC) standard. While using Zabbix agent 2 is recommended, there might be some setups that do not support Zabbix agent 2 or require a custom approach. The key difference between Zabbix agent and ODBC lies in the data collection method - Zabbix 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 Zabbix agent 2, there are some significant differences - you need to configure Zabbix agent or ODBC to be able to monitor a MySQL server. The following instructions walk you through these **differences**. [comment]: # ({/599a98b0-69f193e9}) [comment]: # ({c3f2cc9e-c7da6279}) ##### Monitor MySQL with Zabbix agent To monitor a MySQL server with Zabbix agent, you need to [download and install](https://www.zabbix.com/download) Zabbix server, Zabbix frontend and Zabbix agent according to the instructions for your OS. Once you have successfully installed the required Zabbix components, you need to create a MySQL user as described in the [*Create MySQL user*](#create-mysql-user) section. After you have created the MySQL user, you need to configure Zabbix agent to be able to establish a connection with the MySQL server and monitor it. This includes configuring multiple [user parameters](/manual/config/items/userparameters) for executing custom agent checks, as well as providing Zabbix agent with the necessary credentials for connecting to the MySQL server as the [previously created](#create-mysql-user) "zbx_monitor" user. [comment]: # ({/c3f2cc9e-c7da6279}) [comment]: # ({71f0c028-81cd968b}) **Configure Zabbix agent** 1\. Navigate to the Zabbix agent additional configurations directory. cd /usr/local/etc/zabbix/zabbix_agentd.d ::: noteimportant The Zabbix agent additional configurations directory should be located in the same directory as your Zabbix agent configuration file (*zabbix_agentd.conf*). Depending on your OS and Zabbix installation, this directory can have a different location than specified in this guide. For default locations, check the [`Include`](/manual/appendix/config/zabbix_agentd#include) parameter in the Zabbix agent configuration file. ::: Instead of defining all of the necessary user parameters for monitoring the MySQL server in the Zabbix 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 Zabbix agent additional configurations directory. vi template_db_mysql.conf 3\. Copy the contents from the [*template_db_mysql.conf*](https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/mysql_agent/template_db_mysql.conf) file (located in the Zabbix repository) to the *template_db_mysql.conf* file you created, and save. 4\. Restart Zabbix agent to update its configuration. systemctl restart zabbix-agent Once you have configured Zabbix agent user parameters, you can move on to configure the credentials that will allow Zabbix agent to access the MySQL server. [comment]: # ({/71f0c028-81cd968b}) [comment]: # ({49d8d7e7-98d0d0b4}) 5\. Navigate to the Zabbix 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 Zabbix agent home directory. vi .my.cnf 7\. Copy the following contents to the *.my.cnf* file (replace ** with the password of the "zbx_monitor" user). [client] user='zbx_monitor' password='' [comment]: # ({/49d8d7e7-98d0d0b4}) [comment]: # ({e1d7b01f-3d796e6b}) **Configure Zabbix frontend and test your configuration** To configure Zabbix frontend, follow the instructions in the [*Configure Zabbix frontend*](#configure-zabbix-frontend) section with the following adjustments: - In the *Templates* field, type or select the template "MySQL by Zabbix agent" that will be [linked](/manual/config/templates/linking) to the host. - Configuring *Macros* is not required. Once you have configured Zabbix frontend, you can [view collected metrics](#view-collected-metrics) and [set up problem alerts](#set-up-problem-alerts). To test your configuration, follow the instructions in the [*Test your configuration*](#test-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"). ![](../../../assets/en/manual/guides/mysql_port.png){width="600"} [comment]: # ({/e1d7b01f-3d796e6b}) [comment]: # ({b2aad39e-876f1a94}) ##### Monitor MySQL with ODBC To monitor a MySQL server with ODBC, you need to [download and install](https://www.zabbix.com/download) Zabbix server and Zabbix frontend. Once you have successfully installed the required Zabbix components, you need to create a MySQL user as described in the [*Create MySQL user*](#create-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 - [unixODBC](https://www.unixodbc.org/) - and a unixODBC driver, as well as editing the ODBC driver configuration file. [comment]: # ({/b2aad39e-876f1a94}) [comment]: # ({7530ff59-445fc52a}) **Configure 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 ** 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 [configure Zabbix frontend](#configure-zabbix-frontend). [test] Driver=MariaDB Unicode Server=127.0.0.1 User=zbx_monitor Password= Port=3306 Database=zabbix [comment]: # ({/7530ff59-445fc52a}) [comment]: # ({f773e20d-062f32d6}) **Configure Zabbix frontend and test your configuration** To configure Zabbix frontend, follow the instructions in the [*Configure Zabbix frontend*](#configure-zabbix-frontend) section with the following adjustments: - In the *Templates* field, type or select the template "MySQL by ODBC" that will be [linked](/manual/config/templates/linking) to the host. - Configuring *Interfaces* is not required. - The {$MYSQL.DSN} macro value In the *Inherited and host macros* section of the MySQL server host configuration should be set to the DSN name from the *odbc.ini* file. Once you have configured Zabbix frontend, you can [view collected metrics](#view-collected-metrics), [set up problem alerts](#set-up-problem-alerts) and [test your configuration](#test-your-configuration). [comment]: # ({/f773e20d-062f32d6}) [comment]: # ({c2903286-123e0265}) #### See also - [Creating an item](/manual/config/items/item) - how to start monitoring additional metrics. - [Problem escalations](/manual/config/notifications/action/escalations) - how to create multi-step alert scenarios (e.g., first send message to the system administrator, then, if a problem is not resolved in 45 minutes, send message to the data center manager). - [ODBC monitoring](/manual/config/items/itemtypes/odbc_checks) - how to set up ODBC on other Linux distributions, and how to start monitoring additional database-related metrics with ODBC. - Template [*MySQL by Zabbix agent*](https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/mysql_agent) - additional information about the *MySQL by Zabbix agent* template. - Template [*MySQL by Zabbix agent 2*](https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/mysql_agent2) - additional information about the *MySQL by Zabbix agent 2* template. - Template [*MySQL by ODBC*](https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/mysql_odbc) - additional information about the *MySQL by ODBC* template. [comment]: # ({/c2903286-123e0265})