MySQL 8.0 支持对单个数据库设置只读！

2024-03-27 260

MySQL 8.0.22 支持对单个数据库设置只读，当一个实例中只需要迁移部分数据库时比较实用，避免数据库迁移过程中数据库及其对象被修改。

作者：李富强，爱可生 DBA 团队成员，熟悉 MySQL，TiDB，OceanBase 等数据库。相信持续把对的事情做好一点，会有不一样的收获。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1600 字，预计阅读需要 5 分钟。

新特性概要

对单个数据库设置只读状态，可以通过 ALTER DATABASE 语句中的 READ ONLY 选项来实现，该选项在 MySQL 8.0.22 版本中引入，用于控制是否允许对数据库及其对象（包括其定义、数据和元数据）进行写入操作。

当只需要迁移一个实例当中的部分数据库时，对部分数据库开启 READ ONLY，不用担心数据库迁移期间这些数据库被修改。

使用方法

以设置数据库 lfq 为只读状态举例，可以观测到修改数据库只读状态对已建立连接的用户是立即生效的（即：session1 修改 lfq 数据库为只读，session2 中 lfq 的只读状态是立即生效的。）

#session1
 MySQL  localhost:3000 ssl  SQL > select version(),@@port,connection_id();
+-----------+--------+-----------------+
| version() | @@port | connection_id() |
+-----------+--------+-----------------+
| 8.0.22    |   3000 |              22 |
+-----------+--------+-----------------+
1 row in set (0.0015 sec)
 
#session2
 MySQL  localhost:3000 ssl  SQL > select version(),@@port,connection_id();
+-----------+--------+-----------------+
| version() | @@port | connection_id() |
+-----------+--------+-----------------+
| 8.0.22    |   3000 |              24 |
+-----------+--------+-----------------+
1 row in set (0.0009 sec)


#session1，修改前查一下数据库的只读状态，OPTIONS值为空，代表数据库非只读状态
 MySQL  localhost:3000 ssl  SQL > SELECT * FROM INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS WHERE SCHEMA_NAME = 'lfq';
+--------------+-------------+---------+
| CATALOG_NAME | SCHEMA_NAME | OPTIONS |
+--------------+-------------+---------+
| def          | lfq         |         |
+--------------+-------------+---------+
1 row in set (0.0057 sec)
 
#session1，修改数据库为只读状态
 MySQL  localhost:3000 ssl  SQL > ALTER SCHEMA lfq READ ONLY = 1;
Query OK, 1 row affected (0.0127 sec)
  
#session1，再次查一下数据库的只读状态，OPTIONS值为“READ ONLY=1”，数据库只读状态修改成功
 MySQL  localhost:3000 ssl  SQL > SELECT * FROM INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS WHERE SCHEMA_NAME = 'lfq';
+--------------+-------------+-------------+
| CATALOG_NAME | SCHEMA_NAME | OPTIONS     |
+--------------+-------------+-------------+
| def          | lfq         | READ ONLY=1 |
+--------------+-------------+-------------+
1 row in set (0.0048 sec)
  
#session1，在lfq库中新建一张表测试下,数据库只读状态建表失败
 MySQL  localhost:3000 ssl  SQL > create table lfq.t1(c1 int primary key,n1 varchar(20) );
ERROR: 3989 (HY000): Schema 'lfq' is in read only mode.
 
#session1，表lfq.my_table插入数据测试（my_table为提前建的表），数据库只读状态表插入数据失败
 MySQL  localhost:3000 ssl  lfq  SQL > INSERT INTO my_table (name, age, email) VALUES ('LFQ', 18, 'lfq#actionsky.com');
ERROR: 3989 (HY000): Schema 'lfq' is in read only mode.
 
#session1，表lfq.my_table更新数据测试，数据库只读状态表更新数据失败
 MySQL  localhost:3000 ssl  lfq  SQL > UPDATE my_table SET age = 30 WHERE name = 'LFQ';
ERROR: 3989 (HY000): Schema 'lfq' is in read only mode.
  
#session1，表lfq.my_table删除数据测试，数据库只读状态表删除数据失败
 MySQL  localhost:3000 ssl  lfq  SQL > DELETE FROM my_table WHERE name = 'LFQ';
ERROR: 3989 (HY000): Schema 'lfq' is in read only mode.
   
#session2，查询数据库只读状态，数据库为只读状态，session1修改lfq数据库为只读，session2中lfq的只读状态是立即生效的
 MySQL  localhost:3000 ssl  SQL > SELECT * FROM INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS WHERE SCHEMA_NAME = 'lfq';
