DevOps and Middleware Engineering: Creating MySQL Database on CentOS

Step 1) Refer the following link on how to install CentOS on Virtual box

https://middlewareadmin-pavan.blogspot.com/2021/01/install-centos-machines-and-establish.html

Step 2) Clone a new Virtual Machine from the template

Step 3) Start the virtual machine and connect using Putty.

Password is : osboxes.org

Step 4) Change the host name and disable firewalld

[root@osboxes ~]# sed -i 's/osboxes/mysqldbhost/g' /etc/hostname

[root@osboxes ~]# cat /etc/hostname

mysqldbhost

[root@osboxes ~]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 mysqldbhost

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 mysqldbhost

192.168.1.241 mysqldbhost

[root@osboxes ~]# systemctl stop firewalld

[root@osboxes ~]# systemctl disable firewalld

Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.

Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

Step 5) Download and Install mysql-server

[root@osboxes ~]# wget https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm

[root@osboxes ~]# sudo dnf install mysql-server -y

Step 5) Start mysql server

[root@osboxes ~]# systemctl start mysqld

Step 6) Enable systemd script for mysql auto restart on reboot

[root@osboxes ~]# systemctl enable mysqld

Step 7) Setup Password for the root user and skip others. I did setup up password as Password1

[root@osboxes ~]# mysql_secure_installation

Step 8) Connect to mysql with root user name and Password.

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

Step 9) Verify the port number and exit from the sql prompt.

mysql> SHOW GLOBAL VARIABLES

mysql> SHOW GLOBAL VARIABLES LIKE 'PORT';

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

| Variable_name | Value |

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

| port | 3306 |

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

1 row in set (0.01 sec)

mysql> quit

Bye

Step 10) Install telnet and check if you are able to connect to port 3306

[root@mysqldbhost ~]# yum install telnet

[root@mysqldbhost ~]# telnet localhost 3306

Trying ::1...

Connected to localhost.

Escape character is '^]'.

8.0.21 :DB*z[7C▒Bu4J~ Q Akmysql_native_password

You can also verify the port by running netstat command

[osboxes@mysqldbhost ~]$ netstat -anp | grep 3306

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp6 0 0 :::33060 :::* LISTEN -

tcp6 0 0 :::3306 :::* LISTEN -

Step 11) Add bind-address in /etc/my.conf file for allowing connections from other hosts

[root@mysqldbhost ~]# echo "bind-address = 0.0.0.0" >> /etc/my.cnf

[root@mysqldbhost ~]# cat /etc/my.cnf

# This group is read both both by the client and the server

# use it for options that affect everything

[client-server]

# include all files from the config directory

!includedir /etc/my.cnf.d

bind-address = 0.0.0.0

Step 12) reboot the machine

[root@mysqldbhost ~]# reboot

Step 13) Connect to mysql as a root and create database, Create a user and grant permissions.

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

Enter password:

mysql> CREATE DATABASE HRDATABASE;

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON HRDATABASE.* TO 'PBPUBLIC'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT FILE ON *.* TO 'PBPUBLIC'@'localhost';

Query OK, 0 rows affected (0.11 sec)

Step 14) Create table and instert values into the database HRDATABASE

mysql> USE HRDATABASE

Database changed

mysql> CREATE TABLE EMPLOYEE

-> (

-> ID INT ,

-> NAME CHARACTER (100) ,

-> TITLE CHARACTER (100) ,

-> SALARY CHARACTER (50) ,

-> BONUS_STRUCTURE CHARACTER (50) ,

-> TIME_OFF INT ,

-> SICK_TIME INT ,

-> HEALTH_PLAN CHARACTER (100) ,

-> VISION_PLAN CHARACTER (100) ,

-> DENTAL_PLAN CHARACTER (100) ,

-> PLAN INT ,

-> SAVINGS INT

-> );

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO EMPLOYEE VALUES(1,'Chris Montgomery','Manager','70,000','25% Quarterly',15,5,'Blue Cross and Blue Shield','Aetna Vision','Delta Dental',25000,12000);

INSERT INTO EMPLOYEE VALUES(5,'Jennifer Ackerman','Manager','75,000','25% Quarterly',15,5,'Mathew Thornton Health Plan','Aetna Vision','Aetna Dental',18000,23000);

INSERT INTO EMPLOYEE VALUES(6,'Jill Champagne','Senior Programmer','60,000','20% Quarterly',10,5,'Mathew Thornton Health Plan','Aetna Vision','Delta Dental',12000,16000);

INSERT INTO EMPLOYEE VALUES(7,'Katherine Frederick','Manager','70,000','25% Quarterly',15,5,'Mathew Thornton Health Plan','Aetna Vision','Aetna Dental',25000,6000);

INSERT INTO EMPLOYEE VALUES(8,'Michael Fuller','Programmer','50,000','15% Quarterly',10,5,'Blue Cross and Blue Shield','Aetna Vision','Delta Dental',13500,9500);

INSERT INTO EMPLOYEE VALUES(9,'Ryan Lewis','Programmer','52,000','15% Quarterly',10,5,'Blue Cross and Blue Shield','Aetna Vision','Delta Dental',16000,7600);

INSERT INTO EMPLOYEE VALUES(10,'Thomas Bernard','Manager','65,000','25% Quarterly',15,5,'Mathew Thornton Health Plan','Aetna Vision','Delta Dental',28520,1480);Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO EMPLOYEE VALUES(2,'Doris Sylvester','Programmer','55,000','15% Quarterly',10,5,'Blue Cross and Blue Shield','Aetna Vision','Aetna Dental',22000,16000);