Home » MariaDB: Create Database, User and Grant Privileges From Command Line

MariaDB: Create Database, User and Grant Privileges From Command Line

by Pring
MariaDB: Create Database, User and Grant Privileges From Command Line

I. Introduction.

This tutorial describes how to do common Maria database administration tasks from the command line. We will show you simple tasks like: Create Database, User and Grant Privileges in MariaDB

Read other related posts:

II. Prerequisites.

1. Login to your system via SSH. You can use PuTTy tool or Poderosa tool,…
2. Login with root user.  If you are logged in as another user, you will need to add sudo before each command.

III. Create Database, User and Grant Privileges in MariaDB From Command Line.

The article structure is as follows:

Step 1. Login to MariaDB.
Step 2. Create a Maria Database.
Step 3. Create a User in MariaDB.
Step 4. Grant permissions to access and use the MariaDB.
Step 5. Grant all privileges to a user on a specific database.
Step 6. Apply changes made and get a list of specific user privileges.
Step 7. Create a table in MariaDB.

You can see details of Video on How to Create Database, Table, User in Maria From Command Line From Command Line as below.

[Sorry, the video we will update later]

Or you also can see details as below.

Step 1. Login to MariaDB.

First enter the command below:

mysql -u root -p

Then enter your MariaDB password.

Sample Output:

[root@devopsio ~]# mysql -u root -p
Enter password: <== Enter your password at here
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8319
Server version: 10.1.41-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

Step 2. Create a Maria Database.

Enter the command pattern below:

CREATE DATABASE `devops_test`;

Sample Output:

MariaDB [(none)]> CREATE DATABASE `devops_test`;
Query OK, 1 row affected (0.08 sec)

To see / get a list of MySQL/MariaDB databases. Use the command below.

SHOW DATABASES;

Sample Output:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| devops_test        | <== This is the database you created
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Step 3. Create a User in MariaDB.

Enter the command pattern below:

CREATE USER ‘your_user_name’ IDENTIFIED BY ‘your_password’;

Sample Output:

MariaDB [(none)]> CREATE USER 'devops_user'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.46 sec)

To see / get a list of MySQL/MariaDB users accounts. Use the command below.

SELECT User FROM mysql.user;

Sample Output:

MariaDB [(none)]> SELECT User FROM mysql.user;
+-------------+
| User        |
+-------------+
| devops_user | <== This is the user you created
| root        |
| root        |
| root        |
+-------------+
4 rows in set (0.03 sec)

Step 4. Grant permissions to access and use the MariaDB.

Option 1: Only allow access from location. I think this option is best for you, it’s secure for a web application.
Enter the command pattern below:

GRANT USAGE ON *.* TO ‘your_username’@localhost IDENTIFIED BY ‘your_password’;

Sample Output:

MariaDB [(none)]> GRANT USAGE ON *.* TO 'devops_user'@localhost IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.06 sec)

Option 2: But in the case, you want allow access to MariaDB server from any other computer on the network.
Enter the command pattern below:

GRANT USAGE ON *.* TO ‘your_username’@’%’ IDENTIFIED BY ‘your_password’;

Sample Output:

MariaDB [(none)]> GRANT USAGE ON *.* TO 'devops_user'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

Step 5. Grant all privileges to a user on a specific database.

Enter the command pattern below:

GRANT ALL privileges ON `your_database_name`.* TO ‘your_username’@localhost;

Sample Output:

MariaDB [(none)]> GRANT ALL privileges ON `devops_test`.* TO 'devops_user'@localhost;
Query OK, 0 rows affected (0.04 sec)

Same as above, If you want the user to work with the database from any location.
Enter the command pattern below:

GRANT ALL privileges ON `your_database_name`.* TO ‘your_username’@’%’;

Sample Output:

MariaDB [(none)]> GRANT ALL privileges ON `devops_test`.* TO 'devops_user'@'%';
Query OK, 0 rows affected (0.00 sec)

Step 6. Apply changes made and get a list of specific user privileges.

1. Finally, to apply the newly assigned permissions, you must use the following command.

