**前言：**

預設情況下，我們的 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 引數的用法：

```sql
# 檢視 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 的作用。

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

```sql
# 執行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 只讀狀態相關知識，其實除了從庫外，其餘例項很少設定全域性只讀，只是遇到某種需求的情況下需要將資料庫設為只讀狀態，寫本篇文章的目的也是遇到此類需求時，可以有個參考。

MySQL設定資料庫為只讀

相關文章