Activities

October 2011
M T W T F S S
« Sep   Nov »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

Amazon RDS : – How to change the mysql variables

I have asked to try AWS RDS feature for one of our heavily traffic web site. As per RDS planning and designing, it’s looks great features of managing someone who doesn’t any idea about databases. They are providing time based backup and restoration.

Here I’m listing out some of my findings

Highlights

1. Easy manageable and scalable
2. Backup and replication can be done through few mouse clicks easily.
3. Time based recovery and able to set the scheduler for the backup.
4. Can accessible anywhere from web but remote access to limited.

Drawbacks

1. No server login to RDS instances ( ssh/ftp won’t work)
2. It’s expensive compare with other normal instance.
3. We can’t change the default DB Parameter Group Name.
4. Can’t modify the parameter over AWS management window.
5. We can’t change the certain mysql variables like wait_timeout

C:\>rds-modify-db-parameter-group test-parm  --parameters "name=wait_timeout, value=86400, method=immediate"  --region us-east-1
rds-modify-db-parameter-group:  Malformed input-value:86400 is outside of range:1-28800
Usage:
rds-modify-db-parameter-group
        DBParameterGroupName  --parameters  "name=value, value=value,
       method=value" [ --parameters "name=value, value=value, method=value" ...]
        [General Options]
For more information and a full list of options, run "rds-modify-db-parameter-group --help"

Also I’m getting following errors because of this
ERROR 2006 (HY000) at line 169421 in file: ‘../db.sql’: MySQL server has gone away

How do I change the DB Parameter Group Name from CLI

1. Download the tools from this area http://aws.amazon.com/developertools/2928 and extract it as c:\RDS
2. Set the system variable AWS_RDS_HOME and add and add the c:\rds\bin in system path
3. Update the file under “c:\RDS\credential.cfg” with your amazon credentials. You may get it from “Account–> Security ” find the entry Access Key ID and Secret Access key

Here you go, test the amazon RDS connectivity by issuing this command.

#rds-describe-db-instances –region us-east-1 This will list the instance started under RDS.

This example shows you how to add new mysql parameter in “DB Parameter Group” Amazon won’t support to edit the default group. So you need to create a new one from AWS management console.

The following command would help to modify the existing variable in that particular Param group.
Here,
DB Parameter Groups : test-parm
mysql variable name : wait_timeout
Value : 86400

C:\>rds-modify-db-parameter-group test-parm  --parameters "name=wait_timeout, value=20000, method=immediate"  --region us-east-1
DBPARAMETERGROUP  test-parm

Now I’m going to add this group to running DB instance,

C:\>rds-modify-db-instance  tbrdsinstance  --db-parameter-group-name test-parm  --region us-east-1

What my conclusion is if you have hadny experience with mySQL and it’s administration, Don’t use this for huge database. RDS won’t allow user to change certain mysql variables like wait_timeout and we can’t change any variable at run time. So we need to update the variables in our parameter group and restart the mysql instance to take effect. So a reboot required and it can be avoid if we have direct access.

Also pls note that none of the AWS RDS documentation does not listing out anything about to specify the region name among in parameter list. Unfortunately your command did not succeed if you miss to add it. I waisted lots of time because of this 🙁 🙁

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>