AWS Tutorial - Launch a RDS MySQL instance and connect to it using Linux EC2

hello all and welcome to this AWS tutorial on Amazon's manage relational database service in today's tutorial we will see how to launch in my SQL RDS instance and connect and access it using ec2 Linux before we go further ahead with a demo in the tutorial let us review soother steps first thing that we will do is we will launch a RDS MySQL instance in our default VPC now when you launch this instance please ensure that you check the free tier option if the free tier option is not checked you can potentially land up paying unnecessary charges the second thing after we we have been launched our RDS instance as we will launch our Amazon Linux ec2 instance again this is going to be a free tier instance again and this will be launched in a default EPC with public with a public IP after we have launched our M RDS instance and our ec2 instance we will connect to our Linux ec2 instance via SSH and install MySQL client on our instance after a client has been installed we will connect to our MySQL server using a MySQL client if our connection is successful we will create a database a table insert a few records and select the inserted records okay so let's get started with the tutorial so this is my AWS account okay and this is RDS right here so let's click on IDs and the first thing that we will do is we will launch a RDS instance so let us come up so this is the launch page so I'm going to go ahead and click on create database right here okay so before you even go ahead and select the engine option scroll down and check this box only enable options eligible for RDS free tea free tier usage this is extremely important friends because if this is not checked you can potentially land up you know provisioning a much larger instance and also incurring unnecessary costs so ensure that this particular option is checked so let's go ahead and check this option so the moment you go ahead and check that option is checked now okay you will see that Amazon aurora has been disabled and you only have options of mysql Maria DB Postgres SQL Oracle and sequel server so for this particular tutorial we will go ahead and provision in my SQL instance so select my SQL ensure that only enable options for free tier eligible is checked and click on next so as you see the DB engine type that they have selected because since this is a free tier option is the MySQL Community Edition that is perfectly fine I mean if you're just learning or just exploring MySQL I was strongly encouraged that you go ahead and use the free tier option we will leave the license model as general public DB version as 8.0 and then scroll further down so the next thing that I want to bring to your attention is this again the free tier message that is coming up right here so since we are using the free tier it is telling us that Amazon free tier provides a single DB T to micro instance as well as up to 20 gigabits of storage allowing new AWS customers gain hands-on experience with Amazon RDS and that is perfect so the instance that is selected over here as you see is DBT to micro right here where my mouse is right now let me just even highlight it to you so this option so let it be DBT to micro okay we don't want to select a bigger box this is just for demo purposes or learning purposes and if you select a bigger box yes you will incur charges by default when you are in the free tier mode you will see that there are no other options coming up over here so hence it's very important that you enable the free tier option in the previous screen okay so let's scroll further down and as you see this is not going to be a multi easy deployment so by default no is selected and again this is because this is a free tier option it is going to by default provide us 20 gigabytes of SSD space so that's fine that's as mentioned over here it gives us 20 gigabytes of storage space so that's okay again settings so now we need to provide a name over here for a DB instance so I have basically captured everything right here in a notepad so what I'm going to do is my username password DB instance everything is just going to be my SQL DB 20 80 and this is purely because I don't forget what I'm giving you this demo and I was strongly encouraged if you are just playing around just use the same thing so that you don't have to remember much so let's go back and copy it right here so our DB instance identifier is going to be my SQL 2018 and our username is going to be the same password is also going to be the same and I'm just gonna go ahead and confirm the password and then go ahead and click on next so as you see it's asking us which V PC you want to launch this in and I will be launching this for this this RDS instance for this demo in my default V PC it's going to use a default subnet if you want you can give it public accessibility it's up to you I'm not gonna make this public accessible I'm just gonna say now availability so no preference or if you want you can go and say select a particular availability zone so you can say us ec1 I generally prefer that it's gonna create is known V PC security group so that's perfectly fine and if you want to give by default a database that needs to be created then you can if not you can just leave this empty please note the port over here so the port that MySQL uses is three three zero six and this is very important because you will have to enable this port on our ec2 as well so that our ec2 can communicate with our RDS instance I'm going to leave the DB parameter group and option group by default no encryption will be enabled as you see it's disabled again depending upon the engine and the free tier option and everything some of these features might not be available as you see encryption is disabled right now backup so by default as you see it's seven days and again as it's showing over here by default that by default automated backups are currently supported for I know DP storage if you are using my ASM then we need to schedule a backup so we are covered at this point of time because it's I know DP and I don't have any specific preference for my backup window I'm just gonna leave this you know by default at 7:00 I'll disable any monitoring again logging I'm gonna disable at this time if you happen to check the the logging and sure that after you have deleted this instance ensure that you've also deleted the logs okay otherwise just won't take up space as far as any version upgrades you can enable that again no preferences for any maintenance windows over here and yes as far as deletion protection is concerned I'm going to actually go ahead and uncheck this so that I can go ahead and delete this instance once I'm done and finally we will go ahead and click on create database so let's have this database created shortly so as you see a database instance is being created right now and let's go ahead and view the instance details it takes a few minutes for the RDS instance to be provisioned so be patient and what we are going to do is by the time it's it you know provisions this particular instance we will actually provision our ec2 instance and also install our my SQL client on it so that once this instance is up and running we are able to connect to it so as you see the status is still creating and if you scroll further down is still the endpoint is not yet available because it still is creating it port by default we know it's going to be 3 3 0 6 but as yet not available security rules are also been created and these are just a default option so we just gonna leave this as it is right now and go ahead and provision our ec2 instance so let's click on ec2 and after the page loads what we will do is we will launch a Amazon Linux ec2 instance again this is going to be a free tier instance so you're more than welcome to you use non free tier but I prefer to use free tier for demo and learning purposes so check on Freedia right there select Amazon Linux and click on select and we are going to use a t2 micro instance click on configure details as we are going to launch one instance and default V PC I'll try and launch just in the same subnet as my RDS which is us east 1a and we are going to unable or to assign public IP so that we can connect to this instance we basically SSH into it and then install our client so let's click on add storage you're going to leave those default tags you can probably give a name you can say is c2 you see to my SQL configure security groups we're gonna create a new security group and that's gonna call and we're gonna call us ec2 MySQL SG so we know what why we created this easy security group and what it stands for and as you see by default we have SSH added over here but since we will be connecting to our RDS instance we will add one another rule over here in in type scroll further down and look for my SQL Aurora so there it is my SQL Aurora so again it uses the same port 3 3 0 6 so select my SQL / Aurora right there 3 3 0 6 custom and this is going to be it's up to you but I'm just going to leave this default at this time and click on review and launch and finally launch I'm gonna use my AWS key pair so just just go ahead and check the box and then click on launch instances okay so instance is currently being provisioned it should be up and running shortly so as you see there it is this is our instance they're just coming up at this time and this is the public IP of this instance so let's copy this public IP and what we will do is we will connect to it using putty so copy the address IP address right there I'm gonna increase the font size so that you can easily read and finally we will select our private key file so sir private key file click on open and there it is so click on yes and ec2 - user and there we are we are connected to our ec2 instance so the first thing that we will do is we will change our user to the root user so sudo su and if I have to go back to the steps over here if you remember if I get my presentation then okay there it is so we have launched our RDS MySQL instance we have launched our Amazon ec2 instance we have connected to our Linux ec2 instance via SSH and the next step is to go ahead and install my SQL slides so let's do that so the command for that is yum install MySQL and hit enter so as you see our installation is currently in progress so say yes and the installation should be completed in a bit so there it is so installation has been completed okay so my SQL client has been installed so the next thing that we will do is we will connect to our MySQL RDS instance so let's check if our instance is up and running now so click on services RDS and sometimes it takes a little bit to show up hopefully this has been provisioned so click on DB instances and then okay it's backing up right now okay let's see if it's available or not okay it is still backing up it's probably not yet provisioned let me see if the endpoint is available okay the endpoint is available so let us see if we can try and connect to this okay so you're gonna copy the endpoint from here and I'm gonna switch to my notepad now so this is the command to connect to our MySQL server using a MySQL slide so as you see we need to replace as MySQL instance DNS right here so I'm gonna copy the DNS name here and then basically this is the port 3 3 0 6 this is the user remember we have a username password and everything as my SQL DB 28 gene and once we paste this particular command it will prompt us for the password so let's copy this go back to our SSH window so it's right here so I went clear this okay there so let's now say MySQL our DNS our port and a user so hit enter it's asking me for the password so when I type the password you'll not be able to see it but we rest assured that I'm pal I'm typing the past but so password is basically my SQL DB 2018 and hit enter and if this is successful it will give us the my SQL prompt okay backing up at this time okay so I'm gonna wait for a little bit for it to come up I think it's still backing up so er still backing up so pause the video and once this becomes available I will resume recording so you're gonna give it a little bit okay I'll pause the recording now so as you see our RDS instance is available now the status is available is done backing up and if you scroll further down you will be able to see all the details so this is our endpoint this is the port 3306 right here it is not publicly accessible and these are the security groups as you see that it has created it was created two security groups one security group over here and if you notice the Sider block for inbound it is the very scene I believe this is IP address of the RDS and outbound communique is all open and then these are the other details this is the AR n this is the engine again this is the generic license that we have selected this as a default V PC our availability zone the subnets that are available in the security group and rest of the other parameters right here so let us click on the security group and review the security group okay so this is our security group right here and if you click on inbound you will see that type is MySQL TCP three three zero six but the source is essentially the IP address of the RDS instance itself so if you click on edit and instead of this what we need to since we are going to connect using an ec2 instance we are going to key in the security group for our ec2 and that is easy to MySQL s G so we are able to connect through this particular instance using our ec2 instance so click on save and for outbound all communication outputs are all open okay so now let us go back to our putty session and since our instance has been provisioned now we should be able to connect so let's try and connect so it's the same command my SQL my giving the DNS name of our MySQL RDS instance the port is three three zero six and this is a username my SQL DB 2018 and it will prompt us for the password so hit enter so password again is my SQL DB two zero one eight and hit enter and there it is so we now connected to our MySQL server so after we have connected to a MySQL server we will basically create a database at a table insert a few records in our data in our table and then try to retrieve those inserted records so if I switch back to my notepad I'll basically create a database called as books DB okay I'm gonna make it books leave me right there and go here and paste it right there with a semicolon and hit enter so as you see query okay one row effector that means this particular database was created successfully we can actually check that by saying show databases and as you see our books DB is right here with the rest of the other system databases okay so after we have created our database we are going to use a database with let's say books DB yes are in Abby's name and I believe that this book Stevie my bad okay so the database has been changed to books DB now and we will create a table this is our table and let's paste it right here so we're gonna create a table away your college books with the columns book ID title author published a description and we are gonna create a primary key on book ID because that is an auto incremented value so hit enter and our table should be created so as you see our table has been created query okay it show tables and as you see our tables is right here books after we have created the books table we will insert a couple of records so I've created two records one is to learn AWS RDS so let's paste it right here so insert and all that we are providing right now is the title and the author name so let's hit enter okay it did not like I forgot the commas I need to put that God was right there okay so I forgot the codes over there guys so please ensure that you have the codes and then hit enter so as you see our record has been inserted and the same thing is going to be true for mary jane as well for my SQL tips and tricks so let's copy this right here second record and we will insert this record in our diva so as you see both of our records have been inserted if you want you can potentially create a couple of other books to say my SQL best practices we hit enter so now we have three records so let us go ahead and select the Select course we are going to type select start from books and as you see records are right here so all three records you see the book ID was an incremented incremental column so it has an incrementing value going from one two three three titles authors and we did not enter any value for publish date and description but when you try out you're more than welcome to add other other columns and other values as well so that's it from me guys for today I hope this was helpful we successfully launched a RDS instance of type MySQL that was our engine and we launched an ec2 instance install the MySQL client and also connected to our MySQL aureus server created a database created a table inserted a few rows and then finally selected are inserted ropes so one of the most important thing for this particular tutorial is please ensure that you delete all the resources that you have provisioned to exit from your zip from here exit from here and ensure that you go ahead and especially delete the RDS instance ok I mean you don't want to incur any charges on this just because you left it running and then after you've deleted your RDS instance ensure that you go ahead and delete your ec2 instance as well so you click over here on DB instances and select this and instance actions delete say no for the snapshot you acknowledge it type delete me and click on delete if you remember when we launched this instance we had actually uncheck the option of deletion protection and since we had unchecked that option this allowing us to delete this RDS instance and while it is deleting the RDS instance click on ec2 and delete your ec2 instance as well in this case just go ahead and terminate it so I'm just waiting for the page to load okay actions in state terminate click on yes terminate okay guys so that's pretty much it please post your comments if you would like me to create videos on any specific topic do let me know and I will certainly have them posted see you later thank you for your time take care bye bye

Channel: NamrataHShah

Views: 110,053

Keywords: RDS, EC2, Relational Database Service, AWS, Certification, DB, database, MySQL, Launch a MySQL instance and connect to it using Linux EC2, Associate, Professional

Id: xzCgeRxSzy4

Length: 29min 7sec (1747 seconds)

Published: Wed Oct 31 2018