How to Create, Delete Database and Create User MySQL Database Using the Command Line

Hi everyone, on this occasion I will write my own little note so that in the future I don't forget, and in this article I will write about some commands or syntax in mysql.

This article is simple but really helps me in doing my daily work.

In this case I am using Linux OS RHEL 7 and MySQL Commercial 8.0.13.

Login

For the first command you should know is "Login". To login, you can write the following command:

# mysql -u root -p

After that you enter your password.

Login to MySQL Server

Create Database

Now the next command that is no less important is "Creating a Database":

mysql> create database Bangkit;

Next you can see what databases are on this MySQL server:

mysql> show databases;

Delete Database

Or maybe you don't need the database, you can delete it by:

mysql> drop database Bangkit;

Create & Delete Database

Now, after you create a database like this, of course, you don't necessarily give root access to other people to be able to access the database.

For later in this article I will continue how to create a user, grant access to that user and others.

Create User

To create a user, do the following:

mysql> create user 'kitsake'@'%' identified by '888Bangk!t';

Then you can see if the user actually exists:

mysql> SELECT user,host FROM mysql.user;

Rename User

To rename a user, do the following:

mysql> rename user 'kitsake'@'%' to 'kitsake'@'192.168.1.%';

Then you can see if the user actually exists:

mysql> SELECT user,host FROM mysql.user;

Change Password

To change password a user, do the following:

mysql> alter user 'kitsake'@'%' identified by '87490Bangk!t';

Give Access

Next, all you have to do is give access to the “kitsake” user to be able to access the “Bangkit” database.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON 'Bangkit'.* TO 'kitsake'@'%';

And you can see if the user already has access by:

mysql> show grants for 'kitsake'@'%'; 

Create & Give Acces User Database

Remove Access

to remove the privileges that have been given you can use a syntax like this

mysql> REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON 'Bangkit'.* FROM 'kitsake'@'%';

Maybe that's all I can write in this article, besides being a reminder for me, I hope this article is also useful and useful for those of you who have stopped by my blog.

Thank you.