資料庫性能狀態
(1)QPS(每秒Query量)
QPS = Questions(or Queries) / seconds
mysql > show /*50000 global */ status like 'Question';

(2)TPS(每秒事務量)
TPS = (Com_commit + Com_rollback) / seconds
mysql > show status like 'Com_commit';
mysql > show status like 'Com_rollback';

(3)key Buffer 命中率
key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

mysql> show status like 'Key%';

(4)InnoDB Buffer命中率
innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

mysql> show status like 'innodb_buffer_pool_read%';

(5)Query Cache命中率
Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

mysql> show status like 'Qcache%';
(6)Table Cache狀態量
mysql> show status like 'open%';

(7)Thread Cache 命中率
Thread_cache_hits = (1 - Threads_created / connections ) * 100%

mysql> show status like 'Thread%';

mysql> show status like 'Connections';

(8)鎖定狀態
mysql> show status like '%lock%';

(9)複製延時量
mysql > show slave status

(10) Tmp Table 狀況(臨時表狀況)
mysql > show status like 'Create_tmp%';
(11) Binlog Cache 使用狀況
mysql > show status like 'Binlog_cache%';

(12) Innodb_log_waits 量
mysql > show status like 'innodb_log_waits';
			

			
mysql> show master status;
+---------------------+-----------+--------------+------------------+
| File                | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+-----------+--------------+------------------+
| DBMaster-bin.000018 | 409468882 | example      |                  |
+---------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>
			
			

			
mysql> show slave status/G

得到的列表會有類似下面的數據：

File: mysql-bin.000001
Position: 1374
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
			
			

			
mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+
10 rows in set (0.00 sec)
			
			

			
mysql> show global variables like 'wait_timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| wait_timeout               | 10    |
+----------------------------+-------+
			
			

			
mysql> use mysql;
Database changed

mysql> set wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables  like '%wait_timeout%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 10    |
+--------------------------+-------+
3 rows in set (0.00 sec)
			
			

			
mysql> set GLOBAL table_lock_wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables  like '%table_lock_wait_timeout%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| table_lock_wait_timeout | 10    |
+-------------------------+-------+
1 row in set (0.00 sec)
			
			

			
mysql> use mysql
Database changed

mysql> SET LOW_PRIORITY_UPDATES=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables  like '%priority%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| low_priority_updates     | ON    |
| sql_low_priority_updates | ON    |
+--------------------------+-------+
2 rows in set (0.00 sec)
			
			

			
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
			
			

 SHOW VARIABLES LIKE 'datadir';
			

			
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| datadir       | /var/lib/mysql/         |
+---------------+-------------------------+
1 row in set (0.00 sec)
			
			

			
mysql> show variables like '%storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)