Programming with Concept and Practical: Linux Database MariaDB

MariaDB

MariaDB is a binary replacement for MYSQL,

MariaDB is Default Database RHEL 7

To Install MariaDB packages:

[root@server ~]# yum install -y mariadb mariadb-server

Loaded plugins: fastestmirror, langpacks

base Determining fastest mirrors

Resolving Dependencies

--> Running transaction check

---> Package mariadb.x86_64 1:5.5.56-2.el7 will be installed

---> Package mariadb-server.x86_64 1:5.5.56-2.el7 will be installed

--> Processing Dependency: perl-DBI for package: 1:mariadb-server-5.5.56-2.el7.x86_64

--> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.56-2.el7.x86_64

--> Processing Dependency: perl(Data::Dumper) for package: 1:mariadb-server-5.5.56-2.el7.x86_64

--> Processing Dependency: perl(DBI) for package: 1:mariadb-server-5.5.56-2.el7.x86_64

--> Running transaction check

---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed

---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : perl-Data-Dumper-2.145-3.el7.x86_64 Installing : 1:mariadb-5.5.56-2.el7.x86_64 Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 Installing : perl-IO-Compress-2.061-2.el7.noarch Installing : perl-Net-Daemon-0.48-5.el7.noarch Installing : perl-PlRPC-0.2020-14.el7.noarch Installing : perl-DBI-1.627-4.el7.x86_64 Installing : perl-DBD-MySQL-4.023-5.el7.x86_64 Installing : 1:mariadb-server-5.5.56-2.el7.x86_64

Installed:

mariadb.x86_64 1:5.5.56-2.el7 mariadb-server.x86_64 1:5.5.56-2.el7

Complete!

To Activate the MariaDB Service

[root@server ~]# systemctl enable mariadb.service

Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

To Start the MariaDB

[root@server ~]# systemctl start mariadb

To Execute the basic setup: mysql_secure_installation

Change the root password ?[Y/n] n

Remove anonymous users? [Y/n]Y

Disallow root login remotely? [Y/n]y

Remove test database and acces to it? [Y/n]Y

Reload privilege tables now? [Y/n]y

[root@server ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB

SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current

password for the root user. If you've just installed MariaDB, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDBroot user without the proper authorisation.

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation

go a bit smoother. You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] y

... Success!

Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y

... Success!

By default, MariaDB comes with a database named 'test' that anyone can

access. This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n] y

- Dropping test database...

... Success!

- Removing privileges on test database...

... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] y

... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB

installation should now be secure.

Thanks for using MariaDB!

To Check MariaDb Installed version.

[root@server ~]# mysql -V

mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

To Add mysql to the firewall configuration and reload it

[root@server ~]# firewall-cmd --permanent --add-service=mysql

success

[root@server ~]# firewall-cmd --reload

success

[root@server ~]#

To Login to Mysql or MariaDB

[root@server ~]# mysql -u root -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 10

Server version: 5.5.56-MariaDB MariaDB Server

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

To Create database OnlineCourseDB

MariaDB [(none)]> create database OnlineCourseDB;

Query OK, 1 row affected (0.01 sec)

To List All Database in MariaDB Console

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| OnlineCourseDB |

| mysql |

| performance_schema |

+--------------------+

4 rows in set (0.00 sec)

To Remove database OnlineCourseDB

MariaDB [OnlineCourseDB]> drop database OnlineCourseDB;

Query OK, 2 rows affected (0.08 sec)

To go inside database OnlineCourseDB

MariaDB [(none)]> USE OnlineCourseDB;

Database changed

MariaDB [OnlineCourseDB]>

To Create Table CourseTBL in database OnlineCourseDB

MariaDB [OnlineCourseDB]> CREATE TABLE CourseTBL (

-> CourseID INT NOT NULL AUTO_INCREMENT,

-> CourseName VARCHAR(100),

-> PRIMARY KEY(CourseID)

-> );

Query OK, 0 rows affected (0.08 sec)

To Describe Table Structure CourseTBL

To Create Table ProgrammingTBL in database OnlineCourseDB

