Configuring Zabbix for MySQL in RHEL

Monitoring the database server is very important. Percona has released plugin for monitoring mysql database. We will see how to configure it in step by step

0. I assume you already have a zabbix server otherwise click here to see how to configure zabbix server
in the below explanations zabbix server means the server where you have configured the zabbix
Database server is the server you want to monitor and it is  where you are going to install the client softwares.

1. Find the version of the zabbix software in the zabbix server.

# zabbix_agent --version
Zabbix agent v2.4.7 (revision 56694) (12 November 2015)
Compilation time: Nov 13 2015 05:35:30

2. Download the zabbix and zabbix agent softwares

Check for the same version of zabbix and zabbix_agent from the repository and download it

in my case, I will download the below files


3. Install the rpms

# yum install -y zabbix-2.4.7-1.el6.x86_64.rpm
# yum install -y zabbix-agent-2.4.7-1.el6.x86_64.rpm

4. Now we do some thing which we cannot find in the documentation this will help us configuring the mysql monitoring.

The installation will create a user zabbix with nolog

# mkdir -p /home/zabbix
# chown -R zabbix:zabbix /home/zabbix

edit /etc/passwd to give log in for the user zabbix and change home dir

example line:
zabbix:x:498:498:Zabbix Monitoring System:/home/zabbix:/bin/bash

5. switch user to zabbix and copy the configuration file to home

# su - zabbix
# cp -r /etc/zabbix/ ~/
 edit /home/zabbix/zabbix/zabbix_agentd.conf and change the following parameters

Server=IP of Zabbix Server
ServerActive=IP of Zabbix Server
Hostname=use the FQDN of the database server

6. Start zabbix agent with the following command as zabbix user

# zabbix_agentd -c /home/zabbix/zabbix/zabbix_agentd.conf

7. Log in to the zabbix server and do telnet to the database server through the zabbix port and see the connection is fine

telnet database_server_ip 10050

if not working, edit the iptables to allow connection

# iptables -A INPUT -p tcp -m tcp --dport 10050 -j ACCEPT

You have configured the zabix agent now. Let us add the host in the zabbix server

8. log in to the zabbix console and add the host

Go to Configuration -> Hosts -> Create Host -> Host tab and fill the Host name field with the FQDN of the monitored zabbix agent machine,
use the same value as above for Visible name field.
Next, add this host to a group of monitored servers and use the IP Address of the monitored machine at Agent interfaces field –
alternatively you can also use DNS resolution if it’s the case. Use the below screenshots as a guide.

Next, move to Templates tab and hit Select. A new window with templates should open. Choose Template OS Linux then scroll down
and hit on Select button to add it and automatically close the window.
Once the template appears to Link new template box, hit on Add text to link it to zabbix server,
then hit on the lower Add button to finish the process and completely add the monitored host.
The visible name of the monitored host should now appear hosts window.

That’s all! Just assure that the host Status is set to Enabled and wait a few minutes in order for Zabbix server to contact the agent,
process the received data and inform or eventually alert you if something goes bad on the monitored target.

Our server is added in the zabbix now, let us add percona plugin for monitoring the database

9. Go to database server

get the percona repo and install the plugin

 # yum install -y
 # yum install -y percona-zabbix-templates

 also we need additional softwares for the pluggin
 # yum install -y php
 # yum install -y php-mysql

10. get the conf file and edit the configuration file to include the same.

# mkdir ~zabbix/zabbix_agentd.conf.d/
# cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /home/zabbix/zabbix_agentd.conf.d/userparameter_percona_mysql.conf

# vi /home/zabbix/zabbix/zabbix_agentd.conf

add the following line in the Include section

11. Create the .cnf file for keeping the mysql user and password
# vi /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf
$mysql_user = 'zabbix';
$mysql_pass = 'zabbix_password';

12. create .cnf file in zabbix home

# vi ~/zabbix/.my.cnf
user = zabbix
password = zabbix_password

13. Create user zabbix and give privileges
 MySQL> create user zabbix@localhost identified by 'zabbix_password';
 Query OK, 0 rows affected (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)

14. Now test the checks
# /var/lib/zabbix/percona/scripts/ running-slave
Should return 0 or 1 but not the “Access denied” error.

# /var/lib/zabbix/percona/scripts/ gg

 you sould get some number. error will be as given below
 ERROR: run the command manually to investigate the problem: /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg

 After running that

# /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg
ERROR: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

If mysql is runnig on a non default port(3306), you will get the above error. then the simplest solution is get your actual socket
and create a soft like to /var/lib/mysql/mysql.sock

# grep socket /etc/my.cnf
socket = /mysqldata/mysql.sock

# ln -s /mysqldata/mysql.sock /var/lib/mysql/mysql.sock

15. Add the percona template to the host

Goto zabbix GUI configuration->hosts and click on the host we have added now
go to template tab, search for percona , add the template and update the server

To know How to add percona template in server, click here

No comments:

Post a Comment