ALTER SCHEMA

Syntax:
ALTER {DATABASE | SCHEMA} [db_name]
alter_option ...

alter_option: {
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
| READ ONLY [=] {DEFAULT | 0 | 1}
}

ALTER DATABASE enables you to change the overall characteristics of a
database. These characteristics are stored in the data dictionary. This
statement requires the ALTER privilege on the database. ALTER SCHEMA is
a synonym for ALTER DATABASE.

If the database name is omitted, the statement applies to the default
database. In that case, an error occurs if there is no default
database.

For any alter_option omitted from the statement, the database retains
its current option value, with the exception that changing the
character set may change the collation and vice versa.

o https://dev.mysql.com/doc/refman/8.0/en/alter-database.html#alter-dat
abase-charset

o https://dev.mysql.com/doc/refman/8.0/en/alter-database.html#alter-dat
abase-encryption

o https://dev.mysql.com/doc/refman/8.0/en/alter-database.html#alter-dat
abase-read-only

Character Set and Collation Options

The CHARACTER SET option changes the default database character set.
The COLLATE option changes the default database collation. For
information about character set and collation names, see
https://dev.mysql.com/doc/refman/8.0/en/charset.html.

To see the available character sets and collations, use the SHOW
CHARACTER SET and SHOW COLLATION statements, respectively. See [HELP
SHOW CHARACTER SET], and [HELP SHOW COLLATION].

A stored routine that uses the database defaults when the routine is
created includes those defaults as part of its definition. (In a stored
routine, variables with character data types use the database defaults
if the character set or collation are not specified explicitly. See
[HELP CREATE PROCEDURE].) If you change the default character set or
collation for a database, any stored routines that are to use the new
defaults must be dropped and recreated.

Encryption Option

The ENCRYPTION option, introduced in MySQL 8.0.16, defines the default
database encryption, which is inherited by tables created in the
database. The permitted values are 'Y' (encryption enabled) and 'N'
(encryption disabled). Only newly created tables inherit the default
database encryption. For existing tables associated with the database,
their encryption remains unchanged. If the
table_encryption_privilege_check system variable is enabled, the
TABLE_ENCRYPTION_ADMIN privilege is required to specify a default
encryption setting that differs from the value of the
default_table_encryption system variable. For more information, see
https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html#inn
odb-schema-tablespace-encryption-default.

Read Only Option

The READ ONLY option, introduced in MySQL 8.0.22, controls whether to
permit modification of the database and objects within it. The
permitted values are DEFAULT or 0 (not read only) and 1 (read only).
This option is useful for database migration because a database for
which READ ONLY is enabled can be migrated to another MySQL instance
without concern that the database might be changed during the
operation.

With NDB Cluster, making a database read only on one mysqld server is
synchronized to other mysqld servers in the same cluster, so that the
database becomes read only on all mysqld servers.

The READ ONLY option, if enabled, is displayed in the
INFORMATION_SCHEMA SCHEMATA_EXTENSIONS table. See
https://dev.mysql.com/doc/refman/8.0/en/information-schema-schemata-ext
ensions-table.html.

The READ ONLY option cannot be enabled for these system schemas: mysql,
information_schema, performance_schema.

In ALTER DATABASE statements, the READ ONLY option interacts with other
instances of itself and with other options as follows:

o An error occurs if multiple instances of READ ONLY conflict (for
example, READ ONLY = 1 READ ONLY = 0).

o An ALTER DATABASE statement that contains only (nonconflicting) READ
ONLY options is permitted even for a read-only database.

o A mix of (nonconflicting) READ ONLY options with other options is
permitted if the read-only state of the database either before or
after the statement permits modifications. If the read-only state
both before and after prohibits changes, an error occurs.

This statement succeeds whether or not the database is read only:

ALTER DATABASE mydb READ ONLY = 0 DEFAULT COLLATE utf8mb4_bin;

This statement succeeds if the database is not read only, but fails
if it is already read only:

ALTER DATABASE mydb READ ONLY = 1 DEFAULT COLLATE utf8mb4_bin;

