MySQL read_only与super_read_only参数解读

社区管理员发布于2022-10-18 11:55 / 1394人阅读

一、背景介绍

MySQL read_only参数可以让整个MySQL实例普通权限用户处于只读状态，但是其并不能限制拥有super权限的用户。read_only参数一般是用于主从复制从库的配置，目的是为了规避从库误写数据，导致主从复制异常或者主从数据不一致的隐患。另外，为了避免从库被super权限用户误写数据，MySQL官方在MySQL5.7版本引入了super_read_only参数来限制super用户在从库的只读属性。

官网地址：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_super_read_only

二、配置方法

2.1 ROOT用户获取SUPER权限

update mysql.user set Super_priv='Y' where User='root'; # root用户获取超级权限
flush privileges;                                       # 刷新权限表

2.2 命令临时设置只读参数

（1）查看read_only与super_read_only设置

# 查看当前read_only和super_read_only设置（从库执行）
mysql> SELECT @@global.read_only, @@global.super_read_only;
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
|                  1 |                        0 |
+--------------------+--------------------------+
1 row in set (0.01 sec)

# 查看当前连接用户信息（从库执行）
mysql> select user() ,current_user();
+-------------------+----------------+
| user()            | current_user() |
+-------------------+----------------+
| root@10.25.102.12 | root@%         |
+-------------------+----------------+
1 row in set (0.00 sec)

# 查看root用户当前拥有的权限，可以看出此时root用户没有super权限
mysql> show grants for 'root'@'%'\G;（从库执行）
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'root'@'%' WITH GRANT OPTION
1 row in set (0.00 sec)

# root用户在从库创建数据库db1（从库执行）
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

（2）设置super_read_only配置

# root用户获取超级权限（主库执行）
mysql> update mysql.user set Super_priv='Y' where User='root';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

# 设置super_read_only参数（从库执行）
mysql> set global super_read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.read_only, @@global.super_read_only; 
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
|                  1 |                        1 |
+--------------------+--------------------------+
1 row in set (0.00 sec)

# 查看当前root用户已经拥有super权限（主库执行）
mysql> show grants for 'root'@'%'\G;
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'root'@'%' WITH GRANT OPTION
1 row in set (0.00 sec)

# 设置完super_read_only参数后，从库再次创建数据库，出现了报错
mysql> create database db2;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

注：由上可以看出，设置完super_read_only参数后，即使拥有super权限的用户，也不能在从库写数据！！！

私有云混合云服务器cpu参数解读 mysql参数设置 mysql参数优化查看mysql编译参数

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/128135.html

OneProxy简单实现读写分离与分库分表

摘要：实现读写分离环境主机主机中间件一配置主从略注意关闭三端防火墙，也可以开放端口，数据库端口，连接端口，管理端口当配置同步之后需要在主机创建库并添加的连接用户会自动到数据库在数据库中设置项，只读模式，不会影响同步复制的功能，它可以限定普通用户进 OneProxy实现Mysql读写分离环境： master主机：192.168.1.10slave主机：192.168.1.12oneproxy...

whinc 2019-06-28 16:22 评论0 收藏0
MySQL高可用方案测试

MySQL高可用方案测试 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; margin...

IT那活儿 2023-01-11 13:19 评论0 收藏2496
mysql 只读模式详解

摘要：为了保证主从同步可以一直进行，在库上要保证具有权限的等用户只能在本地登录，不会发生数据变化，其他远程连接的应用用户只按需分配为等权限，保证没有权限，则只需要将设定模式，即可保证主从同步，又可以实现从库只读。 1、设置只读模式（此中模式连super都不能进行写）mysql> show global variables like %read_only%;mysql> flush table...

netmou 2019-07-31 17:12 评论0 收藏0
数据库主从配置

摘要：注意版本之后，已经不支持把配置属性写入配置文件中了，只需要把同步的数据库和要忽略的数据库写入即可。数据库配置主数据库：172.20.17.211 从数据库：172.20.17.210 一、创建主/从数据同步账户( 主从两台数据库都要执行 ) # 开启远程访问支持 /usr/local/mysql/bin/mysql -h172.20.17.211 -uroot -pwoshish...

U2FsdGVkX1x 2019-07-01 12:16 评论0 收藏0
MySQL集群搭建(6)-双主+keepalived高可用

摘要：双主是一个比较简单的高可用架构，适用于中小集群，今天就说说怎么用做的高可用。缺点也比较明显，就是增加从节点的情况下，从节点不会主动切换同步对象，而且脚本需要自己实现，有一定风险。双主 + keepalived 是一个比较简单的 MySQL 高可用架构，适用于中小 MySQL 集群，今天就说说怎么用 keepalived 做 MySQL 的高可用。 1 概述 1.1 keepalive...

silvertheo 2019-08-06 14:48 评论0 收藏0