FLUSH PRIVILEGES;

Sample Output:

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

To see / get a list of specific user privileges. Use the command pattern below.

SHOW GRANTS FOR ‘myuser’@localhost;

Step 7. Create a table in MariaDB.

First, you select database use to create table with the command pattern below.

USE your_database;

Sample Output:

MariaDB [(none)]> USE devops_test;
Database changed

Then create a table, use the command pattern below.

CREATE TABLE table_name (column_name column_type);

The SQL script below.

CREATE TABLE devops_table (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    create_dated DATE,
    PRIMARY KEY (id)
);

Sample Output:

MariaDB [devops_test]> CREATE TABLE devops_table (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(100) NOT NULL,
    ->     create_dated DATE,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.19 sec)

To see / get list of table in a database. Use the command below.

SHOW TABLES;

MariaDB [devops_test]> SHOW TABLES;
+-----------------------+
| Tables_in_devops_test |
+-----------------------+
| devops_table          |
+-----------------------+
1 row in set (0.00 sec)

IV. Conclusion.

In this tutorial, we was Create Database, User and Grant Privileges in MariaDB From Command Line. If you hit a problem or have feedback, leave a comment below.
You can now visit the official MariaDB Documentation page and learn how to get started with MariaDB.


Create Database, Table, User in Maria From Command Line is complete. Wish you success and luck.
Thanks and best regards!
— Pring —

Related Articles

48 comments

vurtil opmer December 11, 2019 - 10:44 am

Wow! Thank you! I constantly needed to write on my website something like that. Can I include a fragment of your post to my blog?

Reply
alcohol rehab nea rme February 27, 2020 - 10:34 pm

Heya! I’m at work surfing around your blog from my new iphone!
Just wanted to say I love reading your blog and look forward to all your posts!
Carry on the fantastic work!

Reply
Justin April 2, 2020 - 10:55 am

Hey there 🙂

Your wordpress site is very sleek – hope you don’t mind me
asking what theme you’re using? (and don’t mind if I steal it?
:P)

I just launched my site –also built in wordpress like yours– but the theme slows
(!) the site down quite a bit.

In case you have a minute, you can find it
by searching for “royal cbd” on Google (would appreciate any
feedback) – it’s still in the works.

Keep up the good work– and hope you all take care of yourself during the
coronavirus scare!

Reply
Skin Tag Removal May 4, 2020 - 7:42 am

Very good post. I’m going through some of these issues as well..

Reply
바카라사이트 November 3, 2020 - 5:17 am

I’m gone to convey my little brother, that he should also pay a visit this webpage on regular
basis to take updated from newest news update.

Reply
예스카지노 November 3, 2020 - 9:21 am

It’s awesome in favor of me to have a web site, which
is beneficial in favor of my experience. thanks admin

Reply
카지노사이트 November 3, 2020 - 9:27 am

It’s amazing designed for me to have a site, which is useful for my experience.
thanks admin

Reply
바카라사이트 November 3, 2020 - 9:44 am

you’re in reality a just right webmaster. The web site loading speed is amazing.
It kind of feels that you are doing any distinctive trick.

Also, The contents are masterwork. you have performed a fantastic job in this
subject!

Reply
카지노사이트 November 3, 2020 - 9:53 am

No matter if some one searches for his essential thing, therefore he/she desires to be available that in detail, therefore that
thing is maintained over here.

Reply
바카라사이트 November 3, 2020 - 10:31 am

Hello there! This blog post couldn’t be written any better!
Reading through this article reminds me of my previous roommate!
He constantly kept preaching about this. I’ll forward this information to him.
Pretty sure he’s going to have a good read. Many thanks for sharing!

Reply
더킹카지노 November 3, 2020 - 11:04 am

Hello! I know this is kinda off topic nevertheless I’d figured I’d
ask. Would you be interested in exchanging links or maybe guest authoring a blog article or
vice-versa? My blog discusses a lot of the same subjects as yours and I believe we could greatly benefit from each other.
If you are interested feel free to send me an email.
I look forward to hearing from you! Wonderful blog by the
way!

