Mysql告警处理

• 连接数过多

SHOW PROCESSLIST;

修改最大连接数，但是这不是一劳永逸的方法，应该要让它自动杀死那些sleep的进程。
SHOW VARIABLES LIKE "max_connections";
SET GLOBAL max_connections=1000;

这个数值指的是mysql在关闭一个非交互的连接之前要等待的秒数，默认是28800s
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
SET GLOBAL wait_timeout=300;

• 修改my.cnf配置注意事项

[mysqld]
max_connections=1000 # 一定要放在 [mysqld] 下面

• 异常连接杀进程

杀进程
SELECT *,CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE USER='root' AND `TIME`>60;

SELECT *,MD5(INFO) AS md5_info FROM information_schema.processlist WHERE INFO IS NOT NULL 
AND COMMAND!='Sleep' AND TIME>0 AND INFO NOT LIKE '%information_schema.processlist%'
ORDER BY TIME DESC

SELECT CONCAT('kill ',ID,';') AS '杀进程' FROM information_schema.processlist WHERE INFO IS NOT NULL 
AND COMMAND!='Sleep' AND TIME>5 AND INFO NOT LIKE '%information_schema.processlist%'
ORDER BY TIME DESC