RDSのcharacter setをutf8に

設定手順

1. 事前準備

• Amazon RDS Command Line Toolkitを導入して環境変数を設定しておく

# 以下のような感じで設定した(~/.ec2/には証明書配置済)
export EC2_PRIVATE_KEY="$(/bin/ls "$HOME"/.ec2/pk-*.pem | /usr/bin/head -1)"
export EC2_CERT="$(/bin/ls "$HOME"/.ec2/cert-*.pem | /usr/bin/head -1)"
export EC2_REGION=ap-northeast-1

1. DB Parameter Groupsの作成

• Management ConsoleのRDSでCreate DB Parameter Groupを実行(以下のようなGroupを作成した)
• Name:mysqlsetting
• Family:mysql5.5

1. DB Parameter Groupsの確認

$ rds-describe-db-parameter-groups 
DBPARAMETERGROUP  default.mysql5.5  mysql5.5  Default parameter group for mysql5.5
DBPARAMETERGROUP  mysqlsetting      mysql5.5  mysql original setting

1. DB Parameter Groupsの設定

• Management Consoleから設定できないのでコマンドラインで

$ rds-modify-db-parameter-group mysqlsetting --parameters "name=character_set_client, value=utf8, method=immediate"
$ rds-modify-db-parameter-group mysqlsetting --parameters "name=character_set_connection, value=utf8, method=immediate"
$ rds-modify-db-parameter-group mysqlsetting --parameters "name=character_set_database, value=utf8, method=immediate"
$ rds-modify-db-parameter-group mysqlsetting --parameters "name=character_set_filesystem, value=utf8, method=immediate"
$ rds-modify-db-parameter-group mysqlsetting --parameters "name=character_set_results, value=utf8, method=immediate"
$ rds-modify-db-parameter-group mysqlsetting --parameters "name=character_set_server, value=utf8, method=immediate"

1. RDSでのDatabaseの確認(作成したDB Parameter GroupsをInstance作成時に指定する)

• Instance作成時に設定したDatabase(なぜかcharacter_set_databaseがlatin1に)

mysql> use default_db
mysql> show variables like '%character%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | latin1                                    |
| character_set_filesystem | utf8                                      |
| character_set_results    | utf8                                      |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.5.20.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

• 自分で作ったDatabase(全てutf-8に)

mysql> create database test;
mysql> use test
mysql> show variables like '%character%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8                                      |
| character_set_filesystem | utf8                                      |
| character_set_results    | utf8                                      |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.5.20.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

参照サイト

• cloudpackブログ - cloudpack（クラウドパック）Amazon EC2などクラウドの導入設計、運用・保守サービス: RDSのcharacter_set_*をutf8に変更