MySQL sets the database as read-only

Foreword:

By default, our MySQL instance is readable and writable. But in some cases, we can set the entire instance to be read-only, such as when doing migration maintenance or from the library to read-only. In this article, let's take a look at the knowledge of MySQL setting read-only.

1. About the read_only parameter

In the MySQL system, two read-only parameters, read_only and super_read_only, are provided. Refer to the official documentation. Here are the functions of these two parameters:

The read_only parameter is not enabled by default, and it will prevent users without super privileges from performing database changes. After it is turned on, when users with ordinary permissions perform operations such as insert, update, delete, etc., a --read-only error will be prompted. However, users with super permissions can still perform changes.

The super_read_only parameter is also turned off by default. After it is turned on, it will not only prevent ordinary users, but also prevent users with super permissions from making changes to the database.

read_only and super_read_only are related, and the relationship between the two is as follows:

• Setting super_read_only=on also implicitly sets read_only=on.
• Setting read_only=off also implicitly sets super_read_only=off.
• You can enable read_only alone without enabling super_read_only.

However, turning on read_only from the database does not affect the master-slave synchronization, that is, the salve side will still read the log on the master and apply the log in the slave instance to ensure that the master-slave database synchronization is consistent. (After testing, turning on super_read_only from the library side still does not affect the master-slave synchronization.)

Let's take a look at the usage of the read_only parameter in detail below:

# 查看 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 settings

In addition to the read_only parameter, executing flush tables with read lock can also set the database to read-only status, so what is the difference between the two? Let's first understand the role of flush tables with read lock.

Executing this command will add a global read lock to the database, making the database in a read-only state. The following statements will be blocked: data update statements (additions, deletions, and modifications), data definition statements (table creation, modification of table structure, etc.), and the submission of update transactions Statement. Let's experiment specifically below:

# 执行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)

It is worth noting that executing flush tables with read lock from the library side will cause the SQL thread to get stuck, and the active and standby delays. The difference with the read_only parameter is that after flush tables with read lock is executed, other clients will continue to wait for data change operations instead of reporting an error immediately, which is extremely easy to cause the database to hang. You must be careful when executing this command.

In terms of personal database operation and maintenance experience, generally only the slave library needs to set the read-only status. It is recommended to enable read_only or super_read_only from the library side to avoid manual writing. Flush tables with read lock is suitable for data migration, which can ensure that the database does not change, but it should be unlocked in time.

summary:

This article mainly introduces the knowledge of MySQL read-only status. In fact, except from the library, the rest of the instances rarely set global read-only, but in the case of certain needs, the database needs to be set to read-only status. This article is written The purpose is also to have a reference when encountering such needs.