Mysql：read-only 配置

重点：

　　super_read_only、read_only：不会从master传递（复制）到slave

　　super_read_only=on隐含实现read_only=on

　　read_only=off隐含实现super_read_only=off

  • super_read_only

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

    If the read_only system variable is enabled, the server permits client updates only from users who have the SUPER privilege. If the super_read_only system variable is also enabled, the server prohibits client updates even from users who have SUPER. See the description of the read_only system variable for a description of read-only mode and information about how read_only and super_read_only interact.

    Client updates prevented when super_read_only is enabled include operations that do not necessarily appear to be updates, such as CREATE FUNCTION (to install a UDF) and INSTALL PLUGIN. These operations are prohibited because they involve changes to tables in the mysql system database.

    Changes to super_read_only on a master server are not replicated to slave servers. The value can be set on a slave server independent of the setting on the master.

  • read_only

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

    When 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:

    Even with read_only enabled, the server permits these operations:

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

    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

  • transaction_read_only

    PropertyValue
    Command-Line Format --transaction-read-only[={OFF|ON}]
    System Variable (>= 5.7.20) transaction_read_only
    Scope (>= 5.7.20) Global, Session
    Dynamic (>= 5.7.20) Yes
    Type Boolean
    Default Value OFF

    The transaction access mode. The value can be OFF (read/write; the default) or ON (read only).

    The transaction access mode has three scopes: global, session, and next transaction. This three-scope implementation leads to some nonstandard access-mode assignment semantics, as described later.

    To set the global transaction access mode at startup, use the --transaction-read-only server option.

    At runtime, the access mode can be set directly using the SET statement to assign a value to the transaction_read_only system variable, or indirectly using the SET TRANSACTION statement. For example, use this SET statement to set the global value:

    SET GLOBAL transaction_read_only = ON;

    Setting the global transaction_read_only value sets the access mode for all subsequent sessions. Existing sessions are unaffected.

    To set the session or next-level transaction_read_only value, use the SET statement. For most session system variables, these statements are equivalent ways to set the value:

    SET @@SESSION.var_name = value;
SET SESSION var_name = value;
SET var_name = value;
SET @@var_name = value;

    As mentioned previously, the transaction access mode has a next-transaction scope, in addition to the global and session scopes. To enable the next-transaction scope to be set, SET syntax for assigning session system variable values has nonstandard semantics for transaction_read_only,

    • To set the session access mode, use any of these syntaxes:

      SET @@SESSION.transaction_read_only = value;
SET SESSION transaction_read_only = value;
SET transaction_read_only = value;

      For each of those syntaxes, these semantics apply:

      • Sets the access mode for all subsequent transactions performed within the session.

      • Permitted within transactions, but does not affect the current ongoing transaction.

      • If executed between transactions, overrides any preceding statement that sets the next-transaction access mode.

      • Corresponds to SET SESSION TRANSACTION {READ WRITE | READ ONLY} (with the SESSION keyword).

    • To set the next-transaction access mode, use this syntax:

      SET @@transaction_read_only = value;

      For that syntax, these semantics apply:

      • Sets the access mode only for the next single transaction performed within the session.

      • Subsequent transactions revert to the session access mode.

      • Not permitted within transactions.

      • Corresponds to SET TRANSACTION {READ WRITE | READ ONLY} (without the SESSION keyword).

    For more information about SET TRANSACTION and its relationship to the transaction_read_only system variable, see Section 13.3.6, “SET TRANSACTION Statement”.

    Note

    transaction_read_only was added in MySQL 5.7.20 as an alias for tx_read_only, which is now deprecated and is removed in MySQL 8.0. Applications should be adjusted to use transaction_read_only in preference to tx_read_only.

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jinzhenshui/p/12548022.html