All Products
Search

This Product

    Managed Service for Prometheus:Create a MySQL user for Managed Service for Prometheus

    Document Center

    Managed Service for Prometheus:Create a MySQL user for Managed Service for Prometheus

    Last Updated:Mar 11, 2026

    Managed Service for Prometheus uses mysqld_exporter to collect monitoring metrics from MySQL databases. This topic describes how to create a dedicated MySQL user with the minimum permissions required by mysqld_exporter.

    Prerequisites

    Before you begin, make sure that you have:

    • A running MySQL database

    • Root or administrator access to the MySQL database

    Quick reference

    For experienced users, run the following SQL statements after you log in as root:

    MySQL 8.0 and later

    CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY '<your-password>';
GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'localhost';
GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost';
FLUSH PRIVILEGES;

    Earlier than MySQL 8.0

    GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'localhost' IDENTIFIED BY '<your-password>';
GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost';
FLUSH PRIVILEGES;

    Replace the following placeholders:

    PlaceholderDescriptionExample
    <your-password>A strong password for the monitoring userEx@mpl3Pwd!
    localhostThe host from which the user connects192.168.1.100 or % (any host)

    For a detailed walkthrough, see the following sections.

    Usage notes

    • $ and mysql> are command-line prompt characters. Do not type them when you run commands.

    • # and -- are comment prefixes. Do not type comments when you run commands.

    Step 1: Log in as root

    Log in to the MySQL client as the root user or another user with administrative privileges:

    $ mysql -u root -p
Enter password: <root-password>

    Step 2: Create the user

    Note

    MySQL 8.0 and later require separate CREATE USER and GRANT statements. If you use a MySQL database earlier than 8.0, skip to Step 3 to create the user and grant permissions in a single statement.

    Run the following statement to create a user named mysqld_exporter:

    mysql> CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY '<your-password>';

    • Replace mysqld_exporter with a custom username if needed.

    • Replace <your-password> with a strong password.

    • Replace localhost with the actual IP address of the host from which the user connects.

    Verify user creation (optional)

    Query the mysql.user table to confirm the user exists:

    mysql> SELECT User, Host FROM mysql.user WHERE User = 'mysqld_exporter';

+-----------------+-----------+
| User            | Host      |
+-----------------+-----------+
| mysqld_exporter | localhost |
+-----------------+-----------+

    Step 3: Grant permissions

    Grant the minimum required permissions to mysqld_exporter:

    -- Allow checking replication status and viewing process information
mysql> GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'localhost';

-- Allow reading performance metrics
mysql> GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost';

-- Reload the grant tables to apply changes
mysql> FLUSH PRIVILEGES;

    For MySQL earlier than 8.0, you can create the user and grant permissions in a single GRANT statement. Append IDENTIFIED BY '<your-password>' to set the password:

    mysql> GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'localhost' IDENTIFIED BY '<your-password>';
mysql> GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost';
mysql> FLUSH PRIVILEGES;

    Each permission serves the following purpose:

    PermissionScopePurpose
    REPLICATION CLIENT*.*Check the replication status of primary and replica databases
    PROCESS*.*View running processes and queries
    SELECTperformance_schema.*Read performance metrics such as query execution statistics

    Verify permissions (optional)

    Verify the permissions granted to the user:

    mysql> SHOW GRANTS FOR 'mysqld_exporter'@'localhost';

+---------------------------------------------------------------------------+
| Grants for mysqld_exporter@localhost                                      |
+---------------------------------------------------------------------------+
| GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'localhost' |
| GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost'     |
+---------------------------------------------------------------------------+

    FAQ

    Operation CREATE USER failed for 'username'@'hostname'

    A user with the same name already exists. Drop the existing user and create a new one:

    mysql> DROP USER 'username'@'hostname';

    Access denied for user 'username'@'hostname' (using password: YES/NO)

    The MySQL server rejected the connection because the username or password is invalid. Check the username and password, and verify that the user has been granted the required permissions.

    Can't find any matching row in the user table

    The specified username or hostname does not exist in the grant tables. Check for typos in the username and hostname. If the values are correct, reload the grant tables:

    mysql> FLUSH PRIVILEGES;

    What's next