Introduction to AWS Database Services
A free video tutorial from BackSpace Academy
The fastest route to cloud certification.
4.4 instructor rating • 3 courses • 219,105 students
Learn more from the full courseAmazon Web Services (AWS) Certified - 4 Certifications!
Videos, labs & practice exams - AWS Certified (Solutions Architect, Developer, SysOps Administrator, Cloud Practitioner)
43:38:09 of on-demand video • Updated July 2020
- You will be fully prepared for the AWS Certified Solutions Architect Associate, AWS Certified Developer Associate and AWS Certified SysOps Administrator Associate exams.
English Welcome back to Backspace Academy. In this lecture we'll have a look at the different options that are available for running databases on AWS will then have a look at a database example and finally we'll finish up with a hands-on lab where we'll be creating a database server on AWS and then remotely connecting in to that database the relational database service or RDS for short is a fully managed database service that makes it easy to launch database servers in the AWS cloud and scale them when required the RDS service can launch service for mySQL including variations of the mySQL database engine with MariaDB and Amazon's own enterprise version of mySQL Amazon Aurora, standard postgre SQL is also available and also available as Amazon's Enterprise Aurora postgre SQL, Microsoft SQL server and oracle are also available Amazon DynamoDB is AWS as no SQL database as a service it's a service service like Amazon s3 and as such you don't need to worry about the underlying infrastructure behind it AWS takes care of everything for you and it provides high speed extremely low latency performance Amazon redshift is a fast fully managed petabyte scale data warehouse that is based upon the postgre SQL database engine if you're looking for a big data storage solution redshift is perfect for this Amazon ElastiCache is an in-memory data store or cache in the cloud it allows you to retrieve information from fast fully managed in-memory caches instead of relying for slower disk based databases the AWS database migration service or orchestrates a migration of databases over to AWS easily and securely it can also migrate data from one database engine type to another totally different database engine type for example you can use it to migrate from Oracle over to Amazon Aurora Amazon Neptune is a fast reliable fully managed graph database service it has a purpose-built high performance graph database engine optimized for storing billions of relationships and clearing the graph with millisecond latency so let's have a look at how we could use these database services let's say we have an on site Oracle relational database and we want to migrate that over to Amazon Aurora on the AWS cloud the first thing we could do is to launch an RDS instance in our virtual private cloud and remember AVP see it's our own private space within the AWS cloud and no one can enter it without us allowing them to enter we could use a database migration service to migrate that data in that on-site Oracle database over to a target RDS Amazon Aurora server now let's say our new database is becoming overwhelmed with requests for frequently accessed data and we would like a high-speed way of accessing that frequently accessed data and this is where ElasticCache can help us we can put an ElasticCache node in front of that our RDS instance and that will cache our frequently accessed data and because it's delivering that data from memory and it's not delivering it from a hard drive it will be delivered with very low latency and at the same time the load on our database will be massively reduced and any requests for anything that is not in the elastication will be simply forwarded to the RDS instance and that way we have high-speed access to our frequently accessed data while at the same time we can still access our less frequently accessed data directly from our RDS instance coming up next we'll be doing a hands-on lab so make sure that you download the introduction to AWS lab notes that come with this course we'll be launching a mySQL database instance and then we'll be connecting remotely into that database instance ok so I'm in the AWS management console now we go to services to the database services and ideas now that will take us into the RDS dashboard but if we don't have any RDS instances created previously then it will go into a welcome screen like this so we're just going to go to the left hand side here click on instances and we're going to launch a DB instance now we have a number of options available for our database engine we have Amazon Aurora which as we said previously is AWS's mySQL enterprise class database and that is not eligible on the free tier so we're not going to use that for now and we have the mySQL Community Edition and again there we've they've got the Amazon or or in case you missed it previously they put it up there but we're not going to use it because it costs us money Maria DB again is a mySQL compatible database and again if you missed it there we have Amazon Aurora as well which we might be selecting PostgreSQL Oracle and Microsoft sequel server so to make sure that we don't get a bill at the end of the month we're going to click on this checkbox here saying free tier eligible only and there we can see Amazon Aurora it's not eligible for the free tier so we go into mySQL and we can see Amazon or or there is grayed out again we can't select it and we're going to select the mySQL Community Edition and again we make sure that we have this option here available it says only show options that are eligible for the RDS free tier we don't want to get a big bill at the end of the month now if we scroll down we have a number of options here so the only one that we really need to be concerned with is the instance class so we're going to use a db2.micro which is a pretty small instance and it's on the free tier we don't have any other options for storage type other than SSD where we're working on the free tier so we leave everything there as it is now we go into the settings of our database service so our database server needs to have a DB instance identifier it needs to have a name so I'm just going to call this one backspace intro - AWS now that needs to be unique within your AWS account for DB instances next we're going to create a user and I'm just going to call the master user admin and I'll give that master user a password and that password has to be at least 8 characters long so once we have completed that we've put in our name of our database we've put in our username for the master user of this database on this database server and we've put in our master password and we've confirmed that password we click on next step now we don't need to configure any of these advanced settings for network and security do we just leave the default settings as they are but as we get more into RDS and other services later on in the course we'll be getting more involved in this sort of stuff but for now we just want to just get our a dip our toes in the water or so with AWS now we have an option to create a database on launch with this database server so we're just going to create a database called test if we leave it blank then there will be no databases on this database server and we can create those later on both but we'll just have one ready to go and we'll call it tests now we don't need to worry about backups because this is just a lab we're going to be doing it on the on the free tier we just don't want to have any hassle with that so I'm just going to change the backup retention period and that will mean that it will disable automated backups for the instance we don't need to worry about monitoring or maintenance we just leave that as it is again this is just a hands-on introduction to RDS and then we can launch that DB instance after a certain amount of time that will be acknowledged and we can view our DB instances and we can see that it has a status of creating and after a while it will go to being available for use, okay so after a few minutes or quite a few minutes we've got our mySQL database server on RDS up and running and it's status is available so the thing that we need to understand here is that we can now connect it through to this RDS instance through an endpoint and that endpoint is here so we need to take note of that and the the part that we're interested in is the endpoint the main so everything there finishing with the Amazon AWS comm and we don't do the :3306 we just select that so I'm just going to select that now because we're going to be using that later on now before we do that we need something we need some software to connect into our mySQL instance so we need to go to the mySQL home page and then and there is a link in the lab notes to this to download the mySQL workbench and install it to our system ok so here I am in the mySQL workbench and down the bottom there we can see with that we have the mySQL connections and we don't have any as yet so we just click on the plus sign next to it and it will add our connection to our RDS interent instance so we just give that a name so it's going to call it back space that will be fine now a host name is going to be that connection string that we got or that domain that we got from our RDS console and it's not going to have the :3306 on the end of it it's just going to have the main details the hostname details and the 3306 is our port number which is already detailed there on the screen now our username is going to be that master user that we set up when we created our RDS instance and I used admin and that is all what we knew we just need to use them we need a host name and we need the port that we're connecting on so if we just test that connection now it will ask for a password and we've successfully made that mySQL connection so we just click on OK and we can add that to there so once we have created that connection we can obviously connect into our database so all we need to do is double click on that and there we have we've connected into our RDS database and we've got our mySQL dashboard they're set up now if we look down here under schemas we can see that we have that test database that we created or that we requested to be created in our launch configure or when we're doing our configuration for our launch details of this RDS instance so if we click on that on that information icon next to tests that will give us some information on that ok and there we can see we've got our back space database with a test schema in there so our dart our server back space and our test database inside there so there we have it we've successfully created an RDS instance and we've connected into it using mySQL workbench which is pretty great now that you've successfully connect it in using the mySQL workbench I'm going to show you how to connect in within mySQL shell software and that will allow you to do command-line work such as issuing SQL commands to your RDS entrant instance so the first thing we need to do is to install or download the mySQL shell and there is a link in the lab notes to that URL to do that now when you get there just scroll down select your operating system if you're on Windows don't do this this installer for Windows it will install my sequel and all this stuff you don't really need all we need is this 16.5 MB file down the bottom here so just download that if you're on a Mac it will look something like this so you will have a dmg archive that you you can download and extract after you've downloaded, ok so now that we're in the mySQL shell we can connect into our RDS instance so the way that we do that is we do back slash not forward slash back slash connect and then we do our username which was admin and then we do @ and then we do that that endpoint our RDS endpoint, so we just jump back into the RDS console we'll select that endpoint and copy it and bring it over now we don't have to worry about removing the :3306 we can leave it there or we can take it off and it will default to 3306 anyway enter our password that we created and that should connect us in in a short amount of time and there we go now what I'd like to do is just issue some SQL commands to the database so I need to change to a sequel mode so again we do back slash SQL and I can issue a command now so show databases now make sure that you put a semicolon on the end of your sequel commands otherwise they're just going to hang them and then we can see we've got our our standard mySQL database is here and down the bottom we've got our test database that was created for us by the RDS service now the test database will be empty so I'm just going to select one of the system databases air so the my sick one will be fine so I just to use and then that schema or that database that I'm going to use so mySQL and will be the one that I use and then a schema is set to mySQL, now when you do that command make sure that you don't put the semicolon on the end of it otherwise it'll hang again now I just want to issue another SQL command again so we can show schemas this time why don't you know will show tables actually and that will list the tables for the mySQL database and there you go so there's all those standard tables that you would have in there so we've managed to connect in using the command line interface or the shell mySQL shell and we've switched over to the SQL mode and we've run SQL commands so what we're going to do now is we're going to jump back in to the AWS RDS console and we're going to delete all this to make sure that we don't get a bill at the end of the month and back in the RDS console we go to or we first of all we select our mySQL instance and we go to instance actions and then we go to delete we don't want to create a final snapshot and then we click the acknowledgment that we won't be able to get this back after we've deleted and we click on delete and that will be deleting and eventually it will disappear from the screen if we keep refreshing the screen here so there we have it we've created that instance and we've connected in and now we're cleaning it all up afterwards so I look forward to seeing you in the next lab