Enabling READ ONLY affects all users of the database, with these
exceptions that are not subject to read-only checks:

o Statements executed by the server as part of server initialization,
restart, upgrade, or replication.

o Statements in a file named at server startup by the init_file system
variable.

o TEMPORARY tables; it is possible to create, alter, drop, and write to
TEMPORARY tables in a read-only database.

o NDB Cluster non-SQL inserts and updates.

Other than for the excepted operations just listed, enabling READ ONLY
prohibits write operations to the database and its objects, including
their definitions, data, and metadata. The following list details
affected SQL statements and operations:

o The database itself:

o CREATE DATABASE

o ALTER DATABASE (except to change the READ ONLY option)

o DROP DATABASE

o Views:

o CREATE VIEW

o ALTER VIEW

o DROP VIEW

o Selecting from views that invoke functions with side effects.

o Updating updatable views.

o Statements that create or drop objects in a writable database are
rejected if they affect metadata of a view in a read-only database
(for example, by making the view valid or invalid).

o Stored routines:

o CREATE PROCEDURE

o DROP PROCEDURE

o CALL (of procedures with side effects)

o CREATE FUNCTION

o DROP FUNCTION

o SELECT (of functions with side effects)

o For procedures and functions, read-only checks follow prelocking
behavior. For CALL statements, read-only checks are done on a
per-statement basis, so if some conditionally executed statement
writing to a read-only database does not actually execute, the call
still succeeds. On the other hand, for a function called within a
SELECT, execution of the function body happens in prelocked mode.
As long as a some statement within the function writes to a
read-only database, execution of the function fails with an error
regardless of whether the statement actually executes.

o Triggers:

o CREATE TRIGGER

o DROP TRIGGER

o Trigger invocation.

o Events:

o CREATE EVENT

o ALTER EVENT

o DROP EVENT

o Event execution:

o Executing an event in the database fails because that would
change the last-execution timestamp, which is event metadata
stored in the data dictionary. Failure of event execution also
has the effect of causing the event scheduler to stop.

o If an event writes to an object in a read-only database,
execution of the event fails with an error, but the event
scheduler is not stopped.

o Tables:

o CREATE TABLE

o ALTER TABLE

o CREATE INDEX

o DROP INDEX

o RENAME TABLE

o TRUNCATE TABLE

o DROP TABLE

o DELETE

o INSERT

o IMPORT TABLE

o LOAD DATA

o LOAD XML

o REPLACE

o UPDATE

o For cascading foreign keys where the child table is in a read-only
database, updates and deletes on the parent are rejected even if
the child table is not directly affected.

o For a MERGE table such as CREATE TABLE s1.t(i int) ENGINE MERGE
UNION (s2.t, s3.t), INSERT_METHOD=..., the following behavior
applies:

o Inserting into the MERGE table (INSERT into s1.t) fails if at
least one of s1, s2, s3 is read only, regardless of insert
method. The insert is refused even if it would actually end up in
a writable table.

o Dropping the MERGE table (DROP TABLE s1.t) succeeds as long as s1
is not read only. It is permitted to drop a MERGE table that
refers to a read-only database.

An ALTER DATABASE statement blocks until all concurrent transactions
that have already accessed an object in the database being altered have
committed. Conversely, a write transaction accessing an object in a
database being altered in a concurrent ALTER DATABASE blocks until the
ALTER DATABASE has committed.

If the Clone plugin is used to clone a local or remote data directory,
the databases in the clone retain the read-only state they had in the
source data directory. The read-only state does not affect the cloning
process itself. If it is not desirable to have the same database
read-only state in the clone, the option must be changed explicitly for
the clone after the cloning process has finished, using ALTER DATABASE
operations on the clone.

When cloning from a donor to a recipient, if the recipient has a user
database that is read only, cloning fails with an error message.
Cloning may be retried after making the database writable.

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:

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

o 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:

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

URL: https://dev.mysql.com/doc/refman/8.0/en/alter-database.html