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

代码语言：javascript复制

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

代码语言：javascript复制

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

ip mysql 脚本 客户端 连接

0 人点赞