技术译文 | MySQL 8 持久化系统变量
作者：Arunjith Aravindan
翻译：管长龙
mysql> SET PERSIST max_connections = 1000;Query OK, 0 rows affected (0.00 sec)mysql> select @\G*************************** 1. row ***************************@: 10001 row in set (0.00 sec)
cat /var/lib/mysql/mysqld-auto.cnf{ "Version" : 1 , "mysql_server" : {"max_connections" : {"Value" : "1000" , "Metadata" : {"Timestamp" : 1602543199335909 , "User" : "root" , "Host" : "localhost" } } } }
mysql> SET PERSIST innodb_log_file_size=50331648*2;ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read-only variablemysql> set persist_only innodb_log_file_size=50331648*2;Query OK, 0 rows affected (0.01 sec)
mysql> RESET PERSIST;Query OK, 0 rows affected (0.00 sec)cat /var/lib/mysql/mysqld-auto.cnf{ "Version" : 1 , "mysql_server" : { } }mysql> select @;+-------------------+| @ |+-------------------+| 1000 |+-------------------+1 row in set (0.01 sec)
mysql> RESET PERSIST max_connections;Query OK, 0 rows affected (0.00 sec)mysql> RESET PERSIST innodb_max_dirty_pages_pct;ERROR 3615 (HY000): Variable innodb_max_dirty_pages_pct does not exist in persisted config filemysql>mysql> RESET PERSIST IF EXISTS innodb_max_dirty_pages_pct;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show warnings;| Warning | 3615 | Variable innodb_max_dirty_pages_pct does not exist in persisted config file |
mysql> select @ ;+-------------------------------------+| @ |+-------------------------------------+| 1 |+-------------------------------------+1 row in set (0.00 sec)grep -i persisted_globals_load /etc/my.cnfpersisted_globals_load=0mysql> restart;Query OK, 0 rows affected (0.00 sec)mysql> select @ ;+-----------------------------------+| @ |+-----------------------------------+| 0 |+----------------------------------+1 row in set (0.00 sec)mysql> select @;+-------------------+| @ |+-------------------+| 500 |+-------------------+1 row in set (0.00 sec)
mysql> CREATE USER 'admin_persist'@'localhost' IDENTIFIED BY '*********';Query OK, 0 rows affected (0.02 sec)mysql> GRANT SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN, SHUTDOWN on *.* to 'admin_persist'@'localhost';Query OK, 0 rows affected (0.03 sec)
mysql> select v.VARIABLE_NAME,g.VARIABLE_VALUE current_value,p.VARIABLE_VALUE as persist_value,SET_TIME,SET_USER,VARIABLE_SOURCE,VARIABLE_PATH from performance_schema.variables_info v JOIN performance_schema.persisted_variables p USING(VARIABLE_NAME) JOIN performance_schema.global_variables g USING(VARIABLE_NAME)\G*************************** 1. row ***************************: innodb_log_file_size: 50331648: 100663296: 2020-10-12 18:54:35.725177: arunVARIABLE_SOURCE: COMPILED:*************************** 2. row ***************************: max_connections: 1000: 1000: 2020-10-12 18:53:19.336115: rootVARIABLE_SOURCE: DYNAMIC:2 rows in set (0.06 sec)mysql> restart;Query OK, 0 rows affected (0.01 sec)select v.VARIABLE_NAME,g.VARIABLE_VALUE current_value,p.VARIABLE_VALUE as persist_value,SET_TIME,SET_USER,VARIABLE_SOURCE,VARIABLE_PATH from performance_schema.variables_info v JOIN performance_schema.persisted_variables p USING(VARIABLE_NAME) JOIN performance_schema.global_variables g USING(VARIABLE_NAME)\G*************************** 1. row ***************************: innodb_log_file_size: 100663296: 100663296: 2020-10-12 18:54:35.725177: arunVARIABLE_SOURCE: PERSISTED: /var/lib/mysql/mysqld-auto.cnf*************************** 2. row ***************************: max_connections: 1000: 1000: 2020-10-12 18:53:19.335909: rootVARIABLE_SOURCE: PERSISTED: /var/lib/mysql/mysqld-auto.cnf2 rows in set (0.16 sec)
技术译文 | MySQL 8 需要多大的 innodb_buffer_pool_instances 值（上）
技术译文 | MySQL 8 需要多大的 innodb_buffer_pool_instances 值（下）
社区近期动态
点一下“阅读原文”了解更多资讯
本文分享自微信公众号 - 爱可生开源社区（ActiontechOSS）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
