MySQL常见的监控项

#监测服务是否正常

mysqladmin -uroot -p123456abc -h localhost ping

#获取mysql当前状态值

mysqladmin -uroot -p123456abc -h localhost status

#获取数据库当前连接信息

mysqladmin -uroot -p123456abc -h localhost processlist

#获取数据库当前的连接数

mysql -uroot -p123456abc -BNe "select host,count(host) from information_schema.processlist group by host"
information_schema

#显示mysql的启动时长

mysql -uroot -p123456abc -e "show status like '%uptime%'" | awk '/ptime/{ calc = $NF/3600;print $(NF-1), calc "Hour"}'

#查看数据库所有库大小

mysql -uroot -p123456abc -e 'select table_schema,round(sum(data_length+index_length)/1024/1024,2) from information_schema.tables group by table_schema'

#补充：mysql数据库，通过sql查看数据量大小和索引数据大小
#查看指定库数据和索引大小总和

select concat(round(sum((data_length+index_length)/1024/1024),2),'M') as total_data from information_schema.tables where table_schema = 'test';

#查看所有库数据和索引大小总和

select concat(round(sum((data_length+index_length)/1024/1024),2),'M') as total_data from information_schema.tables;

#查看指定数据库的某个表

select concat(round(sum((data_length+index_length)/1024/1024),2),'M') as data from tables where table_schema='test' and table_name='test';

#查询一个库中每个表的数据大小，索引大小和总大小

select
concat(round(data_length/(1024*1024),2),'M') as 'data_size',
concat(round(index_length/(1024*1024),2),'M') as 'index_size',
concat(round((data_length+index_length)/(1024*1024),2),'M') as 'total'
from
information_schema.tables a where a.table_schema = 'test'

#查询一个库中所有表的数据大小，索引大小的总大小

select
concat(sum(round(data_length/(1024*1024*1024),2)),'G') as 'data_size',
concat(sum(round(index_length/(1024*1024*1024),2)),'G') as 'index_size',
concat(sum(round((data_length+index_length)/(1024*1024*1024),2)),'G') as 'total'
from
information_schema.tables a where a.table_schema = 'tpch';