Detailed explanation of global parameter persistence in MySQL 8 new features

Detailed explanation of global parameter persistence in MySQL 8 new features

Preface

Since the release of the first official version of MySQL 8.0.11 in 2018, the MySQL version has been updated to 8.0.26. Compared with the stable version 5.7, the performance improvement of 8.0 is unquestionable!

As more and more companies begin to use MySQL version 8.0, it is a challenge and an opportunity for DBAs! 💪🏻

This article mainly discusses the new features of MySQL 8.0: global parameter persistence

Global parameter persistence

MySQL 8.0 supports online modification of global parameters and persistence. By adding the PERSIST keyword, the modified parameters can be persisted to a new configuration file (mysqld-auto.cnf). When you restart MySQL, you can get the latest configuration parameters from the configuration file!

Corresponding Worklog [WL#8688]: dev.mysql.com/worklog/tas…

To enable this feature, use the special syntax SET PERSIST to set any dynamically modifiable global variables!

SET PERSIST

The statement can modify the value of the variable in memory and write the modified value to mysqld-auto.cnf in the data directory.

SET PERSIST_ONLY

The statement does not modify the value of the variable in memory, but only writes the modified value to mysqld-auto.cnf in the data directory.

Take the max_connections parameter as an example:

mysql> select * from performance_schema.persisted_variables;
Empty set (0.00 sec)

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 151 |
|mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> set persist max_connections=300;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.persisted_variables;
+-----------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 300 |
+-----------------+----------------+
1 row in set (0.00 sec)

The system will generate a mysqld-auto.cnf file in json format in the data directory. The format is as follows. When my.cnf and mysqld-auto.cnf exist at the same time, the latter has a higher priority.

{
    "Version": 1, 
    "mysql_server": {
        "max_connections": {
            "Value": "300", 
            "Metadata": {
                "Timestamp": 1632575065787609, 
                "User": "root", 
                "Host": "localhost"
            }
        }
    }
}

📢 Note: Even if you modify the configuration value through SET PERSIST and there is no change, it will be written to the mysqld-auto.cnf file. But you can restore the initial default value by setting it to the DEFAULT value!

If you want to restore the max_connections parameter to its initial default value, just execute:

mysql> set persist max_connections=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.persisted_variables;
+-----------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 151 |
+-----------------+----------------+
1 row in set (0.00 sec)

If you want to remove all global persistent parameters, just execute:

mysql> RESET PERSIST;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.persisted_variables;
Empty set (0.00 sec)

Of course, you can also delete the mysqld-auto.cnf file and restart MySQL!

Final Thoughts

Main code:

Commit f2bc0f89b7f94cc8fe963d08157413a01d14d994

Main entry function (8.0.0):

Most interface functions are defined in the sql/persisted_variable.cc file:
Load the contents of mysqld-auto.cnf at startup: Persisted_variables_cache::load_persist_file(); Parse the validity of json and store it in memory to set the configuration read from the file: Persisted_variables_cache::set_persist_options

When the SET PERSIST command is run, Persisted_variables_cache::set_variable is called to update the value stored in memory and write it to the mysqld-auto.cnf file: Persisted_variables_cache::flush_to_file

Summarize

This is the end of this article about the new feature of MySQL 8: global parameter persistence. For more information about MySQL 8 global parameter persistence, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

Reference Documents:

  • mysqlserverteam.com/mysql-8-0-p…
  • dev.mysql.com/worklog/tas…
  • bugs.mysql.com/bug.php?id=…
You may also be interested in:
  • Detailed explanation of new relational database features in MySQL 8.0
  • A brief discussion on the pitfalls and solutions of the new features of MySQL 8.0 (summary)
  • MySQL 8 new features: how to modify persistent global variables
  • MySQL 8 new features: Descending index details
  • The role of MySQL 8's new feature window functions

<<:  avue-crud implementation example of multi-level complex dynamic header

>>:  HTML Code Writing Guide

Recommend

How to view the IP address of the Docker container

I always thought that Docker had no IP address. I...

The latest mysql-5.7.21 installation and configuration method

1. Unzip the downloaded MySQL compressed package ...

Display special symbols in HTML (with special character correspondence table)

Problem Reproduction When using HTML for editing,...

How to use ss command instead of netstat in Linux operation and maintenance

Preface When operating and managing Linux servers...

Docker-compose creates a bridge, adds a subnet, and deletes a network card

1. Create a docker network card [root@i ~]# brctl...

Share some uncommon but useful JS techniques

Preface Programming languages ​​usually contain v...

MySQL data operation-use of DML statements

illustrate DML (Data Manipulation Language) refer...

Specific use of the wx.getUserProfile interface in the applet

Recently, WeChat Mini Program has proposed adjust...

Reasons why MySQL 8.0 statistics are inaccurate

Preface Whether it is Oracle or MySQL, the new fe...

Use CSS to draw a file upload pattern

As shown below, if it were you, how would you ach...

The whole process of developing a Google plug-in with vue+element

Simple function: Click the plug-in icon in the up...

This article takes you into the world of js data types and data structures

Table of contents 1. What is dynamic typing? 2. D...