In RDS instances the FILE privilege for MySQL is not applicable

Typically in MySQL instances that run on standalone EC2 boxes or local installs, you can set FILE privileges to write a SQL query to write the output into a local flat file such as

$mysql -u$MyUSER -p$MyPASS -h$MyHOST --port=$MyPORT --socket=$MySOCKET -e "select name, username, email, registerDate, lastvisitDate from TestDB.Employee where username not like 'TestUser' INTO OUTFILE '/tmp/dbout.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

In RDS instance, the above query won't work because we don't have access to local file system of RDS, so "/tmp/dbout.csv" cannot be created. You will see an error like

"ERROR 1045 (28000): Access denied for user '<rds_user'@'%' (using password:YES)"

Instead, you would have to run the query with "--execute" switch on the remote EC2 instance and dump the query results to a flat file by separating out the results using "sed" utility as documented on AWS developer forum - threadID=41443

$mysql -u$MyUSER -p$MyPASS -h$MyHOST --port=$MyPORT --socket=$MySOCKET -e "select name, username, email, registerDate, lastvisitDate from TestDB.Employee where username not like 'TestUser';" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g'  >/tmp/dbout.csv

Finding a process that is consuming CPU excessively

• Look at top output

• Next check ps output to see the location on the file system where the process is running from 

$ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10
%CPU   PID USER     COMMAND
59.5 11286 ec2-user      /bin/sh /tmp/ismp002/1978898.tmp
59.1 22608 ec2-user      /bin/sh /tmp/ismp002/2326448.tmp
 5.8 22861 ec2-user     ./engine -Djmx_port=5555
 4.7 22865 ec2-user      ./engine --innerProcess

• Run strace to see what calls are consuming the cpu cycles

$strace -c -p 11286
Process 11286 attached - interrupt to quit
Process 11286 detached
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 90.77    0.667227         273      2447           clone
  6.38    0.046881          10      4892      2446 wait4
  1.33    0.009747           0     22014     19568 stat
  1.02    0.007489           0     31802           rt_sigprocmask
  0.12    0.000864           0      4893           rt_sigaction
  0.11    0.000826           0      2446      2446 ioctl
  0.11    0.000819           0      2446           read
  0.09    0.000659           0      2446      2446 lseek
  0.08    0.000579           0      2446           rt_sigreturn
------ ----------- ----------- --------- --------- ----------------
100.00    0.735091                 75832     26906 total

• In the above case the /tmp/ismp002/1978898.tmp file was not on the system, so it looks like a zombie process that was left running on the system
• do a kill -9 on the unwanted processes

Updating Route53 A records for a hosted zone using restricted IAM policy

If you have an application that is dynamically tearing down ELB's and creating new ELB's with instances in its pool, you will be in a situation where Route53 recordsets have be frequently updated as well. To manually update the A records of newly created ELB's can be tedious. Instead, you can create a restricted IAM user or group policy that allows for "ChangeResourceRecordSets" privilege such as below:-

**************
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "route53:GetHostedZone"
                "route53:ChangeResourceRecordSets"
            ],
            "Resource": "arn:aws:route53:::hostedzone/<ZONE_ID>"
        },
        {
            "Effect": "Allow",
            "Action": [
                "route53:GetHostedZone",
                "route53:ListResourceRecordSets"
            ],
            "Resource": "arn:aws:route53:::hostedzone/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "route53:GetChange"
            ],
            "Resource": "arn:aws:route53:::change/*"
        }
    ]
}
**************

Once the above policy is set, you can use AWS CLI as below to CREATE, DELETE, UPSERT A records on Route 53:-

***************
$aws route53 change-resource-record-sets --hosted-zone-id <ZONE_ID> --change-batch file://opt/sampleupsert.json --profile <domain>
{
    "ChangeInfo": {
        "Status": "PENDING",
        "Comment": "string",
        "SubmittedAt": "2014-09-20T12:40:49.159Z",
        "Id": "/change/CNZHKUS1ZF9Z9"
    }
}
***************

Once the change is submitted, you can query for the status till it shows "INSYNC" as below:-

***************
$aws route53 get-change --id /change/CNZHKUS1ZF9Z9 --profile <domain>
{
    "ChangeInfo": {
        "Status": "INSYNC",
        "Comment": "string",
        "SubmittedAt": "2014-09-20T12:40:49.159Z",
        "Id": "/change/CNZHKUS1ZF9Z9"
    }
}
***************

