5.7以后System and status 变量需要从performance_schema中进行获取,information_schema仍然保留了GLOBAL_STATUS,GLOBAL_VARIABLES两个表做兼容,如果希望沿用information_schema中进行查询的习惯,5.7提供了show_compatibility_56参数,设置为ON可以兼容5.7之前的用法,如果为OFF,查询information_schema库的global_variables时报错:

mysql> use information_schema
mysql> select * from global_variables;
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

--查看show_compatibility_56其值

mysql> show variables like '%show_compatibility_56%';  
+-----------------------+-------+  
| Variable_name         | Value |  
+-----------------------+-------+  
| show_compatibility_56 | OFF   |  
+-----------------------+-------+  
1 row in set (0.01 sec)  

--把show_compatibility_56打开

mysql> set global show_compatibility_56=on;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> show variables like '%show_compatibility_56%';  
+-----------------------+-------+  
| Variable_name         | Value |  
+-----------------------+-------+  
| show_compatibility_56 | ON    |  
+-----------------------+-------+  
1 row in set (0.00 sec)  

再次查看:


mysql> select * from information_schema.global_status limit 3;  
+-----------------------+----------------+  
| VARIABLE_NAME         | VARIABLE_VALUE |  
+-----------------------+----------------+  
| ABORTED_CLIENTS       | 0              |  
| ABORTED_CONNECTS      | 0              |  
| BINLOG_CACHE_DISK_USE | 0              |  
+-----------------------+----------------+  
3 rows in set, 1 warning (0.00 sec)  

VIA:http://mysql.taobao.org/monthly/2016/06/02/

带符号 * 的表示必填项