MySQL Status

1. Dispaly Table locks / cache hit Info

mysql> show global status like 'Table%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Table_locks_immediate      | 71626997 |
| Table_locks_waited         | 26169971 |
| Table_open_cache_hits      | 97796908 |
| Table_open_cache_misses    | 108      |
| Table_open_cache_overflows | 0        |
+----------------------------+----------+
5 rows in set (0.00 sec)

2. Display Command (Insert/Update etc Count)

mysql> show global status like 'Com_%';
 select * from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME like 'Com_%';
 select * from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME IN ('COM_UPDATE','COM_INSERT','COM_SELECT','COM_DELETE');
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| COM_DELETE    | 120892         |
| COM_INSERT    | 202796         |
| COM_SELECT    | 60755185       |
| COM_UPDATE    | 37150062       |
+---------------+----------------+

3. Show Process List from a Host

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE HOST like '192.168.1.203%';

4. Show Global Query Cache status

mysql> show global status like 'Qca%';

5.  set slow_query_log  on/off

set global slow_query_log = 'on';
set global slow_query_log = 'off';

7. Show Index status from Table;

mysql> show index from DBNAME.TABLENAME;

8. show global variables;

show global variables;