Oracle Cloud Infrastructure Documentation

Perform External MySQL DB System-related Prerequisite Tasks

Before you enable and use Database Management for External MySQL DB systems, you must complete the prerequisite tasks listed in the following table.

Task Description More Information
Configure MySQL variables

You must configure the following MySQL variables to collect performance metrics of the External MySQL DB system and monitor and analyze SQL performance in Database Management:

  • performance_schema: This variable is set to ON by default and is required to enable the Performance Schema.
  • max_digest_length: This variable is required for the collection of larger SQL statements. The default value is 1024 and it's recommended that the value be set to 4096 to allow for the collection of longer statements.
  • performance_schema_max_digest_length: This variable is required and the value must match the value set for the max_digest_length variable.
For information on performance_schema, see Performance Schema Quick Start.

For information on the other required variables, see:
Install Management Agents The Oracle Cloud Infrastructure Management Agent service is required to connect to an instance in the External MySQL DB system that is deployed on premises, and Database Management will also use the Management Agent to collect data and metrics. You must install a Management Agent on a host that can connect to the External MySQL DB system. If the host does not have direct access to the internet, you must install a Management Gateway, which acts as a single-point-of-communication between the Management Agent (on the External MySQL DB system host) and Oracle Cloud Infrastructure.

Note that a Management Agent 241023.2127 or later is required to connect to External MySQL DB systems.

 For information on how to install Management Agents, see Install Management Agents.

For information on Management Gateway, see Management Gateway.

Create a user and grant the required privileges You must create a user with the database table privileges required to fetch metrics and data points from the External MySQL DB system:
  1. Create a user using the following command. 
    CREATE USER '<username>'@'<hostname/IP>' IDENTIFIED by '<UNIQUEPASSWORD>';

    This command creates a new user in MySQL with the specified username, hostname, and password. You must make a note of the hostname as you will have to specify the host to create a connector when registering the External MySQL DB system.

    The '<username>'@'<hostname/IP>' can be an exact match or a wildcard match like '%'. For example:

    'johndoe'@'host.example.com' or 'johndoe'@'%'
  2. Grant the required privileges:
    GRANT
    SERVICE_CONNECTION_ADMIN,
    SYSTEM_USER,
    SELECT,
    PROCESS,
    SHOW VIEW,
    SHOW DATABASES,
    REPLICATION CLIENT,
    REPLICATION SLAVE
ON *.* TO '<username>'@'<hostname>';

GRANT EXECUTE ON sys.* TO '<username>'@'<hostname>';
 –
Ensure that you have the required permissions to create and use secrets You must ensure that you have the required Oracle Cloud Infrastructure Vault service permissions to create and use secrets that contain the network protocol credentials and details required to securely connect to the External MySQL DB system.

If creating the network protocol credentials secret directly in the Vault service, the following free-form tags must be associated with the secret to make it available for use when registering the External MySQL DB system:

  • Secret for TCP credentials: TCP_SECRET_MYSQL
  • Secret for TCP with SSL credentials: TLS_SECRET_MYSQL
  • Secret for socket credentials: SOCKET_SECRET_MYSQL
 For information on the permissions required to create a secret that contains the network protocol credentials and details, see Permissions Required to Register External MySQL DB Systems and Enable Database Management.

For information on the Vault service, its concepts, and how to create vaults, keys, and secrets, see Vault.

Register the External MySQL DB system You must register the External MySQL DB system. As part of the registration process, you:
  • Register the External MySQL DB system.
  • Create a connector resource to securely connect to the External MySQL DB system.
  • Enable Database Management for the External MySQL DB system.
For information on the permissions required to register an External MySQL DB system, see Permissions Required to Register External MySQL DB Systems and Enable Database Management.

For information on how to register an External MySQL DB system, see Register an External MySQL DB System.