Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about how MYSQL 8 fixes your set variables. Many people may not know much about it. In order to let everyone know more, Xiaobian summarizes the following contents for everyone. I hope everyone can gain something according to this article.

In other databases, ORACLE ,SQL SERVER in their configuration parameters are relatively few, and MYSQL in which is considered a "exotic." There are many configuration parameters, some configuration parameters are difficult to understand, and there are many possibilities for configuration parameters to change, which is a situation that other databases do not have.

Those who are familiar with MYSQL will experience a situation, that is, my my.cnf and my actual configuration of various variables, may be different, some variables are OK, after MYSQL restart back to my.cnf configuration, but sometimes change back, may give you trouble, or cause some things you do not want to see.

General configuration MYSQL parameters, if I run in MYSQL, configure some system or global variables, if it is decided, I will write these configurations to my.cnf. The next time I start, there will generally be nothing, but I am afraid that I forgot, or in some emergency cases, it may cause inconsistencies between the system configuration and the running configuration.

Of course you can write a program to do some comparison work and avoid some problems. Here in MYSQL 8, the system has already thought of such scenarios for you, and there is already an official solution.

This function is SET PERIST to set your variables

For example, we can check the current innodb_buffer_pool_size in MYSQL 8

We're gonna change him to 8G.

But if you forget to change this configuration in my.cnf, the next time you restart the server, you will see the "original" configuration in the MY.CNF file.

MYSQL 8 has a new configuration command persist, which helps you record the history of your actions on system configuration variables and helps you load this configuration into the running environment the next time you start.

So where is this record file? It's in your data directory, mysqld-auto.cnf.

Open this file and you can see the configuration you have just operated. In JSON, it is recorded in this file. It is clear who configured what.

And I will not lose the configuration options after restarting MYSQL

Of course, if you delete the recorded file, the associated configuration will expire after MYSQL is restarted.

And with certain statements, you can quickly get the date your configuration was modified, and who changed the configuration.

select a.VARIABLE_NAME,b.VARIABLE_value ,SET_TIME,SET_USER,SET_HOST

from performance_schema.variables_info a

join performance_schema.global_variables b

on a.VARIABLE_NAME=b.VARIABLE_NAME

where b.VARIABLE_NAME like 'innodb_buffer%';

Of course, if you want to renege on your configuration, you can run reset persist to erase all the configurations you have done and recorded in your file.

reset persist;

Think of it as a nice feature.

After reading the above, do you have any further understanding of how MYSQL 8 fixes your set variables? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.