一文搞懂 MySQL/GreateSQL 只读参数：read_only 参数核心区别

一文搞懂 MySQL/GreateSQL 只读参数read_only 参数核心区别一、概述在 MySQL/GreatSQL 数据库管理中通过设置只读模式来控制实例的数据写入权限是一项常见的运维操作尤其在搭建主从复制、执行备份、进行维护或实现读写分离时至关重要。MySQL/GreatSQL 提供了多个与只读相关的系统参数它们从不同维度对实例的读写能力进行约束。理解这些参数的作用、区别及相互影响能够帮助我们更精确地控制数据库的行为确保数据一致性和系统高可用。二、参数介绍1. innodb_read_only该参数为静态参数仅能在 MySQL/GreatSQL 服务启动阶段配置运行时无法修改。从 MySQL/GreatSQL 8.0.* 版本开始启用该变量后所有存储引擎的表创建、删除操作都会被禁止。核心原因是无论操作的表属于哪种存储引擎表的增删及其他涉及数据字典修改的表操作都会改动mysql系统库中的数据字典表而这类数据字典表均基于 InnoDB 存储引擎因此innodb_read_only启用后相关修改操作会被直接限制。启用该参数后即使是超级管理员 root 用户其相关操作也会受到严格限制。操作示例库表创建、修改操作greatsql CREATE DATABASE test; ERROR 1836 (HY000): Running in read-only mode greatsql CREATE TABLE t10 (c1 int); ERROR 1836 (HY000): Running in read-only mode greatsql DROP TABLE sbtest1; ERROR 3604 (HY000): Storage engine cant DROP TABLE sysbench.sbtest1 greatsql DELETE FROM sbtest1 WHERE id100; ERROR 1015 (HY000): Cant lock file (errno: 165 - TABLE is read only) greatsql ALTER TABLE sbtest1 DROP key k_1; ERROR 1836 (HY000): Running in read-only mode账户与插件管理类操作因mysql系统库中权限表、插件表、函数表等均为 InnoDB 引擎启用innodb_read_only后以下操作均会执行失败CREATE USER、GRANT、ALTER等账户管理语句# 创建用户、修改用户 greatsql CREATE USER t1% IDENTIFIED BY abc123; ERROR 3501 (HY000): The ACL operation failed due to the following error FROM SE: errcode 165 - TABLE is read only greatsql ALTER USER tt% IDENTIFIED BY abc123; ERROR 3501 (HY000): The ACL operation failed due to the following error FROM SE: errcode 165 - TABLE is read onlyINSTALL PLUGIN、UNINSTALL PLUGIN 等插件管理语句CREATE FUNCTION、DROP FUNCTION 等函数管理语句表分析操作限制启用该参数后执行ANALYZE TABLE会因无法更新数据字典中的统计信息表而失败greatsql ANALYZE TABLE t1; ---------------------------------------------------------------------------------------------- | TABLE | Op | Msg_type | Msg_text | ---------------------------------------------------------------------------------------------- | test01.t1 | analyze | Warning | InnoDB: Running in read-only mode | | test01.t1 | analyze | Error | Running in read-only mode | | test01.t1 | analyze | Error | Unable to store dynamic TABLE statistics INTO data dictionary. | | test01.t1 | analyze | status | Unable to write TABLE statistics to DD TABLEs | ---------------------------------------------------------------------------------------------- 4 rows in set (0.01 sec)非 InnoDB 存储引擎表的特殊操作规则若数据库中已存在 MyISAM 等非 InnoDB 引擎的表仅可对表内数据执行 DML 操作表结构的 DDL 操作仍会被禁止若强制执行 DDL可能出现底层文件丢失但数据库元数据残留的异常情况。数据DML操作可正常执行greatsql INSERT INTO t3 VALUES (1),(2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 greatsql DELETE FROM t3; Query OK, 4 rows affected (0.00 sec)表DDL操作执行失败greatsql DROP TABLE t3; ERROR 1036 (HY000): TABLE columns is read only异常情况表底层文件丢失元数据仍存在无法访问表greatsql SHOW CREATE TABLE t3\G ERROR 1017 (HY000): Cant find file: t3 (errno: 2 - No such file or directory) greatsql SHOW TABLES; ------------------ | TABLES_in_test01 | ------------------ | t1 | | t2 | | t3 | ------------------ 3 rows in set (0.00 sec) greatsql SELECT * FROM t3; ERROR 1017 (HY000): Cant find file: t3 (errno: 2 - No such file or directory) # 针对上述元数据残留问题可通过DROP TABLE IF EXISTS语句清理数据库中的残留表信息 greatsql DROP TABLE IF EXISTS t3; Query OK, 0 rows affected, 1 warning (0.01 sec)2.read_only该参数为动态参数默认处于禁用状态。启用后服务器会禁止普通用户执行任何更新操作拥有CONNECTION_ADMIN权限或已弃用的SUPER权限的用户除外是数据库实例级的只读控制参数。源服务器上 read_only 的配置变更不会复制到副本服务器副本可独立于源服务器设置该参数的值不受源库配置影响.1该参数与super_read_only存在强联动关系启用super_read_only时会隐式强制read_only设置为 ON将read_only置为 OFF 时会隐式强制super_read_only设置为 OFF。2当read_only和super_read_only同时启用时服务器仍允许执行以下操作不做只读限制副本replica服务器中复制线程发起的更新操作该特性常用于复制架构确保副本仅接收源服务器的更新拒绝客户端的直接修改向mysql.gtid_executed系统表写入数据该表存储当前二进制日志中未记录的已执行事务 GTID执行ANALYZE TABLE或OPTIMIZE TABLE语句两类操作仅做表分析 / 优化不修改表结构和数据执行FLUSH STATUS语句该语句会始终写入二进制日志对临时表TEMPORARY TABLES执行的所有操作向日志表mysql.general_log和mysql.slow_log插入数据对Performance Schema表执行UPDATE、TRUNCATE TABLE等更新操作。3尝试启用read_only时包括因启用super_read_only触发的隐式启用需满足以下规则否则会执行失败或被阻塞若当前会话持有通过LOCK TABLES获取的显式锁或存在未提交的事务启用操作会直接失败并抛出错误若其他客户端存在正在执行的语句、活跃的LOCK TABLES WRITE锁或正在进行事务提交启用操作会被阻塞直到锁释放、语句执行完成且事务结束在此期间其他客户端申请表锁或开启事务的请求也会被阻塞直至read_only设置完成若存在持有元数据锁的活跃事务启用操作会被阻塞直至该事务结束持有通过FLUSH TABLES WITH READ LOCK获取的全局读锁时可正常启用read_only全局读锁不涉及表级锁无冲突。3.super_read_only该参数为动态参数默认处于禁用状态是比read_only更严格的实例级只读控制参数。启用后服务器会禁止所有客户端的更新操作即使是拥有CONNECTION_ADMIN或SUPER权限的特权用户也不例外。与read_only一致源服务器上super_read_only的配置变更不会复制到副本服务器副本可独立设置该参数的值。核心限制范围除常规的 DML、DDL 操作外启用super_read_only后表面非更新但会修改mysql系统表的操作也会被禁止包括CREATE FUNCTION函数操作INSTALL PLUGIN、UNINSTALL PLUGIN插件管理INSTALL COMPONENT、UNINSTALL COMPONENT组件管理。以上操作的禁用原因均为会改动mysql系统库中的相关系统表。对事件调度器Event Scheduler的影响1若事件调度器处于启用状态启用super_read_only会导致其无法更新事件数据字典表中的「最后执行」时间戳进而触发以下结果事件调度器在下次尝试执行计划事件时会停止运行并向服务器错误日志写入相关提示消息此情况下event_scheduler系统变量的值仍为 ON不会自动变为 OFF即变量配置与调度器实际运行状态分离与数据库管理员的配置意图可能不一致。2针对事件调度器的恢复MySQL不同版本有不同规则MySQL 8.0.26 及以上版本super_read_only启用后再禁用服务器会根据需要自动重启事件调度器MySQL 8.0.26 以下版本需手动执行启用命令重新启动事件调度器。4.transaction_read_only该参数为事务层面的读写控制开关核心作用是通过开关限定指定作用域内事务是否允许执行写操作与实例级的只读控制参数无关联。基础配置取值仅支持OFF读写模式默认值和ON只读模式作用域分为全局级、会话级、下一个事务级不同作用域的生效范围和持续时间不同。1全局级配置语句SET GLOBAL transaction_read_only ON;生效规则为所有后续新建的会话设定默认的事务访问模式已存在的当前会话不受该配置影响。2会话级配置语句SET SESSION transaction_read_only ON;生效规则影响当前会话中所有后续执行的事务直至手动修改该参数值会话内的事务只读规则才会变更。3下一个事务级配置语句SET transaction_read_only ON;生效规则仅对当前会话的下一个单个事务生效该事务执行完成后参数会自动恢复为默认的读写模式需重新配置才能再次生效。操作示例示例 1全局级 / 会话级只读的限制效果全局级和会话级启用只读后对事务内的DML 和 DDL 操作均会禁止执行时报错一致。1. 设置会话级只读 greatsql SET SESSION transaction_read_onlyON; 2. 执行DML操作报错 greatsql INSERT INTO test.t01 VALUES(c,null,c,null); ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction. 3. 执行DDL操作报错 greatsql CREATE TABLE test.t16 (c1 int); ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction. 4. 执行账户管理DDL报错 greatsql CREATE user test01%; ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.示例 2下一个事务级只读的限制效果下一个事务级启用只读后仅对 DML 操作生效DDL 操作不受限制且配置仅对单次事务有效。1. 设置下一个事务级只读 greatsql SET transaction_read_onlyON; 2. 执行DML操作报错 greatsql INSERT INTO test.t01 VALUES(c,null,c,null); ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction. 3. 重新设置下一个事务级只读上一步DML已消耗该配置需重新设定 greatsql SET transaction_read_onlyON; 4. 执行DDL操作成功执行 greatsql CREATE TABLE test.t15 (c1 int); Query OK, 0 rows affected (0.04 sec)三、总结本文详细解析了 MySQL/GreatSQL 中innodb_read_only、read_only、super_read_only、transaction_read_only四类只读参数的核心作用、适用场景、限制范围及相互关系核心要点总结如下参数层级与定位清晰innodb_read_only静态参数启动时配置该参数仅作用于 InnoDB 存储引擎会限制所有针对 InnoDB 表的写入操作包括数据增删改、表结构修改等且无权限绕过即使是拥有 SUPER 权限的管理员也无法执行 InnoDB 写入操作。read_only动态实例级参数仅限制普通用户特权用户可写入是主从复制中从库常规只读配置。super_read_only更严格的动态实例级参数限制所有用户含特权用户写入与 read_only 存在强制联动适用于高安全级只读管控super_read_only启用会强制开启read_only关闭read_only会强制关闭super_read_only。transaction_read_only事务级动态参数与实例级只读无关可在全局、会话、单事务维度控制只读灵活适配精细化事务读写需求。