Monitoring and reporting on a remote MySQL database.

Depending on your environment, you won't grant every single server on your network the ability to send email. This sort of security precaution had me in a bit of a pickle, as I wanted to step up the mysqlchecks I currently have scripted via cron on my database servers. If your database server has the ability to send email, this solution may not be the best option for you.


I already had a Zabbix Server sitting around, cheerfully gathering data and pumping reports on my little server farm and I thought about giving it some more work to do, without any extra pay.  The zabbix server already had specific port access to these servers, so the security experts at my organization would not give me hell for this particular solution.


The logic was simple. Execute scripted checks from a remote host, run some data processing, if everything is good, just take a timestamp of execution. If everything is not good, send an email for the sysadmin or DBA to action immediately. The nice thing about this script is that it lists the tables that are not flagged as OK by MySQL.

Simple stuff, really.


Assumptions:
Database Server is 192.168.1.10
Monitoring Server is 192.168.1.11
Database to be monitored = mydb
Location of user scripts = /scripts
SSMTP is installed on the monitoring server. Installation Instructions here.

Both servers should already see each other or be able to ssh to/from one another. If not, seek assistance from your company's network gurus (walk with chocolates).

0. Login as root on the Database server.
You'll need to create an account that the remote server will access over the network.

Login as mysql root.

mysql -u root -p
<enter password>

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER on mydb.* to mydbuser@'192.168.1.11' identified by 'mydbuserpassword';

flush privileges;

1. Test that the remote connection works
From terminal on the monitoring server
mysql -u mydbuser -h 192.168.1.10 -p
<enter password>
You should be presented with a mysql prompt.

2. Install script on the Monitoring server
Copy and paste the following into a remotedbcheck.sh file in your scripts directory. Ensure the executable flag is set

chmod +x remotedbcheck.sh

Actual Script
#!/bin/sh
# A Script to execute mysqlcheck -c from a remote server
# and send an email if any table are flagged as NOT OK.
# The tables with issues are sent in the body of the
# email for admin action.
# Written by: Noveck Gowandan
# 09-01-2013
# Version 1.1
# Added script timer which will be appended to new logfile in mylocation (see variables)

##Variables
    # Location of Results and Status Files (default: /tmp)
    mylocation=/tmp 
    # Database to check
    mydbname=mydb
    # Database user with appropriate mysql access
    mydbuser=mydbuser
    # DB pass
    mydbpass=mydbpassword
    # Specify remote database host (IP preferred, hostname if entries are in /etc/hosts)
    mydbhost=192.168.1.10

    # Email variables (Valid Recipent and Sender Email required)
    notifyemail=myemailaddress@myorg.com
    serveremail=monitoring.server@myorg.com

    # These variables can be left as is
    resultsfilename=mysqlcheck-c-results.txt
    statusfilename=mysqlcheck-c-status.txt
    msgtext=`grep -v OK $mylocation/$mydbhost-$resultsfilename`
    status=0
   
# Start timer
time_start=`date +%s`

#Remove any resultsfile that exists
rm -rf $mylocation/$mydbhost-$resultsfilename

#Remove any statusfile that exists
rm -rf $mylocation/$mydbhost-$statusfilename

#Execute mysqlcheck with -c flag only
mysqlcheck -c $mydbname -u $mydbuser -p$mydbpass -h $mydbhost > $mylocation/$mydbhost-$resultsfilename

#Check results file and generate status file
grep -v OK $mylocation/$mydbhost-$resultsfilename | wc -l > $mylocation/$mydbhost-$statusfilename

#Check Status file, send email
status=`cat $mylocation/$mydbhost-$statusfilename`;

if test $status -ge 1
then

ssmtp $notifyemail << EOF
To: $notifyemail
From: $serveremail
Subject: `echo "PROBLEM: MYSQLCHECK "$mydbhost`;

$msgtext
EOF

else

echo "Last Check OK!" >> $mylocation/$mydbhost-$mydbname-check.log
fi

# Stop timer and calculate total time
time_end=`date +%s`
total_time=`expr $(( $time_end - $time_start ))`

# Log output: datestamp and time takes to execute

date >> $mylocation/$mydbhost-$mydbname-check.log
echo "Execution Time was $total_time seconds." >> $mylocation/$mydbhost-$mydbname-check.log
echo "____________" >> $mylocation/$mydbhost-$mydbname-check.log

3. Add script to cron
Depending on your organization and criticality of the service, this may need to be done daily. Given the size of the database in my case and the length of time it takes to run, this example is weekly on Sundays at 2:01 AM.

