[[439802]]

 前言：

默認情況下，我們的 MySQL 實例是可讀寫的。但有些情況下，我們可以將整個實例設置為只讀狀態，比如做遷移維護的時候或者將從庫設為只讀。本篇文章我們來看下 MySQL 設置只讀相關知識。

1.關于 read_only 參數

MySQL系統中，提供有 read_only 和 super_read_only 兩個只讀參數，參考官方文檔，這里介紹下這兩個參數的作用：

read_only 參數默認不開啟，開啟后會阻止沒有 super 權限的用戶執行數據庫變更操作。開啟后，普通權限用戶執行插入、更新、刪除等操作時，會提示 --read-only 錯誤。但具有 super 權限的用戶仍可執行變更操作。

super_read_only 參數同樣默認關閉，開啟后不僅會阻止普通用戶，也會阻止具有 super 權限的用戶對數據庫進行變更操作。

read_only 和 super_read_only 是有關聯的，二者之間的關系如下：

• 設置 super_read_only=on ，也就隱式地設置了 read_only=on。
• 設置 read_only=off ，也就隱式地設置了 super_read_only=off。
• 可以單獨開啟 read_only 而不開啟 super_read_only。

不過，從庫開啟 read_only 并不影響主從同步，即 salve 端仍然會讀取 master 上的日志，并且在 slave 實例中應用日志，保證主從數據庫同步一致。(經測試，從庫端開啟 super_read_only 仍不影響主從同步。)

下面我們具體來操作下，看下 read_only 參數的用法：

# 查看 read_only 參數 
mysql> show global variables like '%read_only%'; 
+-----------------------+-------+ 
| Variable_name         | Value | 
+-----------------------+-------+ 
| innodb_read_only      | OFF   | 
| read_only             | OFF   | 
| super_read_only       | OFF   | 
| transaction_read_only | OFF   | 
| tx_read_only          | OFF   | 
+-----------------------+-------+ 
 
# 動態修改 read_only 參數 （若想重啟生效 則需將 read_only = 1 加入配置文件中） 
mysql> set global read_only = 1; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> show global variables like 'read_only'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| read_only     | ON    | 
+---------------+-------+ 
 
# read_only 開啟的情況下 操作數據 
# 使用超級權限用戶 
mysql> create table tb_a (a int); 
Query OK, 0 rows affected (0.05 sec) 
# 使用普通權限用戶 
mysql> create table tb_b (b int);  
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement 
 
# 開啟 super_read_only，再次使用超級權限用戶來操作數據 
mysql> set global super_read_only = 1; 
Query OK, 0 rows affected (0.00 sec) 
mysql> show global variables like 'super_read_only'; 
+-----------------+-------+ 
| Variable_name   | Value | 
+-----------------+-------+ 
| super_read_only | ON    | 
+-----------------+-------+ 
mysql> create table tb_c (c int);   
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement 
 
# 關閉 read_only 參數 
mysql> set global read_only = 0; 
Query OK, 0 rows affected (0.00 sec) 

2.flush tables with read lock 設置

除了 read_only 參數外，執行 flush tables with read lock 也可將數據庫設置為只讀狀態，那么二者有什么區別呢?我們先來了解下 flush tables with read lock 的作用。

執行此命令會給數據庫加全局讀鎖，使得數據庫處于只讀狀態，以下語句會被阻塞：數據更新語句(增刪改)、數據定義語句(建表、修改表結構等)和更新類事務的提交語句。下面我們來具體實驗下：

# 執行FTWRL 
mysql> flush tables with read lock; 
Query OK, 0 rows affected (0.02 sec) 
 
# 進行數據變更操作 
mysql> insert into tb_a values (1); 
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock 
 
# 解鎖 
mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec) 
mysql> insert into tb_a values (1); 
Query OK, 1 row affected (0.01 sec) 

值得注意的是，從庫端執行 flush tables with read lock 會導致 SQL 線程卡住，主備延遲。與開啟 read_only 參數不同的是，執行 flush tables with read lock 后，其余客戶端執行數據變更操作會持續等待而不是立即報錯，極其容易引起數據庫 hang 住，執行這個命令還是要小心的。

以個人數據庫運維經驗來講，一般只有從庫需要設置只讀狀態，從庫端建議開啟 read_only 或 super_read_only，避免人為寫入。flush tables with read lock 適用于進行數據遷移時，可以保證數據庫不發生數據改變，不過要注意及時解鎖。

總結：

本篇文章主要介紹了 MySQL 只讀狀態相關知識，其實除了從庫外，其余實例很少設置全局只讀，只是遇到某種需求的情況下需要將數據庫設為只讀狀態，寫本篇文章的目的也是遇到此類需求時，可以有個參考。