Monitor s3sync with Zabbix

s3sync is a ruby program that easily transfers directories between a local directory and an S3 bucket:prefix. It behaves somewhat, but not precisely, like the rsync program.

I am using this tool to automatically backup the important data from Debian servers to Amazon S3. I am not going to explain here how to install s3sync as it is not the purpose of this article. However, you can read this very useful article from John Eberly’s blog: How I automated my backups to Amazon S3 using s3sync.

If you followed the steps from John Eberly’s post, you should have an upload.sh script and a crontab job which executes this script periodically.

From this point, here is what you need to do to monitor the success of the synchronisation with Zabbix:

  1. Add the following code at the end of your upload.sh script:
    # print the exit code
    RETVAL=$?
    [ $RETVAL -eq 0 ] && echo "Synchronization succeed"
    [ $RETVAL -ne 0 ] && echo "Synchronization failed"
    
  2. Log the output of the cron script as follow:
    30 2 * * sun /path/to/upload.sh > /var/log/s3sync.log 2>&1
    
  3. On Zabbix, create a new item which will check the existence of the sentence “Synchronization failed” in the file /var/log/s3sync.log:

    Item key: vfs.file.regmatch[/var/log/s3sync.log,Synchronization failed]
  4. Still on Zabbix, define a new trigger for the previously created item:

    Trigger expression: {Template_AmazonCloud_Debian:vfs.file.regmatch[/var/log/s3sync.log,Synchronization failed].last(0)}=1

With these few steps, you should now receive Zabbix alerts when a backup on S3 fails. 🙂

, , , , , , , , ,

No Comments

Refresh GeoIP automatically

GeoIP is a very useful tool provided by MaxMind. It can determine which country, region, city, postal code, and area code the visitor is coming from in real-time. For more information, visit MaxMind website.

This tool is also coming with an Apache module allowing to redirect users depending on their location. For example, we could redirect all users from France to the French home page of a multi-language website, or we could block the traffic to users from a specific country.

To install this module on a Debian server, you simply need to run the following command:

apt-get install libapache2-mod-geoip

But, how does this module work? How does it know where the user comes from?  😯
It is actually quite simple: GeoIP is using a mapping file of IP address by country. On Debian, this file is stored in the folder /usr/share/GeoIP and is named GeoIP.dat.

However, the IP addresses are something which change all the time. So this file will get out-of-date very quickly. This is why MaxMind provides an updated file at the beginning of each month for free. To read the installation instructions, please click the following link: http://www.maxmind.com/app/installation

This is good and well, but who will remember or even have the time to update this file every month? And imagine if you have to do this on hundreds of servers?
The solution is to use a shell script which will download, extract and install the updated GeoIP file automatically once a month:

#!/bin/sh

# Go in the GeoIP folder
cd /usr/share/GeoIP

# Remove the previous GeoIP file (if present)
rm GeoIP.dat.gz

# Download the new GeoIP file
wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCountry/GeoIP.dat.gz

# Remove the previous GeoIP backup file
rm GeoIP.dat.bak

# Backup the existing GeoIP file
mv GeoIP.dat GeoIP.dat.bak

# Extract the new GeoIP file
gunzip GeoIP.dat.gz

# Change the permission of the GeoIP file
chmod 644 GeoIP.dat

# Reload Apache
service apache2 reload

You can place this file in your root folder and set up the following crontab job:

0 0 3 * * /root/update_geoip.sh

This will execute the script automatically on the third day of every month.

, , , , , , ,

2 Comments

Convert SQL Server to MySQL

Some time ago, I had to convert a Microsoft SQL Server database to a MySQL database. The main reason was the cost of the SQL Server license for such a small database.

Looking on the web, I found the following open source application:

Name: mssql2mysql
URL: http://sourceforge.net/projects/mssql2mysql/
Description: mssql2mysql is a python script used to create a SQL dump from a Microsoft SQL server that is ready to use with MySQL. Supports Schema and data dumping, including Primary Keys for each table, allows to dump all data or just a small portion of it.

