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.