MySQL 设置DB 为只读操作说明 -- cnDBA.cn

1 相关参数说明

在MySQL 数据库中，只读有关的参数有如下：

[dave@www.cndba.cn ~]$ mysql -uroot -proot -S /tmp/mysql_3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 2
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

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   |
+-----------------------+-------+
5 rows in set (0.00 sec)

mysql>

这里需要注意一点，从库开启 read_only/ super_read_only 并不影响主从同步，即 salve 端仍然会读取 master 上的日志，并且在 slave 实例中应用日志，保证主从数据库同步一致。

read_only：
参数默认不开启，开启后会阻止没有 super 权限的用户执行数据库变更操作。开启后，普通权限用户执行插入、更新、删除等操作时，会提示 —read-only 错误。但具有 CONNECTION_ADMIN 和 SUPER权限的用户仍可执行变更操作。http://www.cndba.cn/dave/article/108033http://www.cndba.cn/dave/article/108033

If the read_only system variable is enabled, the server permits no client updates except from users who have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege). This variable is disabled by default.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only http://www.cndba.cn/dave/article/108033

super_read_only：
默认关闭，开启后不仅会阻止普通用户，也会阻止具有 CONNECTION_ADMIN 和 SUPER权限的用户对数据库进行变更操作。

If the read_only system variable is enabled, the server permits no client updates except from users who have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege). If the super_read_only system variable is also enabled, the server prohibits client updates even from users who have CONNECTION_ADMIN or SUPER.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only
http://www.cndba.cn/dave/article/108033

transaction_read_only：

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.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_transaction_read_only
http://www.cndba.cn/dave/article/108033

innodb_read_only：

tarts InnoDB in read-only mode. For distributing database applications or data sets on read-only media. Can also be used in data warehouses to share the same data directory between multiple instances.
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_read_only

2 操作示例

查看 read_only 参数

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

动态修改 read_only 参数（若想重启生效则需将 read_only = 1 加入配置文件中） http://www.cndba.cn/dave/article/108033 http://www.cndba.cn/dave/article/108033

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

mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

动态修改 super_read_only 参数（若想重启生效则需将 read_only = 1 加入配置文件中） http://www.cndba.cn/dave/article/108033http://www.cndba.cn/dave/article/108033

mysql> set global super_read_only = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>  show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)

mysql> create database cndba;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql>

开启 super_read_onlyhttp://www.cndba.cn/dave/article/108033

mysql> set global super_read_only = 1; 
mysql> set global super_read_only = 1;
Query OK, 0 rows affected (0.00 sec)

开启read_only

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

mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

签到成功

CNDBA社区