nano /etc/crontab


Append to file
01 2 * * 0 root /bin/sh /scripts/remotedbcheck.sh


That's it!
The script will execute quietly and will not send any email if everything reports as OK. Be prepared if you do get any email from this script, you should act immediately!

Cheers,
-noveck

In pursuit of a monitoring solution for CentOS Linux: Part VI - Zabbix Agent Installation

Continuation of "In pursuit of a monitoring solution for CentOS Linux"
Part I
Part II
Part III
Part IV
Part V 

Part VI - Zabbix Agent Install [Linux Specific]

0. Login as root on the server to be monitored.

1. Install prerequisite packages
yum install gcc

2. Copy Zabbix tarball and extract
mkdir /temp/zabbix && cd /temp/zabbix
copy the zabbix tarball into this folder (via scp from server, or it can be re-downloaded)
tar -xzvf zabbix-1.8.1.tar.gz
cd zabbix-1.8.1.tar.gz

3. Install the zabbix agent
./configure --enable-agent --prefix=/usr/local/zabbix
make install

4. Create the Zabbix User
useradd zabbix

5. Configure Zabbix Agent
echo 'zabbix_agent 10050/tcp' >> /etc/services
echo 'zabbix_trap 10051/tcp' >> /etc/services

6. Copy the sample configs for the agentd.
mkdir /etc/zabbix
cp misc/conf/zabbix_agentd.conf /etc/zabbix

7. Edit agentd config
nano /etc/zabbix/zabbix_agentd.conf

Server=Your.Zabbix.Server.IP
Host_name=your_server_name ***[Remember this name! Case Sensitive]

8. Configure Agent to Automatically start
cp misc/init.d/redhat/zabbix_agentd_ctl /etc/init.d/zabbix_agentd
nano /etc/init.d/zabbix_agentd
Just below #!/bin/sh: add these two lines, including the # hash marks.

# chkconfig: 345 95 95
# description: Zabbix Agentd

Edit the following parameters:

BASEDIR=/usr/local/zabbix
ZABBIX_AGENTD=$BASEDIR/sbin/zabbix_agentd

chkconfig --level 345 zabbix_agentd on 


9. Edit hostsfile to allow communication
nano /etc/hosts.allow

ALL: your.zabbix.server.ip : allow

10.Depending on your firewall setup, open the follwoing ports: 10050 and 10051.

11. Start the Zabbix Agent Service
service zabbix_agentd start

12. Make sure the agent is operational
cat /tmp/zabbix_agentd.log

The expected output is a few lines, one of which should say Zabbix Agent is running...

Now login to the Zabbix Server web interface/ GUI.

13.  Setup the host on the server
Go to Configuration –> Hosts –> Create Host
Host: Your Server Name(use the same name from step 7)
DNS name: your dns info [your dns name only]
IP address: 192.168.x.x [Your Host ip address]
Port: 10050
Status: Monitored [We will change this after installing the agent]
Link with Template: Template_Linux


Save!

Documentation of Triggers and other necessary information on further configuration can be found on the Zabbix Website.


-n

In pursuit of a monitoring solution for CentOS Linux: Part V - Zabbix Server Installation

Continuation of "In pursuit of a monitoring solution for CentOS Linux"
Part I
Part II
Part III
Part IV

Part V - Zabbix Server Installation

This install was done mostly from this guide located at muck.net


0. Login as Root on the Monitoring Server (assuming the same server in the Nagios Server Setup)


1. Install prerequisite packages
yum -y install ntp php php-bcmath php-gd php-mysql httpd mysql gcc mysql-server mysql-devel net-snmp net-snmp-utils net-snmp-devel net-snmp-libs curl-devel mak


2. Fping is not part of the base repository, so the RPM's is downloaded from Dag Rep.
wget http://dag.wieers.com/rpm/packages/fping/fping-2.4-1.b2.2.el5.rf.i386.rpm
rpm -Uvh fping-2.4-1.b2.2.el5.rf.i386.rpm
chmod 7555 /usr/sbin/fping


3. Create the Zabbix User
useradd zabbix

4. Download and Install Zabbix
mkdir /temp/zabbix && cd /temp/zabbix
wget http://downloads.sourceforge.net/project/zabbix/ZABBIX%20Latest%20Stable/1.8.1/zabbix-1.8.1.tar.gz?use_mirror=iweb
tar -xzvf zabbix-1.8.1.tar.gz