To be honest, this tool wasn’t working as expected but it was a very good start! 🙂

Below is the list of changes I’ve made:

  • Add support for the bool data type;
  • Add support for the datetime data type;
  • Only convert tables and views of the ‘dbo’ owner;
  • Add the test columnas[6]==True on the primary key;
  • Add support for the uniqueidentifier data type;
  • Add support for the tinyint data type (SQL Server is tinyint unsigned by default, but not in MySQL!);
  • Add support for the default column values;
  • Add support for the bit data type (use tinyint instead of bit, go to this page for more information).

To download the amended script, please click on the following link: mssql2mysql.tar.bz2

This script worked perfectly fine for me. However, please note that my interest was focused on converting the database structure but not the content!

, , , , ,

1 Comment

Make image backgrounds transparent with tolerance

In one of the projects I am working on at the moment, I needed to convert the background colour of an image to be transparent so the image looks better on a non-white background.

Looking on the Web, I found the following article from Dustin Marx:
Making White Image Backgrounds Transparent with Java 2D/Groovy
If the link is broken, please download his code from the following link: ImageTransparency.java

The method which makes the background colour transparent is called makeColorTransparent. This method works pretty well, except in some cases as shown in the example below:

Original image Converted image
using Dustin’s method

This is actually quite normal. Indeed, his code is converting a specific colour (#FFFFFF in our case) to be transparent. But what if the background is not homogeneous?

This is the reason why I had to modify his method to add a new parameter called tolerance:

private Image makeColorTransparent(final BufferedImage im, final Color color, int tolerance) {
    int temp = 0;
    if (tolerance < 0 || tolerance > 100) {
        System.err.println("The tolerance is a percentage, so the value has to be between 0 and 100.");
        temp = 0;
    } else {
        temp = tolerance * (0xFF000000 | 0xFF000000) / 100;
    }
    final int toleranceRGB = Math.abs(temp);
		
    final ImageFilter filter = new RGBImageFilter() {
        // The color we are looking for (white)... Alpha bits are set to opaque
        public int markerRGBFrom = (color.getRGB() | 0xFF000000) - toleranceRGB;
        public int markerRGBTo = (color.getRGB() | 0xFF000000) + toleranceRGB;

        public final int filterRGB(final int x, final int y, final int rgb) {
            if ((rgb | 0xFF000000) >= markerRGBFrom && (rgb | 0xFF000000) <= markerRGBTo) {
                // Mark the alpha bits as zero - transparent
                return 0x00FFFFFF & rgb;
            } else {
                // Nothing to do
                return rgb;
            }
        }
    };

    final ImageProducer ip = new FilteredImageSource(im.getSource(), filter);
    return Toolkit.getDefaultToolkit().createImage(ip);
}

Such as Photoshop, the tolerance is a percentage value between 0 and 100. The higher the tolerance is, the bigger the range of colours will be.

Let’s take our previous example and apply a 50% tolerance:

That looks much better, isn’t it? 🙂

, , ,

3 Comments

AVG function returns 0.9999

This one is a very odd bug! 😐

For some reason, the AVG function from MySQL is always returning the value 0.9999 instead of the average value. This has been experienced on MySQL 5.5.12 hosted on Amazon RDS (Relational Database Service).
However, the exact same query executed on MySQL 5.1.28 is returning the right values.

Why is that? Is it a bug in MySQL 5.5.12?
I did a search on internet and I couldn’t find anything about it. So to be honest, I am not sure what is this bug or even if MySQL is aware of it.

Anyway, if you encounter the same problem, you can simply replace the AVG function by the combination SUM/COUNT.
For example, the following query:

SELECT student_name, AVG(test_score)
FROM student
GROUP BY student_name;

can be replaced by the one below:

SELECT student_name, SUM(test_score)/COUNT(test_score)
FROM student
GROUP BY student_name;

, , , ,

3 Comments