MySQL 8.0.22 READ ONLY Database Feature: Overview, Usage, Query Methods, Limitations and Exceptions

1. New Feature Overview

MySQL 8.0.22 introduced the READ ONLY option in the ALTER DATABASE statement, allowing a single database to be set to read‑only mode, which prevents any write operations on the database and its objects.

2. Usage

Example: setting database lfq to read‑only. The change takes effect immediately for all active sessions.

#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, before change, OPTIONS is empty (not read‑only)
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, set database to read‑only
MySQL  localhost:3000 ssl  SQL > ALTER SCHEMA lfq READ ONLY = 1;
Query OK, 1 row affected (0.0127 sec)

#session1, after change, OPTIONS shows 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, attempts to create a table fail
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, attempts to insert data fail
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.

#session2, query read‑only status confirms it is active
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)

3. Query READ ONLY Status

Method 1

Query the INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS table; if the OPTIONS column contains READ ONLY=1 , the database is read‑only, otherwise it is not.

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

Method 2

Use SHOW CREATE DATABASE ; if the output contains the comment /* READ ONLY = 1 */ , the database is read‑only.

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)

4. Usage Restrictions and Notes

The READ ONLY option cannot be applied to system schemas such as mysql , information_schema , and performance_schema .

The ALTER DATABASE statement cannot specify multiple conflicting READ ONLY values; doing so results in an error.

When mixing READ ONLY with other options, if the database is already in read‑only mode, attempting to change other options will fail.

Additional notes:

ALTER DATABASE will wait for all concurrent transactions on the target objects to finish before it executes, and vice‑versa.

For read‑only databases, SHOW CREATE DATABASE includes a comment /* READ ONLY = 1 */ . Logical backup tools like mysqldump or mysqlpump restore the database without the read‑only flag; you must manually set it after restoration.

5. Exceptions

Operations that are not restricted by the read‑only state include:

Statements executed as part of MySQL service initialization, restart, upgrade, or replication.

Statements run from a file referenced by the init_file system variable during server startup.

Creation, alteration, deletion, and insertion into temporary tables ( TEMPORARY ).

References

[1] alter-database-read-only