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

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

Log on to MySQL as the root user or an administrator. The following example uses root.

$ 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 to create the mysqld_exporter account.
```
mysql> CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'password';
```
- localhost: The host allowed to connect. Replace this 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. If the query returns the user and the corresponding host, the mysqld_exporter user was created.

-- 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 commands 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: Allows the account to check the replication status of primary and secondary servers.
PROCESS: Allows the account to view process information in MySQL.

Verify the result (Optional)

Run the following command to verify the granted 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'

A user with the same name already exists. Delete the existing user first, and then create the 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 incorrect. Verify the credentials and make sure the user has the appropriate permissions.
Can't find any matching row in the user table

The username or hostname does not exist in the MySQL grant tables. Check for spelling errors and verify the hostname. If the spelling is correct, run FLUSH PRIVILEGES to refresh the grant tables.

References

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