How to Persist Global Variables Without Using Option Files in MySQL 8.0

A really convenient feature in MySQL 8.0 is the ability to persist the values of global variables across server restarts, without writing them into an options file. This was developed primarily for the benefit of Cloud installations of MySQL, but is very handy for a DBA in on-premise installations too.

To use this feature you need to have the SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges.

To demonstrate, we'll increase the value of the max_connections system variable from its default of 151 to 152 and then restart the MySQL server to check that MySQL remembers the new value.

mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (#.## sec)

Use SET PERSIST to persist the change that we're about to make:

mysql> SET PERSIST max_connections = 152; Query OK, 0 rows affected (#.## sec)

You can also use the following variant:

mysql> SET @@persist.max_connections = 152; Query OK, 0 rows affected (0.01 sec)

Let's take advantage of another feature in MySQL 8.0: the RESTART SQL command. This enables you to restart the MySQL server without leaving the mysql prompt. You must have the SHUTDOWN privilege to use it:

mysql> RESTART; Query OK, 0 rows affected (#.## sec)

Did the server remember the change? Let's see:

mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 152 | +-----------------+-------+ 1 row in set (#.## sec)

In case you’re wondering how you would keep track of these changes if they’re not being written to an options file, MySQL 8.0 stores the change in a file called mysqld-auto.cnf, which resides in the data directory (typically /var/lib/mysql). This file also tells you who made the change, and when it was made:

# cat /var/lib/mysql/mysqld-auto.cnf { "Version":1, "mysql_server":{ "max_connections":{ "Value":"152", "Metadata":{ "Timestamp":1526635140519175, "User":"root", "Host":"localhost" } } } }

You can also query the Performance Schema for this information.

mysql> SELECT * FROM performance_schema.persisted_variables; +-----------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-----------------+----------------+ | max_connections | 152 | +-----------------+----------------+ 1 row in set (#.## sec)

Don't edit the mysqld-auto.cnf file or the persisted_variables table directly: let the server handle it. If you want to clear all of the persisted variable settings, execute RESET PERSIST.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569