Creating and managing users in MySQL on Amazon Linux 2 involves several steps, including installing MySQL, creating a new user, and granting that user appropriate permissions.

First, you will need to install MySQL on your Amazon Linux 2 server. This can be done using the package manager, yum. Open a terminal and enter the following command:

sudo yum install -y mariadb-server

Once the installation is complete, start the MySQL service by running the following command:

sudo systemctl start mariadb 

You can then log into MySQL as the root user using the following command:

mysql -u root -p

You will be prompted to enter the root password, which you set during the installation process.

Once logged in, you can create a new user by running the following command:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

This will create a new user named “newuser” with the password “password”. You can replace these values with your desired username and password.

Next, you will need to grant appropriate permissions to the new user. This can be done using the GRANT command. For example, to grant the new user full access to a specific database, you can run the following command:

GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

This will give the new user full access to the database named “database_name”.

Once the user is created and permissions are granted, you can use the following command to flush the privileges:

flush privileges;

To manage the users in MySQL you can use the following commands.

SHOW GRANTS FOR 'user'@'host'; 

This command will show the grants for a specific user.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';

This command will revoke all privileges and grant options from a specific user.

DROP USER 'user'@'host';

This command will delete a user from MySQL.

Conclusion

In summary, creating and managing users in MySQL on Amazon Linux 2 involves installing MySQL, creating a new user, granting appropriate permissions, and using commands to manage the users. Remember to always test your changes in a non-production environment before applying them in production.