VPS

How to Show Users in MySQL using Linux

MySQL is one of the most popular database managers in the world. It has many features that make it reliable, robust and efficient. However, like any program, its maximum reliability is achieved if we use it correctly.

An easy and reliable way to increase MySQL security is to create users with limited permissions on the database. In this tutorial, you’ll learn how to show users in MySQL to manage MySQL databases more efficiently in a Linux terminal environment on your VPS.

Why Create Users in MySQL?

When installing MySQL the first user to be created is the root user – the MySQL administrator. The root user has permission to do everything on the MySQL database, so it is not convenient for other people to access your databases using this account.

On the other hand, hackers always try to log in as a root user to steal the hosted information. Or worse, to destroy the service and the data with it.

With this in mind, a sysadmin has to create users with specific permissions on some databases and in turn on some tables. With this, if the security of that user is compromised the impact is minimal or manageable.

How to Show Users in MySQL on Linux

Here you’ll learn the easiest method out there:

  1. Login Using SSH

First, you need to access our server using SSH. We have a tutorial detailing the process!

ssh your-user@your-server
  1. Make Sure You Have Root Privileges

Once we have access to our server, we have to enter the MySQL console. To do it, we need root privileges. Enter this into the command line:

sudo mysql -u root -p
  1. Type in Your MySQL Root Password

Then, we will have to type in our MySQL root password. It should be different from the system root password.

Once we are in the MySQL console as the root user, we can run sentences and commands.

  1. Show MySQL Users

Now we can list all users created in MySQL through the following MySQL command:

mysql> SELECT user FROM mysql.user;

As a result, we will be able to see all the users that have been created in MySQL.

  1. Add a Host Column (Optional)

There might be duplicate users. This is because MySQL filters access to a server according to the IP address it comes from. So you can also add a host column.

Mysql> SELECT user,host FROM mysql.user;

With this, we will be able to see the MySQL users and from which host or IP address, they have permission to access.

Conclusion

The administration of a database server is not always an easy task. So we must be careful with the creation and administration of user permissions. Now you know how to show MySQL users tied to a database.

You can consult MySQL’s official documentation regarding permissions and user creation to broaden your knowledge!

 

About the author

Edward S.

Edward is Hostinger's copywriter. He's an expert communicator with years of experience in IT as a writer, marketer, and Linux enthusiast. IT is a core pillar of his life, personal and professional. Edward's goal is to encourage millions to achieve an impactful online presence. He also really loves dogs, guitars, and everything related to space.

Add Comment

Click here to post a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get Exclusive Content

Join thousands of webmasters who get our free newsletter with tips on how to drive more traffic and revenue to their websites!

Please wait...

Thank you for sign up!

More in VPS
How to Use the Linux Shutdown Command in Ubuntu 18.04 and CentOS 7
Close