Create ReadOnly user in MySQL

Recently we got a requirement where we needed to created a RO only user for db. I know this is a common usecase. There are 1000s of blogs on this but I’m sharing the most easiest way of doing this.

Logging into MySQL Shell

mysql -u root -p #this is typical login 
mysql -h <hostname> -u <username> -p #this is when you have mysql on a remote location

this will prompty for password, please enter correct and you will be inside Mysql Shell

Create a new MySQL user account

CREATE USER 'ro'@'%' IDENTIFIED BY 'mysecretpassword';

the % in the command above means that user ro 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.

Updating setting to make this effective

FLUSH PRIVILEGES;

We are all Done !