MySQL 状态变量(Server Status Variables)(2)

root@localhost[tempdb]> show session status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 1    | --值变为1
+---------------+-------+

--从information_schema.session_status表查询状态变量OPENED_TABLES
root@localhost[tempdb]> select * from information_schema.session_status
    -> where variable_name like 'opened_tables';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| OPENED_TABLES | 1              |
+---------------+----------------+

--查看全局状态变量opened_tables
root@localhost[tempdb]> show global status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 54    |
+---------------+-------+

root@localhost[tempdb]> select count(*) from mysql.db;
+----------+
| count(*) |
+----------+
|        2 |
+----------+

root@localhost[tempdb]> show global status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 55    |  --值变为55
+---------------+-------+

--从information_schema.global_status表查询状态变量OPENED_TABLES
root@localhost[tempdb]> select * from information_schema.global_status
    -> where variable_name like 'opened_tables';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| OPENED_TABLES | 55            |
+---------------+----------------+

--清洗状态变量统计信息
root@localhost[tempdb]> flush status;
Query OK, 0 rows affected (0.00 sec)

--下面的查询结果可以看出，session级别的opened_tables被重置为0
root@localhost[tempdb]> show session status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 0    |
+---------------+-------+

--Author: Leshami
--Blog  :

--而全局级别的opened_tables未受到任何影响
root@localhost[tempdb]> show global status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 55    |
+---------------+-------+

3、使用mysqladmin extended-status获取状态变量

suse11b:~ # mysqladmin extended-status|grep Connections
| Connections                              | 18          |
suse11b:~ # mysql

root@localhost[(none)]> system mysqladmin extended-status|grep Connections
| Connections                              | 20          |
root@localhost[(none)]> exit
Bye
suse11b:~ # mysqladmin --help |more    #mysqladmin与状态变量有关的使用
extended-status      Gives an extended status message from the server
flush-status          Clear status variables

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04下安装MySQL

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主从服务器

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二进制安装

--------------------------------------分割线 --------------------------------------