将mysql的变量置为0_MySQL 8.0 全局变量的修改持久化 set persist
全局變量的修改持久化
在8之前的版本中，對于全局變量的修改，其只會影響其內存值，而不會持久化到配置文件中。數據庫重啟，又會恢復成修改前的值。從8開始，可通過SET PERSIST命令將全局變量的修改持久化到配置文件中。
修改持久化
mysql>?show?variables?like?'%max_connections%';
+------------------------+-------+
|?Variable_name??????????|?Value?|
+------------------------+-------+
|?max_connections????????|?151???|
|?mysqlx_max_connections?|?100???|
+------------------------+-------+
2?rows?in?set?(0.03?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???|
|?mysqlx_max_connections?|?100???|
+------------------------+-------+
2?rows?in?set?(0.00?sec)
修改后的保存位置
在數據庫啟動時，會首先讀取其它配置文件，最后才讀取mysqld-auto.cnf文件。不建議手動修改該文件，其有可能導致數據庫在啟動過程中因解析錯誤而失敗。如果出現這種情況，可手動刪除mysqld-auto.cnf文件或將persisted_globals_load變量設置為off來避免該文件的加載。
scutech@scutech:~$?cat?/mysql/data/mysqld-auto.cnf
{?"Version"?:?1?,?"mysql_server"?:?{?"max_connections"?:?{?"Value"?:?"300"?,?"Metadata"?:?{?"Timestamp"?:?1583910814250180?,?"User"?:?"root"?,?"Host"?:?"localhost"?}?}?}?}
mysql>?select?*?from?performance_schema.persisted_variables;
+-----------------+----------------+
|?VARIABLE_NAME???|?VARIABLE_VALUE?|
+-----------------+----------------+
|?max_connections?|?300????????????|
+-----------------+----------------+
1?row?in?set?(0.01?sec)12345678910
MySQL?[(none)]>?show?variables?like?'%persisted_globals_load%';
+------------------------+-------+
|?Variable_name??????????|?Value?|
+------------------------+-------+
|?persisted_globals_load?|?ON????|
+------------------------+-------+
1?row?in?set?(0.06?sec)
持久化為默認值
還可以通過下述方式將全局變量持久化為默認值。注意，是默認值，而不是修改前的值。
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)
mysql>?show?variables?like?'%max_connections%';
+------------------------+-------+
|?Variable_name??????????|?Value?|
+------------------------+-------+
|?max_connections????????|?151???|
|?mysqlx_max_connections?|?100???|
+------------------------+-------+
2?rows?in?set?(0.01?sec)
scutech@scutech:~$?cat?/mysql/data/mysqld-auto.cnf
{?"Version"?:?1?,?"mysql_server"?:?{?"max_connections"?:?{?"Value"?:?"151"?,?"Metadata"?:?{?"Timestamp"?:?1583911527762399?,?"User"?:?"root"?,?"Host"?:?"localhost"?}?}?}?}
這個命令同“set global max_connections=default”類似，都會將變量的值設置為默認值，只不過前者還會將默認值持久化到配置文件中。
清空持久化變量
對于已經持久化了變量，可通過reset persist命令清除掉，注意，其只是清空mysqld-auto.cnf和performance_schema.persisted_variables中的內容，對于已經修改了的變量的值，不會產生任何影響。
mysql>?reset?persist;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?*?from?performance_schema.variables_info?where?variable_source?like?'PERSISTED'\G
Empty?set?(0.04?sec)
cat?/mysql/data/mysqld-auto.cnf
{?"Version"?:?1?,?"mysql_server"?:?{??}?}
修改只讀變量
但是對于read only 的參數，修改參數后需要重啟才能生效
mysql>?set?persist?innodb_log_file_size=2073741824;
ERROR?1238?(HY000):?Variable?'innodb_log_file_size'?is?a?read?only?variable
mysql>?set?persist_only?innodb_log_file_size=2073741824;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?show?variables?like?'innodb_log_file_size';
+----------------------+-----------+
|?Variable_name????????|?Value?????|
+----------------------+-----------+
|?innodb_log_file_size?|?104857600?|
+----------------------+-----------+
1?row?in?set?(0.02?sec)
mysql>?restart?;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?show?variables?like?'innodb_log_file_size';
No?connection.?Trying?to?reconnect...
Connection?id:????8
Current?database:?***?NONE?***
+----------------------+------------+
|?Variable_name????????|?Value??????|
+----------------------+------------+
|?innodb_log_file_size?|?2073034752?|
+----------------------+------------+
1?row?in?set?(0.02?sec)
mysql>?select?*?from?performance_schema.variables_info?where?variable_source?like?'PERSISTED'\G
***************************?1.?row?***************************
VARIABLE_NAME:?innodb_log_file_size
VARIABLE_SOURCE:?PERSISTED
VARIABLE_PATH:?/mysql/data/mysqld-auto.cnf
MIN_VALUE:?4194304
MAX_VALUE:?18446744073709551615
SET_TIME:?2020-03-11?15:55:20.334556
SET_USER:?root
SET_HOST:?localhost
1?row?in?set?(0.01?sec)
mysql>?system?cat??/mysql/data/mysqld-auto.cnf
{?"Version"?:?1?,?"mysql_server"?:?{?"mysql_server_static_options"?:?{?"innodb_log_file_size"?:?{?"Value"?:?"2073741824"?,?"Metadata"?:?{?"Timestamp"?:?1583913320334556?,?"User"?:?"root"?,?"Host"?:?"localhost"?}?}?}?}?}mysql>
mysql>?system?ls?-lh?/mysql/data/ib_logfile*
-rw-r-----?1?mysql?mysql?2.0G?Mar?11?15:56?/mysql/data/ib_logfile0
-rw-r-----?1?mysql?mysql?2.0G?Mar?11?15:56?/mysql/data/ib_logfile1
總結
以上是生活随笔為你收集整理的将mysql的变量置为0_MySQL 8.0 全局变量的修改持久化 set persist的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql设计的步骤_mysql笔记8_
- 下一篇： spring boot和spring c