Yesterday I was working on performance schema to troubleshoot a replication issue and my eyes stopped on 2 new additional tables starting 8.0 onwards which I felt quite useful in some cases and these 2 tables are mentioned below. However, this is not the main topic of this article but I will cover a high level introduction of them.

persisted_variables
variables_info

What’s old ? You might be knowing already.

Those who doesn’t know about persisted variables, it let you persist the “modified global variables” even after a mysqld restart i.e. you doesn’t need to go to the my.cnf file and make changes in it. Lets try to understand with an example:

mysql> SET PERSIST max_connections = 200;

Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like '%max_connection%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 200   |
| mysqlx_max_connections | 100   |
+------------------------+-------+

Checking the mysqld-auto.cnf (resides in the data directory having a json based format)

cat mysqld-auto.cnf

{"Version": 2, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "200", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1685521797329463}}}}

After this, if we restart our mysqld, you will notice that your changes are persisted now.

What about performance schema table I mentioned above ?

Talking about variables_info table, if we would like to see which user has changed the value of a variable and at what time so that we can dig down any issues like max_connection, purge_thread, buffer_pool size, auditing and so on. Here I must see this table :

mysql> select * from performance_Schema.variables_info where 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: 2023-05-31 14:18:46.893137
       SET_USER: root
       SET_HOST: localhost
*************************** 2. row ***************************
  VARIABLE_NAME: mysqlx_max_connections
VARIABLE_SOURCE: COMPILED
  VARIABLE_PATH: 
      MIN_VALUE: 1
      MAX_VALUE: 65535
       SET_TIME: NULL
       SET_USER: NULL
       SET_HOST: NULL
2 rows in set (0.01 sec)

What’s new in 8.0.29 ?

Coming down to our main topic. Starting 8.0.29 we will be able to secure sensitive variables which will be storing data like passwords, key rings etc and even apply the access level security on them. At this moment, there are no such variables but we should remember that to use such variables, we need to enable component keyring_file which will help us to secure these variables once they will be introduced. In this blog I have explained how to install components.

mysql> install component 'file://component_keyring_file';
Query OK, 0 rows affected (0.02 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  select * from component;
+--------------+--------------------+-------------------------------+
| component_id | component_group_id | component_urn                 |
+--------------+--------------------+-------------------------------+
|            5 |                  1 | file://component_keyring_file |
+--------------+--------------------+-------------------------------+
1 row in set (0.00 sec)

mysql> show global variables like '%keyring%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| keyring_operations | ON    |
+--------------------+-------+

Talking about how can we encrypt these variables and control its access, we have two options :

• persist_sensitive_variables_in_plaintext – variable to control the encryption.
• SENSITIVE_VARIABLES_OBSERVER  – this is a type of privilege.

Are these useful ?

Once there will be some sensitive variable and if we are using it, we need to be extra cautious about them so that this data won’t be visible to any users. Additionally, master key ring needs to be rotated occasionally to make this more secure. Secondly, sensitive variables should be put in OFF state so that in case if data won’t be encrypted, mysqld wont restart. Still, it will be too early to comment on anything.