签到成功

知道了

CNDBA社区CNDBA社区

MySQL 设置DB 为只读 操作说明

2022-05-29 14:28 427 0 原创 MySQL
作者: dave

1 相关参数说明


在MySQL 数据库中,只读有关的参数有如下: https://www.cndba.cn/cndba/dave/article/108033

[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 实例中应用日志,保证主从数据库同步一致。https://www.cndba.cn/cndba/dave/article/108033

read_only:
参数默认不开启,开启后会阻止没有 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). This variable is disabled by default.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only

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

transaction_read_only:

https://www.cndba.cn/cndba/dave/article/108033

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

innodb_read_only:https://www.cndba.cn/cndba/dave/article/108033

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

https://www.cndba.cn/cndba/dave/article/108033

2 操作示例


查看 read_only 参数 https://www.cndba.cn/cndba/dave/article/108033https://www.cndba.cn/cndba/dave/article/108033https://www.cndba.cn/cndba/dave/article/108033

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 加入配置文件中)

https://www.cndba.cn/cndba/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 加入配置文件中)

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_only

https://www.cndba.cn/cndba/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社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2091
    原创
  • 2
    翻译
  • 503
    转载
  • 176
    评论
  • 访问:5042792次
  • 积分:3762
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