MySQL的variables和status

VARIABLES

在文档中的位置：

MySQL Server Administration ----->  The MySQL Server ---->  Server System Variables

variables按作用域可以分为三种：

----  GLOBAL

----  SESSION

----  GLOBAL | SESSION

在informance_schema库中，有两张表 GLOBAL_VARIABLES 和SESSION_VARIABLES 记录variable的名字和值。                        

GLOBAL类型的variable，如果是dynamic的，修改的时候需要使用set global命令，修改之后立即对所有session生效。

SESSION类型的variable，如果是dynamic的，修改的时候使用set命令，修改之后只对当前session立即生效，不会影响global和其他session。

GLOBAL | SESSION的variable，修改的时候使用set global或者set命令。

                                                           使用set global命令，对当前已连接的session不生效，只对新建立的连接生效。

                                                           使用set命令，只对当前session生效，对已存在的其他连接和新建的连接无影响。

以如下两个variable为例：

sort_buffer_size作用域为global | session

Command-Line Format--sort_buffer_size=#System VariableNamesort_buffer_sizeVariable ScopeGlobal, SessionDynamic VariableYes

query_cache_size的作用域为global

Command-Line Format--query_cache_size=#System VariableNamequery_cache_sizeVariable ScopeGlobalDynamic VariableYes

mysql> use information_schemaReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables like '%VARIABLE%';+-------------------------------------------+| Tables_in_information_schema (%VARIABLE%) |+-------------------------------------------+| GLOBAL_VARIABLES                          || SESSION_VARIABLES                         |+-------------------------------------------+2 rows in set (0.00 sec)mysql> desc GLOBAL_VARIABLES;+----------------+---------------+------+-----+---------+-------+| Field          | Type          | Null | Key | Default | Extra |+----------------+---------------+------+-----+---------+-------+| VARIABLE_NAME  | varchar(64)   | NO   |     |         |       || VARIABLE_VALUE | varchar(1024) | YES  |     | NULL    |       |+----------------+---------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> show variables like '%innodb_buffer_pool_size%';+-------------------------+-----------+| Variable_name           | Value     |+-------------------------+-----------+| innodb_buffer_pool_size | 134217728 |+-------------------------+-----------+1 row in set (0.00 sec)mysql> set global innodb_buffer_pool_size=104857600;ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a <span style="background-color: rgb(255, 0, 0);">read only variable</span>

mysql> show variables like '%sort_buffer%';+-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| innodb_sort_buffer_size | 1048576 || myisam_sort_buffer_size | 8388608 || sort_buffer_size        | 262144  |+-------------------------+---------+3 rows in set (0.00 sec)mysql> set global sort_buffer_size=1048576;Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%sort_buffer%';+-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| innodb_sort_buffer_size | 1048576 || myisam_sort_buffer_size | 8388608 || sort_buffer_size        | 262144  |+-------------------------+---------+3 rows in set (0.00 sec)mysql> exitBye[root@mysqlrep2 ~]# /usr/local/mysql/bin/mysql -uroot -p -S /dbdata/data/mysql.sock Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.27 MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show variables like '%sort_buffer%';+-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| innodb_sort_buffer_size | 1048576 || myisam_sort_buffer_size | 8388608 || sort_buffer_size        | 1048576 |+-------------------------+---------+3 rows in set (0.00 sec)

mysql> set sort_buffer_size=1000000;Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%sort_buffer%';+-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| innodb_sort_buffer_size | 1048576 || myisam_sort_buffer_size | 8388608 || sort_buffer_size        | 1000000 |+-------------------------+---------+3 rows in set (0.00 sec)mysql> mysql> mysql> exitBye[root@mysqlrep2 ~]# /usr/local/mysql/bin/mysql -uroot -p -S /dbdata/data/mysql.sock Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.27 MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show variables like '%sort_buffer%';+-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| innodb_sort_buffer_size | 1048576 || myisam_sort_buffer_size | 8388608 || sort_buffer_size        | 1048576 |+-------------------------+---------+3 rows in set (0.00 sec)mysql> mysql> 

mysql> mysql> show variables like '%query_cache_size%';+------------------+---------+| Variable_name    | Value   |+------------------+---------+| query_cache_size | 1048576 |+------------------+---------+1 row in set (0.00 sec)mysql> set query_cache_size=1000000;ERROR 1229 (HY000): Variable 'query_cache_size' is a <span style="background-color: rgb(255, 0, 0);">GLOBAL variable</span> and should be set with SET GLOBAL

mysql> set global query_cache_size=10485760;Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%query_cache_size%';+------------------+----------+| Variable_name    | Value    |+------------------+----------+| query_cache_size | 10485760 |+------------------+----------+1 row in set (0.00 sec)mysql> exitBye[root@mysqlrep2 ~]# /usr/local/mysql/bin/mysql -uroot -p -S /dbdata/data/mysql.sock Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.6.27 MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show variables like '%query_cache_size%';+------------------+----------+| Variable_name    | Value    |+------------------+----------+| query_cache_size | 10485760 |+------------------+----------+1 row in set (0.00 sec)

STATUS

在文档中的位置：

MySQL Server Administration ----->  The MySQL Server ---->  Server Status Variables

status是数据库运行状态的记录或统计

如Bytes_received，Bytes_sent。

com_xxx 记录的是数据库运行的命令的统计，比如Com_delete是运行的delete命令的次数统计，类似的还有Com_drop_index等。

ststus按作用域可以分为三种：

----  GLOBAL          只在global级别进行统计

----  SESSION        只在session级别进行统计

----  GLOBAL | SESSION  在global和session级别都统计

flush status 清空当前session的统计，但对global的无影响。做统计或优化时有用。

Many status variables are reset to 0 by the FLUSH STATUS statement.

以Com_show_status

Com_show_statusintegerGLOBAL | SESSION

当前系统总的统计是10次

mysql> show global status;+-----------------------------------------------+-------------+| Variable_name                                 | Value       |+-----------------------------------------------+-------------+............| Com_show_slave_status                         | 0           || Com_show_status                               | 10          || Com_show_storage_engines                      | 0           |............+-----------------------------------------------+-------------+341 rows in set (0.00 sec)mysql> 

当前session运行了3次

mysql> show status;+-----------------------------------------------+-------------+| Variable_name                                 | Value       |+-----------------------------------------------+-------------+............| Com_show_slave_status                         | 0           || Com_show_status                               | 3           || Com_show_storage_engines                      | 0           |............+-----------------------------------------------+-------------+341 rows in set (0.00 sec)

把当前session的统计清0

系统统计的总的次数不受影响

mysql> flush status;Query OK, 0 rows affected (0.00 sec)mysql> show global status;+-----------------------------------------------+-------------+| Variable_name                                 | Value       |+-----------------------------------------------+-------------+............       || Com_show_slave_status                         | 0           || Com_show_status                               | 12          || Com_show_storage_engines                      | 0           |............+-----------------------------------------------+-------------+341 rows in set (0.00 sec)mysql> 

当前session重新统计

mysql> show status;+-----------------------------------------------+-------------+| Variable_name                                 | Value       |+-----------------------------------------------+-------------+............| Com_show_slave_status                         | 0           || Com_show_status                               | 2           || Com_show_storage_engines                      | 0           |............+-----------------------------------------------+-------------+341 rows in set (0.00 sec)mysql>