MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

15.1.2 ALTER DATABASE Statement

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.

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 Chapter 12, Character Sets, Collations, Unicode.

To see the available character sets and collations, use the SHOW CHARACTER SET and SHOW COLLATION statements, respectively. See Section 15.7.7.3, “SHOW CHARACTER SET Statement”, and Section 15.7.7.4, “SHOW COLLATION Statement”.

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 Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”.) 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).

The mysql system schema cannot be set to default encryption. The existing tables within it are part of the general mysql tablespace, which may be encrypted. The information_schema contains only views. It is not possible to create any tables within it. There is nothing on the disk to encrypt. All tables in the performance_schema use the PERFORMANCE_SCHEMA engine, which is purely in-memory. It is not possible to create any other tables in it. There is nothing on the disk to encrypt.

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 Defining an Encryption Default for Schemas and General Tablespaces.

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 Section 28.3.32, “The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table”.

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:

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

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:

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:

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

$> mysqldump --databases mydb > mydb.sql

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

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

    $> 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;