If you work in the tech world, you must be well acquainted with  MySQL. Before we get to understand how to create users in MySQL, we will go through a basic introduction to MySQL. MySQL is popularly known as an open-source relational DBMS. It is easily one of the most famous open-source database systems across the globe.

Moreover, MySQL is an extremely powerful and flexible program. It is a part of the LAMP (Linux-based operating system, the Apache web server, MySQL database, and PHP for processing) technology stack. You can use MySQL for various purposes, including storing or retrieving the data in several websites, applications, or services.

Now, when we talk about database management, it is all about retrieving data from a specific database. There are several ways to retrieve information. One of the most common ways out of a ton of them is executing queries in the command line!

But what are queries? Let us understand! A query is simply a command used to retrieve data from a table. In SQL, there are tons of queries used to perform various functions. Most of the queries initialise with the select option.

Here is a prototype for you to understand the basic syntax of SQL queries. But first, here are some prerequisites to fulfil the demand. 

As the basic foundation of SQL is a database, let’s learn to create one first!

How to create a simple database?

We will start with the queries, but first, it is important to understand how to create a database with some tables. We will fill these tables with some data and then execute queries. 

For more information on managing your MySQL installation, including verifying your current MySQL version, check out our guide on how to check MySQL version.

Now, let us create a database for a company that has n number of employees. 

To create a database MySQL command, Open the MySQL prompt, and the first query is as follows:

sudo mysql

The next step is to create a database, use the following command:

CREATE DATABASE ‘company’;

To select this database, type this command:

USE company;

The next step is creating tables in this database and several columns. Let’s do that!

CREATE TABLE employee (

name varchar(30),

age real,

address real,

department real

);

Now, when you execute this command, the output will be displayed on your screen as given below:

Output:

Query OK, 0 rows affected (0.00 sec)

The next step is to insert the values for each column in the employee table. Take a look at the sample data:

INSERT INTO employee(name,age,address,department)

VALUES ('Ana’, '27’, '24 abc street, 'seo’),

('Joe’, '24’, '2 bakers street, 'testing’),

('Phoebe’, '39’, '26 park avenue, 'marketing’),

('Rob’, '21’, '108 tryst avenue, 'sales’),

('Charles’, '45’, 'royal street, 56, 'seo’);

After you fill in the information, the output will be as shown below:

Output:

Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

Simple, right?

Now, let us hop onto our core topic and understand the procedure to create a user in MySQL. 

How to create a user in MySQL [MySQL create new user]?

Head towards your terminal window. The next step is to launch your MySQL shell as the root user. 

Use the following command:

mysql -u root -p

The next step is to type the root password for your account. Tap enter to proceed. 

Moving on, use the below-stated command in MySQL to create users with passwords:

CREATE USER ‘username’ IDENTIFIED BY ‘password’;

Set the username and password as per your wish.

Additionally, there is an alternative method for you to achieve the same task. Take a look. 

To define the user, you can use username@localhost on your machine with MySQL.

If you wish to establish a remote connection, you can use username@ip_address. And, at the place of ip_address, use the original address of your remote system that hosts MySQL.

Now to execute it, your command would look like this:

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

Simple, right?

Well, moving on to the next segment!

Whenever you log in with a new account, it is necessary to set the permissions for the user. Let us understand the procedure to conclude the same.

How to grant permissions [grant privileges MySQL]?

Permissions are nothing but the actions you, as a user, can take to perform several actions in the database. Take a look at the permissions given below:

  • All privileges: user has full access to the database
  • Insert: user can insert rows in the table
  • Create: user can create new tables as well as databases
  • Delete: the user is able to delete any row in the table
  • Select: user can read the data of the databases
  • Update: user can easily update rows of tables
  • Grant: user can modify the account privileges of another user

When we talk about the grant option, here is the syntax to grant privileges to another account:

GRANT permission_type ON database.table TO 'username'@'localhost';

Now, there comes a situation where you wish to take the privileges of the user away. To do that, here are some commands.

How can you list MySQL user account privileges?

If you wish to know about the current privileges of the user, you have to follow this command as stated below:

SHOW GRANTS username;

Now, to grant all the privileges to a database using this command:

GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';

Moreover, if you want to grant all the privileges on a particular database to a user account, here is the command for you:

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

To narrow it down a little further, what if you want to grant all the privileges to a user account in a particular table of a database? Well, type this command:

GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';

Lastly, if you wish to remove a user account completely, you can use the below-mentioned command:

DROP USER 'username'@'localhost';

Conclusion  

MySQL is a vast area that has immense features and functions. We showed you how to create a user in MySQL along with the help to execute some additional basic commands. Execute them to create tables, databases, and users. Execute them to create tables, databases, and users. 

For a comprehensive understanding of MySQL, check out our guide on how to install MySQL on Ubuntu to ensure you have the right setup before creating users and databases.

With this, you have successfully learnt the basics of creating a MySQL user along with other important commands. We hope that the information in this article serves you in the best way possible.

For a deeper understanding of the differences between MySQL and MariaDB, check out our article on MySQL vs MariaDB to make an informed choice for your database needs.

People also read: