IP block from MySQL via iptables

19/09/2013 [D_A] HOWTO, Kasulik, Tehniline, 0

Nifty bash script:

#!/bin/bash
# 19.09.2013 – Kristjan Tarjus
# this script retrieves data from mysql using bash and adds them to iptables for blocking

dbase="YOURDBDATABASE"
table="YOURDBTABLE"
host="YOURDBHOST"
user="YOURDBUSER"
dbpass="YOURDBPASS"

#For multiple servers - this script checks via IP
function int-ip { /sbin/ifconfig $1 | grep "inet addr" | awk -F: '{print $2}' | awk '{print $1}'; }
iip=`int-ip eth0`

/usr/bin/mysql -h$host -u$user -p$dbpass -N -e"use $dbase; select ip,stat from $table where servu NOT LIKE '%"$iip"%' and stat = 'NEW';" | while read ip stat; do
if [ "$stat" == "NEW" ];
then
iptables -A INPUT -s $ip -j DROP #blocks ip from mysql
/usr/bin/mysql -h$host -u$user -p$dbpass -N -e"use $dbase; update $table set servu = CONCAT(servu,'$iip|') where ip = '"$ip"';"
#For troubleshooting uncomment following line
#echo $ip $iip
fi
done


Mysql on windows and wamp backup and restore hg and mercurial on bitbucket – ssh on linux connection problems