and the sampleupsert.json file which is passed as argument to --change-batch parameter in "change-resource-record-sets" method of AWS CLI, looks like

***************
{
  "Comment": "string",
  "Changes": [
    {
      "Action": "UPSERT",
      "ResourceRecordSet": {
        "Name": "<domain>",
        "Type": "A",
        "AliasTarget": {
          "HostedZoneId": "<ZONE_ID>",
          "DNSName": "<DNS_NAME of ELB>",
          "EvaluateTargetHealth": false
        }
      }
    }
  ]
}
***************

The above can also be done using Boto library and simple python code as below:-

***************
import boto.route53


conn = boto.route53.connect_to_region('us-east-1')
from boto.route53.record import ResourceRecordSets
zone_id = "<ZONE_ID>"
changes = ResourceRecordSets(conn, zone_id)
change = changes.add_change("UPSERT", '<domain>', "A")
change.set_alias("<ZONE_ID_2>", "<ELB A record>")
changes.commit()
***************

If ZONE_ID is not known, then you have to modify the above policy to allow listing of zones and then iterate through the code as shown in this blog - managing-amazon-route-53-dns-with-boto

Domain delegation to Amazon Route 53

If you have domains registered by external domain name provider like Bluehost or Network Solutions. You can set up domain delegation to amazon Route53 service to handle sub domains more easily.

In Amazon Route 53 console, you can create a new hosted zone by clicking on "New Hosted Zone" and then specifying a name

Once you create the hosted zone, you will see NS records and SOA records as below:-

Next you will have to set up domain delegation in internal and external DNS server and add the NS records provided by Amazon Route 53 in the above screenshot. Once you have updated the DNS entries, you can run the below dig query to confirm that NS records match what has been provided by Amazon

$ dig -t NS example.mycompany.com

; <<>> DiG 9.7.1 <<>> -t NS example.mycompany.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 32360
;; flags: qr rd ra; QUERY: 1, ANSWER: 4, AUTHORITY: 0, ADDITIONAL: 4

;; QUESTION SECTION:
;example.mycompany.com.              IN      NS

;; ANSWER SECTION:
example.mycompany.com.       0       IN      NS      ns-1467.awsdns-55.org.
example.mycompany.com.       0       IN      NS      ns-540.awsdns-03.net.
example.mycompany.com.       0       IN      NS      ns-1714.awsdns-22.co.uk.
example.mycompany.com.       0       IN      NS      ns-292.awsdns-36.com.
....

IPv4 CIDR block notations for VPC segmentation

reference - http://wiki.samat.org/CheatSheet/IPv4CIDRNotation

unix grep to search for multiple filter expressions

Recently, I was searching logs for a multiple string patterns. There are multiple ways to achieve that using "grep -E <string1>  grep -E <string2>". However, the below seemed more efficient

$ grep "08/Aug.*<service name>" access_log | cut -d[ -f2 | cut -d] -f1 | awk -F: '{print $2":00"}' | sort
 -n | uniq -c

Using wget to determine if ELB is misconfigured and is attached to a private subnet

Typically, you would want ELB's to be available in atleast 2 zones in a particular region, so that if one zone goes down, the ELB in the second zone will handle all the requests. If you ELB is configured correctly for multiple zones, you can do a "nslookup" on the ELB A record and you will get multiple EIP's returned (1 for each zone)

If the ELB is attached to a private subnet, you would see a request failure using wget:-

****************
$ wget http://<elb-name>.ap-northeast-1.elb.amazonaws.com/index.html
--2014-09-08 18:08:29--  http://<elb-name>.ap-northeast-1.elb.amazonaws.com/index.html
Resolving <elb-name>.ap-northeast-1.elb.amazonaws.com (<elb-name>.ap-northeast-1.elb.amazonaws.com)... 54.92.98.228, 54.238.149.12
Connecting to <elb-name>.ap-northeast-1.elb.amazonaws.com (<elb-name>.ap-northeast-1.elb.amazonaws.com)|54.92.98.228|:80... failed: Connection timed out.
Connecting to <elb-name>.ap-northeast-1.elb.amazonaws.com (<elb-name>.ap-northeast-1.elb.amazonaws.com)|54.238.149.12|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/xml]
Saving to: `index.html'
****************

The other way to confirm and check is through the AWS console:-

In the consile make sure the "subnet id" in the ELB's availability zone's have an igw-* associated with the zones that it has been added to. ELB's need to be public subnets so that they can be accessed from outside.