{{ it.name }}
{{ it.text }}
作者:姚嵩
爱可生南区交付服务部经理,爱好音乐,动漫,电影,游戏,人文,美食,旅游,还有其他。
虽然都很菜,但毕竟是爱好。
本文来源:原创投稿
*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。
MySQL 变量存在的介质及意义:
MySQL 的变量存在于内存、以及配置文件中;
其中内存中的变量是在运行时生效,配置文件中的变量是在 mysqld 程序启动时加载到内存中;
MySQL 8 以后,对于持久化变量的值,有了其他方式:
SET PERSIST
语句可以修改内存中变量的值,并且将修改后的值写⼊数据⽬录中的 mysqld-auto.cnf 中。
SET PERSIST_ONLY
语句不会修改内存中变量的值,只是将修改后的值写⼊数据⽬录中的 mysqld-auto.cnf 中。
SET PERSIST
需要 system_variables_admin 权限
SET PERSIST_ONLY
需要 system_variables_admin 和 persist_ro_variables_admin 权限
set persist max_connections=500 ;
set @@persist.max_connections=500 ;
# 效果同上
set persist max_connections=default ;
set persist_only back_log=500 ;
set @@persist_only.back_log=500 ;
set persist_only back_log=1000 ,persist max_connections=2000 ;
select * from performances_chema.persisted_variables ;
reset persist back_log ;
reset persist if exists back_log ;
reset persist ;
涉及文件:
mysql> show variables like "max_connections";+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 200 |+-----------------+-------+1 row in set (0.00 sec)
mysql> set persist max_connections=300 ;Query OK, 0 rows affected (0.00 sec)mysql> show variables like "max_connections";+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 300 |+-----------------+-------+1 row in set (0.00 sec)
mysql> set persist max_connections=default ;Query OK, 0 rows affected (0.00 sec)mysql> show variables like "max_connections";+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+1 row in set (0.01 sec)
mysql> set persist_only max_connections=300 ;Query OK, 0 rows affected (0.00 sec)mysql> show variables like "max_connections";+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+1 row in set (0.00 sec)
mysql> select * from performance_schema.persisted_variables ;+-----------------+----------------+| VARIABLE_NAME | VARIABLE_VALUE |+-----------------+----------------+| max_connections | 300 |+-----------------+----------------+1 row in set (0.00 sec)
mysql> reset persist ;Query OK, 0 rows affected (0.00 sec)mysql> select * from performance_schema.persisted_variables ;Empty set (0.00 sec)mysql> set session net_read_timeout=20;Query OK, 0 rows affected (0.00 sec)mysql> select @@global.net_read_timeout,@@session.net_read_timeout ;+---------------------------+----------------------------+| @@global.net_read_timeout | @@session.net_read_timeout |+---------------------------+----------------------------+| 30 | 20 |+---------------------------+----------------------------+1 row in set (0.00 sec)
mysql> set persist net_read_timeout=50 ;Query OK, 0 rows affected (0.00 sec)mysql> select @@global.net_read_timeout,@@session.net_read_timeout ;+---------------------------+----------------------------+| @@global.net_read_timeout | @@session.net_read_timeout |+---------------------------+----------------------------+| 50 | 20 |+---------------------------+----------------------------+1 row in set (0.00 sec)mysql> select * from performance_schema.persisted_variables ;+------------------+----------------+| VARIABLE_NAME | VARIABLE_VALUE |+------------------+----------------+| net_read_timeout | 50 |+------------------+----------------+1 row in set (0.00 sec)
mysql> set @@session.net_read_timeout=@@global.net_read_timeout ;Query OK, 0 rows affected (0.00 sec)mysql> select @@global.net_read_timeout,@@session.net_read_timeout ;+---------------------------+----------------------------+| @@global.net_read_timeout | @@session.net_read_timeout |+---------------------------+----------------------------+| 50 | 50 |+---------------------------+----------------------------+1 row in set (0.00 sec)