mysql常用sql

常用命令

登录命令

mysql -h$ip -P$port -u$user -p

查看mysql版本号

select version(); 

按客户端 IP 分组，看哪个客户端的链接数最多

SELECT
	client_ip,
	count(client_ip) AS client_num
FROM
	(
		SELECT
			substring_index(HOST, ':', 1) AS client_ip
		FROM
			information_schema. PROCESSLIST
	) AS connect_info
GROUP BY
	client_ip
ORDER BY
	client_num DESC;

查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

SELECT
	*
FROM
	information_schema. PROCESSLIST
WHERE
	Command != 'Sleep'
ORDER BY
	Time DESC;

找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀 （此处 5分钟 可根据自己的需要调整SQL）

可复制查询结果到控制台，直接执行，杀死堵塞进程

SELECT
	concat('kill ', id, ';')
FROM
	information_schema. PROCESSLIST
WHERE
	Command != 'Sleep'
AND Time > 300
ORDER BY
	Time DESC;

查看mysql隔离级别

show VARIABLES like "%ISOLATION";

查看msyql redo log的刷新

show VARIABLES like "innodb_flush_log_at_trx_commit";

查看msyql bin log的刷新

show VARIABLES like "sync_binlog";

查看持续时间超过60S的事务

SELECT
	*
FROM
	information_schema.INNODB_TRX
WHERE
	TIME_TO_SEC(
		TIMEDIFF(now(), trx_started)
	) > 60