Introduction to database management in MariaDB

2020/05/20 - Tutorials

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. There are several database servers and clients available like Oracle, MySQL, MySQLi, MariaDB, MongoDB, etc.

Installing Database

For Installing MariaDB in your centos follow this steps.

yum install mariadb mariadb-client

This command will install the MariaDB server and client on your machine

Start and enable MariaDB service

The following commands will start and enable the MariaDB service on your server.

systemctl start mariadb
systemctl enable mariadb

To check whether the service is running you can check through the following command

systemctl status mariadb

And if it’s successful you can see the following output.

Creating user in MariaDB

You can create a root password and set some privileges to the MariaDB server.

mysql_secure_installation

After creating a root password and giving some privileges to the MariaDB, You can enter into the database through the following command

mysql -u root -p

You will be asked to enter the root password, after that you will be prompted into the MariaDB server.

Basic Database Administration commands

We will now discuss some of the important database commands

  • Change root password
mysqladmin -u root -p oldpassword password newpassword

change the old password and new password with yours

  • Check server is running
mysqladmin -u root -p ping
  • To see MySQL server variables and values
mysqladmin -u root -p variables
  • To Shutdown MYSQL server safely
mysqladmin -u root -p shutdown
  • To connect remote mysql server
mysqladmin -h remoteip -u root -p

Change remoteip with real IP

  • To view options and usage
 mysqladmin --help

Familiarizing with MariaDB Database

Following we will discuss some basic commands we usually use in MariaDB

  • Show the available databases.
show databases;
  • Create a database
create database thestack;
  • Enter into a particular database.
use thestack;
  • List the tables in a database
show tables;

Basic SQL commands

SQL is a special programming language designed for managing data held in relational databases. Some common SQL commands include insert, update, delete and select which are commonly referred as CRUD. Some commonly used data types in SQL are int, varchar

  • Creating a table clients with some arguments
create table clients (id int(30),name varchar(30),service varchar(30));
  • Insert values into the table
insert into clients values(1,'abc','VPS');
  • Display the table
select * from clients;
  • Delete a record from table
delete from clients where id = 1;

One of the important clauses we commonly used in the SQL is where clause. The table below shows some operators for where clause.

Operator Description
= Equal
!= Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column

Managing Database Users

The CREATE USER statement creates new accounts.

create user [email protected] identified by 'a122322'

The GRANT statement can be used to grant privileges to accounts.

grant all on thestack.clients to [email protected]

The REVOKE statement allows for revoking privileges from accounts

revoke all on thestack.clients from [email protected]

After granting or revoking a privilege, we use the FLUSH command to reload all privileges from the privileges tables in the MySQL database.

flush privileges;

Related Articles