+--------------+-------------+-------------+
| CATALOG_NAME | SCHEMA_NAME | OPTIONS     |
+--------------+-------------+-------------+
| def          | lfq         | READ ONLY=1 |
+--------------+-------------+-------------+
1 row in set (0.0016 sec)
 
#session2，在lfq库中新建一张表测试下,数据库为只读状态下建表失败
 MySQL  localhost:3000 ssl  SQL > create table lfq.t1(c1 int primary key,n1 varchar(20) );
ERROR: 3989 (HY000): Schema 'lfq' is in read only mode.

查询 READ ONLY 状态

方法一

通过查询 INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS 表（在 MySQL 8.0.22 版本引入），输出结果中如果 OPTIONS 列的值为 READ ONLY=1，则说明数据库为只读状态，如果 OPTIONS 列的值为空，则说明数据库为非只读状态。

MySQL  localhost:3000 ssl  SQL > select version(),@@port;
+-----------+--------+
| version() | @@port |
+-----------+--------+
| 8.0.22    |   3000 |
+-----------+--------+
1 row in set (0.0029 sec)
MySQL  localhost:3000 ssl  SQL > ALTER SCHEMA lfq READ ONLY = 1;
Query OK, 1 row affected (0.0098 sec)
 MySQL  localhost:3000 ssl  SQL >
 MySQL  localhost:3000 ssl  SQL >
 MySQL  localhost:3000 ssl  SQL >
 MySQL  localhost:3000 ssl  SQL > SELECT * FROM INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS WHERE SCHEMA_NAME = 'lfq';
+--------------+-------------+-------------+
| CATALOG_NAME | SCHEMA_NAME | OPTIONS     |
+--------------+-------------+-------------+
| def          | lfq         | READ ONLY=1 |
+--------------+-------------+-------------+
1 row in set (0.0063 sec)
 MySQL  localhost:3000 ssl  SQL > ALTER SCHEMA lfq READ ONLY = 0;
Query OK, 1 row affected (0.0098 sec)
 MySQL  localhost:3000 ssl  SQL > SELECT * FROM INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS WHERE SCHEMA_NAME = 'lfq';
+--------------+-------------+---------+
| CATALOG_NAME | SCHEMA_NAME | OPTIONS |
+--------------+-------------+---------+
| def          | lfq         |         |
+--------------+-------------+---------+
1 row in set (0.0017 sec)

方法二

通过 SHOW CREATE DATABASE 语句查看，如果输出结果中带关键字 READ ONLY=1，则表明数据库为只读状态。

MySQL  localhost:3000 ssl  SQL > ALTER SCHEMA lfq READ ONLY = 1;
Query OK, 1 row affected (0.0118 sec)
 MySQL  localhost:3000 ssl  SQL >  show create database lfq;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                            |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------+
| lfq      | CREATE DATABASE `lfq` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1 */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0011 sec)
 MySQL  localhost:3000 ssl  SQL > ALTER SCHEMA lfq READ ONLY = 0;
Query OK, 1 row affected (0.0108 sec)
 MySQL  localhost:3000 ssl  SQL >  show create database lfq;
+----------+------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                        |
+----------+------------------------------------------------------------------------------------------------------------------------+
| lfq      | CREATE DATABASE `lfq` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0023 sec)

使用限制以及注意事项

READ ONLY 选项不能用在 mysql, information_schema, performance_schema 等系统数据库上。

MySQL  localhost:3000 ssl  SQL > ALTER SCHEMA mysql READ ONLY = 1 ;
ERROR: 3552 (HY000): Access to system schema 'mysql' is rejected.

ALTER DATABASE 语句不能同时指定多个不同值的 READ ONLY 选项，否则会报错。

MySQL  localhost:3000 ssl  SQL > ALTER SCHEMA lfq READ ONLY = 1 READ ONLY = 0;
ERROR: 1302 (HY000): Conflicting declarations: 'READ ONLY=0' and 'READ ONLY=1'

ALTER DATABASE 语句在 READ ONLY 选项和其他选项混用时且 READ ONLY 设置为 1，执行 ALTER DATABASE 语句前如果数据库的 READ ONLY = 1，则修改报错。

MySQL  localhost:3000 ssl  SQL > ALTER SCHEMA lfq READ ONLY = 1 ;
Query OK, 1 row affected (0.0141 sec)
 MySQL  localhost:3000 ssl  SQL > SELECT * FROM INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS WHERE SCHEMA_NAME = 'lfq';