5. Start/Restart Mysql and create the empty database shell
service mysqld restart
mysql -u root -p
In MYSQL:
mysql> CREATE DATABASE zabbix character set utf8;
mysql> GRANT DROP,INDEX,CREATE,SELECT,INSERT,UPDATE,ALTER,DELETE ON zabbix.* TO zabbixmysqluser@localhost IDENTIFIED BY ‘zabbixmysqlpassword’;
mysql> quit;

6. Create the DB Schema
cd zabbix-1.8.1
cat create/schema/mysql.sql | mysql -u zabbixmysqluser -pzabbixmysqlpassword zabbix
cat create/data/data.sql | mysql -u zabbixmysqluser -pzabbixmysqlpassword zabbix
cat create/data/images_mysql.sql | mysql -u zabbixmysqluser -pzabbixmysqlpassword zabbix
./configure --enable-server --prefix=/usr/local/zabbix –-ith-mysql --with-net-snmp --with-libcurl
make install
make clean

7. Compile the Zabbix Agent
./configure --enable-agent --prefix=/usr/local/zabbix --enable-static
make install

8. Add Zabbix Server and Agent ports to the Services file
echo ‘zabbix_agent 10050/tcp’ >> /etc/services
echo ‘zabbix_trap 10051/tcp’ >> /etc/services

9. Copy the sample configs to /etc/zabbix for server and agentd.
mkdir /etc/zabbix
cp misc/conf/zabbix_agentd.conf /etc/zabbix
cp misc/conf/zabbix_server.conf /etc/zabbix


10. Modify Zabbix server configuration
nano /etc/zabbix/zabbix_server.conf
Change the following entries

DBUser=zabbixmysqluser
BPassword=zabbixmysqlpassword
DBSocket=/var/lib/mysql/mysql.sock
FpingLocation=/usr/sbin/fping

11. Modify the Agent configuration
nano /etc/zabbix/zabbix_agentd.conf
Change the following entries

Server=127.0.0.1,Your.Zabbix.Server.IP
Hostname=UniqueHostnameForEachAgent

12. Copy Server and Agent files
cp misc/init.d/redhat/zabbix_agentd_ctl /etc/init.d/zabbix_agentd
cp misc/init.d/redhat/zabbix_server_ctl /etc/init.d/zabbix_server
Modify /etc/init.d/zabbix_agentd AND /etc/init.d/zabbix_server:

BASEDIR=/usr/local/zabbix

13. Edit Agentd and Server config
nano /etc/init.d/zabbix_agentd (Note the # hash marks, they are necessary), add near the top, just below #!/bin/sh:

# chkconfig: 345 95 95
# description: Zabbix Agentd

nano /etc/init.d/zabbix_server (again, note the # Hash marks, they are required), add near the top, just below #!/bin/sh:

# chkconfig: 345 95 95
# description: Zabbix Server

14. Configure Services to start automatically on boot.
chkconfig zabbix_server on
chkconfig zabbix_agentd on
chkconfig httpd on
chkconfig mysqld on

15. Depending on your firewall setup, open the follwoing ports:for port 80, 10050, and 10051.

16. Copy phpfiles to site root
cp -r frontends/php /var/www/html/zabbix

17. Tweak php.ini
nano /etc/php.ini

max_execution_time = 300
date.timezone = Pick a Timezone from here

18. Start Apache and make the zabbix folder writable
service httpd start
chmod 777 /var/www/html/zabbix/conf

19. Launch http://your.server.ip/zabbix in your web browser.
A Setup Screen should be displayed.
Click the EULA, and confirm all prerequisites are met on the next screen.
Setup the DB connection using the user and password set in step 6.

20. When the setup is complete, the webroot needs to be secured and the services restarted.
chmod 755 /var/www/html/zabbix/conf
mv /var/www/html/zabbix/setup.php /var/www/html/zabbix/setup.php.bak
service zabbix_agentd start
service zabbix_server start

21. Zabbix is now accessible using http://your.server.ip/zabbix

username: admin
Password:
(no password)

On to configuring the Zabbix Agents!

-n

In pursuit of a monitoring solution for CentOS Linux: Part II - The Winners

This is an update to my earlier post: In pursuit of a monitoring solution for CentOS Linux

Based on some testing for the past couple weeks, the winner, or rather winners turned out to be Nagios and Zabbix.

Nagios was a great monitoring solution and was highly customizable and the plugins were simple to modify.

Zabbix is a lot more complex, but offers very nifty graphs for analysis. Graphs are always good ;)

Future posts will have the Nagios and Zabbix installation instructions for CentOS5.x - both the Client and the Server.

-n