components

simplifieDB

About the future of system variables in MySQL

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 :

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.

Other posts –

Components in MySQL 8

This article discuss about the less discussed topic of MySQL 8 . Read about components in mysql and how it is differ from plugins.

simplifieDB's avatar by simplifieDB
Featured simplifieDB

Components in MySQL 8

This article is purely based on my understanding of Components and the outcome of my dirty hands. Reference of this Article is “MySQL official documents” & my experience with components.

Oracle introduced Components in MySQL 8.0 and user can locate its table under mysql database. See below figure.

1EDDDCE9-B386-4EEE-954D-2742904FDCBF

What is a component and why we need it ??

Components are similar to the Plugins but are more secure and provides improved & additional features. Moreover plugins will be deprecated in future versions.

If we want “audit logs” to be enable or have strong password mechanism, a user need to install respective plugins. But there are few challenges :

1. Plugins can interact only with server but not with each other which limits its usage.
2. Plugins interact directly with server components which means they are not encapsulated .
3. While installing plugins a user needs to mention the extension file name as well. For example .so or .dll

These are some of the over head or disadvantage of plugins which a DBA normally face. I remember once I required to install an “audit” plugin and being on linux platform , it requires a “.so” file . So in this case I made ensure that I should have  “.so” extension plugin .

But components remove above mentioned challenges. Components are more secure as it is encapsulated and provides defined set of services. Major advantages are :

1. Components can interact each other.
2. With components DBA doesn’t need to worry about remembering the extension file name. For example while installing “validate password” component user can simply install it as shown in below picture post which a user can check whether component has been successfully installed . Additionally we can uninstall if not required.

FAA1B240-1FA1-4C90-B8C4-DAA49EB1736E

3. A user can create his own plugins using existing macros . Some of the header for this component are as follows :

#include “validate_password_imp.h“

#include <assert.h>

#include <string.h>

#include <algorithm>

#include <atomic>

#include <fstream>

#include <iomanip>

#include <set>

#include <sstream>

#include “mysql/components/library_mysys/my_memory.h“

#include “mysql/components/services/mysql_rwlock.h“

#include “mysql/components/services/psi_memory.h“

#include “mysqld_error.h”

Respective macros can be found from MySQL distribution code

3.a After this, copy any macros of existing components and get the basic framework ready.
3.b Copy any of the service ( header file) and compile it using the source distribution of the MySQL server.

Currently below plugins are available to use. At the current moment not all components are available and thus plugins can be used in place but in future, plugins will be completely removed.

  1. Error logging
  2. Audit Messaging and logs
  3. MySQL Enterprise Data Masking and De-Identification Password Validation
  4. MySQL Enterprise Firewall
  5. Plugin API
  6. Thread Pool
  7. Version Tokens

Once enable, user can see the respective variables using “show global variables” .

Such variables starts with prefix “component name”. For  example in “validate_password”  , below are the variables which will be visible once user enable this component :

components

What about  “Audit log” component & how it is useful ??.

There are various cases when application need to append some specific string in audit logs. In such case we can use an UDF which is known as “audit_api_message_emit_udf()”.

  1. First install “audit log” component in similar manner which we used to install validate password component:

component

2. Now we wanna check if we can add some message to the audit log:

UDF_audit

If the required arguments has been passed, we will receive OK message which means that it is a success..This link provide argument required for this UDF

Hence stating this we have other components too, we can make our own also and in future it will replace the plugins. Moreover if are using plugins these days and planning to work on MySQL 8 , then you have to remove plugins as it will be going to remove in future.