DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Analysing General Query Log

10.22.2011
| 2241 views |
  • submit to reddit
        // A few commands to know the schema, IP accessing the data using general log.

time grep -w Connect /var/log/mysql/general.log | awk -F'Connect' '{print $2}' > connectme.log

// connection string
sort connectme.log | uniq -c | sort -n
3350 application_user@10.10.11.26 on db1_16JUL2011
7330 application_user@172.16.55.133 on FWQAdb2
7716 application_user@10.10.11.14 on FWQAdb3
10084 application_user@172.116.155.133 on FWQAdb4

// schema, user and IP count
awk -F' on' '{print $2}' connectme.log | sort | uniq -c | sort -n
4528 db1_29062011
5027 db2_16JUL2011
9295 FWQAdb3
10084 FWQAdb4
10488 FWQAdb5

awk -F'@' '{print $1}' connectme.log | sort | uniq -c | sort -n
46 amar
28450 akbar
31453 anthony
41646 dbagroup

awk -F'@' '{print $2}' connectme.log | awk '{print $1}' | sort | uniq -c | sort -n
4189 10.10.11.167
7157 10.10.11.26
8600 10.10.10.67
9073 10.10.11.14
17414 172.116.155.133

// failed connections analysis
grep 'Access denied' connectme.log | awk -F'@' '{print $1}' | awk '{print $NF}' | sort | uniq -c | sort -n
1 'amar'
1 'akbar'
13 'anthony'
16 'user1'
831 'user2'

grep 'Access denied' connectme.log | awk -F'@' '{print $2}' | awk '{print $1}' | sort | uniq -c | sort -n
6 '10.10.10.69'
26 '10.10.11.140'
815 '10.10.11.57'