preface:

By default, our MySQL instance is read-write. However, in some cases, we can set the entire instance to read-only status, such as during migration maintenance or set the slave library to read-only. In this article, let's take a look at the read-only knowledge of MySQL settings.

1. About read_only parameter

In MySQL system, read is provided_ Only and super_read_only two read-only parameters. Refer to the official document. Here are the functions of these two parameters:

read_ The only parameter is not enabled by default. When enabled, users without super permission will be prevented from performing database changes. When enabled, users with normal permissions will be prompted with -- read only error when performing insert, update, delete and other operations. However, users with super permission can still perform the change operation.

super_ read_ The only parameter is also turned off by default. When turned on, it will prevent not only ordinary users, but also users with super permission from changing the database.

read_only and super_read_only is related. The relationship between the two is as follows:

• Set super_read_only=on, which implicitly sets read_only=on.
• Set read_only=off, which implicitly sets super_read_only=off.
• Read can be opened separately_ Only without turning on super_read_only.

However, open read from the library_ Only does not affect the master-slave synchronization, that is, the salve side will still read the logs on the master and apply the logs in the slave instance to ensure the synchronization of the master-slave database. (after testing, enabling super_read_only from the library still does not affect the master-slave synchronization.)

Let's take a look at read_ Usage of the only parameter:

# View read_only parameter
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   |
+-----------------------+-------+

# Dynamically modify read_ Only parameter (if you want the restart to take effect, you need to add read_only = 1 to the configuration file)
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_ Operate data when only is on
# Use superuser
mysql> create table tb_a (a int);
Query OK, 0 rows affected (0.05 sec)
# Users with normal permissions
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

# Turn on super_read_only, use the super user to manipulate the data again
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

# Close read_only parameter
mysql> set global read_only = 0;
Query OK, 0 rows affected (0.00 sec)

2.flush tables with read lock settings

Except read_ In addition to the only parameter, executing flush tables with read lock can also set the database to read-only status. What is the difference between the two? Let's first understand the function 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 (addition, deletion and modification), data definition statements (creating tables, modifying table structures, etc.) and submission statements of update transactions. Let's make a specific experiment:

# Execute FTWRL
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec)

# Data change operation
mysql> insert into tb_a values (1);
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

# Unlock
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 should be noted that executing flush tables with read lock from the library side will cause the SQL thread to get stuck and delay the active and standby. And open read_ The only parameter is different. After flush tables with read lock is executed, other clients will continue to wait for data changes instead of immediately reporting errors, which is extremely easy to cause the database to hang. Be careful when executing this command.

In terms of personal database operation and maintenance experience, generally only the slave database needs to be set to read-only status, and it is recommended to enable read from the database end_ Only or super_read_only, avoid manual writing. flush tables with read lock is applicable to data migration, which can ensure that the database does not change, but pay attention to unlocking in time.

Summary:

This article mainly introduces the related knowledge of MySQL read-only state. In fact, except from the slave database, the other instances rarely set global read-only. Only when there is a certain demand, the database needs to be set to read-only state. The purpose of this article is to have a reference when there is such demand.