Database Population:

As in the above section, we have granted all privileges to all other users, now we can directly login to MySQL without root access and perform DDL or DML operations.

In the below, we are creating emptable under TestDB database and inserting values into emp table and retrieving the results back from emp table. Below are the commands to perform these actions.

MySQL

1

2

3

4

5

6

7

8

9

10

11

$mysqlTestDB

mysql>CREATETABLEemp(e_idINTNOT NULLPRIMARY KEYAUTO_INCREMENT,

->e_nameVARCHAR(50)NOT NULL,

->salaryDECIMAL(10,2),

->designationVARCHAR(50),

->deptVARCHAR(50));

mysql>INSERTINTOempVALUES(NULL,'siva',10000.00,'team lead','AIM');

mysql>INSERTINTOempVALUES(NULL,'raj',25000.50,'tech lead','TRAD');

mysql>INSERTINTOempVALUES(NULL,'praveen',30000.00,'sse','AIM');

mysql>SELECT*FROMemp;

Creation of Dedicated User for MySQL:

In this blog, we use MySQL for hive local metastore setup and also for testing SQOOP import and export functionality from other relational databases. So, it is better to have dedicated user id (mysql_usr) for MySQL access under mysql_grp in ubuntu.

We can follow below command in the same sequence to add a new user mysql_usr to new group mysql_grp.