7.1MySQL服务状态管理

1、查看所有的连接

show PROCESSLIST;

2、查看所有的线程

mysql> show status like ‘Threads%’;
+—————————-+———-+
| Variable_name | Value |
+—————————-+———-+
| Threads_cached | 58 |
| Threads_connected | 57 | ###这个数值指的是打开的连接数
| Threads_created | 3676 |
| Threads_running | 4 | ###这个数值指的是激活的连接数，这个数值一般远低于connected数值
+—————————-+———-+

3、查看mysql与时间相关的配置

show variables like ‘%time%’;

4、查看mysql的最大连接数量配置

show variables like ‘%max_connections%’;
可以在/etc/my.cnf里面设置数据库的最大连接数 max_connections = 1000

5、批量删除指定的用户连接

CREATE DEFINER=`centos`@`%` PROCEDURE `killConnection`()
BEGIN
    declare done int default 0;
    declare s_table_name varchar(100) default '';
    declare mc cursor for 
    select id from information_schema.PROCESSLIST 
    where User='centos';
    declare continue handler for not found set done = 1;
    open mc;
        set @strSql = "";
    fetch mc into s_table_name;
    while(not done) do  
         set @strSql = concat("kill  ",s_table_name," ;");
         #select @strSql;
         PREPARE stmt FROM @strSql;  
         EXECUTE stmt;  
         deallocate prepare stmt;
         fetch mc into s_table_name;
    end while;  
    close mc;
END