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
http://repo.zabbix.com/zabbix/
in my case, I will download the below files
zabbix-2.4.7-1.el6.x86_64.rpm
zabbix-agent-2.4.7-1.el6.x86_64.rpm
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 http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
# 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
Include=/home/zabbix/zabbix_agentd.conf.d/
11. Create the .cnf file for keeping the mysql user and password
# vi /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf
<?php
$mysql_user = 'zabbix';
$mysql_pass = 'zabbix_password';
12. create .cnf file in zabbix home
# vi ~/zabbix/.my.cnf
[client]
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)
mysql> GRANT RELOAD, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'zabbix'@'localhost';
Query OK, 0 rows affected (0.00 sec)
14. Now test the checks
# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh running-slave
0
Should return 0 or 1 but not the “Access denied” error.
# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg
405647
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
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
http://repo.zabbix.com/zabbix/
in my case, I will download the below files
zabbix-2.4.7-1.el6.x86_64.rpm
zabbix-agent-2.4.7-1.el6.x86_64.rpm
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 http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
# 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
Include=/home/zabbix/zabbix_agentd.conf.d/
11. Create the .cnf file for keeping the mysql user and password
# vi /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf
<?php
$mysql_user = 'zabbix';
$mysql_pass = 'zabbix_password';
12. create .cnf file in zabbix home
# vi ~/zabbix/.my.cnf
[client]
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)
mysql> GRANT RELOAD, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'zabbix'@'localhost';
Query OK, 0 rows affected (0.00 sec)
14. Now test the checks
# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh running-slave
0
Should return 0 or 1 but not the “Access denied” error.
# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg
405647
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