READ ONLY  is permitted for ALTER DATABASE , but not for CREATE DATABASE . However, for a read-only database, the statement produced by SHOW CREATE DATABASE  does include READ ONLY=1  within a comment to indicate its read-only status: 

mysql> ALTER DATABASE mydb READ ONLY = 1; mysql> SHOW CREATE DATABASE mydb\G *************************** 1. row *************************** Database: mydb Create Database: CREATE DATABASE ‘mydb‘ /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION=’N’ */ /* READ ONLY = 1 */ 

If the server executes a CREATE DATABASE  statement containing such a comment, the server ignores the comment and the READ ONLY  option is not processed. This has implications for mysqldump  and mysqlpump , which use SHOW CREATE DATABASE  to produce CREATE DATABASE  statements in dump output: 

•In a dump file, the CREATE DATABASE  statement for a read-only database contains the commented READ ONLY  option. 

•The dump file can be restored as usual, but because the server ignores the commented READ ONLY option, the restored database is not  read only. If the database is to be read ony after being restored, you must execute ALTER DATABASE  manually to make it so. 

Suppose that mydb  is read only and you dump it as follows: 

shell> mysqldump --databases mydb > mydb.sql 

A restore operation later must be followed by ALTER DATABASE  if mydb  should still be read only: 

shell> mysql mysql> SOURCE mydb.sql; mysql> ALTER DATABASE mydb READ ONLY = 1; 

MySQL Enterprise Backup is not subject to this issue. It backs up and restores a read-only database like any other, but enables the READ ONLY  option at restore time if it was enabled at backup time. 

ALTER DATABASE  is written to the binary log, so a change to the READ ONLY  option on a replication source server also affects replicas. To prevent this from happening, binary logging must be disabled prior to execution of the ALTER DATABASE  statement. For example, to prepare for migrating a database without affecting replicas, perform these operations: 

1. Within a single session, disable binary logging and enable READ ONLY  for the database: 

mysql> SET sql_log_bin = OFF; mysql> ALTER DATABASE mydb READ ONLY = 1; 

2. Dump the database, for example, with mysqldump  or mysqlpump : 

shell> mysqldump --databases mydb > mydb.sql 

3. Within a single session, disable binary logging and disable READ ONLY  for the database: 

## 13.1.3 ALTER EVENT Statement 

mysql> SET sql_log_bin = OFF; mysql> ALTER DATABASE mydb READ ONLY = 0; 

ALTER [DEFINER = user ] 

