Amazon RDS - Creating/Modifying Parameter Groups

The steps below will guide you through creating a new parameter group and applying it to your instance.

(This requires that you already have the RDS tools installed on your system and all environmental variables and authentication setup as well.)

1. Create new parameter group. (Replace MySQL5.5 with the appropriate value)

rds-create-db-parameter-group MyParamGroupName -f MySQL5.5 -d "Parameter group for testing"

     2. Set values for new parameter group.

rds-modify-db-parameter-group MyParamGroupName --parameters "name=max_allowed_packet, value=33554432, method=immediate"

      3. Change parameter group for existing DB instance.

rds-modify-db-instance MyDBInstance --db-parameter-group-name=MyParamGroupName

      4. Reboot DB instance to apply changes.

rds-reboot-db-instance MyDBInstance

      5. Check status of db instance.

rds-describe-db-instances

Setup for Using Amazon RDS

IN order to do any sort of MySQL configuration when using Amazon RDS, you will need to setup these tools on your workstation.

•  Download the following zip and extract it to the location of your choosing.

http://aws.amazon.com/developertools/2928?_encoding=UTF8&jiveRedirect=1



Installation:
-------------
1. Ensure that JAVA version 1.5 or higher is installed on your system: (java -version)
2. Unzip the deployment zip file
3. Set the following environment variables:
    a.  AWS_RDS_HOME - The directory where the deployment files were copied to
        check with:
           Unix: ls ${AWS_RDS_HOME}/bin should list rds-describe-db-instances ...)
           Windows: dir %AWS_RDS_HOME%\bin should list rds-describe-db-instances ...)
    b.  JAVA_HOME - Java Installation home directory
           Mac: $ JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/CurrentJDK/Home

4. Add ${AWS_RDS_HOME}/bin (in Windows: %AWS_RDS_HOME%\bin) to your path
5. (Unix only) Set execute permissions on all files in the bin directory: chmod +x ${AWS_RDS_HOME}/bin/*

Configuration:
--------------
Provide the command line tool with your AWS user credentials. There are two ways you can provide credentials: AWS keys, or using X.509 certificates.

Using AWS Keys
--------------
1. Create a credential file: The deployment includes a template file ${AWS_RDS_HOME}/credential-file-path.template. Edit a copy of this file to add your information.
     On UNIX, limit permissions to the owner of the credential file: $ chmod 600 <the file created above>.
2. There are several ways to provide your credential information:
      a. Set the following environment variable: AWS_CREDENTIAL_FILE=<the file created in 1>
      b. Alternatively, provide the following option with every command --aws-credential-file <the file created in 1>
      c. Explicitly specify credentials on the command line: --I ACCESS_KEY --S SECRET_KEY

Useful Commands for Disk Cleanup

Top 20 Largest Files/Folders
du -m --max-depth=4 / | sort -nr | head -n 20

Lists all directories over 1GB
du -h / | grep ^[0-9.]*G


File and Folder Sizes from Current directory
(du -x -s * | sort -n -k1 ) 2>/dev/null

SQL Query - Show Large Tables

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2) idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

MySQL - Change Character Set and Collation

The following statement will update all tables to utf8. Change the char set and collate values to the desired character set and set the table schema to the schema you are modifying.


Backup DB before applying changes!!


SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as stmt
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'database_name'
ORDER BY 1

Easy MySQL Backup Script

Below is an easy script for doing a quick backup of all mysql databases on the same server.


#!/bin/bash
#Dump new files
USER=root
PASSWORD=password
HOST=localhost

for i in $(echo 'SHOW DATABASES;' | mysql -u$USER -p$PASSWORD -h$HOST|grep -v '^Database$'); do
  mysqldump                                                     \
  -u$USER -p$PASSWORD -h$HOST                                   \
  -Q -c -C --add-drop-table --add-locks --quick --lock-tables   \
  $i > /root/mysql_backup/$i.sql;
tar czvf /root/mysql_backup/$i.sql.tar.gz -C / root/mysql_backup/$i.sql;
done;