in this video we will take a look at using the aws data migration service to transfer
data from our my sql instance to an s3 bucket and a common scenario here is you want to
create some sort of data lake architecture and by implementing this you can take
changes that might be in your application database your transactional database that's in
something like mysql and get it all over into s3 where you can combine it with other information
and have it all stored in one location so let's get started in like everything else you can find
this from going to services and looking under migration and transfer in our case and it's
dms the first step here is to create an instance so we'll give it a name
you can put a description just you know demo whatever you want
and we will give it the smallest amount of processing
and vpc the virtual private cloud you can select the option that you have
and create this is really the core
processing behind it this is what will allow it to run and now what we need to do is create endpoints
for the instance to recognize so where does it start and where does it finish
our source endpoint in our case it is an rdsdb so it will recognize the one that we created and it
knows that it's mysql and give the admin if this is unfamiliar to you there is another video that
shows how to create a mysql instance using rds and run a test this will make sure that it can connect
successful here now let's create a new one which will be the endpoint meaning where do we want this
information to land in our case s3 we'll put in an identifier this will be an s3 type and here's
the role so here's the server access role that we created in the most recent video which was
the policy and the role again if this is not familiar to you feel free to check out the other
video on this so what we'll do is hop over to the iam dashboard and get the role that we created
now if you recall if you did watch i accidentally named it policy but it is a role copy that
arn and paste it here so now this will inherit this service will inherit all the permissions
that we created on that last video which if you recall were the ability to full permissions on
s3 so it can read and it can write on s3 which it will need here and it also has the permission to
operate as part of this data migration service now what bucket do we want to put this in
we'll use the one that we created in a previous video which is this test bucket grab that name
and put it in here you can put a folder a specific folder if you want we won't right now and let's
just test this out so all right so successful let's create it so now we have our two endpoints
we have the db the mysql database and we have the esri bucket where we want that information to land
the next step is to create a task meaning what do we want we now have an instance
running this we have a source and an endpoint but what do we want it to do
give it a name i will say this is we're going to copy it so i'll just call it copy db to s3
the instance select our sources and targets that we've created
and the different types here i think are pretty self-explanatory and what we'll want to do is
not only migrate everything that exists today but also continue to replicate it and we'll actually
have to make another change for that to work and specifically that means being able to recognize
every single event that happens so when you insert a new record delete something
it needs to be monitoring and replicating that as well so let's select that one and here's that
note saying hey if you want to do this you need to have your logs
set to row level changes so let's hop back into our rds because that's where we can change that
setting let's select parameter groups
i'm going to create a new one that has this setting enabled you could adjust one that
exists already but i think a more appropriate thing to do would be to create a new one that is
you know exactly what you want
now let's go in here and search first click edit parameters and let's find the one we want
in this case it's bin log and it's bin log format the default is mixed but if you recall
it said it must be set to row for those ongoing changes to be captured and save it
if we double check we can see it's there okay so we've we've satisfied that requirement
there are a bunch of other settings here as well we're going to leave these
on as the default and now down here we can actually pinpoint exactly what we want this
service to look for in our rds instance so you know is there a particular schema
or tables that we only want to focus on that's where you can modify this here
as an example let's connect to our mysql instance to prove how this works
let's create a new database
call this testdb
and within it
we will let's create um let's create a table
also called test table and what we'll do then is say hey we only want this
migration service to look where it falls within this testdb we don't want any other
databases or schemas to be recognized let's add some values to this table
we'll update this to say only look where the schema which in the case of what we just did is
really the equivalent to a database as it was designed
testdb only look from there create that task
now this is running we can see it's full load ongoing replication so we would expect
those initial values to be put in to the s3 bucket they should have been captured so this actually
failed and i think i know why so let's let's take a look the logs must be set to row now
we did do that we did create that group and we said look at row format but what happened
was we actually didn't apply that group to the mysql server so that parameter group with that
setting exists but it's not being used so let's go back to the db instance and make that change
go to configuration the parameter group is still on that default meaning we need to use
that demo one that we created so this actually proves that you do need to have that row setting
for this to work now let's modify and flip that
let's find the
parameter group there it is let's use demo group now this should have that row level logs enabled
continue and we're going to have to apply this immediately for this for the sake of time
so modify click apply immediately and modify db instance
this might take a few so i'll pause this and we'll wait
okay so now if we refresh we see this is pending a reboot so we actually need to reboot the server
for this to take effect so i'll go ahead and do this pause the video while we wait
now it's it's ready this parameter group is in sync and let's try this again
restart
and now it's going to try again to first copy everything and then second
once it's successfully copied it'll continue to monitor those logs okay so now this is running
and after some time we can say load complete and replication ongoing let's
check our s3 bucket to confirm that that those files made it over there
and here it is testdb right down to the table and it drops it in as a csv let's take a look at this
we have those two records so pretty neat it just it recognized those
those records that were in our my sql copy them over to s3 now let's see what happens when we add
some more information let's add some new records or delete something and see what kind of you know
what that looks like how quickly it captures it and let's let's see what that looks like
so i'll just go in here and add a new one
i'm doing this very now this is a very simple example but you can imagine if you have
you know some sort of live application and you're constantly adding data this is pretty
valuable to to be able to put it all into one place that can act sort of like a data lake
and now for pretty quickly you can see that here's here's the next one and that was only
a few seconds steve there it is and you see that indicator of i for insert so that's helpful for
maybe future etl processes that you can pick out what was an insert what's what's a delete
uh let's let's delete one here delete john sorry john
go back
refresh not yet we can take a look at the statistics so it did recognize it
it picked it up so maybe we just need to go um try again refresh
so okay this is a bit of a little little bit there there it is a little bit of a lag but
still pretty pretty solid um let's take a look this one should be the delete
there you go see a d for delete and the record
so within just a few seconds really you know that information is being replicated over
and we we can monitor
i hope you found this helpful and thanks for watching