MariaDB [OnlineCourseDB]> CREATE TABLE ProgrammingTBL (

-> ProgrammeID INT NOT NULL AUTO_INCREMENT,

-> ProgrammeName VARCHAR(100) NOT NULL,

-> InstructorID INT NOT NULL,

-> ProgrammePrice DECIMAL(6,2) NOT NULL,

-> ProgrammeIsAvailable BOOLEAN,

-> PRIMARY KEY(ProgrammeID)

-> );

Query OK, 0 rows affected (0.00 sec)

To Insert into table CourseTBL

MariaDB [OnlineCourseDB]> INSERT INTO CourseTBL (CourseName) VALUES ('JAVA'), ('PHP'), ('MYSQL');

Query OK, 3 rows affected (0.03 sec)

Records: 3 Duplicates: 0 Warnings: 0

To View selective Record from Table CourseTBL

MariaDB [OnlineCourseDB]> SELECT * FROM CourseTBL WHERE CourseName='JAVA';

+----------+------------+

| CourseID | CourseName |

+----------+------------+

| 1 | JAVA |

+----------+------------+

1 row in set (0.00 sec)

To ALL Record from Table CourseTBL

MariaDB [OnlineCourseDB]> SELECT * FROM CourseTBL;

+----------+------------+

| CourseID | CourseName |

+----------+------------+

| 1 | JAVA |

| 2 | PHP |

| 3 | MYSQL |

+----------+------------+

3 rows in set (0.00 sec)

To Insert record into table ProgrammingTBL

MariaDB [OnlineCourseDB]>MariaDB [OnlineCourseDB]> INSERT INTO ProgrammingTBL (ProgrammeName, InstructorID, ProgrammePrice, ProgrammeIsAvailable)

-> VALUES ('JAVA', 100, 2000, 1),

-> ('PHP', 103, 2300.0, 1),

-> ('MYSQL',107, 8435.99, 1);

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

To view Selective record from table ProgrammingTBL

MariaDB [OnlineCourseDB]> SELECT * FROM ProgrammingTBL;

To update selective record from table ProgrammingTBL

MariaDB [OnlineCourseDB]> UPDATE ProgrammingTBL SET ProgrammePrice=9999.99 WHERE ProgrammeID=2;

Query OK, 1 row affected, 1 warning (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 1

To Delete selective Record table ProgrammingTBL

MariaDB [OnlineCourseDB]> DELETE FROM ProgrammingTBL WHERE ProgrammeID=2;

Query OK, 1 row affected (0.07 sec)

To Create User OnlineCourseuser with password agoutam

MariaDB [OnlineCourseDB]> CREATE USER OnlineCourseuser@localhost IDENTIFIED BY 'agoutam';

Query OK, 0 rows affected (0.00 sec

To provide permission to User OnlineCourseuser on database OnlineCourseDB

MariaDB [OnlineCourseDB]> GRANT ALL PRIVILEGES ON OnlineCourseDB.* to OnlineCourseuser@localhost;

Query OK, 0 rows affected (0.01 sec)

MariaDB [OnlineCourseDB]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

To take Back up of database called OnlineCourseDB

[root@server ~]# mysqldump --user=root --password="root" --result-file=course.sql OnlineCourseDB

[root@server ~]# ls -lrt course.sql

-rw-r--r--. 1 root root 2867 Oct 27 15:39 course.sql

To view Dump Database sql file course.sql

[root@server ~]# tail -20 course.sql

-- Dumping data for table `ProgrammingTBL`

LOCK TABLES `ProgrammingTBL` WRITE;

/*!40000 ALTER TABLE `ProgrammingTBL` DISABLE KEYS */;

INSERT INTO `ProgrammingTBL` VALUES (1,'JAVA',100,2000.00,1),(3,'MYSQL',107,8435.99,1);

/*!40000 ALTER TABLE `ProgrammingTBL` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-10-27 15:39:13

To Restore from back up course.sql to database OnlineCourseDB

[root@server ~]# mysql --user=root --password="root" OnlineCourseDB<course.sql

Programming with Concept and Practical

Friday, 27 October 2017

RHEL 7 Database MariaDb

What is iSCSI and How Does it Work?