DevOps Prerequisite (Part 6): Database

Here we will learn about 2 databases

MySQL which is a SQL based databases and MongoDB which is a NoSQL database.

SQL
It basically stores data in rows and column format

If you want to add any data, all of the data get assigned to a column. You can now provide values for them.

Here you can see the Salary and Grade column and it's not full. But all of the data has now a column which they can fill or not.

NoSQL
Here it keeps data like pages.

You can add extra details here which won't impact other datas. Other data won't have that special label or row at all.

This is the benefit of NoSQL.
This is a json representation of NoSQL database.

This is a simple difference between sql queries and nosql queries

Example of SQL vs NoSQL are

MySQL basics

There are community edition and commercial one

You can install sql by this. Here, mysql-server is installed as a service. So, we can start the service.

Database logs are stored in /var/log/mysqld.log. It listens on 3306 port

Now, if we want to connect to the database, we need a password. Luckily, we have a password generated (here, g/io%...)Use the one time password it generated to login.

Now, change your default password to a new one.

You can now check the default databases.

You can create a database using CREATE DATABASE command

Then you can use the database using USE and generate a table within that.

Then you create a table using CREATE TABLE command

You can also insert data into the table using INSERT INTO command

Creating user

Firstly log into root user and then create an user.

Here the user can access to the server from 192.168.1.10

If we want the user to connect from all systems, we can use % instead of specifying an IP.

What after the user creation? Surely access to database etc?

You can pick any of the permissions from here and assign the user.

These are some of the permission examples you can give to our user john who can access from any system (%)

Labs

Installing MySQL in Centos

sudo yum install https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

sudo yum install mysql-community-server

Start the MySQL

sudo service mysqld start

Check the temporary password that has been set for root

sudo grep 'temporary password' /var/log/mysqld.log

Let's change it:
Here we have set the password to P@ssw0rd123 and for this, we checked the demo password and then logged in there. And then changed the password.

Let's create a new user and set him a password

CREATE USER 'kk_user'@'localhost' IDENTIFIED BY 'S3cure#3214';

Here kk_user is the user and he can access the localserver only . We did set it using "localhost" . We have also set password for him and that is "S3cure#3214".

Let's grant him all of the access

GRANT ALL PRIVILEGES ON kk_db.* TO 'kk_user'@'localhost';

now we have given kk_user the full access to our database which we created (kk_db) by using * after .

MongoDB

MongoDB also has community and enterprise versions

MongoDB stores data in json like format.

Multiple documents create a document and multiple document creates a database.

You can keep multiple databases within a MongoDB server

We can use cloud or server version.

Here we are using the server version. Once installed....

We can start and the service and check it's status.

The logs are stored in /var/log/mongodb/mongod.log file

You can see it's listening on 127.0.0.2 and expecting to listen from port 27017

You can change the host IP from /etc/mongod.conf file

You can connect to the mongodb server using mongo

Here, No access control is enabled here.

Check databases using show dbs

Create a new database and switch to it using use and use db to check which database you are at

You can now create collections and insert your data in json format.

To search data use find().

Install MongoDB on Centos