How To Create a New User and Grant Permissions in MySQL on CentOS 7

Introduction

MySQL is an open-source relational database management system. It is the most popular open-source database in the world.It is commonly deployed as part of the LAMP stack (which stands for Linux, Apache, MySQL, and PHP) .This article outlines how to create a new MySQL user and grant them the permissions needed to perform a variety of actions.

Prerequisites

In order to follow along with this article, you’ll need access to a MySQL database. This article assumes that this database is installed on a AWS instance running CentOS 7, though the principles it outlines should be applicable regardless of how you access your database.

If you don’t have access to a MySQL database and would like to set one up yourself, you can follow one of our article on How To Install MySQL. Again, regardless of your server’s underlying operating system, the methods for creating a new MySQL user and granting them permissions will generally be the same.

Creating a New User

Upon installation, MySQL creates a root user account which you can use to manage your database. This user has full privileges over the MySQL server. The root has complete control over every database, table, user, and so on.

In my CetnOS systems running MySQL 5.7, the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. To access mysql with root user and password you need execute bellow commend.

$ mysql -u root -p

Once you have access to the MySQL prompt, you can create a new user with a CREATE USER statement. Follow this general syntax bellow:

CREATE USER 'username' IDENTIFIED BY 'password';

Replace username and password with a username and password of your choice.

Alternatively, you can set up a user by specifying the machine hosting the database.

• If you are working on the machine with MySQL, use username@localhost to define the user.

• If you are connecting remotely, use username@ip_address, and replace ip_address with the actual address of the remote system hosting MySQL.