In module 1: Provisioning Durable storage with S3, I discussed how to move local storage files and images to the cloud storage and how to integrate cloud files link in website sources code.
In this module2: I will migrate my local MySQL database to Amazan MySQL RDS. Therefore, my website data will be access and restore in the Cloud database instead of my local machine.
MySQL is the popular one source relational database system. By the help of AmazonRDS, it is very easy to setup, operate and scale MySQL deployment in the cloud. Amazon RDS for MySQL provides amazon clients access to the capabilities of an existing MySQL database engine. As a result, we can use our existing application and tools whose we used with our local system, now we can use those with amazon RDS. Amazon RDS automatically patches the database software and backs up the database, storing the backups for a user-defined retention period and enabling point-in-time recovery.
I will also create amazon RDS read-only replica for enhanced performance and durability for database (DB) instances. The read replica operates as a DB instance that allows only read-only connections. Your primary production DB instance as the source database when you create a read replica. Amazon RDS takes a snapshot of the source instance and creates a read-only instance from the snapshot.
I will also discuss how to create MySQL RDS instance and how I will use RDS with ASP.NET application.
Task lists for module 2
- Launch a new MySQL RDS instance
- Connect to the AWS RDS from the local machine MySQL workbench
- Create a read-replica of dinostoredb in the Amazon RDS
- Create a new connection to the replica in MySQL workbench and Test the new databases
- To changes Code by building the project, and opening it in a browser
- Check out cloud dinostoremembershipdb tables in MySQL workbench
- Check the replica database working while rebooting the primary database
The architecture of the project:
Steps for the task: Launch a new MySQL RDS instance
As my local database system is the MySQL database, I will migrate my local database in the cloud platform. Therefore, I chose MySQL RDS instance because of similarity of table and attribute formats, easy to establish connectivity, and familiar to operate and maintenance. I will launch a New MySQL RDS instance for creating my cloud database system.
- Click RDS under Database from services menu in the management console. Click “Launch a DB instance” from Instance create instance panel. Then choose a DB instance engine “MySQL” and click select.
- Now you have to choose Production database plan.Select MySQL database instance. and click Next Step.
- Now you have to specify the DB details. I chose DB instance class db2.t2.miceo -1vCPY, 1 GB memory because it is Burst Capable Current Generation. The facilities of Burst Capable Current Generation is that provide baseline performance level with the ability to burst to full CPU usage. Note: This DB instance class requires that the DB instance is in a VPC. If you want to move your existing DB to db2.t2 Instance, you must require a VPC. Without a VPC, You can’t move your existing Database.It is also well suited for the application with lower throughput and websites that require additional periodical computer cycle.
- In the DB Instance setting, You need to provide DB instance Identifier = DinoStoreInstance, Master UserName = admin And Master Password = Walaginsondy1. Note: You can choose your own password. but you need to remember the password because you will require writing this password into your source code. Furthermore, when you will connect your instance from the local machine to cloud RDS though endpoint connectivity.
- Click Next Step. In this step, we will do ” Configure Advance Settings“. On the Network & Security option, you have to mention VPC. You can create a new VPC or you can select existing VPC from drop-down options. Note: The purpose of the security group is that it allows your current IP address to connect to your EC2 instance that you will create later.
- In the Database options, put the Database name: dinostoredb. Then Launch DB Instance. After a few minutes, Your RDS instance will be created. You can view it in RDS dashboard.
- If you want to enable Automatic Backup option, select period of days; otherwise select zero days to be disabled automatic backup (disable = zero days)
- Now from your RDS instance details copy the end point of DB Instance =dinostoredbinstance when newly created instance will be available.
- Now You need to go the security group of your newly created RDS instance. You have to edit the inbound rule to allow traffic from MySQL/Aurora source from your local machine IP means (MY IP).
Note: I’ve allowed My IP because I don’t want anyone from outside of my network will able to access my database instance, mainly for security reasons. MY IP opts only allows the network IP address that is registered under my public IP address.Therefore, My local machine will able to access cloud RDS instance using my public IP address. If you use separate public IP address or move your local machine to the separate network, Your “MY IP” will be changed.
Steps for the task: Connect to the AWS RDS from The local machine MySQL workbench
In this task, I will establish a database connection with my cloud RDS instance from my local machine. Thereafter, I will create two schemas in the cloud DB instance exactly same as my local database schemas such “dinostoredb” and “dinostoremembershipdb”. I will import .csv file of product table data under the dinostoredb schema in the cloud as well.
- Go to your MySQL Workbench, click the “Manage connection” to establish a server connection from the local machine to cloud DB instance. Enter the hostname: Endpoint link of your RDS instance dinostoredbinstance.cjoncnib4rsw.us-west-2.rds.amazonaws.com (without port number: 3306). Put the username: cloud DB instance Master username: admin, password. After giving the right credential, you will able to establish. Note: If you aren’t able to establish DB connection after providing the right credential, you need to check your inbound rules from security group and VPC setup
- Now click “Connect to Database”. Now our new connection has been established with the new cloud database system.
- After connecting with Cloud database, You need to create two database schemas by the following script. “CREATE DATABASE dinostoredb;
CREATE DATABASE dinostoremembershipdb;
- Now you need to create tables under the dinostoredb scheme similar to your local dinostoredb scheme tables.Then add products attributes to the products table by uploading CSV file with the cloud S3 Buckets Image Reference field information. For checking the product table data, execute the following query: “select * from products;”. Note: Sometimes you can lose your cloud DB connection because of your network bandwidth or weak connection. In this case, again establish your connection from your local machine to cloud via MySQL workbench.
Steps for the task: Create a read-replica of dinostoredb in the Amazon RDS and Test the new databases
The previous task, I created a production database which is my source database. Now I will create a database a read-replica Amazon RDS for dinostoredb in the cloud platform. This replica database will be worked as failover as if our production database goes down accidently, replica database supports the system as a backup production database. It is a read-only connection and it also helps to increase the performance of the DB instance.
To create the dinostoredb replica database, Selecting the dinostoredb in the RDS dashboard instance page and right click the mouse pointer and select “Create Read Replica”.
- Now you have to provide the exact information for the Read-only DB instance that you previously provided your original database system.* DB instance identifier = dinostoreinstancereplica* Keep DB instance class the same as the main DB (=micro)* Pick a different availability zone from the main DB
- Click “Create Read Replica“. Your Read-only replica database will be ready in minutes. Now you can see from RDS Dashboard page, your two DB instance is “Available” status.
- This step I will connect my replica database from the local machine same way I connected previously my production cloud database from my local MySQL workbench.
- To establish the connection, You need to open your MySQL Workbench. Go to the “Manage connection” from Database tab. You need to provide the necessary credential to establish the connection such as hostname: Endpoint:
dinostoreinstancereplica.cjoncnib4rsw.us-west-2.rds.amazonaws.com (without the port number: 3306), username: admin, Password: your DB instance password.
- Click “Test connection“. The connection will be established successfully. Connect to your replica connection, It will show the exact result that your production database has been giving.
Steps for the task: Changing the code by building the project, and opening it in a browser
In the previous two tasks, we configured our Production database and replica database in the cloud. And We also made the connection from our local machine to cloud database.
In this task, I will edit the source code for change connection link in the Microsoft Visual studio. As you remember, how did I changed the URL link in my module-1, that way, I will change the connection cloud link URL with the local connection link.
- You need to go the “Web.config” file from your solution NET702.DinoStore project in the visual studio. Now change the connection strings and use the new cloud RDS strings for “DefaultConnection and StoreSqlb“, and also copied the another line with the name “StoresqlDBReplica” and put the link there to the appropriate link of cloud replica database. Note: Be sure your code has the correct user ID and password for your cloud RDS instances. Otherwise, you could not able to connect your website from the browser after compile and run through the browser.
- In the “Default.aspx.cs source code”, you need to change ConfigurationManager that is connectionStrings from ‘StoreSqlDb’ to StoreSqlDbReplica’under the “//connection string for MySQL” comment line.
The result of this change help the manage website efficiently and it will increase the performance of the system because the images of the websites will be pulled from replica database instead of the primary database. It will reduce the workload of the primary database system, and l increase the writes capacity cycles.
3. Now you need to save your project file. Then start the debug. After 100% debug, click the “Build Solution” for compiling the code and apply the changes we have done.
4. After the “Build the project“, open the project with google chrome browser. Create the new account with your details.
5. If you forward the next without giving all information, It will show the following caution.
6. Provide the username and password, answer the security question (if your account will be locked in the future, security question and answer will help you to unlock the account) and your email address. Click “Create User“. A user will be created.
7. After creating the user account, go to the database to check the newly created user whether it is created or not. As you see the following screenshot, after executing the query on users table, I got the details information of users that I recently created.
If you want to check your replica database whether the user has been created there or not. Execute the following query and you will get the same result.
Steps for the task: Check out cloud dinostoremembershipdb tables in MySQL workbench
In this task, I will check the cloud dinostorememebership database from MySQL Workbench.
1. Now I will try to login the website to check -can I log in or not with my your ID and password. Yes, I can log in my website with my user id credential.
It will create the cloud membership database table, and create one for the custom table. You should now see ‘Logout’ available as it recognises that you are logged in.
2. Now I will check the several tables of my cloud dinostoremembershipdb schema from my MySQL Workbench for checking user activities.
The my_aspner_users table contains the new user of the website:
Steps for the task: Check the replica database working while rebooting the primary database
In this task, I will check my replica database is working or not while I reboot my primary database and at that time, User is connected with dino store website. For testing this connection, I log in my Dinostore website.
- For testing your replica database working properly or not, be sure you are working Dinostore website open in your browser.
- Now go to AWS RDS instance dashboard. Right click your mouse and click “Reboot” with failover.
3. While the primary database is rebooting, I will check the website by refreshing the pages: Yes, I can still able to access my website while my primary database is rebooting because my replica database is working here backup standby database. My user is retrieving data from the replica database system.
4. I will try to login the website with my user login credential to check I can log in on the website when my primary database is rebooting:
Yes, I can login into the website while my primary database is rebooting because my user information is already stored in the replica database, and also Amazon RDS takes a snapshot of the source instance while creating the replica database as a result replica database is the mirror of the primary database.
In my previous tasks, I already checked my replica database and primary database user tables. They both contains the same information. If my primary server goes down, my replica database will take over the system activity automatically without any interrupt.
Now I will check my primary database is working properly after reboot the system by checking the status of the instance and accessing the website by apply refresh page:
The Problems can occur and solutions:
Even though I did not get any error during implementing of this module, but you can come across some errors such asCloud DB instance connection error during
Problem 1: Cloud DB instance connection error during establishing the connection from the local machine.
Solution 1: Check your hostname. Give the endpoint of respective RDS instance without the port number. DB instance name must be similar to the database instance name that provided during configuring Cloud DB instance, and your correct password.
Solution 2: If your solution 1 does not work after providing the right credential. Go to the Amazon Security Group setting for your DB instance. Check your inbound rules and MyIP. Are you changed your network location? If not, edit the rules again, check your MYIP. If you changed your local network location, edit inbound rule and pick my IP again, and Save it.Make sure you allow the MYSQL/Aurora port number 3306 for inbound traffic.
Solution 3: If your solution 2 does not work after changing the inbound rules, go to the VPC. Make sure your RDS instance is running the same VPC you have configured for that instance. Check the IP address. Another reason, you will not able to get the connection if your instance region and VPC region is different. You have to create RDS instance under the same region of VPC.
Solution 4: If the solution 3 does not work, check all steps of module 2 tasks from the begin to end. Maybe you have made some small mistake during implementing of the RDS instance.
Troubleshooting tip: You will able to solve any problems if you have the proper understanding of your system. You need to develop your technical and analytical skill for the understanding of any systems. Moreover, You can’t predict any errors because systems are unpredictable. But you need to prepare yourself to handle those unpredictable problems by reading and researching on technologies.
The next Module I will discuss “Using DynamoDB as a session State Provider”
Thank you 🙂