RDS-Notes/RDS MySQL Step-By-Step .txt at main · Ayush-Rathor/RDS-Notes · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
#################################
RDS (Relational Database Service)
#################################

-> Every application will contain 3 layers

			1) Front End (User interface)

			2) Back End (Business Logic)

			3) Database

-> End users will communicate with our application using frontend (user interface).

-> When end-user perform some operation in the front-end then it will send request to backend. Backend contains business logic of the application.

-> Backend logic will communicate with Database to perform DB operations.

	(insert / update / retrieve / delete)

###########################################
Challenges with Database Setup on our own
###########################################

1) Setup Machine to install Database Server
2) Purchase Database Server License
3) Install Database Server in our Machine
4) Setup Network for our machine
5) Setup power for machine
6) Setup a server room to keep our machines
7) Setup AC for room for cool temperature
8) Setup Security for room
9) Setup Database backups
10) Disaster Recovery

-> If we use AWS cloud, then AWS will take care of all the above works which are required to setup Database for our application.

-> In AWS, we have RDS service to create and setup database required for our applications.

-> We just need to pay the money and use Database using AWS RDS service. DB setup and maintenence will be taken care by AWS people.

##### RDS is full managed by AWS #####

** Using RDS we can Easily set up, operate, and scale a relational database in the cloud ***

######################################
Steps to create MYSQL DB using AWS RDS
######################################

1) Login into AWS management console

2) Goto RDS Service

3) Click on 'Create Database'

		Choose a database creation method : Easy Create
		Engine Option : MySQL
		Template : Free Tier
		DB instance Identifier : db-1 (Note : you can give anything)
		Username : admin
		Password : Choose a passord


4) Click on 'Create Database' (It will take few minutes of time to create)

	Note: Notedown username and password of the database

5) Once Database created, it will provide database Endpoint URL to access

6) Change Database to Public Access

7) Enable All Traffic in Security Group attached to Database.

###################
MySQL DB Properties
###################

Endpoint : database-1.cbair9bd9y7d.ap-south-1.rds.amazonaws.com
Uname : admin
Pwd : devdb
Port : 3306 (it is default port for mysql db)


Note: We need to provide DB properties to project development team / testing team

#############################
Steps to test MYSQL DB Setup
#############################

1) Download and install Visual Studio using below link

	Link : https://aka.ms/vs/17/release/vc_redist.x64.exe

2) Download and install MySQL Workbench using below link

	Link : https://dev.mysql.com/downloads/workbench/

3) Create Database Connection in MySQL workbench using Database properties

4) Once we are able to connect with Database then we can execute below queries in Workbench

###############
MySQL Queries
###############

show databases;

create database sbidb;

use sbidb;

show tables;

create table emp_dtls(emp_id integer(10), emp_name varchar(20), emp_salary integer(10));

insert into emp_dtls values(101, 'Alex', 5000);
insert into emp_dtls values(102, 'Jhon', 6000);
insert into emp_dtls values(103, 'Ayush', 7000);

select * from emp_dtls;


####################################
Working with MySQL client in Ubuntu
####################################

$ sudo apt-get update

$ sudo apt-get install mysql-client

$ mysql -h <endpoint> -u <username> -p (click enter and give password)


##########################################
Working with MySQL client in AMAZON Linux
##########################################

$ sudo yum update

$ sudo yum install mysql

$ mysql -h <endpoint-url> -u <username> -p (click enter and give password)

-h : It represents host (DB edpoint URL)

-u : It represents DB username

-p : It represents DB password


Ex:   mysql -h database-1.cbair9bd9y7d.ap-south-1.rds.amazonaws.com -u admin -p


=> We can execute below queries to see the data which we have inserted previously using Workbench.

show databases;

use sbidb;

show tables;

select * from emp_dtls;