2019年7月3日 星期三

【MYSQL】 MySQL系統變量(system variables)

    MySQL系統變量(system variables)

什麼是系統變量 
   系統變量實際上用於控制數據庫的一些行為和方式的參數。例如我們啟動數據庫的時,設定內存大小,使用什麼隔離級別,或日誌文件大小,存放位置...等等很多。當然我們數據庫系統啟動後,有些系統變量(參數)也可以通過動態修改來及時調整數據庫。
   系統變量取值:都有默認值,可以在啟動時或是啟動後修改。
   設置範圍:【全局】或【會話 級別,全局級別需要super權限就是影響整個伺服器(更改當下前連進系統的不影響),會話級別只影響自身會話。
   設置方法:啟動前可以通過配置文件以及啟動選項來修改,啟動後通過SET子句來設置。
   生效週期:全局變量全局可見,但只影響在更改後所連接進伺服器的。當前會話及已登錄的會話不受影響。

   對於有關係到size大小的設置值,可以使用後綴K、M或G分別表示千字節、兆字節或gigabytes,不區分大小寫。
  

查詢測試的版本
mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 8.0.16                       |
| version_comment         | MySQL Community Server - GPL |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.12              |
| version_compile_zlib    | 1.2.11                       |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

修改變量及查詢變量的語法
 更改方式(藍色是變量名稱)查看語法(藍色是變量名稱)
Global全局
set global sort_buffer_size=value;
set @@global.sort_buffer_size=value;
select @@global.sort_buffer_size;
show global variables like ' sort_buffer_size';
Session會話
set session sort_buffer_size=value;
set @@session.sort_buffer_size=value;
set sort_buffer_size=value;
select @@sort_buffer_size;
select @@session.sort_buffer_size;
show session variables like 'sort_buffer_size';
Both both表示既可以是作為全局級別的,也可以作為會話級別的Global跟Session變更語法都可使用,看是針對全局或會話
Varies 是根據數據庫版本而定  
--查看全部系统变量
show variables;

因變量參數太多,可參考官方說明

Name
System VarVar ScopeDynamic
變量名系統變量級別動態YES.NO

Session Global   Both、級別的差別
(範圍分 Global、  Session Both)由上連結可以查看參數 Var Scope值
1.Session測試查看及變更 use_secondary_engine而官網寫 Var Scope 的var範圍是 Session
--查看該參數的設置
mysql> show variables like "use_secondary_engine%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| use_secondary_engine | ON    |
+----------------------+-------+
1 row in set (0.01 sec)

--修改變量為關(用session會話方式更改)
mysql> set use_secondary_engine=off;
Query OK, 0 rows affected (0.00 sec)

--修改完後查看
mysql> show variables like "use_secondary_engine%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| use_secondary_engine | OFF   |
+----------------------+-------+
1 row in set (0.00 sec)

--用全局方式修改變量會報錯(因為他不是global而是session)
mysql> set global use_secondary_engine=off;
ERROR 1228 (HY000): Variable 'use_secondary_engine' is a SESSION variable and can't be used with SET GLOBAL

--使用另一台機器,在修改後登入,因為是會話級別,所以前者操作,只會影響自身的連線,不會影響別人的連線,設定也會在登出後恢復
mysql> show variables like "use_secondary_engine%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| use_secondary_engine | ON    |
+----------------------+-------+
1 row in set (0.00 sec)



2.Global 測試查看及變更 sha256_password_proxy_users 官網寫 Var Scope 的var範圍是 Global
--查看該參數的設置
mysql> show variables like "sha256_password_proxy_users%";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| sha256_password_proxy_users | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

--用會話方式修改變量會報錯(因為他不是session而是global)
mysql> set sha256_password_proxy_users=on;
ERROR 1229 (HY000): Variable 'sha256_password_proxy_users' is a GLOBAL variable and should be set with SET GLOBAL

--用全局方式修改OK
mysql> set global sha256_password_proxy_users=on;
Query OK, 0 rows affected (0.00 sec)

--當下查詢變量改更改
mysql> show variables like "sha256_password_proxy_users%";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| sha256_password_proxy_users | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)


--使用另一台機器,測試不管在登入前或登入後的使用者皆都更改了
mysql> show variables like "use_secondary_engine%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| use_secondary_engine | ON    |
+----------------------+-------+
1 row in set (0.00 sec)


3.Both測試查看及變更 binlog_format 官網寫 Var Scope 的var範圍是 Both
#用會話方式更改沒問題
mysql> set binlog_format ="Mixed";
Query OK, 0 rows affected (0.01 sec)

#查詢也己變更
mysql> show variables like "binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

#但用全局方式查詢顯示全局仍為ROW
mysql> show global variables like "binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

#用全局方式變更
mysql> set global binlog_format ="Mixed";
Query OK, 0 rows affected (0.00 sec)

#用全局方式查詢也己更改
mysql> show global variables like "binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)


總結
官網顯示 Dynamic 動態 分YES或NO 
YES:可以進入DB內更改
NO :只能從配置文件my.cnf更改,重啟後才會生效

可能出現問題

--只能讀不能直接修改,要透過my.cnf修改重啟才可
mysql> set global back_log=152;
ERROR 1238 (HY000): Variable 'back_log' is a read only variable

--應該用global用成session
mysql> set sha256_password_proxy_users=on;
ERROR 1229 (HY000): Variable 'sha256_password_proxy_users' is a GLOBAL variable and should be set with SET GLOBAL

--應該用session用成global
mysql> set global use_secondary_engine=off;
ERROR 1228 (HY000): Variable 'use_secondary_engine' is a SESSION variable and can't be used with SET GLOBAL

沒有留言:

張貼留言

【MYSQL】MYSQL的SYS表說明(版本8.0)

mysql> use sys Reading table information for completion of table and column names You can turn off this feature to get a quicker s...