Using MySQL Command Line to Create a User

Previous Series:
MySQL via Command Line 101: Basic Database Interaction

The purpose of using MySQL is to control data in a client-friendly environment, such as to add, delete, change, and query data. After an administrator creates a MySQL user from the command line on Linux, the next step is to grant permissions to that user. The goal is to ensure that the user is able to log in and access the MySQL server to perform tasks. Learn more about how to create a MySQL user from command line and alter permissions.

Prerequisites

• Server running CentOS or AlmaLinux.
• Root level access and log in as the root user.
• Have access to a terminal.
• Basic knowledge of the command line.

How to Create a MySQL User on Linux from Command Line

Once MySQL has been installed, a root user is created. The root user has access to the whole database without any restrictions. That means all data and all tables are up for searching and editing. If you have a temporary partner or a developer, giving them these root privileges is not something that is recommended, as they will have complete access to your data. 

That is why you can create a MySQL user whose privileges are manageable, and access is restricted. That way, once the partnership is concluded, or you don’t need a developer's services any longer, you can simply delete their user or change their password. This means they no longer have access to the database, and you keep your data safe.

Managing the permissions of the user will be explained in detail a bit later in the article.

Step 1: Access the MySQL Server

The first step is to connect to the MySQL server as the root user using the terminal and the following command.

mysql -u root -p

After which you will be prompted to enter a password for the root user. If you don’t know the password for the user, you can change it. This will work for any database user, not just the root.

The -u flag marks the user as which we are trying to get into the MySQL server. 

The -p flag initiates the password authentication method.

Some Linux users with MySQL higher than 5.7 use an auth_socket plugin for authentication. This plugin requires that the user on Linux has the same name as MySQL, meaning that the command would look like this.

sudo mysql

You will know that you have gained access to the MySQL server if you see the below in the terminal.

mysql>

If not, or if it displays an error message, it means that your credentials aren't correct and need to be double-checked or reset.

Step 2: Create the MySQL User

MySQL users can be created in such a way that they can connect to the MySQL server from a certain host where the database is, either from a certain IP address or from any machine, in the following way.

By localhost:

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test123test!';

By IP address:

mysql> CREATE USER 'testuser'@'ip_address' IDENTIFIED BY 'test123test!';

From any machine:

mysql> CREATE USER 'testuser'@'%' IDENTIFIED BY 'test123test!';

The username in these cases would be testuser, and the password would be test123test!.

For your use, please change them accordingly.

These commands will create a user for you; however, you still need to grant them additional permissions for accessing and managing the database.

Step 3: Confirm the Created User

There are multiple ways to confirm that a user has been created, along with some other important information about them.

Using the below command, you will get a list of all the users on the MySQL database server. However, this command may give you duplicate users since MySQL filters access to a server by using the IP address it comes from.

mysql> SELECT user FROM mysql.user;

Using the following command, you will get a list of database users on the MySQL database server, along with the IP or host they are allowed to access.

mysql> SELECT user,host FROM mysql.user;

This next command will allow you to see the users on the MySQL database server without duplicates.

mysql> SELECT DISTINCT user FROM mysql.user;

The below command will allow you to check the expiration and the locking state of the user.

mysql> SELECT user, account_locked, password_expired FROM mysql.user;

Granting a User Permissions

After the user is created, make sure that the permissions are granted before logging in. Those permissions include:

• Create: User can create databases.
• Select: User can search the database for needed information.
• Update: User can update table rows.
• Insert: User can insert data into tables. 
• Delete: User can delete rows and columns.
• Drop: User can delete whole databases.
• Grant Option: User is able to manage privileges for other users.
• All privileges: User has full access to the database.

The basic command for granting permissions would be the following.

mysql> GRANT permission_type ON database.table TO 'testuser'@'localhost';

If you wish for a user to be limited to a single database, this is the command you would use.

mysql> GRANT INSERT *database_name.table_name* TO 'testuser'@'localhost';

To create a user with all privileges to all databases, use this command.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';

To create a user with all privileges to a single database, use this command.

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'testuser'@'localhost';

Checking, Revoking, and Deleting Users and Their Privileges

To check all the privileges a user has, you can use this command.

mysql> SHOW GRANTS FOR username(testuser in our case);

To revoke privileges to users, use this command.

mysql> REVOKE permission_type ON database.table TO 'testuser'@'localhost';

To delete users from the MySQL database server, use this command.

 mysql> DROP USER 'testuser'@'localhost';

Wrapping up

You now know more about how to create a MySQL user from command line and alter permissions. MySQL is an excellent database management system capable of many database functions. Knowing how to create a MySQL user on Linux from the command line helps administrators keep track of and update database users. Existing Liquid Web customers with managed hosting services enjoy 24/7/365 support for these and other functions.

Liquid Web’s Linux-based VPS Hosting, Cloud Dedicated Servers, and Dedicated Servers come with MySQL and are updatable to MariaDB for those that prefer it. Contact our sales team today for more information.