How to fix missing foreign keys and/or indexes – AWS DMS

AWS Database Migration Service (DMS) helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. The AWS Database Migration Service can migrate your data to and from most widely used commercial and open-source databases.

The Database Migration Service is a data mover. It creates only the structures required to migrate your data, (this is for performance reasons mainly.) Additionally, it doesn’t migrate secondary indexes, default values, procedures, triggers, auto increment columns etc. These objects/modifications need to be made after migrating the data, (and typically prior to switching the app.)

But it can be fixed by importing schema manually.

Problem

missing foreign keys and/or indexes

Solution

To fix foreign keys & indexes missing issue, follow this

  1. Import Database schema manually to RDS.
  2. Set Target table preparation mode to Truncate

Using JSON:

dms

Using DMS GUI:

dms

Now run the task.

You will see all foreign keys and indexes in target (RDS).

Howto list all instances in all regions from mutliple accounts using awscli – AWS

AWS Cloud spans 69 Availability Zones within 22 geographic regions around the world, with announced plans for 9 more Availability Zones and three more Regions in Cape Town, Jakarta, and Milan.

If you are using more than one region it takes much time to browse through all regions in a browser and check which instances are running.

To save time, we are using awscli command in a shell script which will list all instances from all regions. You can use multiple profile names.

scrot

 

You can specify multiple profile names as follows:

This will run jobs in parallel and exit when all jobs are completed.

Setting Up Ansible for AWS with Dynamic Inventory (EC2)

If your Ansible inventory fluctuates over time, with hosts spinning up and shutting down in response to business demands, the static inventory solutions described in Working with Inventory will not serve your needs. You may need to track hosts from multiple sources

Ansible integrates all of these options via a dynamic external inventory system. Ansible supports two ways to connect with external inventory: Inventory Plugins and inventory scripts.

If you use Amazon Web Services EC2, maintaining an inventory file might not be the best approach, because hosts may come and go over time, be managed by external applications, or you might even be using AWS autoscaling. For this reason, you can use the EC2 external inventory script.

You can use this script in one of two ways.

  1. The easiest is to use Ansible’s -i command-line option and specify the path to the script after marking it executable:
  2. The second option is to copy the script to /etc/ansible/hosts and chmod +x it. You will also need to copy the ec2.ini file to /etc/ansible/ec2.ini. Then you can run ansible as you would normally.

You can test the script by itself to make sure your config is correct:


After a few moments, you should see your entire EC2 inventory across all regions in JSON.

If you use Boto profiles to manage multiple AWS accounts, you can pass --profile PROFILE name to the ec2.py script.

You can then run ec2.py --profile prod to get the inventory for the prod account, although this option is not supported by ansible-playbook. You can also use the AWS_PROFILE variable – for example:

 

ec2.py

 

How to update Route53 records after EC2 instance restart

Amazon Route 53 is a highly available and scalable cloud Domain Name System (DNS) web service. If you are not using Elastic IPs for your EC2 instances, chances are stopping and starting the server will result in different IPs after the instance comes back online. If you have A records pointing to those IPs in Route53 you will need a way to update them. After the script is executed, it will automatically gather the new Public IP and update the DNS record for it in Route53.

Edit 1:

As I am getting many comments on hosted zone parsing error… I wanted to add this

Before running script please check your host name is set to fully qualified domain name (FQDN) with this command
hostname -f

or

hostname -d

or

hostnamectl

If you don’t want to set host name system wide, feel free to modify the script manually

*note: use set -ex option to debug the script

AWS Cognito Configurations

Introduction

Cognito is the AWS solution for managing user profiles, and Federated Identities help keep track of your users across multiple logins. Integrated into the AWS ecosystem, AWS Cognito opens up a world of possibility for advanced front end development as Cognito+IAM roles give you selective secure access to other AWS services.

Go to AWS Cognito on the AWS console to get started!

AWS console

Initial Setup — Cognito

AWS Cognito
AWS Cognito

We will be setting up AWS Cognito, which is a custom login pool (such as login with email). Cognito IS NOT a login manager for any type of login (such as Facebook and Gmail), only for custom logins.

Let’s first make a user pool by clicking on “Manage your User Pools”. A user pool is a group of users that fulfill the same designation. The setup screen should look like this:

User Pool Name
User Pool Name

We’re gonna walk through this process step by step, so enter the Pool name of “App_Users” and click “Step through settings”. The next step is “Attributes”, where we define the attributes that our “App_Users” will have.

User Attributes
User Attributes

