Create the mysqld_exporter user and authorize it to collect MySQL database monitoring data - Application Real-Time Monitoring Service

To collect monitoring data from a MySQL database to Managed Service for Prometheus, you must create a MySQL user and grant minimum permissions to the user. In this topic, a user with the ID mysqld_exporter is created.

Usage notes

$ and mysql> 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.

Procedure

Note

MySQL databases 8.0 and later do not support using the GRANT statement to create new users. Users must be separately created and authorized. In this topic, Step 2 describes how to create a user and Step 3 describes how to authorize the user.

If you are using a MySQL database earlier than 8.0, you can create and authorize a user after logging on as the root user or administrator. For more information, see Step 3: Authorize the user.

Step 1: Log on as the root user or administrator

Start and log on to the MySQL database. The root user is used as an example.

$ mysql -u root -p

# Replace "password" with the password of the root user.
Enter password: password

Step 2: Create a user

Run the following command in the command line to create a user named mysqld_exporter:
```
mysql> CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'password';
```
- localhost: the IP address of the host that can be connected. Replace it with the actual IP address.
- Replace mysqld_exporter and password with a custom username and password.

Verification (optional)

Run the following commands in the command line. If the user and host information are queried, mysqld_exporter is created.

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

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

Step 3: Authorize the user

Run the following commands in the command line to grant minimum permissions to mysqld_exporter:

-- Grant the user the permissions to access all tables and check the process information and the status of the master and slave databases. 
-- If you use the GRANT statement to create a new user, we recommend that you add [IDENTIFIED BY 'password'] at the end of the following command to set the password. 
mysql> GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'localhost';

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

-- Reload the grant tables to make the authorization take effect. 
mysql> FLUSH PRIVILEGES;

REPLICATION CLIENT: the statement that authorizes the user to check the status of the master and slave databases.
PROCESS: the statement that authorizes the user to check the process information.

Verification (optional)

Run the following commands in the command line to check whether the user has been granted the required permissions:

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

-- Results
+---------------------------------------------------------------------------+
| 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 'usernam'@'hostname'
This error indicates that user creation failed because a user with the same name already exists. To resolve this issue, delete the existing user and then create a new user. Run the following command to delete a user:
```
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 request because the username or password was invalid. To resolve this issue, check the username and password, and check whether the user has been granted the required permissions.
Can't find any matching row in the user table
This error indicates that the username or hostname does not exist in the grant tables. To resolve this issue, check for spelling errors and make sure that the hostname is valid. If you are sure the spelling is correct, use the FLUSH PRIVILEGES command to refresh the grant tables.

References

For information about how to use Managed Service for Prometheus to collect MySQL monitoring data, see Use Managed Service for Prometheus to monitor MySQL databases.