posts cleanup shell script
Posted on: October 13, 2017Updated: November 24, 2017
Shell script to keep tabs on MYSQL database
In previous post, we've taken a closer look at how to create PHP and MYSQL login system that collects attempt and ban data and compares a user's IP against it to see whether the IP is banned or not. While the system works like it is supposed to, the data collected in the login_attempts and login_banned tables becomes obsolete after 10 minutes since our script is set up that way. This doesn't mean that it gets deleted or disappears. Quite contrary, as things stand, the data keeps stockpiling after each entry and you can see why this can be problematic on the long run.
PART 1 - Create the script
So to keep things smooth, we will create a shell script that will be run daily, store data for the previous day in the log files and then delete it from MYSQL database. Additionally, the script will check how many times has the same IP been banned in the previous day and if the answer is more than 3 times, it will send an email with information to the owner.
#!/bin/bash
# Make sure to change these, it needs to be the same database as for the login system
host="localhost"
dbuser="db_example_user"
dbpass="db_example_pass"
dbname="db_example_name"
to[email protected]
# Create a line(80 times long) to be printed in a log file(For transparency)
line=$(printf "%80s" )
# Get all the data from login_banned and login_attempts table for the past day
banned=$(mysql -u $dbuser -p$dbpass -h $host $dbname -s -N -e "SELECT * FROM login_banned WHERE timestamp < (now() - interval 1 day)" )
attempts=$(mysql -u $dbuser -p$dbpass -h $host $dbname -s -N -e "SELECT * FROM login_attempts WHERE timestamp < (now() - interval 1 day)" )
# If the variable $banned produces any result, save it to a log file that will be created in the same folder as script
if [[ " $banned" ]]; then
echo -e "\nUsername | IP address | Timestamp \n ${line// /-}\n $banned" >> banned_log.txt;
fi
# Same thing for $attempts variable and save to a different log file.
if [[ " $attempts" ]]; then
echo -e "\nUsername | IP address | Timestamp \n ${line// /-}\n $attempts" >> attempts_log.txt;
fi
# Sql query to count how many times have the same ip address been recorded in DB for the past day. Get the both the count and IP address
# Create a loop to handle both query columns(IP count and IP address) and another loop to handle the ban count of each unique IP separately
# So if the count result(of each unique IP) is greater than 3, then send an email to alert the owner
while read -r count ip; do
for x in $count; do
if [[ " $x" -gt "3" ]]; then
echo "IP: $ip, banned $x times" | mail -s "IP to be banned" " $to" ;
fi
done
done < <(echo "SELECT COUNT(ip_address), ip_address FROM login_banned GROUP BY ip_address" | mysql -u $dbuser -p$dbpass -h $host $dbname -N)
# Delete data from login_banned and login_attempts tables for the past day
echo "DELETE FROM login_banned WHERE timestamp < (now() - interval 1 day)" | mysql -u $dbuser -p$dbpass -h $host $dbname
echo "DELETE FROM login_attempts WHERE timestamp < (now() - interval 1 day)" | mysql -u $dbuser -p$dbpass -h $host $dbname
exit
PART 2 - Configure cron
All we need to do now is to configure cron to run the script daily on a linux server. The example will contain cron running at a particular time in the day so make sure to know in what timezone the server is located to avoid misunderstanding. We will set cron to run the script every day at midnight.
0 0 * * * /path/to/script
If everything was done right, the script is now up and running. For additional information or troubleshooting the script or cron, take a look at this post.