We now, we only want to have an email, password and “agentName”. The email is our unique identifier for a user and the password is a mandatory field (which is why you don’t see it in the list of standard attributes). We want users to be able to have a codename to go by, so let’s set up “agentName” is a custom attribute. We are only using “agentName” to show how to add custom attributes. Scroll down and you will see the option to add custom attributes.

Custom Attributes
Custom Attributes

As of the date this tutorial was written, you cannot go back and change the custom attributes (even though AWS appears to be able to), so be sure to get this right the first time! If you need to change attributes, you will have to create a new user pool. Hopefully AWS fixes this issue soon. Anyways, moving on to account policies!

Account Policies
Account Policies

So we can see here that our passwords can be enforced to require certain characters. Obviously requiring a mix of various character types would be more secure, but users often don’t like that. For a middle ground, lets just require the password to be 8+ characters in length, and include at least 1 number. We also want users to be able to sign themselves up. The other parts are not so important, so let’s move onto the next step: verifications.

Account Verifications
Account Verifications

This part is cool, we can easily integrate multi-factor authentication (MFA). This means users must sign up with an email as well as another form of authentication such as a phone number. A PIN would be sent to that phone number and the user would use it to verify their account. We won’t be using MFA in this tutorial, just email verification. Set MFA to “off” and check only “Email” as a verification method. We can leave the “AppUsers-SMS-Role” (IAM role) that has been filled in, as we won’t be using it but may use it in the future. Cognito uses that IAM role to be authorized to send SMS text messages used in MFA. Since we’re not using MFA, we can move on to: Message Customizations.

Custom Account Messages
Custom Account Messages

This part is cool, we can easily integrate multi-factor authentication (MFA). This means users must sign up with an email as well as another form of authentication such as a phone number. A PIN would be sent to that phone number and the user would use it to verify their account. We won’t be using MFA in this tutorial, just email verification. Set MFA to “off” and check only “Email” as a verification method. We can leave the “AppUsers-SMS-Role” (IAM role) that has been filled in, as we won’t be using it but may use it in the future. Cognito uses that IAM role to be authorized to send SMS text messages used in MFA. Since we’re not using MFA, we can move on to: Message Customizations.

Custom Account Messages
Custom Account Messages

