224 阿裏雲技術社區[雲棲]

MySQL運維實戰（一）之係統變量潛規則

Agenda

踩坑經曆
測試用例
結論
實戰用途

一、踩坑經曆

設置了slow log 的時間，但是抓不到正確的sql
設置了read_only ，為啥還有寫入進來
設置了sql_safe_update , 為啥還能全表刪除
測試方法的不對，導致設置了read_only後，有的時候可以insert，有的時候不可以insert

太多這樣的問題, 所以打算一窺究竟

二、測試用例

測試設置參數後，是否會生效

2.1 官方文檔說明

https://dev.mysql.com/doc/refman/5.7/en/set-variable.html

* 重點說明

If you change a session system variable, the value remains in effect within your session until you change the variable to a different value or the session ends. The change has no effect on other sessions.

If you change a global system variable, the value is remembered and used for new sessions until you change the variable to a different value or the server exits. The change is visible to any client that accesses the global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any current client sessions (not even the session within which the SET GLOBAL statement occurred).

官方重點說明，設置global變量的時候，隻對後麵連接進來的session生效，對當前session和之前的session不生效
接下來，我們好好測試下

2.2 係統變量的Scope

1. Global : 全局級別
    set global variables = xx;  --正確
    set variables = xx; --報錯 （因為是scope=Global，所以不能設置session變量 ）

2. Session : 會話級別
    set variables = xx; --正確
    set global variables = xx;  --報錯 （因為是Scope=session，所以不能設置Global變量）

3. Both : 兩者皆可
    3.1 Global : set global variables = xx; --正確（因為是scope=both，他既可以設置全局變量，也可以設置session變量）
    3.2 Session : set variables = xx;  --正確（因為是scope=both，他既可以設置全局變量，也可以設置session變量）

2.3 Session 級別測試

1. session 級別的變量代表：sql_log_bin
2. 該類型的變量，設置後，隻會影響當前session，其他session不受影響

2.4 Global 級別測試

變量代表

1. Global 級別的變量代表：read_only , log_queries_not_using_indexes

測試一

* processlist_id = 100:

lc_rx:lc> select @@global.log_queries_not_using_indexes;
+----------------------------------------+
| @@global.log_queries_not_using_indexes |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set (0.00 sec)


lc_rx:lc> select * from lc_1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)


此時查看slow log，並未發現任何slow


* processlist_id = 120:

dba:(none)> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)

* processlist_id = 100:

lc_rx:lc> select @@global.log_queries_not_using_indexes;
+----------------------------------------+
| @@global.log_queries_not_using_indexes |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)


lc_rx:lc> select * from lc_1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

此時，去發現slow log

# Time: 2017-08-04T16:05:04.303005+08:00
# User@Host: lc_rx[lc_rx] @ localhost []  Id:   296
# Query_time: 0.000149  Lock_time: 0.000081 Rows_sent: 5  Rows_examined: 5
SET timestamp=1501833904;
select * from lc_1;


* 結論
    說明全局參數變量不管是在session前，還是session後設置，都是立馬讓所有session生效

測試二

dba:(none)> show processlist;
+-----+-------+----------------------+------+------------------+---------+---------------------------------------------------------------+------------------+
| Id  | User  | Host                 | db   | Command          | Time    | State                                                         | Info             |
+-----+-------+----------------------+------+------------------+---------+---------------------------------------------------------------+------------------+
| 303 | lc_rx | localhost            | lc   | Sleep            |      83 |                                                               | NULL             |
| 304 | dba   | localhost            | NULL | Query            |       0 | starting                                                      | show processlist |
+-----+-------+----------------------+------+------------------+---------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

* PROCESSLIST_ID=303

lc_rx:lc> select @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)


lc_rx:lc> insert into lc_1 select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0


* PROCESSLIST_ID=304

dba:(none)> set global read_only=on;
Query OK, 0 rows affected (0.00 sec)


* PROCESSLIST_ID=303

lc_rx:lc> select @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

lc_rx:lc> insert into lc_1 select 3;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

* 結論：
    PROCESSLIST_ID=304 設置的參數，導致PROCESSLIST_ID=303 也生效了

2.5 如何查看當下所有session中的係統變量值呢？

5.7 可以看到
遺憾的是：隻能看到Both和session的變量，scope=global沒法看(因為會立即生效)


dba:(none)> select * from performance_schema.variables_by_thread as a,\
    ->     (select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_COMMAND,PROCESSLIST_STATE from performance_schema.threads where PROCESSLIST_USER<>'NULL') as b\
    ->         where a.THREAD_ID = b.THREAD_ID and a.VARIABLE_NAME = 'sql_safe_updates';
+-----------+------------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
| THREAD_ID | VARIABLE_NAME    | VARIABLE_VALUE | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_STATE                                             |
+-----------+------------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
|       313 | sql_safe_updates | OFF            |       313 |            232 | repl             | xx.xxx.xxx.xxx   | Binlog Dump GTID    | Master has sent all binlog to slave; waiting for more updates |
|       381 | sql_safe_updates | ON             |       381 |            300 | dba              | localhost        | Query               | Sending data                                                  |
+-----------+------------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

2.6 Both 級別測試

用我們剛剛學到的知識，來驗證更加快速和靠譜

變量代表

