Where does my MySQL configuration variable value come from ?

As you may already know, there are many different places where a MySQL configuration variables can be initialized.

In MySQL 8.0, we added in performance_schema a table allowing you to easily find where a variable was defined.

Let’s check this in action with max_connections for example.

I started mysqld and now I check the value of max_connections:

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

We can also use the performance.schema table called variables_info to get some more details about it:

mysql> SELECT t1.*, VARIABLE_VALUE 
       FROM performance_schema.variables_info t1 
       JOIN performance_schema.global_variables t2 
         ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
      WHERE t1.VARIABLE_NAME LIKE 'max_connections'\G
*************************** 1. row ***************************
  VARIABLE_NAME: max_connections
VARIABLE_SOURCE: COMPILED
  VARIABLE_PATH: 
      MIN_VALUE: 1
      MAX_VALUE: 100000
       SET_TIME: NULL
       SET_USER: NULL
       SET_HOST: NULL
 VARIABLE_VALUE: 151
1 row in set (0.09 sec)

I joined the global_variables table to display the value too.

Now let’s change this value in /etc/my.cnf:

[mysqld]
max_connections=200

We restart mysqld and we check again (query is the same):

*************************** 1. row ***************************
  VARIABLE_NAME: max_connections
VARIABLE_SOURCE: GLOBAL
  VARIABLE_PATH: /etc/my.cnf
      MIN_VALUE: 1
      MAX_VALUE: 100000
       SET_TIME: NULL
       SET_USER: NULL
       SET_HOST: NULL
 VARIABLE_VALUE: 200

This time in VARIABLE_PATH we can see that the variable was defined in /etc/my.cnf !

And when we have multiple files ?

Let’s check, I will now set it in an extra file /etc/mysql/my.cnf:

[mysqld]
max_connections=300

We restart mysqld and we check again:

*************************** 1. row ***************************
  VARIABLE_NAME: max_connections
VARIABLE_SOURCE: GLOBAL
  VARIABLE_PATH: /etc/mysql/my.cnf
      MIN_VALUE: 1
      MAX_VALUE: 100000
       SET_TIME: NULL
       SET_USER: NULL
       SET_HOST: NULL
 VARIABLE_VALUE: 300

As you can see, this time even if the variable is set in multiple file, we can see which one is selected by MySQL. This will help DBA playing with multiple configuration files to find where the variables were defined.

And of course, when we change it dynamically, we can also check it:

mysql> set global max_connections=2000;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT t1.*, VARIABLE_VALUE 
       FROM performance_schema.variables_info t1 
       JOIN performance_schema.global_variables t2 
         ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
      WHERE t1.VARIABLE_NAME LIKE 'max_connections'\G
*************************** 1. row ***************************
  VARIABLE_NAME: max_connections
VARIABLE_SOURCE: DYNAMIC
  VARIABLE_PATH: 
      MIN_VALUE: 1
      MAX_VALUE: 100000
       SET_TIME: 2017-11-14 11:54:19
       SET_USER: root
       SET_HOST: localhost
 VARIABLE_VALUE: 2000

This time, we can even read which user changed it from where and when !

Great MysQL 8.0 feature !