When users receive their account verification emails, we can specify what goes into that email. Here we have made a custom email and programmatically placed in the verification PIN represented as {####}. Unfortunately we can’t pass in other variables such as a verification link. To accomplish this, we would have to use a combination of AWS Lambda and AWS SES.

SES (Simple Email Service)
SES (Simple Email Service)

Next click “Verify a New Address”, and enter the email you would like to verify.

Now login to your email and open the email from AWS. Click the link inside the email to verify, and you will be redirected to the AWS SES page again. You have successfully verified an email! That was easy.

Now that’s done, let’s return back to AWS Cognito and move on to: Tags.

User Pool Tags
User Pool Tags

It is not mandatory to add tags to a user pool, but it is definitely useful for managing many AWS services. Let’s just add a tag for ‘AppName’ and set it to a value of ‘MyApp’. We can now move on to: Devices.

Devices
Devices

We can opt to remember our user’s devices. I usually select “Always” because remembering user devices is both free and requires no coding on our part. The information is useful too, so why not? Next step: Apps.

Apps
Apps

We want certain apps to have access to our user pool. These apps are not present anywhere else on the AWS ecosystem, which means when we create an “app”, it is a Cognito-only identifier. Apps are useful because we can have multiple apps accessing the same user pool (imagine an Uber clone app, and a complimentary Driving Test Practice App). We will set the refresh token to 30 days, which means each login attempt will return a refresh token that we can use for authentication instead of logging in every time. We un-click “Generate Client Secret” because we intend to log into our user pool from the front end instead of back end (ergo, we cannot keep secrets on the front end because that is insecure). Click “Create App” and then “Next Step” to move on to: Triggers.

Triggers
Triggers

We can trigger various actions in the user authentication and setup flow. Remember how we said we can create more complex account verification emails using AWS Lambda and AWS SES? This is where we would set that up. For the scope of this tutorial, we will not be using any AWS Lambda triggers. Let’s move on to the final step: Review.

Review
Review

Here we review all the setup configurations we have made. If you are sure about this info, click “Create Pool” and our Cognito User Pool will be generated!

Take note of the Pool Id us-east-1_6i5p2Fwao in the Pool details tab.

Notice the Pool Id
Notice the Pool Id

And the App client id 5jr0qvudipsikhk2n1ltcq684b in the Apps tab. We will need both of these in our client side app.

Notice the App client id
Notice the App client id

Now that Cognito is set up, we can set up Federated Identities for multiple login providers. In this tutorial we do not cover the specifics of FB Login as it is not within in the scope of this tutorial series. However, integrating FB Login is super easy and we will show how it’s done in the below section.

Initial Setup — Federated Identities

AWS Cognito
AWS Cognito

Next we want to setup “Federated Identities”. If we have an app that allows multiple login providers (Amazon Cognito, Facebook, Gmail..etc) to the same user, we would use Federated Identities to centralize all these logins. In this tutorial, we will be using both our Amazon Cognito login, as well as a potential Facebook Login. Go to Federated Identities and begin the process to create a new identity pool. Give it an appropriate name.

create a new identity pool
Create a new identity pool

Now expand the “Authentication providers” section and you will see the below screen. Under Cognito, we are going to add the Cognito User Pool that we just created. Copy and paste the User Pool ID and App Client ID that we made note of earlier.

Authentication providers
Authentication providers

And if we wanted Facebook login for the same user identity pool, we can go to the Facebook tab and simply enter our Facebook App ID. That’s all there is to it on the AWS console!

Facebook tab
Facebook tab

Save the identity pool and you will be redirected to the below screen where IAM roles are created to represent the Federated Identity Pool. The unauthenticated IAM role is for non-logged in users, and the authenticated version is for logged in users. We can grant these IAM roles permission to access other AWS resources like S3 buckets and such. That is how we achieve greater security by integrating our app throughout the AWS ecosystem. Continue to finish creating this Identity Pool.

IAM roles
IAM roles

You should now see the below screen after successfully creating the identity pool. You now only need to make note of 1 thing which is the Identity Pool ID (i.e. us-east-1:65bd1e7d-546c-4f8c-b1bc-9e3e571cfaa7) which we will use later in our code. Great!

Sample code
Sample code

Exit everything and go back to the AWS Cognito main screen. If we enter the Cognito section or the Federated Identities section, we see that we have the 2 necessary pools set up. AWS Cognito and AWS Federated Identities are ready to go!

AWS Cognito
AWS Cognito
AWS Federated Identities
AWS Federated Identities

That’s all for set up! With these 2 pools we can integrate the rest of our code into Amazon’s complete authentication service and achieve top tier user management.

List of AWS regions and availability zones

List of  AWS Regions

This is complete list of  AWS regions available currently.

S.No Code Name
1 us-east-1 US East (N. Virginia)
2 us-west-2 US West (Oregon)
3 us-west-1 US West (N. California)
4 eu-west-1 EU (Ireland)
5 eu-central-1 EU (Frankfurt)
6 ap-southeast-1 Asia Pacific (Singapore)
7 ap-northeast-1 Asia Pacific (Tokyo)
8 ap-southeast-2 Asia Pacific (Sydney)
9 ap-northeast-2 Asia Pacific (Seoul)
10 sa-east-1 South America (São Paulo)
11 cn-north-1 China (Beijing)
12 ap-south-1 India (Mumbai)

AWS upcoming regions

 

S.No Code Name
1 N/A OHIO
2 N/A MONTREAL
3 N/A UK
4 N/A INDIA
5 N/A NINGXIA

List of  AWS regions and their availability zones

S.No AWS region code AWS region name Number Of Availability Zones Availability Zone Names
1 us-east-1 Virginia 4 us-east-1a
us-east-1b
us-east-1cus-east-1dus-east-1e

us-east-1f

2 us-west-2 Oregon 3 us-west-2a
us-west-2b
us-west-2c
3 us-west-1 N. California 3 us-west-1a
us-west-1b
4 eu-west-1 Ireland 3 eu-west-1a
eu-west-1b
eu-west-1c
5 eu-central-1 Frankfurt 2 eu-central-1a
eu-central-1b
6 ap-southeast-1 Singapore 2 ap-southeast-1a
ap-southeast-1b
7 ap-southeast-2 Sydney 3 ap-southeast-2a
ap-southeast-2b
ap-southeast-2c
8 ap-northeast-1 Tokyo 2 ap-northeast-1a
ap-northeast-1c
9 ap-northeast-2 Seoul N/A N/A
10 sa-east-1 Sao Paulo 3 sa-east-1a
sa-east-1b
sa-east-1c
11 cn-north-1 China (Beijing) N/A N/A
12 ap-south-1 India (Mumbai) 2 ap-south-1a
ap-south-1b

If you are familiar with AWS CLI you can always check regions and availability zones using following aws cli commands

Find regions using AWS CLI

Command:  aws ec2 describe-regions