Reply
우리카지노 November 3, 2020 - 11:38 am

I’m not sure why but this blog is loading very slow for me.
Is anyone else having this issue or is it a issue on my end?
I’ll check back later on and see if the problem still exists.

Reply
더킹카지노 November 3, 2020 - 3:01 pm

Woah! I’m really digging the template/theme of this site.

It’s simple, yet effective. A lot of times it’s tough
to get that “perfect balance” between user friendliness and visual appeal.

I must say you’ve done a amazing job with this. In addition,
the blog loads extremely fast for me on Opera. Superb Blog!

Reply
바카라사이트 November 3, 2020 - 3:43 pm

This is really interesting, You are a very skilled blogger.
I’ve joined your rss feed and look forward to seeking more of your magnificent post.
Also, I’ve shared your web site in my social networks!

Reply
우리카지노 November 3, 2020 - 4:00 pm

Good day! I know this is kinda off topic but I was wondering if
you knew where I could find a captcha plugin for my comment form?

I’m using the same blog platform as yours and I’m having
difficulty finding one? Thanks a lot!

Reply
예스카지노 November 3, 2020 - 4:11 pm

This is a really good tip particularly to those fresh
to the blogosphere. Brief but very precise info… Appreciate your
sharing this one. A must read post!

Reply
카지노사이트 November 3, 2020 - 4:28 pm

When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get four
e-mails with the same comment. Is there any way you can remove
people from that service? Cheers!

Reply
카지노사이트 November 3, 2020 - 4:44 pm

fantastic issues altogether, you simply received a brand new reader.
What might you recommend about your publish that you just made a few
days ago? Any positive?

Reply
파라오카지노 November 3, 2020 - 4:44 pm

It’s actually very complicated in this busy life to listen news on Television, thus I
just use the web for that reason, and obtain the latest news.

Reply
바카라사이트 November 3, 2020 - 5:07 pm

Thanks for sharing your thoughts on 바카라사이트.
Regards

Reply
바카라사이트 November 4, 2020 - 7:13 am

Spot on with this write-up, I absolutely believe this amazing site
needs much more attention. I’ll probably be back again to read more, thanks for the advice!

Reply
우리카지노 November 4, 2020 - 1:41 pm

Hi to all, how is the whole thing, I think every one
is getting more from this web site, and your views are fastidious
for new users.

Reply
우리카지노 November 4, 2020 - 1:44 pm

I loved as much as you will receive carried out right here.
The sketch is tasteful, your authored subject
matter stylish. nonetheless, you command get got an impatience over that you wish be delivering the following.
unwell unquestionably come further formerly again as exactly
the same nearly very often inside case you shield this increase.

Reply
우리카지노 November 4, 2020 - 2:40 pm

My partner and I absolutely love your blog and find nearly all of your post’s
to be precisely what I’m looking for. Do you offer guest writers to write
content in your case? I wouldn’t mind publishing a post or elaborating on a few of the subjects you write related to here.
Again, awesome web log!

Reply
우리카지노 November 4, 2020 - 3:06 pm

Good day! This is my 1st comment here so I just wanted
to give a quick shout out and say I genuinely enjoy reading through your posts.
Can you recommend any other blogs/websites/forums that
deal with the same subjects? Thanks a ton!

Reply
카지노사이트 November 4, 2020 - 3:11 pm

I think the admin of this web page is actually working hard in support
of his web site, as here every material is quality based
data.

Reply
카지노사이트 November 4, 2020 - 3:15 pm

I am genuinely grateful to the owner of this web site who has shared this fantastic piece
of writing at at this time.

Reply
카지노사이트 November 4, 2020 - 3:45 pm

It’s a shame you don’t have a donate button! I’d definitely donate to this outstanding blog!
I guess for now i’ll settle for book-marking and adding your RSS feed to my Google account.
I look forward to brand new updates and will talk about this website with my Facebook group.
Chat soon!

Reply
예스카지노 November 4, 2020 - 4:20 pm

