Show Hosts Connected to MySQL

| Comments

For the terminal jockies, this is a quick and dirty for finding who, or what, is connected to your server. This proves useful when you’re running in tmux and want a pane for tracking what is left to disconnect from your database before shutting it down.

This could be done in tools like mytop, but who needs all that overhead when you have this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#!/bin/bash                                                            

MYSQL=/usr/bin/mysql                                     
DB_HOST='127.0.0.1'
DB_USER='user'
DB_PASS='pass'
DB='information_schema'                                                
                                                                       
read -r -d '' HOST_QUERY <<'EOF'                                       
SELECT DISTINCT SUBSTRING_INDEX(`host`, ":", 1) AS HOST                
FROM PROCESSLIST                                                       
EOF                                                                    
                                                                       
$MYSQL -u $DB_USER -p"$DB_PASS" -h $DB_HOST -N -B $DB -e "$HOST_QUERY" 

Mix that up with a little watch magic, and you’ve got realtime updates:

1
watch -n 1 ./connected_hosts.sh

Comments