MySQL教程 2023-04-05

MySQL Variables read_only 数据库参数变量解释及正确配置使用

本站中文解释

变量

read_only变量用于设置MySQL实例是否只读，以及对MySQL实例的控制程度。当read_only变量的值设置为 ON 时，MySQL实例的实际状态为只读，MySQL用户只能执行 SELECT 操作，不能执行 UPDATE、DELETE 等更新操作。

设置read_only变量的命令为:

SET global read_only = ON;

或者

SET SESSION read_only = ON;

官方英文解释

read_only

Command-Line Format	`--read-only[={OFF\|ON}]`
System Variable	`read_only`
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	`OFF`

If the read_only system
variable is enabled, the server permits no client updates
except from users who have the
SUPER privilege. This variable
is disabled by default.

The server also supports a
super_read_only system
variable (disabled by default), which has these effects:

If super_read_only is
enabled, the server prohibits client updates, even from
users who have the SUPER
privilege.
Setting super_read_only
to ON implicitly forces
read_only to
ON.
Setting read_only to
OFF implicitly forces
super_read_only to
OFF.

Even with read_only enabled,
the server permits these operations:

Updates performed by replication threads, if the server is
a replica. In replication setups, it can be useful to
enable read_only on
replica servers to ensure that replicas accept updates
only from the source server and not from clients.
Use of ANALYZE TABLE or
OPTIMIZE TABLE statements.
The purpose of read-only mode is to prevent changes to
table structure or contents. Analysis and optimization do
not qualify as such changes. This means, for example, that
consistency checks on read-only replicas can be performed
with mysqlcheck
--all-databases
--analyze.
Use of FLUSH STATUS
statements, which are always written to the binary log.
Operations on TEMPORARY tables.
Inserts into the log tables
(mysql.general_log and
mysql.slow_log); see
Section 5.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”.
As of MySQL 5.7.16, updates to Performance Schema tables,
such as UPDATE or
TRUNCATE TABLE operations.

Changes to read_only on a
replication source server are not replicated to replica
servers. The value can be set on a replica independent of the
setting on the source.

The following conditions apply to attempts to enable
read_only (including implicit
attempts resulting from enabling
super_read_only):

The attempt fails and an error occurs if you have any
explicit locks (acquired with LOCK TABLES) or have a pending transaction.
The attempt blocks while other clients have any ongoing
statement, active LOCK TABLES WRITE, or
ongoing commit, until the locks are released and the
statements and transactions end. While the attempt to
enable read_only is
pending, requests by other clients for table locks or to
begin transactions also block until
read_only has been set.
The attempt blocks if there are active transactions that
hold metadata locks, until those transactions end.
read_only can be enabled
while you hold a global read lock (acquired with
FLUSH TABLES WITH READ LOCK) because that does not involve table locks.