March 3, 2017

Changing MySQL DB parameter on Amazon RDS instance

MySQL instances on Amazon RDS service usually comes with default MySQL configuration settings, which might not always work for our needs. For example: we had to change max_allowed_packet DB parameter value to allow importing large DNA sets. This is a quick guide (using Amazon RDS command line toolkit) to change the MySQL DB parameter. Similar updates can also be made to the DB instances from an Amazon Management Console. If you are making changes to the production environment, I suggest you look at Amazon RDS developer guide to reduce the impact and minimise the risk.

Requirements:

  • Download RDS command line toolkit from the above link, unzip them
  • Add <unzipped_folder>/bin to the $PATH variable
  • Add/export $AWS_RDS_HOME variable. E.g: export AWS_RDS_HOME=<unzipped_folder>

Create custom parameter group
We cannot make changes to the default parameter group. Hence we start with creating own custom parameter group

rds-create-db-parameter-group <custom-parameter-group-name> --description='Parameter group for custom parameters' --engine=MySQL5.1

Change parameter value
Now that we have a custom parameter group, parameter values can be changed to the requirements.

rds-modify-db-parameter-group  <custom-parameter-group-name> --parameters "name=max_allowed_packet,value=16776192, method=immediate"

Modify DB instance to use new parameter group

Note that the changes so far made are to the parameter group and not to the actual DB instance. So DB instance needs to be modified to use the new DB Parameter Group.

rds-modify-db-instance <instance-name> --db-parameter-group-name=<custom-parameter-group-name>

Restart DB instance
When a parameter group value on DB Instance is changed, DB instance must be started

rds-reboot-db-instance <instance-name>

Check the instance status
Once the DB instance is restarted, we can check the changes made using rds-describe-db-instances command.

At this point, we have an RDS instance with required parameter values and ready to test the import functionality for large DNA sets. If we need to change the value of a different parameter, it can be changed on parameter group which will be reflected to all the DB instances using the parameter group. In the rds-modify-db-parameter-group command if the method specified is "immediate" and the parameter apply type is "dynamic", the change is reflected without restarting the DB Instance(s). If the method used is "pending-reboot" or the parameter apply type is "static", change(s) will take place only after rebooting the DB Instance(s).

TIP: If you are having problems after changing the parameter group, you can reset the parameter to default value using the rds-reset-db-parameter-group command

Topics: Amazon, AWS, Big data technology, Blog, MySQL, RDS