You must configure the following MySQL variables and tables to perform tasks such as collecting performance metrics of the External MySQL DB system and monitoring and analyzing 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.
• performance_schema_max_sql_text_length: This variable is recommended as it determines the maximum number of bytes used to store SQL statements, including the QUERY_SAMPLE_TEXT column. If left at the default value, samples of longer SQL statements will be incomplete and EXPLAIN will not be available for them. The value set for this variable must match the value set for the max_digest_length variable.
• performance-schema-consumer-events-statements-history: It's recommended that this table be set to ON, as it enables the collection of information about SQL statement samples and tracking recent statement history per thread.
• performance-schema-consumer-events-statements-history-long: It's recommended that this table be set to ON, as it enables the tracking of a large number of recent and currently running SQL statements across all threads. By storing a longer history of executed SQL statements, this table increases the chances of capturing errors that may occur intermittently or over extended periods.
• performance_schema_events_statements_history_long_size: This variable is recommended as it controls the maximum number of SQL statements retained in the events_statements_history_long table. By default, the table holds up to 10,000 statements. Increasing this variable allows for capturing a larger set of recent queries across all threads, which is particularly useful on systems with a high statement volume.

For information on the other required variables and tables, see:

• max_digest_length
• performance_schema_max_digest_length
• performance_schema_max_sql_text_length
• The events_statements_history Table
• The events_statements_history_long Table
• performance_schema_events_statements_history_long_size

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

For information on Management Gateway, see Management Gateway.

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

   Optionally, to view the Explain plan in Performance Hub for Data Manipulation Language (DML) statements such as INSERT, UPDATE, DELETE, and REPLACE, you require additional privileges, which are the same as those required to execute the explained statements. Here are examples of the privileges required to execute (and explain) INSERT and UPDATE statements:

   GRANT INSERT ON <database_name>.<table_name> TO '<username>'@'<hostname>';
   GRANT UPDATE ON <database_name>.<table_name> TO '<username>'@'<hostname>';

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 Vault service, its concepts, and how to create vaults, keys, and secrets, see Vault.

• 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 how to register an External MySQL DB system, see Register an External MySQL DB System.