1. Both 級別的變量代表：sql_safe_updates , long_query_time

測試

* 第一次查看long_query_time參數，PROCESSLIST_ID=307，308，309 都是一樣的，都是300s

dba:(none)> select * from performance_schema.variables_by_thread as a,     (select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_COMMAND,PROCESSLIST_STATE from performance_schema.threads where PROCESSLIST_USER<>'NULL') as b         where a.THREAD_ID = b.THREAD_ID and a.VARIABLE_NAME = 'long_query_time';
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_STATE                                             |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
|       388 | long_query_time | 300.000000     |       388 |            307 | dba              | localhost        | Sleep               | NULL                                                          |
|       389 | long_query_time | 300.000000     |       389 |            308 | dba              | localhost        | Query               | Sending data                                                  |
|       390 | long_query_time | 300.000000     |       390 |            309 | dba              | localhost        | Sleep               | NULL                                                          |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)


* 我們再PROCESSLIST_ID=308的session上進行設置long_query_time=100，我們能看到這個時候所有的session都還是300，沒有生效

dba:(none)> set global long_query_time=100;
Query OK, 0 rows affected (0.00 sec)

dba:(none)> select * from performance_schema.variables_by_thread as a,     (select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_COMMAND,PROCESSLIST_STATE from performance_schema.threads where PROCESSLIST_USER<>'NULL') as b         where a.THREAD_ID = b.THREAD_ID and a.VARIABLE_NAME = 'long_query_time';
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_STATE                                             |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
|       388 | long_query_time | 300.000000     |       388 |            307 | dba              | localhost        | Sleep               | NULL                                                          |
|       389 | long_query_time | 300.000000     |       389 |            308 | dba              | localhost        | Query               | Sending data                                                  |
|       390 | long_query_time | 300.000000     |       390 |            309 | dba              | localhost        | Sleep               | NULL                                                          |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

* 接下來，我們再斷開309，重連時，processlist id 應該是310，這時候的結果就是100s了。這一點說明，在執行set global參數後進來的session才會生效，對當前session和之前的session不生效

dba:(none)> select * from performance_schema.variables_by_thread as a,     (select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_COMMAND,PROCESSLIST_STATE from performance_schema.threads where PROCESSLIST_USER<>'NULL') as b         where a.THREAD_ID = b.THREAD_ID and a.VARIABLE_NAME = 'long_query_time';
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_STATE                                             |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
|       388 | long_query_time | 300.000000     |       388 |            307 | dba              | localhost        | Sleep               | NULL                                                          |
|       389 | long_query_time | 300.000000     |       389 |            308 | dba              | localhost        | Query               | Sending data                                                  |
|       391 | long_query_time | 100.000000     |       391 |            310 | dba              | localhost        | Sleep               | NULL                                                          |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

三、結論

官方文檔也不是很靠譜，也有很多差強人意的地方
自己動手，測試驗證的時候做好測試方案和計劃，以免遺漏導致測試失敗，得出錯誤的結論

四、實戰意義

4.1 項目背景

a. 修改sql_safe_update=on, 這裏麵有很多難點，其中的一個難點就是如何讓所有session生效

4.2 解決方案

MySQL5.7+

結合今天的知識，通過performance_schema.variables_by_thread，performance_schema.threads表，可以知道哪些變量已經生效，哪些變量還沒生效

MySQL5.7-

1. 如果對今天的Both變量知識理解了，不難發現，還有一個變通的辦法

2. 執行這條命令即可
    2.1 set global $both_scope_variables = on|off
    2.2 select max(ID) from information_schema.PROCESSLIST;

3. kill掉所有小於processlist < max(ID) 的session即可
    3.1 當然，係統用戶進程你不能kill，read_only的用戶你沒必要kill
    3.2 其他的自行腦補

最後更新：2017-08-13 22:31:32

MySQL運維實戰（一）之係統變量潛規則

Agenda

一、踩坑經曆

二、測試用例

2.1 官方文檔說明

2.2 係統變量的Scope

2.3 Session 級別測試

2.4 Global 級別測試

2.5 如何查看當下所有session中的係統變量值呢？

2.6 Both 級別測試

三、結論

四、實戰意義

4.1 項目背景

4.2 解決方案

上一篇：【印象】2017雲棲大會城市峰會：上海、南京、成都等精彩回顧

下一篇：日處理20億數據，實時用戶行為服務係統架構實踐

相關內容

熱門內容

最新內容

MySQL運維實戰（一）之 係統變量潛規則

Agenda

一、踩坑經曆

二、測試用例

2.1 官方文檔說明

2.2 係統變量的Scope

2.3 Session 級別測試

2.4 Global 級別測試

2.5 如何查看當下所有session中的係統變量值呢？

2.6 Both 級別測試

三、結論

四、實戰意義

4.1 項目背景

4.2 解決方案

上一篇： 【印象】2017雲棲大會城市峰會：上海、南京、成都等精彩回顧

下一篇： 日處理20億數據，實時用戶行為服務係統架構實踐

相關內容

熱門內容

最新內容

MySQL運維實戰（一）之係統變量潛規則

上一篇：【印象】2017雲棲大會城市峰會：上海、南京、成都等精彩回顧

下一篇：日處理20億數據，實時用戶行為服務係統架構實踐