How to create a read-only MySQL user?

1. First, login as a MySQL administrator from your terminal / command prompt using the following command:

mysql -u root -p

1. You’ll prompted to enter the password. Type the password for the root account.

CREATE USER 'new_user'@'%' IDENTIFIED BY 'password_secret';

The % in the command above means that user report can be used to connect from any host. You can limit the access by defining the host from where the user can connect. Omitting this information will only allow the user to connect from the same machine.

1. Grant the SELECT privilege to user.

GRANT SELECT ON database_name.* TO 'new_user'@'%';

This command gives the user read-only access to the database from the local host only. If you know the host name or IP address of the host that the collector is will be installed on, type the following command:

GRANT SELECT ON database_name.* TO 'report'@'host_name or IP_address';

1. Execute the following command to make the privilege changes saved and take effect.

FLUSH PRIVILEGES;
SHOW GRANTS FOR 'report'@'host_name or IP_address';

Type quit. Finally, show the granted privileges of user to verify