DB System

A MySQL DB system is a logical container for the MySQL instance. It provides an interface enabling management of tasks such as provisioning, backup and restore, monitoring, and so on. It also provides a read/write endpoint enabling you to connect to the MySQL instance using the standard protocols.

DB System Overview

A MySQL Database Service DB System consists of a compute instance, operating system, MySQL Server, Virtual Network Interface Card (VNIC), and block storage.

  • A compute instance (with resources defined by the associated shape. See Supported Shapes.
  • Oracle Linux Operating System.
  • MySQL Server Enterprise Edition 8.0.
  • Virtual Network Interface Card (VNIC) that attaches the DB system to a subnet of the Virtual Cloud Network (VCN).
  • Network-attached block storage. MySQL Database Service uses the Higher Performance option for all block storage. See Block Volume.
Note

You cannot access the operating system. Only the MySQL instance is exposed through the endpoint of the DB system.

Security Best Practices

Oracle considers cloud security its highest priority. Use features such as in-transit encryption, data masking, and deletion plan to keep your data safe and secure.

Table 2-1 Security Best Practices

Feature Best Practice
Database access control and account management Use MySQL security features to control access and manage your account. See Access Control and Account Management.
Public API audit Use audit service to view Oracle Cloud Infrastructure public API activities that happened in the tenancy or compartment, and to find out the details of the API activities, such as the source, target, or time the API activity occurred. See Overview of Audit.
authentication_oci plugin Use MySQL authentication_oci plugin to map MySQL users to existing users and groups defined in the IAM service. See Connecting to a DB System Using authentication_oci Plugin.
connection-control plugin By default, MySQL Database Service supports connection-control plugin to provide a deterrent that slows down brute force attacks against MySQL user accounts. See Plugins and Components.
In-transit encryption Your data is always encrypted at rest. You can use in-transit encryption for a given user to secure your data. See Data Security.
Data masking Use data masking to protect your sensitive data. See Data Masking.
Deletion plan Use deletion plan to protect your DB system against delete operations. See Advanced Option: Deletion Plan.
Identity and Access Management As a security administrator, assign minimum privileges to users. Use IAM policies to control access and use of MySQL resources. See Required IAM Policy.
validate_password component MySQL Database Service enforces strong passwords with the validate_password component. Make sure your applications comply with the password requirements. See Plugins and Components.
Virtual cloud network (VCN)
  • Configure network security groups or security lists of the VCN to restrict the authorized public IP addresses to a single IP address or a small range of IP addresses. See Adding Ingress Rules.
  • Configure your MySQL DB system to use private subnets of your VCN. To connect to your MySQL DB system from an external network, use a Bastion Session or a VPN connection. If you can connect to your DB system over the internet only, restrict the authorized public IP addresses to a single IP address or a small range of IP addresses, and use in-transit encryption. See Connecting to a DB System Using a Network Load Balancer.

MySQL Server

Unsupported MySQL Server Features

Certain features of MySQL Server are currently unsupported in MySQL Database Service.

  • Authentication plugins:
    • Client-Side Cleartext Pluggable Authentication
    • PAM Pluggable Authentication
    • Windows Pluggable Authentication
    • LDAP Pluggable Authentication
    • Kerberos Pluggable Authentication
    • No-Login Pluggable Authentication
    • Socket Peer-Credential Pluggable Authentication
    • FIDO Pluggable Authentication
    • Test Pluggable Authentication
    • Pluggable Authentication System Variables
  • Modification of system tables
  • Binary log access
  • Error logging to the system log
    Note

    You can view error logs using performance schema. See Viewing performance_schema.error_log Using a Command-Line Client.
  • Group replication plugin
  • InnoDB tablespace encryption
  • Password strength plugin
  • Setting global variables
  • Persisted system variables
    Note

    Use MySQL configurations instead. See Configurations.
  • Replication filters
  • Semisynchronous replication
  • Transportable tablespace

MySQL Storage Engine

MySQL Database Service supports the InnoDB storage engine only.

If you intend to migrate to MySQL Database Service, and are not using InnoDB, convert your existing database to InnoDB before attempting to migrate to MySQL Database Service.

Plugins and Components

Certain MySQL Server plugins and components are loaded in the DB system automatically. You do not need to install any of these plugins.

  • MySQL Enterprise Thread Pool: The thread pool plugin provides an alternative thread-handling model designed to reduce overhead and improve performance. See MySQL Enterprise Thread Pool.
  • The connection_control plugin: MySQL Server includes a plugin library that enables administrators to introduce an increasing delay in server response to connection attempts after a configurable number of consecutive failed attempts. The default value of the variables of the connection-control plugin are as follows, and you cannot change the default values:
    • connection_control_failed_connections_threshold : 3
    • connection_control_max_connection_delay: 10000
    • connection_control_min_connection_delay:1000
    See Connection-Control Plugins.
  • The validate_password component: The component serves to improve security by requiring account passwords and enabling strength testing of potential passwords. The default value of the variables of the validate_password component are as follows, and you cannot change the default values:
    • validate_password.check_user_name: ON
    • validate_password.length: 8
    • validate_password.mixed_case_count:1
    • validate_password.number_count:1
    • validate_password.policy: MEDIUM
    • validate_password.special_char_count :1
    See Password Validation Component.
  • Data masking: The general-purpose masking functions mask arbitrary strings, special-purpose masking functions mask specific types of values, and generate functions generate random values. See Data Masking.
Data Masking

MySQL Database Service supports various MySQL data masking functions that mask data to remove identifying characteristics and generate random data with specific characteristics.

MySQL Database Service supports the following data masking functions:

  • gen_range
  • gen_rnd_email
  • gen_rnd_ssn
  • gen_rnd_us_phone
  • mask_inner
  • mask_outer
  • mask_pan
  • mask_pan_relaxed
  • mask_ssn

MySQL Database Service does not support the following data masking functions:

  • gen_blocklist
  • gen_dictionary
  • gen_dictionary_drop
  • gen_dictionary_load
  • gen_rnd_pan

Limitations

There are certain limitations of the MySQL Server.

The following are currently not permitted in MySQL Server:
  • Custom TLS certificates.
  • Granting privileges using wildcards: You must use fully-qualified grants. By default, the system variable partial-revokes is enabled and you cannot be disable it. As a result, wildcards are treated literally. See partial_revokes.

Default MySQL Privileges

Certain MySQL privileges are granted to the DB system administrator and certain privileges are revoked from the mysql and sys schemas.

Related Topics

Global Privileges Granted

Certain global MySQL privileges are granted to the DB system administrator.

Global Dynamic Privileges Granted

Certain global dynamic MySQL privileges are granted to the DB system administrator.

Privileges Revoked from mysql Schema

Certain privileges are revoked from the mysql schema. You cannot grant these privileges to any DB system user.

Table 2-4 Privileges Revoked from mysql Schema

Related Topics

Privileges Revoked from sys Schema

Certain privileges are revoked from the sys schema. You cannot grant these privileges to any DB system user.

Table 2-5 Privileges Revoked from sys Schema

Related Topics

Listing Grants Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to list the privileges granted to a user.

  1. Do one of the following to list the privileges granted to a user:
    • To view the privileges granted to the current user, run the following command:
      SHOW GRANTS \G
    • To view the privileges granted to a named user, run the following command:
      SHOW GRANTS FOR <Username> \G
    If the command was run for an administrator user, User001, the command returns the following:
    SHOW GRANTS \G
    *************************** 1. row ***************************
    Grants for User001@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
    PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, 
    LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, 
    SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, 
    DROP ROLE ON *.* TO `User001`@`%` WITH GRANT OPTION
    *************************** 2. row ***************************
    Grants for User001@%: GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,CONNECTION_ADMIN,
    FLUSH_TABLES,REPLICATION_APPLIER,ROLE_ADMIN,XA_RECOVER_ADMIN ON *.* TO `User001`@`%` 
    WITH GRANT OPTION
    *************************** 3. row ***************************
    Grants for User001@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, 
    ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, 
    ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `User001`@`%`
    *************************** 4. row ***************************
    Grants for User001@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
    CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, 
    ALTER ROUTINE, EVENT, TRIGGER ON `sys`.* FROM `User001`@`%`
    *************************** 5. row ***************************
    Grants for User001@%: GRANT PROXY ON ''@'' TO 'User001'@'%' WITH GRANT OPTION
    *************************** 6. row ***************************
    Grants for User001@%: GRANT `administrator`@`%` TO `User001`@`%` WITH ADMIN OPTION

    The rows correspond to the following:

    • Row 1: Static privileges.
    • Row 2: Dynamic privileges.
    • Row 3: Privileges revoked from the mysql schema.
    • Row 4: Privileges revoked from the sys schema.
    • Row 5: Proxy users.
    • Row 6: Role assigned to the user.

Reserved Usernames

Certain usernames are reserved and you cannot use them for the administrator username.

  • administrator
  • ociadmin
  • ocirpl
  • mysql.sys
  • mysql.session
  • mysql.infoschema

Server Versioning

The MySQL Server of the MySQL Database Service uses a versioning system that consists of three numbers, an update version, and the -cloud suffix.

For example, 8.0.27-u3-cloud is a MySQL Server version.

  • First number: Specifies the major release number.
  • Second number: Specifies the minor release number. The major and minor numbers together constitute the release series number. The series number describes the stable feature set.
  • Third number: Specifies the version number within the release series. This is incremented for each new bugfix release.
  • u*: Specifies the MySQL Database Service-specific update number. Fixes and feature development for the Oracle Cloud Infrastructure version of MySQL Server are delivered according to a different schedule to the on-premise version.
  • cloud: Specifies that this version of MySQL server was built for use in MySQL Database Service only.
Retrieving a MySQL Server Version Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to retrieve your MySQL Server version.

  1. Connect to your DB system using the command-line client of your choice.
  2. Run the following command:
    SELECT @@version;
You get a response similar to the following:
 SELECT @@version;
+-----------------+
| @@version       |
+-----------------+
| 8.0.27-u3-cloud |
+-----------------+
1 row in set (0.1687 sec)

Server Upgrades

MySQL Database Service supports the two most recent release versions.

MySQL Database Service currently supports the following versions of MySQL:

MySQL Database Service supports the following two types of upgrades:

  • Automatic upgrades:
    • If your MySQL Server version is running two versions behind the latest release version, MySQL Database Service automatically upgrades the minor version during the maintenance window. For example, if your MySQL Server version is 8.0.28 (or a lower version), the server automatically upgrades to the latest release version, 8.0.29.
    • Upgrade versions, such as 8.0.28-u1, are applied automatically during the maintenance window that you define on the DB system. The upgrade versions contain critical patches such as security and stability fixes.
  • Manual upgrades: You can manually upgrade the version using the Console or the CLI. See Upgrading the MySQL Server.
    Note

    You cannot rollback (downgrade) a MySQL version. It is recommended to perform a full backup before upgrading your DB system.

Crash Recovery

MySQL Server supports crash recovery, which ensures durability and enables data recovery in the event of an unexpected server exit. While this redundancy is advantageous during normal operation of the server, it can lower the performance of large data imports. It is possible to disable the crash recovery processes, temporarily, enabling you to execute DML statements without the overhead of synchronization.
Note

If any component of a standalone DB System fails while crash recovery is disabled, the DB System will enter a FAILED state and will be unrecoverable. It is strongly recommended to perform a full manual backup before disabling crash recovery. Highly available DB Systems in multi-Availability Domain regions are more failure resistant but, in certain circumstances, can also become unrecoverable.
Disabling crash recovery disables the following:
  • InnoDB Redo Log.
  • Doublewrite buffer.
  • Binary log synchronization.
If you disable crash recovery, it is not possible to use the following MySQL Database Service processes which depend on it:
  • Backups (Manual and Automatic)
  • DB System Stop and Restart
Note

It is not recommended to run a DB System without crash recovery except when performing large imports of data.

If crash recovery is disabled when a DB System upgrade operation begins, it is re-enabled for the duration of the upgrade process, and disabled again when the upgrade is finished. The same is true during a failover of a Highly Available DB System primary to secondary. When the promotion process is complete, crash recovery is disabled again.

To edit crash recovery, see the following:

DB System Storage

MySQL Database Storage performance scales with the storage size selected for the DB system.

You cannot limit or edit the MySQL DB system storage IOPS. You must provision the storage size based on your data size and performance requirements.

MySQL Database Service uses the Block Volume service Higher Performance option over iSCSI. The final performance results may vary for different shapes and scenarios. It is recommended to perform benchmark analysis during your Proof of Concept testing to verify your environment's configuration has adequate performance for your application's requirements.

DB System Networking

The MySQL DB system endpoints are not directly accessible from the internet. Use a networking service such as a VPN, Oracle FastConnect, or a compute instance to access the endpoint of a DB system.

  • The Networking service reserves three IP addresses in each subnet.
  • A standalone MySQL DB system requires three IP addresses: one for the DB system's IP address, one for the compute instance hosting the MySQL instance, and one for maintenance and upgrade tasks on the MySQL instance.
  • A highly available MySQL DB system requires up to seven IP addresses: one for the DB system's IP address, one per compute instance hosting the MySQL instance (three in total), and one per MySQL instance for maintenance and upgrade tasks (up to three in total).

Related Topics

Supported Shapes

A shape is a template that determines the number of OCPUs, amount of memory, and other resources that are allocated within a DB system. MySQL configurations are associated with shapes. To apply a specific configuration to the DB system, select the associated shape. MySQL shapes are available with AMD processors and Intel processors.

The following configurations are supported:

  • Standalone: Optimised for single instance DB systems.
  • High Availability: Optimised for highly available DB systems. See High Availability.
  • HeatWave: Optimised for HeatWave clusters. See HeatWave.

Table 2-6 Supported Shapes

Supported Shapes OCPUs Memory (RAM) Max Network Bandwidth Associated Configurations
VM.Standard.E2.1 1 8 1 Gbps VM.Standard.E2.1 (Standalone)
VM.Standard.E2.2 2 16 2 Gbps VM.Standard.E2.2 (Standalone)
VM.Standard.E2.4 4 32 4.1 Gbps VM.Standard.E2.4 (Standalone)
VM.Standard.E2.8 8 64 8.2Gbps VM.Standard.E2.8 (Standalone)
MySQL.VM.Standard.E3.1.8GB 1 8 1 Gbps
  • MySQL.VM.Standard.E3.1.8GB.Standalone
  • MySQL.VM.Standard.E3.1.8GB.HA
MySQL.VM.Standard.E3.1.16GB 1 16 1 Gbps
  • MySQL.VM.Standard.E3.1.16GB.Standalone
  • MySQL.VM.Standard.E3.1.16GB.HA
MySQL.VM.Standard.E3.2.32GB 2 32 2 Gbps
  • MySQL.VM.Standard.E3.2.32GB.Standalone
  • MySQL.VM.Standard.E3.2.32GB.HA
MySQL.VM.Standard.E3.4.64GB 4 64 4 Gbps
  • MySQL.VM.Standard.E3.4.64GB.Standalone
  • MySQL.VM.Standard.E3.4.64GB.HA
MySQL.VM.Standard.E3.8.128GB 8 128 8 Gbps
  • MySQL.VM.Standard.E3.8.128GB.Standalone
  • MySQL.VM.Standard.E3.8.128GB.HA
MySQL.VM.Standard.E3.16.256GB 16 256 16 Gbps
  • MySQL.VM.Standard.E3.16.256GB.Standalone
  • MySQL.VM.Standard.E3.16.256GB.HA
MySQL.VM.Standard.E3.24.384GB 24 384 24 Gbps
  • MySQL.VM.Standard.E3.24.384GB.Standalone
  • MySQL.VM.Standard.E3.24.384GB.HA
MySQL.VM.Standard.E3.32.512GB 32 512 32 Gbps
  • MySQL.VM.Standard.E3.32.512GB.Standalone
  • MySQL.VM.Standard.E3.32.512GB.HA
MySQL.VM.Standard.E3.48.768 GB 48 768 40 Gbps
  • MySQL.VM.Standard.E3.48.768GB.Standalone
  • MySQL.VM.Standard.E3.48.768GB.HA
MySQL.VM.Standard.E3.64.1024GB 64 1024 40 Gbps
  • MySQL.VM.Standard.E3.64.1024GB.Standalone
  • MySQL.VM.Standard.E3.64.1024GB.HA
MySQL.VM.Standard.E4.1.8GB 1 8 1 Gbps
  • MySQL.VM.Standard.E4.1.8GB.Standalone
  • MySQL.VM.Standard.E4.1.8GB.HA
MySQL.VM.Standard.E4.1.16GB 1 16 1 Gbps
  • MySQL.VM.Standard.E4.1.16GB.Standalone
  • MySQL.VM.Standard.E4.1.16GB.HA
MySQL.VM.Standard.E4.2.32GB 2 32 2 Gbps
  • MySQL.VM.Standard.E4.2.32GB.Standalone
  • MySQL.VM.Standard.E4.2.32GB.HA
MySQL.VM.Standard.E4.4.64GB 4 64 4 Gbps
  • MySQL.VM.Standard.E4.4.64GB.Standalone
  • MySQL.VM.Standard.E4.4.64GB.HA
MySQL.VM.Standard.E4.8.128GB 8 128 8 Gbps
  • MySQL.VM.Standard.E4.8.128GB.Standalone
  • MySQL.VM.Standard.E4.8.128GB.HA
MySQL.VM.Standard.E4.16.256GB 16 256 16 Gbps
  • MySQL.VM.Standard.E4.16.256GB.Standalone
  • MySQL.VM.Standard.E4.16.256GB.HA
MySQL.VM.Standard.E4.24.384GB 24 384 24 Gbps
  • MySQL.VM.Standard.E4.24.384GB.Standalone
  • MySQL.VM.Standard.E4.24.384GB.HA
MySQL.VM.Standard.E4.32.512GB 32 512 32 Gbps
  • MySQL.VM.Standard.E4.32.512GB.Standalone
  • MySQL.VM.Standard.E4.32.512GB.HA
MySQL.VM.Standard.E4.48.768GB 48 768 40 Gbps
  • MySQL.VM.Standard.E4.48.768GB.Standalone
  • MySQL.VM.Standard.E4.48.768GB.HA
MySQL.VM.Standard.E4.64.1024GB 64 1024 40 Gbps
  • MySQL.VM.Standard.E4.64.1024GB.Standalone
  • MySQL.VM.Standard.E4.64.1024GB.HA
MySQL.VM.Standard2.1.15GB 1 15 1 Gbps
  • MySQL.VM.Standard2.1.15GB.Standalone
  • MySQL.VM.Standard2.1.15GB.HA
MySQL.VM.Standard2.2.30GB 2 30 2 Gbps
  • MySQL.VM.Standard2.2.30GB.Standalone
  • MySQL.VM.Standard2.2.30GB.HA
MySQL.VM.Standard2.4.60GB 4 60 4.1 Gbps
  • MySQL.VM.Standard2.4.60GB.Standalone
  • MySQL.VM.Standard2.4.60GB.HA
MySQL.VM.Standard2.8.120GB 8 120 8.2 Gbps
  • MySQL.VM.Standard2.8.120GB.Standalone
  • MySQL.VM.Standard2.8.120GB.HA
MySQL.VM.Standard2.16.240GB 16 240 16.4 Gbps
  • MySQL.VM.Standard2.16.240GB.Standalone
  • MySQL.VM.Standard2.16.240GB.HA
MySQL.VM.Standard3.1.8GB 1 8 1 Gbps
  • MySQL.VM.Standard3.1.8GB.Standalone
  • MySQL.VM.Standard3.1.8GB.HA
MySQL.VM.Standard3.1.16GB 1 16 1 Gbps
  • MySQL.VM.Standard3.1.16GB.Standalone
  • MySQL.VM.Standard3.1.16GB.HA
MySQL.VM.Standard3.2.32GB 2 32 2 Gbps
  • MySQL.VM.Standard3.2.32GB.Standalone
  • MySQL.VM.Standard3.2.32GB.HA
MySQL.VM.Standard3.4.64GB 4 64 4 Gbps
  • MySQL.VM.Standard3.4.64GB.Standalone
  • MySQL.VM.Standard3.4.64GB.HA
MySQL.VM.Standard3.8.128GB 8 128 8 Gbps
  • MySQL.VM.Standard3.8.128GB.Standalone
  • MySQL.VM.Standard3.8.128GB.HA
MySQL.VM.Standard3.16.256GB 16 256 16 Gbps
  • MySQL.VM.Standard3.16.256GB.Standalone
  • MySQL.VM.Standard3.16.256GB.HA
MySQL.VM.Standard3.24.384GB 24 384 24 Gbps
  • MySQL.VM.Standard3.24.384GB.Standalone
  • MySQL.VM.Standard3.24.384GB.HA
MySQL.VM.Standard3.32.512GB 32 512 32 Gbps
  • MySQL.VM.Standard3.32.512GB.Standalone
  • MySQL.VM.Standard3.32.512GB.HA
MySQL.VM.Optimized3.1.8GB 1 8 4 Gbps
  • MySQL.VM.Optimized3.1.8GB.Standalone
  • MySQL.VM.Optimized3.1.8GB.HA
MySQL.VM.Optimized3.1.16GB 1 16 4 Gbps
  • MySQL.VM.Optimized3.1.16GB.Standalone
  • MySQL.VM.Optimized3.1.16GB.HA
MySQL.VM.Optimized3.2.32GB 2 32 8 Gbps
  • MySQL.VM.Optimized3.2.32GB.Standalone
  • MySQL.VM.Optimized3.2.32GB.HA
MySQL.VM.Optimized3.4.64GB 4 64 16 Gbps
  • MySQL.VM.Optimized3.4.64GB.Standalone
  • MySQL.VM.Optimized3.4.64GB.HA
MySQL.VM.Optimized3.8.128GB 8 128 32 Gbps
  • MySQL.VM.Optimized3.8.128GB.Standalone
  • MySQL.VM.Optimized3.8.128GB.HA
MySQL.VM.Optimized3.16.256GB 16 256 40 Gbps
  • MySQL.VM.Optimized3.16.256GB.Standalone
  • MySQL.VM.Optimized3.16.256GB.HA
MySQL.HeatWave.BM.Standard.E3
Note

Supported on DB system node only.
128 2048 40 Gbps MySQL.HeatWave.BM.Standard.E3.Standalone
MySQL.HeatWave.VM.Standard.E3
Note

Supported on DB system and cluster nodes.
16 512 16 Gbps MySQL.HeatWave.VM.Standard.E3.Standalone