Oracle Cloud Infrastructure Documentation

MySQL Server

MySQL Database Service is a managed service and supports a variety of features.

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. You can 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 enters a FAILED state and is unrecoverable. It is recommended to perform a full manual backup before disabling crash recovery. Highly available DB systems in multi-availability domains 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, you cannot use the following MySQL Database Service processes:

  • 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.

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

Reserved Usernames

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

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

MySQL Server Versions

MySQL Database Service supports the two most recent release versions of MySQL.

MySQL Database Service currently supports the following versions:

Retrieving the Version of a MySQL Server

Retrieve the version of a MySQL Server. MySQL Database Service supports the two most recent release versions of MySQL.

Using a Command-Line Client

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

  1. Connect to the 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)

8.0.27-u3-cloud is a MySQL Server version.

  • First number: Specifies the major release number such as 8.
  • Second number: Specifies the minor release number such as 0. 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 such as 27. This is incremented for each new bugfix release.
  • u*: Specifies the MySQL Database Service-specific update number such as u3. 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.

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

Listing Priviliges 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.

Global Privileges Granted

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

Table 5-3 Global Privileges Granted

Global Privileges Granted
ALTER ALTER ROUTINE
CREATE CREATE ROLE
CREATE ROUTINE CREATE TEMPORARY TABLES
CREATE USER CREATE VIEW
DELETE DROP
EVENT EXECUTE
INDEX INSERT
LOCK TABLES PROCESS
REFERENCES REPLICATION CLIENT
REPLICATION SLAVE SELECT
SHOW DATABASES SHOW VIEW
TRIGGER UPDATE

Global Dynamic Privileges Granted

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

Table 5-4 Global Dynamic Privileges Granted

Global Dynamic Privileges
APPLICATION_PASSWORD_ADMIN CONNECTION_ADMIN
FLUSH_TABLES REPLICATION_APPLIER
RESOURCE_GROUP_ADMIN RESOURCE_GROUP_USER
ROLE_ADMIN XA_RECOVER_ADMIN

Privileges Revoked from mysql Schema

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

Table 5-5 Privileges Revoked from mysql Schema

Privileges Revoked from mysql Schema
INSERT UPDATE
DELETE CREATE VIEW
DROP REFERENCES
INDEX ALTER
CREATE TEMPORARY TABLES LOCK TABLES
CREATE VIEW CREATE ROUTINE
EXECUTE ALTER ROUTINE
TRIGGER EVENT

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 5-6 Privileges Revoked from sys Schema

Privileges Revoked from sys Schema
ALTER ALTER ROUTINE
CREATE CREATE ROUTINE
CREATE TEMPORARY TABLES CREATE VIEW
DROP EVENT
INDEX LOCK TABLES
REFERENCES TRIGGER

Related Topics

Unsupported Features

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

  • Authentication plugins. See 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. You can view error logs using the Performance Schema. See Viewing the Error Log.
  • Group replication plugin
  • InnoDB tablespace encryption
  • Password strength plugin
  • Setting global variables
  • Persisted system variables. Use MySQL configurations instead. See Configuration of a DB System.
  • Replication filters
  • Semisynchronous replication
  • Transportable tablespace
  • 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.