When someone writes an post he/she keeps the idea of a user in his/her brain that how
a user can be aware of it. So that’s why this piece of writing is perfect.
Thanks!

Reply
바카라사이트 November 4, 2020 - 6:10 pm

My partner and I stumbled over here from a different page and thought I may as well check things out.
I like what I see so now i am following you.
Look forward to finding out about your web page again.

Reply
우리카지노 November 6, 2020 - 4:24 pm

Hi, i read your blog from time to time and i own a similar one and i was just curious if you
get a lot of spam comments? If so how do you stop it, any plugin or anything you can advise?
I get so much lately it’s driving me mad so any support is very much appreciated.

Reply
카지노사이트 November 6, 2020 - 5:34 pm

The other day, while I was at work, my cousin stole my iphone and tested to see if it can survive a 40 foot drop, just so
she can be a youtube sensation. My apple ipad
is now destroyed and she has 83 views. I know this is totally off topic
but I had to share it with someone!

Reply
바카라사이트 November 6, 2020 - 5:36 pm

I go to see day-to-day a few web pages and blogs to read posts, but this weblog gives quality based
content.

Reply
바카라사이트 November 6, 2020 - 5:51 pm

We stumbled over here different page and thought I might check things out.

I like what I see so i am just following
you. Look forward to looking at your web page for a second time.

Reply
카지노사이트 November 6, 2020 - 6:20 pm

Wonderful post! We will be linking to this particularly great article on our website.
Keep up the good writing.

Reply
파라오카지노 November 6, 2020 - 6:44 pm

Hello There. I found your blog using msn. This is a very well written article.

I’ll make sure to bookmark it and come back to read more
of your useful info. Thanks for the post. I’ll certainly comeback.

Reply
우리카지노 November 6, 2020 - 6:59 pm

Ahaa, its good discussion regarding this article here at
this blog, I have read all that, so at this time me
also commenting at this place.

Reply
더킹카지노 November 6, 2020 - 7:40 pm

I’m not sure where you are getting your info, but good topic.
I needs to spend some time learning more or understanding more.

Thanks for great info I was looking for this info for my mission.

Reply
바카라사이트 November 8, 2020 - 8:17 pm

Greetings! Very helpful advice within this article! It’s the little changes that produce the biggest changes.
Many thanks for sharing!

Reply
더킹카지노 November 8, 2020 - 11:32 pm

Wow that was unusual. I just wrote an incredibly long comment but after I clicked submit my comment didn’t appear.
Grrrr… well I’m not writing all that over again. Anyways, just wanted to say wonderful
blog!

Reply
우리카지노 November 9, 2020 - 8:52 am

I do not even know how I ended up here, but I
thought this post was good. I don’t know who you are but certainly you’re going to a famous blogger if you are not already 😉 Cheers!

Reply
카지노사이트 November 9, 2020 - 9:21 am

Hello, i read your blog occasionally and i own a similar one and i was just wondering if you get a lot of spam comments?

If so how do you prevent it, any plugin or anything you can advise?
I get so much lately it’s driving me insane so any support is very much appreciated.

Reply
카지노사이트 November 9, 2020 - 9:35 am

Hi, this weekend is good for me, since this moment i am reading this enormous informative
article here at my home.

Reply
바카라사이트 November 9, 2020 - 9:56 am

If some one needs to be updated with newest technologies then he must be pay a quick visit this website and be
up to date every day.

Reply
바카라사이트 November 9, 2020 - 11:40 am

I like the helpful info you provide for your articles.
I will bookmark your weblog and test again here regularly.
I’m quite sure I will be told many new stuff right here!
Good luck for the following!

Reply
카지노사이트 November 9, 2020 - 11:44 am

I read this article completely about the resemblance
of hottest and earlier technologies, it’s awesome
article.

Reply
카지노사이트 November 9, 2020 - 12:27 pm

Every weekend i used to pay a quick visit this web site, because i wish for enjoyment, as this this
site conations genuinely nice funny information too.

Reply
blqphatomi November 13, 2020 - 12:09 pm

Muchas gracias. ?Como puedo iniciar sesion?

Reply

Leave a Comment