Monitor MySQL using Zabbix Agent and MySQL App template

With Zabbix (tested with Zabbix 2.4 and 3.0), we can monitor the MySQL server performances using Zabbix Agent and the Template App MySQL included in Zabbix Server.

 First of all, we have to create a MySQL user account to be used to access mysql and get the necessary stats.
Login to the client and run in command line the command above (type the MySQL root password when prompted):
mysql -uroot -p -e"GRANT USAGE ON *.* TO 'zabbix'@'127.0.0.1' IDENTIFIED BY 'abc123'";
mysql -uroot -p -e"GRANT USAGE ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY 'abc123'";
mysql -uroot -p -e"flush privileges"
Note that the USAGE privilege specifier stands for “no privileges”, but change the 'abc123' password to something more secure: something like 'KJ37ae!$' is stronger (don't worry, we don't have to type it frequently)
Test the new zabbix mysql account to make sure it works with:
mysql -uzabbix -pabc123 -e"status"

If it works, the output will look something like:
mysql  Ver 15.1 Distrib 5.5.47-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          412664
Current database:
Current user:           zabbix@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         5.5.47-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 18 days 4 hours 27 min 56 sec

Now create a new .my.cnf file under the zabbix config folder /etc/zabbix. This will allow mysqladmin to connect to the mysql database.

vim /etc/zabbix/.my.cnf

and paste this content into the file:
[mysql]
user=zabbix
password=abc123

[mysqladmin]
user=zabbix
password=abc123

Edit the file /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf and replace HOME = /var/lib/zabbix with HOME = /etc/zabbix (should appear three times).

UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/etc/zabbix mysql -N | awk '{print $$2}'
UserParameter=mysql.size[*],echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema='$1'")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name='$2'");" | HOME=/etc/zabbix mysql -N
UserParameter=mysql.ping,HOME=/etc/zabbix mysqladmin ping | grep -c alive
UserParameter=mysql.version,mysql -V

Finally, restart Zabbix Agent with
service zabbix-agent restart

On the Zabbix Server you can now apply the Template App MySQL template to the host you want to monitor.

Comments

Post a Comment

Popular posts from this blog

Configure & Install WMIC for Observium

WMI Poller Configuring Windows Firewall and User Access for WMI This guide assumes you know what Group Policy is. Allowing WMI Connections in Windows Firewall Enabling Firewall Settings via GPO 1.   Open Group Policy Management 2.   Create and/or edit the Group Policy Object you wish put these settings into   3.   Expand Computer Config > Policies > Windows Settings > Security Settings > Windows Firewall with Advanced Security > Windows Firewall with Advanced Security > Inbound Rules 4.   Create a New Rule under Inbound Rules 5.   Choose the Predefined option, and select Windows Management Instrumentation (WMI) from the drop-down list, click Next 6.   Select the "WMI-In" option and click Next 7.   Allow the connection > Finish Enabling Firewall Settings for Individual Server 1.   Open up Windows Firewall with Advanced Security, either through Control Pane...
Read more

Zabbix Proxies on CentOS 7

Image
Overview A Zabbix proxy can collect performance and availability data on behalf of the Zabbix server. This way, a proxy can take on itself some of the load of collecting data and offload the Zabbix server. Also, using a proxy is the easiest way of implementing centralized and distributed monitoring, when all agents and proxies report to one Zabbix server and all data is collected centrally. A Zabbix proxy can be used to: Monitor remote locations Monitor locations having unreliable communications Offload the Zabbix server when monitoring thousands of devices Simplify the maintenance of distributed monitoring Setting up Proxy Server We will be using a separate Linux server with CentOS 7 as the base operating system with MariaDB database server installed on it that will be used as a local database for Proxy server. So in this article we assume that you already had set up your Zabbix Server. [root@localhost ~]# rpm --import http://repo.zabbix.com/RPM-GPG...
Read more

Join CentOS 7 into Active Directory using realm and sssd

Image
Introduction to SSSD and Realmd   Staring from Red Hat 7 and CentOS 7, SSSD or 'System Security Services Daemon' and realm have been introduced. SSSD's main function is to access a remote identity and authentication resource through a common framework that provides caching and offline support to the system. SSSD provides PAM ans NSS integration and a database to store local users, as well as core and extended user data retrieved from a center server. The main reason to transition from winbind to sssd is that sssd can be used for both direct and indirect integration and allows to switch from one integration approach to another without significant migration costs. The most convenient way to configure SSSD or winbind in order to directly integrate a Linux system with AD is use the realm service. Because it allows callers to configure network authentication and domain membership in a standard way. The realm service automatically discovers information about accessib...
Read more