Troubleshooting

MysQL 8.0 Places Where MySQL Configuration Variables Can be Initialized

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 !

Source link

Leave a Reply

Your email address will not be published. Required fields are marked *