查询 MySQL 客户端连接 IP 的脚本

cat find_mysql_client_ip.sh 
#!/bin/bash

sql="select distinct substr(host,1,instr(host,':')-1) ip 
  from information_schema.processlist
 where id <> connection_id() 
   and command not like 'Binlog Dump%'
   and host not like '127.0.0.1%'
   and host not like 'localhost%'
   and user <> 'system user'
 order by ip;"

db_hosts=~/db_hosts.txt
i=1

while read ary
do
    node=(echo ${ary})
    if [ $i -gt 1 ]; then
        mysql -uroot -p123456 -h${node[3]} -P3306 -e "select concat('实例 ',$((i-1)),'：');$sql;select ''" -N
    fi
    i=$((i+1))
done < $db_hosts

cat db_hosts.txt
no name  主库ip
1. 实例1 172.31.7.74
2. 实例2 172.31.7.52
3. 实例3 172.31.7.132
4. 实例4 172.31.7.124
5. 实例5 172.31.7.133
6. 实例6 172.31.7.108
7. 实例7 172.31.7.114
8. 实例8 172.31.7.84