mysql server status_MySQL 状态变量(Server Status Variables)

1、状态变量   反映当前mysql数据库server自当次启动以来的累计相关状态信息，分为会话级与全局级别状态信息。

与系统变量相似，有些状态变量有全局和会话级别。而有些仅仅有全局级别。如binlog_cache_disk_use仅有全局状态，而bytes_sent两者都有。

能够通过show status like ‘%variable_name%‘ 或者show global status like ‘%variable_name%‘来查看。

在未使用Like的情形下show status会显示所有的状态变量。

能够通过查询系统表information_schema.global_status以及information_schema.session_status来获取状态变量信息。

能够在命令行下通过mysqladmin extended-status方式来获取状态变量的相关信息。

能够通过命令行方式mysqladmin extended-status -r -i 5或innotop持续观察状态变量的改变情况。

一些状态变量重能够用FLUSH STATUS语句重置为零值。

2、show方式查看状态变量--当前演示环境

root@localhost[(none)]> show variables like ‘version‘;

+---------------+------------+

| Variable_name | Value |

+---------------+------------+

| version | 5.5.39-log |

+---------------+------------+

a、查看全部状态变量

root@localhost[(none)]> show status;

+------------------------------------------+-------------+

| Variable_name | Value |

+------------------------------------------+-------------+

| Aborted_clients | 0 |

| Binlog_stmt_cache_use | 1 |

| Bytes_received | 135 |

| Bytes_sent | 266 |

| ................ | |

| Threads_running | 1 |

| Uptime | 76242 |

| Uptime_since_flush_status | 76242 |

+------------------------------------------+-------------+

312 rows in set (0.00 sec) --能够看出当前版本号5.5.39有312个状态变量

b、查看仅有global的状态变量(connections)

--查看指定的状态变量，下面两个与connection相关的都为全局状态变量

root@localhost[(none)]> show global status like ‘connection%‘;

+----------------------+-------+

| Variable_name | Value |

+----------------------+-------+

| Connections | 11 | --连接到MySQLserver的数量(包括成功或失败的)。

+----------------------+-------+

suse11b:~ # mysql -ufred

fred@localhost[(none)]> show global status like ‘%connection%‘;

+----------------------+-------+

| Variable_name | Value |

+----------------------+-------+

| Connections | 12 | --连接之后。我们看到Connections的值变为12了。

+----------------------+-------+

c、查看既有global又有session状态的变量

--查看session状态变量opened_tables

root@localhost[tempdb]> show session status like ‘opened_tables‘;

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Opened_tables | 0 |

+---------------+-------+

root@localhost[tempdb]> select count(*) from tb_slow;

+----------+

| count(*) |

+----------+

| 424448 |

+----------+

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 : http://blog.csdn.net/leshami

--而全局级别的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

MySQL 状态变量(Server Status Variables)

标签：命令   命令行   session   min   sage   auth   决策   medium   0 rows

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/jzdwajue/p/6851603.html