More things from AWS CLI

Exporting RDS Parameter Groups

aws rds --profile aws_profile describe-db-parameters --db-parameter-group-name paramter_group_name

Creating a Read-Replica of an RDS instance

aws rds create-db-instance-read-replica --db-instance-identifier $UPGRADE_TARGET --source-db-instance-identifier $SOURCE_INSTANCE --profile $AWS_PROFILE

Upgrade an RDS instance

aws rds modify-db-instance --db-instance-identifier $UPGRADE_TARGET --engine-version $UPGRADE_VERSION --profile $AWS_PROFILE --allow-major-version-upgrade --apply-immediately

Promote a Read-Replica

aws rds promote-read-replica --db-instance-identifier $UPGRADE_TARGET --profile $AWS_PROFILE

Rename an RDS instance

aws rds modify-db-instance --db-instance-identifier $SOURCE_INSTANCE --new-db-instance-identifier $SOURCE_SAVE_NAME --apply-immediately --profile $AWS_PROFILE

Enable Multi-AZ on an instance

aws rds modify-db-instance --db-instance-identifier $UPGRADE_TARGET --multi-az --apply-immediately --profile $AWS_PROFILE

Finding IPs in a Subnet

aws ec2 describe-network-interfaces --filters "Name=subnet-id,Values=subnet-subnetid" --region region_name

MySQL Queries I don't want to forget

Filterable process list

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

Replication Commands

show master status;

show slave status\G

Finding old databases

select table_schema, table_name, create_time, datediff( create_time, now()) from tables

   where table_schema like 'filter%' and table_name = 'known_table'

    and  datediff (create_time, now()) <  -7







MySQL Metrics that matter.

Finding metrics that matter in MySQL.

Having now transitioned into a role that requires MySQL knowledge instead of Postgres knowledge, it is time to learn some new queries. 

Total database size

SELECT table_schema "Data Base Name",
     sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
     sum( data_free )/ 1024 / 1024 "Free Space in MB"
 FROM information_schema.TABLES
 GROUP BY table_schema ;

Free Space here is an interesting notion. This "free space" is the space available inside the engine's (InnoDB or MyISAM) table spaces. This is not a reflection of total available disk space.

Top 20 tables by size

SELECT       table_schema as `Database`, table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
     FROM information_schema.TABLES
     ORDER BY (data_length + index_length) DESC 
     LIMIT 20;

If your database is small (relatively few tables), you can take out the limit clause and see all tables.

More updates as I find fun new queries.

Finding stuff in aws cli

TL;DR

Finding default routes: aws ec2 describe-route-tables --query "RouteTables[*].Routes[?DestinationCidrBlock==\`0.0.0.0/0\`]"

Finding Routes that terminate in instances: aws ec2 describe-route-tables --query "RouteTables[*].Routes[?DestinationCidrBlock==\`0.0.0.0/0\`].{Destination:DestinationCidrBlock, Instance:InstanceId, State:State}"
Finding the VPC name for a route that terminates on an instance: 

aws ec2 describe-vpcs --vpc-ids $(aws ec2 describe-route-tables  | 
     jq '.RouteTables[] |  { VpcId, Instance: .Routes[] | 
       select( .InstanceId != null)  }' | 
     jq '.VpcId' | sed 's/\"//g')  |
     jq '.Vpcs[] |
       { VpcId, Name: .Tags[] |
       select ( .Key == "Name") }'

Finding the newest snapshot

aws --profile=prod ec2 describe-snapshots --filter 'Name=volume-id,Values=vol-1c6e8b1a' | jq '.[]|max_by(.StartTime)|.SnapshotId'

Find out how many 0b files in an s3 bucket

aws s3 ls --recursive s3://<folder_name> | awk '{if ($3 == 0) print}'

Background

In my new gig, I'm learning lots more AWS, so I'll be sharing some AWS love here now. The last few weeks, I have been replacing NAT instances with NAT Gateways. It is a great way for your VPC VM's to get to the internet without having to manage a VM.

Well, I thought I was done so I decided to find a way to audit my assumptions. Well, I was wrong. The TL;DR above shows what it took to find the default routes that terminated specifically in instances, then from there find the name of the VPC. Now that I have found those, I know how many more NAT gateways I need to build. 

Based on a recommendation from a coworker, I add jq which is a very powerful JSON processor that blends awk, sed and grep for JSON objects. It was a bit complex to learn, but now that I have some skills, it will be very handy in the future!

Touchy DNS in Kitchen/Vagrant VM's?

TL; DR

Add to your drive section in .kitchen.yml

1 2	customize: natdnshostresolver1: "on"

Symptom

I often work remotely. It's a beautiful thing; however, "Thar be dragons!" Does your cookbook need private DNS? Resources behind the firewall? Well, let's connect to VPN. WooHoo... Wait... The chef-client install failed. It can't get to the internet? WHAT? 

Solution

My VPN client has split tunneling DNS and split tunnel networking. So... How do you working around it? Tell kitchen to tell vagrant to use your host workstation as the DNS server.

Git Pull request Kung Fu

TL;DR

Merging via command line

If you do not want to use the merge button or an automatic merge cannot be performed, you can perform a manual merge on the command line.

Step 1: From your project repository, bring in the changes and test.

git fetch origin
git checkout -b tscalf-unit_tests origin/tscalf-unit_tests
git merge master

Step 2: Merge the changes and update.

git checkout master
git merge --no-ff tscalf-unit_tests
git push origin master

CoffeeScript Substrings

TL;DR

1 2 3 4 5

run_list = [ 'role[flexo]','role[bender]','role[roberto]'] run_list.forEach (result) -> role = result.match /\[(\S*)\]/i alert(role[1]) # Should yield a message box with "flexo" then "bender" then "roberto"

What I was looking for:

Given a Chef run_list, I needed the name of each role via CoffeeScript. I am working at having hubot tell our deployment processor to run a release. I wanted to help the user find the application they were looking for.

Helpful Links:

Try CoffeeScript - Paste your coffeescript and test it in browser

Hubot on GItHub
NodeJS library for Chef