Introduction to database management in MariaDB
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;