1、MySQL服务相关
1
2
3
4
5
6
7
8
9
10
11
12
| #启动
systemctl start mysql
#停止
systemctl stop mysql
#重启
systemctl restart mysql
#状态
systemctl status mysql
#开启启动
systemctl enable mysql
#禁止开机启动
systemctl disable mysql
|
2、查看数据库执行进程和耗时情况
1
2
3
4
5
| -- 查看数据库执行进程级耗时情况，耗时单位秒
select *
from information_schema.`PROCESSLIST`
where command !='Sleep'
order by Time desc;
|
按客户端 IP 分组，看哪个客户端的链接数最多
1
2
3
4
5
6
7
| 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;
|
找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
1
2
3
4
| select concat('kill ', id, ';')
from information_schema.processlist
where Command != 'Sleep' and Time > 300
order by Time desc;
|
3、查询表行数和占用空间
1
2
3
4
5
6
7
| select
table_name '表名',
table_rows '表行数',
concat(((data_length)/(1024*1024*1024)),'GB') '数据占用空间',
concat(((index_length)/(1024*1024*1024)),'GB') '索引占用空间',
concat(((data_length+index_length)/(1024*1024*1024)),'GB') '总占用空间'
from information_schema.`TABLES` where table_schema=DATABASE()
|
