Get Social

How to monitor mysql queries

Sometimes, you need to figure out which request comes from cms / cmf or another script to the Mysql server.
For example, I had to deal with the jqgrid-php library. It was unclear which query comes to the mysql server from the script.

You can, of course, install and configure mysql-proxy, but there is a simpler solution:

1. Login to Mysql-server as mysql admin (root) user.

2. Set where to write the log:
set global general_log_file='/tmp/mysql_query.log';

3. Enable log:
set global general_log = 1;

4. Run your scripts

5. Turn it off so it does not clog the server
set global general_log = 0;

6. Analyze the log:
less /tmp/mysql_query.log

7. it’s all!

Script for domain names monitoring

Script for domain names monitoring image

Often, who have multiple sites, think about – how to control when a domain name renewal term will expire. Especially if domain names are served by different registrars. So it was in the companies where I worked: one and a half to two dozen domain names were always was used. I wanted to find out about domain expiration and pay it on time.

I’ll share the script on Bash, which I sketched for this. In fact, it’s a parser.
What he does, you can understand from the comments, but I will describe a little more:

So the task: the script, runs on a schedule; it check domains; sends email report.

[1] Where do we get data from?

This script uses whois command results. Sometimes console query: “whois some kind of domain” does not give the expected result. This is due to the fact that the whois command does not poll suitable servers that contain complete information. Therefore, we can add such servers to the configuration file whois.conf

[2] How it works?

1. We see an array of domain names that need to be checked (fill it by yourself).
2. Next – create a temporary file, which is beautifully filled with a parser
3. File contents are sent to the mail and the file is deleted.

[3] Source?

Here it is:

#!/bin/bash

## Script for monitoring domain names -> checking domain expiration date
## url: http://rocknroot.pp.ua/en/blog.script-for-domain-names-monitoring.html


# DON'T forget add to /etc/whois.conf:
# \.ua$ whois.com.ua
# \.by$ whois.cctld.by
#(if needed)
# Or place somewhere your whois.conf and specify a parameter to the whois command

export LC_ALL=en_US.utf8

# domain names array for polling
ARR_DOMAIN+=(
yahoo.com
google.com
)

# If there is no possibility to edit whois.conf - then you can try to enter the following
# value in the bash array: '-h whois.cctld.by tut.by' (in quotation marks)

# Create a file for temporary data
echo "" > tmp_file_domain_renew

# Writes table header there 
printf "%-35s\t %-25s\t %-25s\n" "Domain     " "< 50 days" "> 50 days" >> tmp_file_domain_renew
printf "%-35s\t %-25s\t %-25s\n" "---------------" "----------" "----------" >> tmp_file_domain_renew

# Parses information about the validity period of the domain
for DOMAIN_ in "${ARR_DOMAIN[@]}"
 do
 sleep 0.2
 dateexp_=`whois $DOMAIN_ | \
 egrep -i 'Expiration Date:|paid-till:|OK-UNTIL|Domain Currently Expires:|Record expires on|expires:|Registry Expiry Date:'| \
 head -n 1 | \
 sed 's/\(OK-UNTIL\) \(.\{,8\}\).*/\2/i' | \
 sed 's/.*Currently //' | \
 sed 's/.*Expiration Date:/ExpDate: /' | \
 sed 's/.*Registry Expiry Date:/RegExpDate: /' | \
 sed 's/T[0-9][0-9]\:.*//'| \
 awk '{$1=""; print $0}' | \
 sed 's/^\ //' | \
 sed 's/\([0-9]*\)\.\([0-9]*\)\.\([0-9]*\)/\1\2\3/i'`

# Calculate how many days remain from the current date
dateexp_s=`date -d "${dateexp_}" +"%s"`
datenow_s=`date +"%s"`
diff_s=`expr $dateexp_s - $datenow_s`
diff_d=`expr $diff_s / 3600 / 24`

# Fill in the columns of the table, depending on whether
# More or less, in this case, 50 days
if [[ $diff_d -ge 50 ]];
then
  printf "%-35s\t %-25s\t %-25s\n" "$DOMAIN_"  "-" "$diff_d" >> tmp_file_domain_renew
else
  printf "%-35s\t %-25s\t %-25s\n" "$DOMAIN_" "$diff_d" "-" >> tmp_file_domain_renew
fi

done

# send to email
mail -s DomainRenewCheck yourmail@example.com < tmp_file_domain_renew

# cleaning
rm tmp_file_domain_renew

[4] Checking.

Do not forget to make executable with the command:

chmod +x domain_renew.sh

The result should be something like this (in your mailbox):

Domain                          < 50 days               > 50 days                
—————                  ———-              ———-            
yahoo.com                               –                         320                      
google.com                             16                        –                           

[5] How to add schedule task?

Run “crontab -e” in the console and prescribe script path and time when it needs to run.
That’s all. Now it’s a little easier to track when the domain expires. 🙂