Learn how to create a Read Replica from MySQL to Aurora, connect to it via MySQL Workbench, and see instantaneous changes reflected in the replica.
- [Instructor] I'm here at the RDS console looking at the details of the MySQL RDS demo database. What I'd like to do in this demo is create a read replica of this MySQL instance. So I used the checkbox to select this database and then go to instance actions. You see we actually have two options here. We can create a read replica which would be another MySQL instance or because aurora is MySQL compatible, we can create an aurora read replica. Let's select the second option. Now you can see that we're off to another page and we have a few more options.
The existing DB instance class is preselected. A db t2 dot small. Multi-AZ deployment is not something that we need right now. If we were to do that, we'd actually end up with two additional databases. The read replica and its own replica and the multi-AZ configuration. We do need to give a new instance identifier. So this is our MySQL RDS demo replica. We might put the word aurora in there to make sure it's clear but the console will tell us it's aurora.
We can accept the default VPC and subnet and like our source, we'll keep it publicly accessible, although, unless continued to say this is not really acceptable for production environment. We still want it to accept MySQL traffic from the world. The database port is 3306 just like MySQL. We can keep all the rest of the defaults and go down to create read replica. At this point, the aurora read replica is launching. If I collapse the MySQL RDS demo in line, we'll see aurora right under it in the creating state.
This step can take a while to complete. Not only are we standing up a new RDS instance in the aurora database, but aws also has to set up the replication between the two databases. So we'll wait for this to be done and then we'll see how the replication works. Now that the aurora read replica is available, let's connect to it and see what's happening. We'll expand this selection from the aurora MySQL. Hover over the endpoint, click, and then copy. Then we'll head back to MySQL work page.
We still have the previous connection open which is fine. We'll keep it. Click on the home icon over here to set up the new connection. Like before, we'll click the plus button to create a new connection. We'll give it a name. Aurora read replica. We'll get rid of the default hostname and pasting what we just copied, making sure to backspace over 3306 and the colon. Because this is a read replica, we have exactly the same usernames. We use MySQL RDS user. And the same password.
Default database will be the same as well. Let's click test connection to make sure everything looks good. Alright, successful connection. So we'll click OK. And click on it to initiate the connection. So now we have two tabs. The original source database and the read replica. You can see we've defaulted to the MySQL RDS demo here in the replica so we know that that carried over. You can also expand the tables and see everything we have in the source database. Let's go back to the SQL on tab one. And we can take that query, it selects all the salespeople.
I'll paste it here and we can see the results we had earlier. Great. Now, of course, this read replica is supposed to stay in sync. So I'm going to create one new salesperson using my name. I'll insert it to employees, I'll give myself some high employee ids so I don't conflict with anything I've create before. And then insert myself into the sales department. Undo the insert. And then commit. Now I'm back in aurora, let's see how long it takes for that to show up. Look at that. Practically instantaneous read replica with aurora.
And let's head back to the aws console. As I said, this instance can be used for variety of purposes. It makes a great endpoint for reporting or anything else that really only needs a read-only database. The other thing that we can do is in a disaster scenario, we can highlight the aurora MySQL RDS replica choose instance actions and promote read replica. What this will do is we'll make this read replica become a full fledged RDS instance of its own. At that point, you can redirect traffic straight to it and what will become a normal, full fledged RDS database but with all your data that was replicated before the promotion.
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