In the guide we published before, we discussed how to manage MySQL databases via command line. But managing MySQL databases is not everything in the field of MySQL Database administration. It is because databases are individual databases and you cannot manage databases without creating a MySQL user with appropriate privileges to perform actions on databases.
So, Managing MySQL users and privileges are equally important in MySQL database administration. In this guide, I will show you how to manage MySQL users and privileges. I will show you how to create a MySQL User, How to get the list of MySQL users, How to create a database and grant all privileges on that database to a specific MySQL User, and how to delete a specific MySQL user. We will also learn How to create a MySQL user equivalent to a root user, the one who can create new users and grant privileges to other users.
I recommend you to practically follow this guide to learn MySQL user management. For that, you will need a VPS, Click here to get a brand new VPS from Fastycloud. Once done, Install MySQL on your VPS using the commands given below.
$ sudo apt-get install mysql-server mysql-client -y $ sudo mysql_secure_installation
After the MySQL is installed on your server, execute the following command to login to your MySQL server as a root user. A root user has all the privileges, meaning, A root user can create new users, grant privileges, create databases and much more. There are no limitations for a root user.
$ mysql -uroot -p;
It will ask you for MySQL password. Enter the correct MySQL password that you have set while installing MySQL on your server. Once you are logged in, you are ready to create first MySQL user.
MySQL Create User
Before we execute the first query in MySQL to create a MySQL user, let’s see how user management and authentication works in MySQL. The first thing to know is, MySQL authenticates a user with Username, Password and Host. It means that we have to define Username, Password and Host of a user in MySQL, not only username and password.
For example, Let’s say we want to create a user with username “test”, password “qawsedrf” and host “localhost”. To create a MySQL user with this information, we have to execute the following query in MySQL console or CLI.
mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'qawsedrf';
Here, you already know what is the username and password. But what is the significance of host in MySQL? The host is the hostname/IP Address from which a MySQL user can access the MySQL server. It means that if you want to access the MySQL server from the same computer, you have to use localhost as a host. If you want to access it from the remote machine, you can enter the Hostname or the IP address of that machine as a host.
If you want to access the MySQL server remotely from any machine on the planet, you can set the host as % . In this case, the query to create a MySQL user will look like this.
mysql> CREATE USER 'test'@'%' IDENTIFIED BY 'qswaefrd';
So, this is how you can create a user account in MySQL. But, Right now, our user does not have any kind of privileges on database(s). It means that our user cannot manage database hosted on the MySQL server we just installed. To allow our user to manage database(s), we have to grant him privileges. We will cover this part later in this guide.
MySQL Show Users
Getting a list of MySQL users is the easiest task of all in MySQL. It just takes one query to get list of all the user accounts active on our MySQL server. All the information about MySQL users is stored in the mysql.user table. Here, mysql is the database and user is the table.
To get a list of available MySQL users, we just have to execute a normal MySQL query to fetch data from the table I mentioned in previous paragraph. To get list of all the MySQL users, execute the following query.
mysql> SELECT User,Host from mysql.user;
If you are following this guide practically, You should see the following output.
+------------------+-----------+ | User | Host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | | test | localhost | +------------------+-----------+ 5 rows in set (0.00 sec)
You can also get more information about MySQL users by adding the columns you want to fetch in the SELECT query. So, This is how you get a list of MySQL users.
MySQL create database and user
This task is the most common and the most efficient. It is because we can create a database and a user with all the privileges on that database. You can do this for all the databases you have or want to have on your MySQL server.
This method is easy and secure as a specific user can only perform actions on a specific database. And you can have multiple user-database couples on your MySQL server.
In the language of MySQL, we will create a user and a database. And then we will grant all the privileges on a database to a user. So, Let’s first create a MySQL user.
mysql> CREATE USER 'testOneUser'@'localhost' IDENTIFIED BY 'qawsedrf';
Now, Let’s create a MySQL Database.
mysql> CREATE DATABASE 'testOne';
Now, Let’s grant all the privileges on testOne database to testOneUser using the following query.
mysql> GRANT ALL PRIVILEGES ON testOne.* to 'testOneUser'@'localhost';
Finally, our user testOneUser can now manage all the tables in testOne database. For instance, if you want to provide all privileges on a specific table of a specific database only, you can replace testOne.* with testOne.TABLE_NAME in the query.
So, This is how you can create a database and user in MySQL. Now, the last thing we have to learn is to Drop or Delete a user in MySQL. It is as easy as executing a drop query.
MySQL Delete/Drop user
MySQL has a different word for delete and it is called ‘drop’. So, you can drop database as well as users. And the query for drop starts with ‘DROP’. To drop a user account in MySQL, you must know the Username and Host of a user. Our user testOneUser has a host localhost. It means that, if you want to delete a user 'testOneUser'@'localhost', you have to execute the following query in your MySQL server.
mysql> DROP USER 'testOneUser'@'localhost';
So, this is how you can delete MySQL user accounts. And this is how you can manage users in your MySQL server.
Conclusion: Once you understand how the Database, Users and Privileges are managed in MySQL, it becomes easier to understand how you can effectively manage databases and users on your MySQL server. In short, Databases are individual databases that can be managed by MySQL users according to the privileges they have on a specific database or a group of databases.