MySQL中统计各个IP的连接数的方法总结_Mysql

mysql中统计各个ip的连接数

在mysql中统计各个ip的连接数，可以使用以下几种方法：

方法1：查询 information_schema.processlist

select 
    substring_index(host, ':', 1) as client_ip,
    count(*) as connection_count
from information_schema.processlist 
where host != ''
group by client_ip
order by connection_count desc;

方法2：使用 show processlist 命令

-- 先创建临时表存储结果
create temporary table temp_processlist as
show processlist;

-- 然后查询统计
select 
    substring_index(host, ':', 1) as client_ip,
    count(*) as connection_count
from temp_processlist
where host != ''
group by client_ip
order by connection_count desc;

方法3：更详细的连接信息统计

select 
    substring_index(host, ':', 1) as client_ip,
    count(*) as total_connections,
    sum(case when command = 'sleep' then 1 else 0 end) as sleep_connections,
    sum(case when command != 'sleep' then 1 else 0 end) as active_connections,
    group_concat(distinct user) as connected_users
from information_schema.processlist 
where host != ''
group by client_ip
order by total_connections desc;

方法4：实时监控脚本（shell）

#!/bin/bash
# 保存为 monitor_connections.sh

mysql -u root -p -e "
select 
    substring_index(host, ':', 1) as '客户端ip',
    count(*) as '连接数',
    group_concat(distinct user) as '用户列表'
from information_schema.processlist 
where host != ''
group by substring_index(host, ':', 1)
order by count(*) desc;"

方法5：定期统计并记录到表

-- 创建统计记录表
create table if not exists connection_stats (
    id int auto_increment primary key,
    stat_time datetime default current_timestamp,
    client_ip varchar(45),
    connection_count int,
    unique_users int
);

-- 插入统计数据的存储过程
delimiter //
create procedure log_connection_stats()
begin
    insert into connection_stats (client_ip, connection_count, unique_users)
    select 
        substring_index(host, ':', 1) as client_ip,
        count(*) as connection_count,
        count(distinct user) as unique_users
    from information_schema.processlist 
    where host != ''
    group by substring_index(host, ':', 1);
end //
delimiter ;

-- 执行统计
call log_connection_stats();

方法6：查看当前连接详情

select 
    id as connection_id,
    user as mysql_user,
    substring_index(host, ':', 1) as client_ip,
    db as database_name,
    command as command_type,
    time as time_seconds,
    state as connection_state,
    info as current_query
from information_schema.processlist 
where host != ''
order by client_ip, time desc;

实用技巧

1. 查找连接数过多的ip

select 
    substring_index(host, ':', 1) as client_ip,
    count(*) as connection_count
from information_schema.processlist 
where host != ''
group by client_ip
having connection_count > 10  -- 设置阈值
order by connection_count desc;

2. 杀死特定ip的所有连接

-- 先生成kill语句预览
select concat('kill ', id, ';') as kill_command
from information_schema.processlist 
where substring_index(host, ':', 1) = '192.168.1.100';  -- 替换为目标ip

-- 确认无误后执行kill

到此这篇关于mysql中统计各个ip的连接数的方法总结的文章就介绍到这了,更多相关mysql统计ip连接数内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！