Starting with MySQL 8.0 we are introducing a new feature which will allow configuration variables to be persisted. This means that for a number of configuration settings, you will no longer need to manually edit a configuration file and can instead make modifications directly via an SQL connection.
Background
MySQL has always offered the ability to edit settings via configuration files (for example: /etc/my.cnf), with changes taking effect the next time that the MySQL server restarts. Over time as compute resources have become more powerful, database caches have also become larger. This means that restarting a server has only become less desirable; as the time to come back online at full health increases.
In the MySQL team; we have addressed this problem from a number of angles:
- In MySQL 5.6 we introduced the ability to Save and restore the buffer pool state, in order to reload caches faster. In MySQL 5.7 this feature is now enabled by default.
- We have made it possible to change more configuration variables without restarting a MySQL server.
If a configuration variable is able to be changed without restarting the MySQL Server it is said to be DYNAMIC. For example, the setting innodb_buffer_pool_size was made dynamic in MySQL 5.7 and can be changed as follows:
|
1
2
3
|
# The value of the InnoDB Buffer Pool is changed to 512M mysql> SET GLOBAL innodb_buffer_pool_size = 512 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) |
Upon the next restart, the value of the innodb_buffer_pool_size will reset to the previous value as it was specified in the configuration file(s). That is to say that the value does not persist, and that a DBA wishing to change a setting without a restart should change it in two places (in the running server and in a configuration file). Manually editing a configuration file and waiting potentially months between restarts can introduce risk that there was a typo or error in the file which prevents MySQL from starting.
As a further usability issue, for some deployments of MySQL the DBA may not be granted access to the local filesystem to edit configuration files. It is also difficult for automated tools to modify and persist variables in a standard way (e.g. a load balancing script which flips offline_mode, read_only depending on current topology state) that will work for all MySQL users.
New features introduced in 8.0
SET PERSIST Statement
In Worklog #8688 we introduced the ability to set a global variable and have the change persist through restarts:
|
1
2
|
mysql> SET PERSIST innodb_buffer_pool_size = 512 * 1024 * 1024; Query OK, 0 rows affected (0.01 sec) |
With this new feature, a MySQL server can be configured and managed over a SQL connection thus removing manual file operations (on configuration files) to be done by DBAs. This feature addresses the usability issues described above, and allows MySQL to be more easily deployed and configured on cloud platforms.
Any GLOBAL DYNAMIC configuration variable can be modified with the SET PERSIST statement. Under the hood the settings will be persisted to a file named mysqld-auto.cnf which will be created in the data directory. This file will be read during server startup just like any other configuration file, and all variables present in this file will be applied as the highest priority. That means the file mysqld-auto.cnf will be the last file to be applied on server startup (even after command-line options) and takes precedence if a specific setting has been specified in more than one location.
The file mysqld-auto.cnf is created the first time a SET PERSIST statement is executed. Further SET PERSIST statement executions will append the contents to this file. This file is in JSON format and can be parsed using json parser. Here is an example showing the feature working with a server restart:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
mysql> select @@global.max_connections; +--------------------------+ | @@global.max_connections | +--------------------------+ | 151 | +--------------------------+ 1 row in set (0.00 sec) mysql> SET PERSIST max_connections=38; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.max_connections; +--------------------------+ | @@global.max_connections | +--------------------------+ | 38 | +--------------------------+ 1 row in set (0.00 sec) mysql> shutdown; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.max_connections; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 4 Current database: *** NONE *** +--------------------------+ | @@global.max_connections | +--------------------------+ | 38 | +--------------------------+ 1 row in set (0.01 sec) |
A persisted variable can be forgotten (aka reset) by explicitly setting it to the value DEFAULT. This will remove it from mysqld-auto.cnf and the previous precedence of configuration files will apply again. As a demonstration:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
mysql> select @@global.sort_buffer_size; +---------------------------+ | @@global.sort_buffer_size | +---------------------------+ | 262144 | +---------------------------+ 1 row in set (0.02 sec) mysql> SET PERSIST sort_buffer_size=512000; Query OK, 0 rows affected (0.00 sec) mysql> shutdown; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.sort_buffer_size; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 4 Current database: *** NONE *** +---------------------------+ | @@global.sort_buffer_size | +---------------------------+ | 512000 | +---------------------------+ 1 row in set (0.02 sec) mysql> SET PERSIST sort_buffer_size=DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql> shutdown; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.sort_buffer_size; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 4 Current database: *** NONE *** +---------------------------+ | @@global.sort_buffer_size | +---------------------------+ | 262144 | +---------------------------+ 1 row in set (0.03 sec) |
variables_info Performance Schema Table
There is a potential debugging issue present from having multiple locations to edit a configuration variable (/etc/my.cnf and the mysqld-auto.cnf file). While this is not actually a new problem (it’s always been possible to specify a setting twice in two different configuration files), persistent configuration doesn’t make it any easier.
To address this problem, Worklog #8688 also introduces a Performance Schema table to show the source of where a configuration variable has been set as well as providing the minimum and maximum values. In the below output we can see that variable source for max_connections is PERSISTED which means variable is set from mysqld-auto.cnf file:
|
1
2
3
4
5
6
7
8
|
mysql> select * from performance_schema.variables_info where variable_name like 'max_conn%'; +--------------------+-----------------+----------------------------------------------------+-----------+------------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | +--------------------+-----------------+----------------------------------------------------+-----------+------------+ | max_connect_errors | COMPILED | | 1 | 4294967295 | | max_connections | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 1 | 100000 | +--------------------+-----------------+----------------------------------------------------+-----------+------------+ 2 rows in set (0.01 sec) |
Below query reports all the variables which are set from command line:
|
1
2
3
4
5
6
7
|
mysql> select * from performance_schema.variables_info where variable_source = 'COMMAND_LINE'; +---------------+-----------------+---------------+-----------+-----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | +---------------+-----------------+---------------+-----------+-----------+ | log_syslog | COMMAND_LINE | | 0 | 0 | +---------------+-----------------+---------------+-----------+-----------+ 1 row in set (0.01 sec) |
If any variable is read from global configuration files like C:\my.ini or C:\Windows\my.ini then we can check for the same from variables_info table using the below query:
|
1
2
3
4
5
6
7
8
9
10
|
mysql> select * from performance_schema.variables_info where variable_source = 'GLOBAL'; +----------------------------------+-----------------+---------------+-----------+----------------------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | +----------------------------------+-----------------+---------------+-----------+----------------------+ | connect_timeout | GLOBAL | C:\my.ini | 2 | 31536000 | | innodb_buffer_pool_size | GLOBAL | C:\my.ini | 5242880 | 9223372036854775807 | | innodb_log_file_size | GLOBAL | C:\my.ini | 4194304 | 18446744073709551615 | | performance_schema_accounts_size | GLOBAL | C:\my.ini | -1 | 1048576 | +----------------------------------+-----------------+---------------+-----------+----------------------+ 4 rows in set (0.01 sec) |
If a variable is dynamically changed in any session then this is also reflected in this new table. Below example illustrates the same:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> select * from performance_schema.variables_info where variable_name= 'max_connections'; +-----------------+-----------------+----------------------------------------------------+-----------+-----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | +-----------------+-----------------+----------------------------------------------------+-----------+-----------+ | max_connections | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 1 | 100000 | +-----------------+-----------------+----------------------------------------------------+-----------+-----------+ 1 row in set (0.01 sec) mysql> mysql> SET GLOBAL max_connections=71; Query OK, 0 rows affected (0.00 sec) mysql> select * from performance_schema.variables_info where variable_name= 'max_connections'; +-----------------+-----------------+---------------+-----------+-----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | +-----------------+-----------------+---------------+-----------+-----------+ | max_connections | DYNAMIC | | 1 | 100000 | +-----------------+-----------------+---------------+-----------+-----------+ 1 row in set (0.01 sec) |
A new read only system variable named persisted-globals-load is introduced which will help DBA to enable or disable reading of this new configuration file. By default this variable is set to ON. The below output shows how server behaves when this variable is ON or OFF:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
mysql> select @@global.persisted_globals_load; +---------------------------------+ | @@global.persisted_globals_load | +---------------------------------+ | 1 | +---------------------------------+ 1 row in set (0.02 sec) mysql> select * from performance_schema.variables_info where variable_source='PERSISTED'; +---------------------+-----------------+----------------------------------------------------+-----------+----------------------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | +---------------------+-----------------+----------------------------------------------------+-----------+----------------------+ | long_query_time | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 0 | 4719230590769954816 | | max_connections | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 1 | 100000 | | max_heap_table_size | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 16384 | 18446744073709551615 | | slave_net_timeout | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 1 | 31536000 | | sort_buffer_size | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 32768 | 4294967295 | +---------------------+-----------------+----------------------------------------------------+-----------+----------------------+ 5 rows in set (0.01 sec) mysql> shutdown; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.persisted_globals_load; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current database: *** NONE *** +---------------------------------+ | @@global.persisted_globals_load | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set (0.02 sec) mysql> select * from performance_schema.variables_info where variable_source='PERSISTED'; Empty set (0.01 sec) |
To know all the variables which are set from command line and any of the configuration files run below query:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> select * from performance_schema.variables_info where variable_source!='COMPILED'; +----------------------------------+-----------------+----------------------------------------------------+-----------+----------------------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | +----------------------------------+-----------------+----------------------------------------------------+-----------+----------------------+ | connect_timeout | GLOBAL | C:\my.ini | 2 | 31536000 | | foreign_key_checks | DYNAMIC | | 0 | 0 | | innodb_buffer_pool_size | GLOBAL | C:\my.ini | 5242880 | 9223372036854775807 | | innodb_log_file_size | GLOBAL | C:\my.ini | 4194304 | 18446744073709551615 | | log_syslog | COMMAND_LINE | | 0 | 0 | | long_query_time | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 0 | 4719230590769954816 | | max_connections | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 1 | 100000 | | max_heap_table_size | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 16384 | 18446744073709551615 | | performance_schema_accounts_size | GLOBAL | C:\my.ini | -1 | 1048576 | | slave_net_timeout | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 1 | 31536000 | | sort_buffer_size | PERSISTED | C:\Users\bhsatish\mysql-trunk\data\mysqld-auto.cnf | 32768 | 4294967295 | +----------------------------------+-----------------+----------------------------------------------------+-----------+----------------------+ 11 rows in set (0.01 sec) |
Conclusion
We look forward to hearing from MySQL users as you begin trying out this new feature. If you encounter any problems with this new feature – or you have ideas for enhancements – please let us know here in the comments, open a bug report / feature request at bugs.mysql.com.
Thank you for using MySQL!