Change the MySQL default character set on Amazon Relational Database Service (RDS)

Updated . Posted . Visible to the public.

Look here for informations how you can show the MySQL default character set.

At first you need the Amazon RDS Command Line Toolkit Show archive.org snapshot

  • download and unzip the Amazon RDS Command Line Toolkit Show archive.org snapshot

  • set the needed environment variables:

    export AWS_RDS_HOME=/home/foobar/Downloads/RDSCli-1.4.007/
    export JAVA_HOME=/usr
    
  • create an aws-credential (e.g. aws-credential.txt) file like this:

    AWSAccessKeyId=AWS access ID
    AWSSecretKey=AWS secret key
    

According to amazon there must be given the following data:

You can either use the AWS Accounts access keys and they can be found at http://aws.amazon.com under Account->Security Credentials or you can use the access keys of a user created with IA

After that you can create a new parameter group

/home/foobar/Downloads/RDSCli-1.4.007/bin/rds-create-db-parameter-group utf8 -f mysql5.1 -d "default parameter with utf8" --aws-credential-file ./aws-credential.txt --region eu-west-1

The output should look like this:

DBPARAMETERGROUP  utf8  mysql5.1  default with utf8

Now change the parameter of the new parameter group

/home/foobar/Downloads/RDSCli-1.4.007/bin/rds-modify-db-parameter-group utf8 --region eu-west-1 --aws-credential-file .aws-credential.txt \
--parameters="name=character_set_server, value=utf8, method=immediate" \
--parameters="name=character_set_filesystem, value=utf8, method=immediate" \
--parameters="name=character_set_client, value=utf8, method=immediate" \
--parameters="name=character_set_connection, value=utf8, method=immediate" \
--parameters="name=character_set_database, value=utf8, method=immediate" \
--parameters="name=character_set_results, value=utf8, method=immediate" \
--parameters="name=collation_server, value=utf8_general_ci, method=immediate" \
--parameters="name=collation_connection, value=utf8_general_ci, method=immediate"

The output should look like this:

DBPARAMETERGROUP  utf8

Change the parameter set of your RDS-Instance

/home/foobar/Downloads/RDSCli-1.4.007/bin/rds-modify-db-instance --aws-credential-file ./aws-credential.txt --region eu-west-1 --db-instance-identifier example-db --db-parameter-group-name utf8

The output should look like this:

DBINSTANCE  example-db  2011-09-15T08:51:52.630Z  db.m1.large  mysql  7  example-db  available  example-db.gosfg54sd8.eu-west-1.rds.amazonaws.com  3306  eu-west-1a  1  y  5.1.57  general-public-license
  SECGROUP  sg-example  active
  PARAMGRP  utf8  applying

After that you have to restart your RDS-Instance to apply the changes

/home/foobar/Downloads/RDSCli-1.4.007/bin/rds-reboot-db-instance --region eu-west-1 --aws-credential-file ./aws-credential.txt --db-instance-identifier example-db

The output should look like this:

DBINSTANCE  example-db  2011-09-15T08:51:52.630Z  db.m1.large  mysql  7  example-db  rebooting  example-db.gosfg54sd8.eu-west-1.rds.amazonaws.com  3306  eu-west-1a  1  y  5.1.57  general-public-license
  SECGROUP  sg-example  active
  PARAMGRP  utf8  pending-reboot

After the reboot is finished check whether the default character set has changed.

Last edit
License
Source code in this card is licensed under the MIT License.
Posted by Kim Klotz to makandra dev (2011-10-26 13:24)