Managed Service for Prometheus uses the mysqld_exporter component to collect metrics from MySQL databases. To enable metric collection, create a dedicated MySQL user with the minimum required privileges.
Prerequisites
Before you begin, ensure that you have:
A running MySQL instance (5.x, 8.0, or later)
Access to the MySQL CLI as
rootor another account withCREATE USERandGRANTprivileges
Usage notes
$andmysql>are prompt characters. Do not enter prompt characters when you enter commands.#and--are annotation prefixes. Do not enter annotations or prefixes when you enter commands.
Step 1: Log in to MySQL
Log in as root or an administrator:
mysql -u root -p
Enter password: <your-root-password>Step 2: Create the exporter user
MySQL 8.0 and later require separateCREATE USERandGRANTstatements. If you use MySQL 5.x, skip to MySQL 5.x: create and authorize in one step.
Run the following statement to create a user named mysqld_exporter:
CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY '<password>';Replace the following placeholders with actual values:
| Placeholder | Description | Example |
|---|---|---|
localhost | Host the user connects from | localhost, 192.168.1.%, or % |
<password> | A strong password for the user | Str0ng!Pass |
To use a different username, replace mysqld_exporter with a name of your choice.
Verify user creation (optional)
SELECT User, Host FROM mysql.user WHERE User = 'mysqld_exporter';Expected output:
+-----------------+-----------+
| User | Host |
+-----------------+-----------+
| mysqld_exporter | localhost |
+-----------------+-----------+If the query returns this row, the user was created.
Step 3: Grant privileges
Grant the minimum required privileges to mysqld_exporter:
GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'localhost';| Privilege | Purpose |
|---|---|
REPLICATION CLIENT | Check the status of the source and replica databases |
PROCESS | Check the process information |
Grant performance_schema access
To collect metrics from performance_schema, grant an additional SELECT privilege:
GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost';Apply the changes
Reload the grant tables so the new privileges take effect immediately:
FLUSH PRIVILEGES;Verify privileges (optional)
SHOW GRANTS FOR 'mysqld_exporter'@'localhost';Expected output:
+---------------------------------------------------------------------------+
| Grants for mysqld_exporter@localhost |
+---------------------------------------------------------------------------+
| GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'localhost' |
| GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost' |
+---------------------------------------------------------------------------+If the output matches the table above, the user has the correct privileges.
MySQL 5.x: create and authorize in one step
On MySQL versions earlier than 8.0, you can create the user and grant privileges in a single statement. Use the following commands instead of Step 2 and Step 3:
GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'localhost' IDENTIFIED BY '<password>';
GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost';
FLUSH PRIVILEGES;FAQ
Operation CREATE USER failed for 'username'@'hostname'
Cause: A user with the same name already exists.
Solution: Drop the existing user and re-create it:
DROP USER 'username'@'hostname';Then repeat Step 2.
Access denied for user 'username'@'hostname' (using password: YES/NO)
Cause: The username or password is incorrect, or the user lacks the required privileges.
Solution:
Verify the username and password are correct.
Confirm the user exists:
SELECT User, Host FROM mysql.user WHERE User = 'username';Check granted privileges:
SHOW GRANTS FOR 'username'@'hostname';
Can't find any matching row in the user table
Cause: The specified username or hostname does not exist in the grant tables.
Solution:
Check for typos in the username and hostname.
Verify the hostname matches the host column in
mysql.user.Reload the grant tables:
FLUSH PRIVILEGES;
What's next
Use Managed Service for Prometheus to monitor MySQL databases