Using MySQL Command Line to Create a User

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

In this article, we will be discussing how to use MySQL to create a new user on Linux via the command line. We will be working on a Liquid Web core-managed server running CentOS version 6.5 as the root user. The commands used should also work on later versions of MySQL on CentOS as well.

MySQL is a relational database management application primarily used on Linux and is a component of the LAMP stack (Linux, Apache, MySQL, and PHP).

Preflight Check

• Log in as the root user.
• Have access to a terminal.
• Basic knowledge of the command line.

Step 1. Log in to MySQL

Initially, we will log in to the server as the root user, and then open a terminal to access the MySQL server from the command line using the following command:

mysql -u root -p

In this case, we've specified the user as being root using the -u flag, and then utilized the -p flag so the MySQL log in prompts us for a password. Next, we can enter our current password to complete the login.

Note: If we need to change our root password (or any other users' password) in the database, please review this tutorial on changing a password for MySQL via the command line.

You should now be at a MySQL prompt that looks very similar to this.

mysql> 

Step 2. Create the MySQL User

Now, we will create a user with the name testuser, and the password test123test! using the following command.

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

That's it! We have created our first MySQL user using a single command. This user will not be able to work with any of the MySQL databases until they are granted additional privileges. In fact, they will not be able to login without granting them additional permissions. To give the new user the proper permissions, please see our next tutorial on granting permissions to a MySQL user via the command line.

View a List of MySQL Users

To view a full list of MySQL users, including the host they're associated with, can be accomplished using the following select statement.

SELECT User,Host FROM mysql.user;

Conclusion

MySQL is an excellent open-source relational database management system. In 2010, MySQL was forked into MariaDB after its purchase by Oracle. MariaDb is a community driven and developed, relational database management system (RDBMS). It continues to be a free and open-source software licensed under the GNU General Public License.

Talk to an Expert Now

Our Support Team are experienced in maintaining and managing this type of software. Our Linux System administrators and technicians have intimate knowledge of multiple web hosting technologies, especially those discussed in this article. If you have questions, we are available to assist 24 hours a day, 7 days a week 365 days a year.

If you are a Fully Managed VPS server, Cloud Dedicated, VMWare Private Cloud, Private Parent server or a Dedicated server owner and you are uncomfortable with performing any of the steps outlined, we can be reached via phone @800.580.4985, a chat or support ticket to assist you with this process.