Create a mysqld_exporter account for Prometheus and grant permissions to collect MySQL monitoring data-Managed Service for Prometheus(Prometheus)-阿里云帮助中心

To collect monitoring data from a MySQL database using Prometheus, you must create a MySQL access account for Prometheus and grant it the minimum required read-only permissions. This topic uses mysqld_exporter as an example to describe the process.

Example notes

$ and mysql> are command prompts. Do not include the prompts when you run commands.
# and -- are comment prefixes. Do not include the prefixes or the comment text when you run commands.

Procedure

Note

MySQL 8.0 and later versions do not allow you to use the GRANT statement to create new users. You must create the user and grant permissions in separate steps. Therefore, this example is divided into two steps: creation and authorization.

If you use a version of MySQL earlier than 8.0, you can create an account and grant permissions to it in a single step. For more information, see the operations and comments in Step 3: Grant permissions.

Step 1: Log on as the root user or an administrator

Start and log on to MySQL. This example uses the root user.

$ mysql -u root -p

# After the "Enter password" prompt, enter the password for the root user.
Enter password: password

Step 2: Create an account

Run the following command on the MySQL command line to create the mysqld_exporter account.
```
mysql> CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'password';
```
- localhost: The IP address of the host that is allowed to connect. Replace this value with the actual IP address.
- mysqld_exporter and password are the custom username and password. Replace them as needed.

Optional: Validation

Run the following command on the command line. If the query returns the user and the corresponding host, the mysqld_exporter user was created successfully.

-- Query user information from the mysql.user table.
mysql> SELECT User,Host FROM mysql.user WHERE User = 'mysqld_exporter';

-- Query result
+-----------------+-----------+
| User            | Host      |
+-----------------+-----------+
| mysqld_exporter | localhost |
+-----------------+-----------+

Step 3: Grant permissions

Run the following grant commands on the command line to grant the minimum required permissions to the mysqld_exporter account.

-- Grant permissions to check the status of primary and secondary servers and view processes for all tables.
-- If you use the GRANT statement to directly create a new user, add [IDENTIFIED BY 'password'] at the end to set the user password.
mysql> GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'localhost';

-- Grant the read permission on the performance_schema.* table.
mysql> GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost';

-- Reload the grant tables to apply the new permissions.
mysql> FLUSH PRIVILEGES;

REPLICATION CLIENT: This permission allows the account to check the status of primary and secondary servers.
PROCESS: This permission allows the account to view process information in MySQL.

Verify the result (Optional)

Run the following command on the command line to check whether the user has been granted the required permissions.

-- View the permissions held by the user.
mysql> SHOW GRANTS FOR 'mysqld_exporter'@'localhost';

-- Query result
+---------------------------------------------------------------------------+
| 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'
This error indicates that the user creation failed because a user with the same name already exists. To resolve this issue, delete the existing user, and then create the new user. The command to delete a user is:
```
mysql> DROP USER 'username'@'hostname';
```
Access denied for user 'username'@'hostname' (using password: YES/NO)
This error indicates that the MySQL server rejected the connection attempt because the provided username or password was incorrect. To resolve this issue, verify that the username and password are correct, and make sure the user has the appropriate permissions.
Can't find any matching row in the user table
This error indicates that the username or hostname does not exist in the MySQL grant tables. To resolve this issue, check for spelling errors and make sure that you entered the hostname correctly. If the spelling is correct, run the FLUSH PRIVILEGES command to refresh the grant tables.

References

For more information, see How to use Prometheus to monitor MySQL.