+--------------+-------------+-------------+
| CATALOG_NAME | SCHEMA_NAME | OPTIONS     |
+--------------+-------------+-------------+
| def          | lfq         | READ ONLY=1 |
+--------------+-------------+-------------+
1 row in set (0.0069 sec)
 MySQL  localhost:3000 ssl  SQL > ALTER DATABASE lfq READ ONLY = 1 DEFAULT COLLATE utf8mb4_bin;
ERROR: 3989 (HY000): Schema 'lfq' is in read only mode.

ALTER DATABASE 语句会等待该数据库中正在更改的对象的并发事务都已提交后才能执行，反过来也一样，数据库中正在更改的对象的并发事务的执行，需要等待 ALTER DATABASE 语句执行完成。
对于只读数据库，SHOW CREATE DATABASE 生成的语句包含带注释的 READ ONLY 选项（/* READ ONLY = 1 */），使用逻辑备份工具 mysqldump 或者 mysqlpump 备份只读数据库，通过备份文件恢复出来的数据库不是只读的，如果恢复后需要只读，则需要手动执行 ALTER DATABASE 语句设置数据库为只读。

例外情况

不受数据库只读状态的约束。

作为 MySQL 服务初始化，重启，升级，复制功能中的一部分执行的语句。
在服务器启动时由 init_file 系统变量命名的文件中的语句。
可以在只读数据库中创建、更改、删除和写入临时表（TEMPORARY 表。）

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

SQLE 获取

类型	地址
版本库	https://github.com/actiontech/sqle
文档	https://actiontech.github.io/sqle-docs/
发布信息	https://github.com/actiontech/sqle/releases
数据审核插件开发文档	https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse

微信关注我们

原文链接：https://my.oschina.net/actiontechoss/blog/11049300

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

新版Redis不再“开源”，对使用者都有哪些影响？

2024年3月20日，Redis Labs宣布从Redis 7.4开始，将原先比较宽松的BSD源码使用协议修改为RSAv2和SSPLv1协议。该变化意味着 Redis 在 OSI（开放源代码促进会）定义下不再是严格的开源产品。该变化引发Redis使用者的广泛激烈讨论，那么对Redis使用者都有哪些影响呢？ Redis协议变更之后，都有哪些影响？ BSD协议非常宽松，使用者可以任意使用以及商用。RSALv2是由Redis Labs创建的协议，用户可以内部使用，但不能将其商业化提供给其他人使用。SSPLv1是由 MongoDB 公司创建的协议，该协议具有传染性，以此构建的软件都必须以相同协议开放源代码。在文章的FAQ有相应的回复，可以简单概括为： 1. 对于直接使用开源自建Redis的最终用户，可以继续使用，但不允许向其他公司提供Redis商业服务。 2. 基于开源Redis提供Redis服务的第三方供应商，如果未进行商业合作，不能提供Redis 7.4及以后的高版本。对使用第三方Redis供应商的用户，可能购买不到Redis 7.4及以后新版本，或者已有Redis实例不能升级到Redi...

2024-03-27

380

前言对于数据库来说，正确的选择索引是基本的要求，选错索引轻则导致查询缓慢，重则导致数据库整体不可用。PolarDB-X存在多种不同的索引，局部索引、全局索引、列存索引、归档表索引。局部索引就是单机数据库上常用的索引，目的是避免全表扫描。全局索引是分布式数据库为了避免全分片扫描，冗余一份数据，采用与主表不同分区键的索引表。列存索引是主表的列存副本，提供HTAP能力。归档表索引是归档表上的列布隆过滤器，为归档表提供一定的TP查询能力。本文主要介绍一种CN上的局部索引算法：XPlan索引选择。什么是XPlan PolarDB-X包含计算节点(CN)和数据节点(DN)，CN负责SQL解析、优化和执行，DN节负责数据的持久化，CN与DN之间通过RPC通信。DN 100%兼容Mysql，也是作为PolarDB-X标准版进行售卖的。 CN与DN之间RPC通信的内容其实就是标准的SQL，CN会将解析优化好的语法树转成SQL传给DN重新解析、优化。对比起来，将CN的语法树直接传给DN执行听起来就更优[1]。但这样其实不一定好，主要原因是作为存算分离的架构，数据都在DN上，DN可以直接在数...

2024-03-27

259

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。