MySQL教程

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.


数据运维技术 » MySQL Variables read_only 数据库 参数变量解释及正确配置使用
分享到：

相关推荐