mysql日常检查
1) 进入mysql终端# mysql -uroot -p
2) 查看版本
# mysqladmin -uroot -p version
select @@version;
3) 列出数据库
show databases;
4) 显示哪些线程正在运行
show processlist
5) 查看配置
# cat /etc/my.cnf
6) 查看引擎
>show engines;
默认的引擎
show variables like '%storage_engine%';
7) 查询日志
show variables like 'log_%';
# cat /etc/my.cnf |grep log
log-bin=mysql-bin
binlog_format=mixed
log-error=/var/log/mysqld.log
# tail -n 20 /var/log/mysqld.log
8) 查看所有库大小
select concat(round(sum(DATA_LENGTH/1024/1024)+sum(INDEX_LENGTH/1024/1024), 2), 'MB') as data from information_schema.tables
所有库大小
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size from information_schema.tables group by TABLE_SCHEMA order by data_length desc;
指定库所有表大小
select TABLE_NAME, concat(truncate(data_length/1024,2),' KB') as data_size, concat(truncate(index_length/1024,2),' KB') as index_size from information_schema.tables where TABLE_SCHEMA = 'mysql' group by TABLE_NAME order by data_length desc;
9) 查询表锁统计
show status like 'table%';
如果waited数值较高，说明锁争用较严重
10) 检查键效率
show status like 'key_read%';
key_reads/key_read_requests 尽可能低，至少是1:100，最好是1:1000
11) 检查请求缓存命中
show status like 'Qcache%';
Qcache_lowmem_prunes值非常大，说明缓冲不够
Qcache_hits非常大，说明查询缓冲使用非常频繁，需增大缓冲
12) 判断是否主从
show global status like 'Slave_running';
13) 临时表
show status like '%tmp%';
正常created_tmp_disk_tables/created_tmp_tables*100%
页:
[1]