概述

有朋友问怎么去看连接mysql数据库有哪些用户及对应的ip地址等等信息，所以顺便整理了下这块内容，下面是涉及的一些命令。

1、查看当前连接到数据库的用户和host

## 查看当前连接到数据库的用户和host ##
select distinct user,host from `information_schema`.`processlist` p where p.user not in('root','repl','system user');

2、查看每个host的当前连接数和总连接数

select * from performance_schema.hosts;

ps1: 系统表performance_schema.hosts在mysql 5.6.3版本中引入，用来保存mysql服务器启动后的连接情况。

3、按照登录用户+登录服务器查看登录信息

select user as
 login_user,
 left ( host, position( ':' in host ) - 1 ) as login_ip,
 count( 1 ) as login_count 
from
 `information_schema`.`processlist` p 
where
 p.user not in ( 'root', 'repl', 'system user' ) 
group by
 user,
 left ( host, position( ':' in host ) - 1 );

4、按照登录用户+数据库+登录服务器查看登录信息

select
db as database_name,
user as login_user,
left(host,position(':' in host)-1) as login_ip,
count(1) as login_count
from `information_schema`.`processlist` p
where p.user not in('root','repl','system user')
group by db,user,left(host,position(':' in host)-1);