The Maatkit tools provide a suite of additional MySQL commands. There is one command I use constantly and that is mk-query-digest.
Unfortunately the documentation does leave a lot to be desired for usability. While throughout, it is a man page and not a user guide. Several of us have discussed writing better documentation however it’s always a matter of time. I have however learned a number of tips and I’d like to share them in smaller digests.
The first is showing additional display. Maatkit works on truncating per line output to a reasonable length of 73 characters?
One of those lines is the list of hosts that connected to MySQL for a query, for example.
# Hosts 4 192.168.40... (2), 192.168.40... (2)... 2 more # Hosts 3 22.214.171.124 (12), 9126.96.36.199 (6)... 1 more
The problem is I want to know what that 1 more is so I can gather a complete list of IP addresses that connect to this server. You do that with the –show-all=host argument.
$ cat external.tcpdump | ./mk-query-digest --type tcpdump | grep Hosts | uniq -c # 1 # Hosts 3 188.8.131.52 (12), 9184.108.40.206 (6)... 1 more 1 # Hosts 1 220.127.116.11
$ cat external.tcpdump | ./mk-query-digest --type tcpdump --show-all=host | grep Hosts | uniq -c 1 # Hosts 3 18.104.22.168 (12), 922.214.171.124 (6), 126.96.36.199 (2) 1 # Hosts 1 188.8.131.52
You can apply the same principle to the Users as well with –show-all=user
$ cat external.tcpdump | ./mk-query-digest --type tcpdump --show-all=user | grep Users | uniq -c 1 # Users 2 xxx (13), phpmysqlmo... (5) 49 # Users 1 xxx
The problem is a still gett a truncation of the name ‘phpmysqlmo…’ That’s the one thing I’m trying to uncover, because that IP and usernme are not valid permissions for this system.