Mariadb with example

Question:

1. you are required to backup database called staff to mydb.sql.
2. you need to restore mydb.sql to employees database.
3. create user shiba and grant select, update, delete and insert permissions to employee table. password for the use should be ‘password’ without quote.
4. create another user reewa and grant select only priviledge to employee table, 172.168.0.0/24 subnet.
5. select last name of user whose first name is ram and department is sales.

Solution:

• Installing mariadb
  yum -y install mariadb-server

• starting service
  systemctl start mariadb

• adding to startup
  systemctl enable mariadb

• adding to firewall (only require if you need to access Mariadb from other macine using mysql frontend tools)
  firewall-cmd –permanenet –add-service mysql

• securing installation (assigining mysql password, removening unwanted access.)
  mysql_secure_installation

• (say yes to all and give, in case of current password, just enter and give new password for mysql root user).
  

  ---

  Note you don’t have staff database, so you can copy the SQL file from dump data attached with this site below.

  ---

• Login to mysql
  mysql -u root -p

• create table staff using following command
  create table staff;

• restore staff.sql file to staff database.
  mysql -u root -p staff < staff.sql

• Backup to mydb.sql file
  mysqldump -u root -p staff > mydb.sql

• first create employees logging in to mysql.
  create database employees;

• exit out of Mariadb and use following command to restore database to employees:
  mysql -u root -p employees < mydb.sql

• creating user shiba and give select, update, delete and insert priviledge. Password ‘password’
  create user shiba;
  grant select, update, delete, insert on employees.* to shiba@localhost identified by ‘password';
  flush privileges;

• creating user reewa and giving select access of employees database from 172.168.0.0/24 subnet.
  create user reewa;
  grant select on employees.* to reewa@’172.168.0.%’ identified by ‘password';

• select last name of user whose first name is ram and department is sales.
  select lname from emp, dept where emp.deptid=dept.deptid and fname=’ram';

Copy following content to staff.sql to practice above exercise:

— MySQL dump 10.14  Distrib 5.5.35-MariaDB, for Linux (x86_64)
—
— Host: localhost    Database: staff
— ——————————————————
— Server version    5.5.35-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

—
— Table structure for table `dept`
—

DROP TABLE IF EXISTS `dept`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dept` (
`deptid` int(1) DEFAULT NULL,
`dept_name` varchar(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Dumping data for table `dept`
—

LOCK TABLES `dept` WRITE;
/*!40000 ALTER TABLE `dept` DISABLE KEYS */;
INSERT INTO `dept` VALUES (101,’IT’),(102,’Sale’);
/*!40000 ALTER TABLE `dept` ENABLE KEYS */;
UNLOCK TABLES;

—
— Table structure for table `emp`
—

DROP TABLE IF EXISTS `emp`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp` (
`id` int(3) DEFAULT NULL,
`fname` varchar(20) DEFAULT NULL,
`lname` varchar(20) DEFAULT NULL,
`deptid` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Dumping data for table `emp`
—

LOCK TABLES `emp` WRITE;
/*!40000 ALTER TABLE `emp` DISABLE KEYS */;
INSERT INTO `emp` VALUES (1,’shiba’,’tamrakar’,101),(1,’reewa’,’tamrakar’,101),(1,’deepika’,’parajuli’,101),(1,’ram’,’parajuli’,102),(1,’shyam’,’joshi’,102);
/*!40000 ALTER TABLE `emp` 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 2015-08-10 21:50:02