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

  Property	Value
  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

  Property	Value
  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:

  • 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 slave threads, if the server is a replication slave. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master 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 replication slaves can be performed with mysqlcheck --all-databases --analyze.

  • 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 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

  Property	Value
  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.

 

• tx_read_only

  Property	Value
  Deprecated	5.7.20
  System Variable	tx_read_only
  Scope	Global, Session
  Dynamic	Yes
  Type	Boolean
  Default Value	OFF

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

  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. See the description of transaction_read_only for details.