Mysql数据库巡检(二)
Mysql数据库巡检（二）
1. 查看数据库版本
mysql>select version();
2. 数据库端口
mysql>show global variables like 'port';
3. 查询数据库
mysql> show databases;
4. 数据库用户信息
mysql> use mysql;
mysql> select user,host from user;
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
5. 数据库字符集
mysql>SHOW VARIABLES LIKE 'character%';
6. 数据库的安装目录
mysql> select @@basedir as basePath from dual ;
mysql>show variables like '%basedir%';
7. 数据文件目录
mysql> select @@datadir as dataPath from dual ;
mysql> show variables Like '%datadir%';
8. 各个schema的总大小，表大小，索引大小，表个数
mysql> select table_schema, sum(data_length+index_length)/1024/1024 as total_mb,
sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb,
count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;
9. 查询Innodb引擎的状态
mysql> show engine innodb status;
PROCESS
无权限
10. 查询当前活跃的进程状态
mysql> show processlist;
11. *查看Threads使用情况*
mysql> show global status like 'thread%';
12. Thread_cache命中率
1 - Threads_created / connections x 100%
show global status like 'connections';
13. 查看QPS-TPS情况
QPS= Questions/Uptime
mysql> show global status like 'questions';
mysql> show global status like 'Uptime';
TPS=(com_commit+com_rollback) /Uptime
mysql> show status like 'com_commit'; #（不会记录隐式提交的事务）
mysql> show status like 'com_rollback' ;
mysql> show global status like 'Uptime';
14. 查看DML per second
记录每一次的语句，只记录隐式提交的数据，如 autocommit=1
mysql> SHOW GLOBAL STATUS WHERE variable_name IN
('Com_insert','Com_delete','Com_select','Com_update');
15. 流量监控
mysql> show status like 'bytes%';
16. 查看索引使用情况
mysql> show status like 'handler%';
17. 查看执行全表扫描的数量
mysql> show status like 'select_scan';
18. 查看慢查询情况
mysql> show status like 'slow_queries';
19. 查看表缓存情况
mysql> show global status like 'open%_tables';
20. 查看锁使用情况
mysql> show status like '%lock%';
21. Binlog cache使用情况
mysql> show status like 'binlog_cache%';
22. 查看wait事件
mysql> show status like 'Innodb_buffer_pool_wait_free';
23. 检查错误日志
mysql> select @@global.log_error;
24. 查看主从同步状态
MySQL> show slave status \G;
无权限
PERLICATION CLIENT
*如何判断主从完全同步**？*
Master_Log_File和Relay_Master_Log_File所指向的文件必须一致
Relay_Log_Pos和Exec_Master_Log_Pos的为止也要一致才行
Slave_SQL_Running_State：显示为wait 意思是中继日志的sql语句已经全部执行完毕
