Learn how to create a Database Migration Service replication instance, identify the various configuration steps involved, and successfully connect to two different database endpoints.
- [Instructor] In this video, we're going to use the database migration service to perform a heterogeneous migration from the existing MySQL instance that we already have in RDS to a new RDS instance for the postgres database engine. Before we go into RDS, we'll need to create a security group to allow postgres traffic into it. Let's go to the EC2 section and on the left hand side under network and security, choose security groups. We'll click create security group and give it a name. PostgresFromWorld. This will allow port 5432 from anywhere.
Click add rule. Scroll down to the predefined postgres which fills in 5432 and choose anywhere. Before I hit create, I have to emphasize just one more time that this is not a suitable security exposure for this database for any real life production workload. We'll click create. Having created that, we'll head back out to the AWS main console and go into RDS. Click DB instances and launch DB instance. This time we'll choose postgres.
Click the blue select button. Much like with MySQL, we'll select the Dev/Test configuration. Choose next step. We can keep the default license model and DB engine version, and for instance class, we can select the same as the MySQL, t2.small. Although, you could make this bigger or smaller, if you like. Multi-AZ, we don't need. Scrolling down, we need to give some names. The DB Instance Identifier and user name. And I'm going to make this similar to the way that we named the ones for MySQL. So we'll call this psql-rds-demo.
Although to be careful, I have to use hyphens and not underscores here. And we'll call the master user psql underscore rds user. And I'll provide a password. Click next step. On this page, we can keep the default VPC and subnet group, as well as the publicly accessible flag set to yes. Under security groups, I'll search for my PostgresFromWorld option. Here we can set-up the default database that RDS will create in this database engine when the RDS instance comes up.
It can be basically anything that we like, because we're not going to use it. We're counting on this RDS instance to be the target for our replication using database migration service. Encryption is set to yes and that's fine. Everything else can stay at default and we'll launch DB Instance. While that comes up, let's go back to the main AWS console. And actually, before we go to database migration service, back into RDS. There's something that we need to change on our MySQL instance. As I said, RDS takes care of the provisioning and creating of your database whenever you stand up a new instance.
That means that a lot of the configuration parameters that you would normally have to configure on your own are taken care of by RDS. But what if you need to override them? We'll find momentarily that when we want to set-up continuous replication using database migration service, that the default parameters included in the MySQL parameters set are not good enough. We need to make a change. If we expand MySQL and look at the second tab with a magnifying glass on it, we see something called parameter group. This says default.mysql5.6.
Let's look at parameter groups over here on the side menu. You can see default.mysql5.6 right there. If we click into it, we can see all the parameters that were set for this RDS instance. Let's go back by clicking on parameter groups. What we want is to modify this so we have a few options that are different. Click parameter groups up here at the top, and then create parameter group. Here you can see that the parameter group that we're going to create will be based on the existing mysql5.6 group, which is great because we don't want to have to re-type all of those parameters that we saw on a previous screen.
We just want to tweak a few values. So we'll give this group a name. And we'll call it mysql-params-for-replication. And why don't we say mysql5.6, so we know what it's based on. Includes parameter values for replication. So now you can see that we have the parameter group, the new one in the list here. In order to actually edit the parameters, we need to click that button up above. Edit parameters. Here you see everything that we saw before, only now we have text inputs and drop downs.
The parameters we want to change are the following. Search binlog underscore format. In order to achieve ongoing replication with data base migration service, we'll need to set the parameter for binlog_format to the ROW value in the MySQL database. This isn't necessary for a one-time migration, but it is necessary if we want to continue to replicate. Since we want to do that later in the demo, we'll set it now. The other thing that we want to change is not necessary for replication, but it is useful. In order to allow us to create functions and triggers and things like that in the MySQL database, we could change this parameter.
Log binary trust function creators. And we'll set that to one. So save these changes. Now we have a parameter set that's just like the default mysql5.6 set, but with two exceptions. What we want to do is associate this new parameter set with the existing database. So we go back to instances, select MySQL, choose instance actions, and go to modify. On this page, we can modify all sorts of things about the RDS instance, including instance class and it's security groups.
Some of these changes would actually require a restart of the database instance. Down here under database options, we have the DB parameter group selection. We have two options now. We will select mysql5.6-params-for-replication. There's a very important checkbox here. Apply immediately. For changes like this that are not going to harm any ongoing transactions, we're going to check this and hit continue. You may want to take notice of this message that pops up. If you choose to apply changes immediately, any changes in the pending modification queue are also applied.
So if any of those modifications that we had not done yet required down time, the reboot would occur at this time. Let's hit continue. We can see very clearly that there is only one change happening in this change set. And it's the DB parameter group. And it's going to apply immediately. And now we'll modify DB instance. So you can see, here the parameter group has already changed, but there is a status of applying. It will take a few minutes for that to take effect. Meanwhile, let's collapse the MySQL and take a look at our Postgres. Sure enough, it's available. So now we can exit RDS, head out to the main AWS console, and go into the database migration service.
Here in DMS, we want to go with get started. You can see a picture here of the overall workflow. We're going to take our source database through a replication instance to the target database. So the first thing we'll have to do is create a replication instance. Click next. Alright, we're creating a replication instance. There's a pre-filled name here, which we can keep. And description. The instance class is a dms dot star kind of class. These are distinct, but similar to EC2 or RDS instance types.
The instance class dms.t2.medium is sufficient for the demo that we're going to perform here. Just make sure to pay attention to the teardown video later, so that it does not keep running and charge you money. We'll stay at the existing replication instance version in the default VPC. We don't need to be multi-AZ. And publicly accessible is fine. Scroll down and choose next. At this point the replication instance is being created in the background. One thing I'm going to point out and that you should know for later, is that this instance will not appear in the EC2 instances lists under EC2.
It's its own thing that stays here in the database migration service console. Because it can continue to charge money the same way that a running EC2 instance could, you want to make sure to remember that. Now at this point, we have a left and right side. The source and target database. What we're doing in this step is we are creating endpoints. These are connections to databases that apply not only to this replication instance, but will be saved by DMS to be reused later if we so wish. So we'll give them descriptive names. This will be the MySQL endpoint.
The source engine is mysql. The server name is the one from RDS. The easiest way to find this will be to open up a new tab. I'm going to go to the AWS console home icon here, but instead of clicking it, I'm going to right click and choose open link in new tab. This way I keep this window open, but I can also go navigate to RDS. In the second tab, I'll go to RDS and look at my instances. Click DB instances and then MySQL. Expand it and as we've done before, let's copy the endpoint.
Now go back to tab one. I'll paste in the server name, and just like we did in MySQL workbench, I'm going to backspace over the port and colon. And put the port 3306 separately here in the port field. Because I've not brought my own certificate, I'm going to say SSL mode is none. Just be warned that when you do this, the data in transit will not be encrypted. And I'm going to give my user name, which was mysql_rds_user. And the password. At this point, we would be able to run a test to make sure that the replication instance can reach the RDS data base.
However, that replication instance is still being created, so for now, we'll move on to the target side. This will be our postgres endpoint. It's target engine is of course postgres. The server name we'll get from RDS on the other tab. Collapse MySQL, open up Postgres, and copy it's endpoint. Back on the first tab, we'll paste it in, backspace over the port and colon. And put the port here. 5432. SSL mode again is none.
And I'll give the username. Psql underscore rds underscore user. And the password. Here I'll give the database name. We'll call it postgres_demo. Now that we've set-up both sides and we can see from the top that the replication instance has been successfully created, we can scroll down to the bottom and test our connections. Click run test. And we can see that the MySQL side has connected successfully. Let's do the same on the postgres side.
So we can actually see that I got an error here. And that's because I mistyped the database name. It tells me database postgres demo does not exist. If you get an error in your connection, this is where it will show up and you'll see the detailed error message. So let's clean that up. We called it psql demo after all. And run the test again. It says the connection is already being tested, but I think we can safely ignore that warning. And now the postgres connection is successful, as well. Click next. You have to scroll to the top here and now you can see that our next step will be to create the migration task.
Join AWS architect Brandon Rich and learn how to configure object storage solutions and lifecycle management in Simple Storage Service (S3), a web service offered by AWS, and migrate, back up, and replicate relational data in RDS. Find out how to leverage flexible network storage with Elastic File System (EFS), and use the new AWS Glue service to move and transform data. Plus, learn how Snowball can help you transfer truckloads of data in and out of the cloud.
- What is data management?
- AWS S3 basics
- S3 bucket creation
- S3 upload and logging
- S3 event notifications
- S3 data lifecycle configuration
- Working with Amazon Elastic Block Store volumes
- Creating and mounting an EFS
- Creating an AWS RDS instance
- RDS backup and recovery
- Moving data with AWS Database Migration Service
- Moving data with Data Pipeline and Glue