This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

preface

Since the first release of MySQL 8.0.11 in 2018, MySQL has been updated to 8.0.26, which is a significant performance improvement over the stable 5.7 release.

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

This article focuses on a new feature in MySQL 8.0: global parameter persistence

Global parameter persistence

MySQL 8.0 supports online modification of global parameters and persistence. If you add the PERSIST keyword, you can PERSIST the modified parameters to a new configuration file (mysqld-auto-.cnf). When you restart MySQL, you can obtain the latest configuration parameters from the file.

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

Enable this feature and use the special syntax SET PERSIST to SET any dynamically modifable global variable!

• SET PERSIST

The mysqlD-auto-cnf statement modifies the value of a variable in memory and writes the modified value to the mysqld-auto-cnf data directory.

• SET PERSIST_ONLY

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

In order tomax_connectionsTake parameters 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)
Copy the code

CNF: mysqld-auto-cnf: mysqld-auto-cnf: mysqld-auto-cnf: mysqld-auto-cnf: mysqld-auto-cnf: mysqld-auto-cnf: mysqld-auto-cnf: mysqld-auto-cnf

{
    "Version": 1."mysql_server": {
        "max_connections": {
            "Value": "300"."Metadata": {
                "Timestamp": 1632575065787609."User": "root"."Host": "localhost"}}}}Copy the code

📢 Note: even if you modify the configuration through SET PERSIST and nothing changes, it is written to the mysqld-auto-.cnf file. But you can restore the original DEFAULT by setting it to DEFAULT!

If you want to restore the max_connections parameter to its original default value, simply do:

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)
Copy the code

If you want to remove all global persistence parameters, simply execute:

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

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

MySQL > delete mysqld-auto. CNF from mysqld-auto.

Write in the last

The main code: Commit f2bc0f89b7f94cc8fe963d08157413a01d14d994

Main entry function (8.0.0) :

The interface functions are mostly defined in the SQL /persisted_variable.cc file: Persisted_variables_cache::load_persist_file(); Set the configuration read from the file by parsing the validity of JSON and storing it in memory: Persisted_variables_cache::set_persist_options When running the SET PERSIST command, Call Persisted_variables_cache::set_variable to update the values stored in the memory and write them to the mysqld-auto. CNF file by Persisted_variables_cache::flush_to_fileCopy the code

Reference Documents:

• Mysqlserverteam.com/mysql-8-0-p…
• Dev.mysql.com/worklog/tas…
• Bugs.mysql.com/bug.php?id=…

---

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.

❤️ technical exchange can follow the public number: Lucifer think twice before you do ❤️