RDSの文字化け対策のために

[Web] AWSを使ってみる �CRDSでの文字化けに対処してみた

で、Amazon RDS Command Line Toolkit を使ってParameter Group を更新しました。

Toolkitをインストールして、環境設定して……、というのが面倒だなーと思っていたら、
AWSの管理画面(Management Console)で設定できることがわかりました orz

手順は、
[Web] AWSを使ってみる �CRDSでの文字化けに対処してみた
でAWSの管理画面で「Parameter Group」を作るところまでは同じです。

AWS管理画面(Management Console) > Serceves(画面左上) > RDS > Parameter Groups(画面左)

で、一覧表示されるので、
変更したいParameter Groupを選択（デフォルト以外で）すると、画面上の「Edit Parameters」ボタンが有効になるので押します。



すると、設定できるパラメーター一覧が表示されるので、該当のパラメーターについて入力します。
※下の例は文字化け対策としてUTF-8を設定しました



その後、画面上に戻ると

・Cancel Editing　※変更しないで元の画面に戻る
・Preview Changes　※どの値が変更されたのか一覧が表示される（反映はまだされない）
・Save Changes　※変更された値を反映させる　※誤った値が入力されていると教えてくれる

のボタンがあるので、それぞれ選択します。



Save Changesを押した後、Parameter Groupsを使用しているRDSのインスタンスの状態を見ると
Parameter Group が「applying」になっています。
この状態ではまだRDSに反映されていません。



数分待つと「pending-reboot」となります。



この後、RDSのインスタンスを再起動(Reboo)すると、Parameter Groupが反映されます。

管理画面(Management Console)を使うもよし、Amazon RDS Command Line Toolkit を使うもよし、
作業しやすい方で作業出来るっていいね！

色々と準備が出来たところで、やっとアプリで使うPHPを作ることができます。
流れはこんな感じです。

�@EC2でPHPを作ってコンソール画面から実行してみる
�APHPをクーロンで実行してみる
�Bシェルスクリプトを利用する
�C修正後のクーロン
�Dデバッグ用に時間を出力する


�@EC2でPHPを作ってコンソール画面から実行してみる

Webブラウザを通して実行していたPHPですが、内部処理だけに使いたいときは
コンソールから実行しています。

$ php -f ~/[PHPファイル名].php [パラメーター]　　←ホーム( ~/ )にPHPファイルを置いてます

PHPの中身↓
==================================================
// PHPのパラメーターを取得
$var = array_reverse(explode('=',$_SERVER['argv'][1]));
echo "$var[0]"; ← PHP実行コマンドのパラメーターが表示される
==================================================

【参考】
第 43章PHP をコマンドラインから使用する


�APHPをクーロンで実行してみる

毎度手作業でPHPを実行するのは大変なので、
クーロンを使って定期的に実行してもらいます。

クーロンの状況について順番に確認していきます。
● 実行されているか
/etc/rc.d/init.d/crond status

● 2から5がONになっているか
chkconfig --list crond

● crontab に実行権限があるか
ls -la /etc/crontab

● cronの実行ログを確認
sudo tail -f /var/log/cron

● クーロンの編集　※ここで実行したいPHPについて記述
crontab -e

例として、こんな感じです↓
==================================================
* * * * * /usr/bin/php -q ~/[PHPファイル名].php 2 >> ~/test.log;
→PHPファイルにパラメーター「2」を渡して”毎分”実行。ログを test.log に追記する。
==================================================

● 中身を削除
crontab -r

● 登録状況を確認
crontab -l

【参考】
・はじめての crontab / crontab コマンドの使い方
・[サーバ]crontabがどうしても動かないときに確認すべき3つの点+α
・"crontab" 指定した時間にコマンドを自動実行 @LCD -Linux Command Dictionary- 【山澤】
・[FedoraCore6][apache]cronでphpAdd Star

クーロンの最小実行単位時間は「分」ですが、sleep コマンドを使うと”秒単位”にすることができます。

==================================================
* * * * * sleep 1; /usr/bin/php -q ~/[PHPファイル名].php 2 >> ~/test.log;　←毎分「１秒」に実行
* * * * * sleep 2; /usr/bin/php -q ~/[PHPファイル名].php 2 >> ~/test.log;　←毎分「２秒」に実行
* * * * * sleep 3; /usr/bin/php -q ~/[PHPファイル名].php 2 >> ~/test.log;　←毎分「３秒」に実行
==================================================

【参考】
・crontab 秒間実行について

これで秒単位で実行できるぞー、と安心したのも束の間、
この方法だと「sleepで指定した時間、コンソールが固まる(sleepを実行してるから)」
という事態に陥ってしまいました！

sleep 1000; とか記述してたので、使えるようになるまでかなり待ちました orz
そんな時に見つけた方法が、「クーロン ＋ シェルスクリプト」という方法でした。


�Bシェルスクリプトを利用する

シェルスクリプトは、コンソールで実行したいコマンドをファイルにまとめて記述したファイルのことです。
シェルスクリプト( .sh ファイル )を実行すると、中身に記述されたコマンドが実行されます。

下の例だと、毎秒PHPファイルを実行しつつ、各回に1から10のパラメーターを渡しています。

0秒目　： PHPを実行（パラメーター = 1）
1秒目　： PHPを実行（パラメーター = 2）
2秒目　： PHPを実行（パラメーター = 3）
…
という感じです。

~/test.sh ↓
==================================================
#!/bin/sh
for i in `seq 1 10`
do
    /usr/bin/php -q ~/[PHPファイル名].php $i >> ~/test.log;
    sleep 1
done
exit 0
==================================================

�C修正後のクーロン

�Bでシェルスクリプトが出来たので、�Aのクーロンを修正します。
下の修正では、3分かけてPHPファイルを30回実行した例です。

==================================================
1 * * * * sh ~/test1.sh　←毎時「１分」に実行 ※0秒から9秒の間に毎秒実行される
2 * * * * sh ~/test2.sh　←毎時「２分」に実行 ※0秒から9秒の間に毎秒実行される
3 * * * * sh ~/test3.sh　←毎時「３分」に実行 ※0秒から9秒の間に毎秒実行される

※test1.sh, test2.sh, test3.sh の中身は部分的に修正しています↓
・test1.sh → for i in `seq 1 10`
・test2.sh → for i in `seq 11 20`
・test3.sh → for i in `seq 21 30`
==================================================

【参考】
・シェルスクリプト入門 [制御構文]
・for 文の使用方法


�Dデバッグ用に時間を出力する

�Cがちゃんと時間通りに実行されているかについて、PHPから時間を出力して確認したいとき
「strtotime()」を使いましたが、デフォルトの状態では警告が出てしまします。
そのときは、php.ini の一部を書き変える必要がありました。

$ sudo vim /etc/php.ini
;date.timezone =
↓
date.timezone = "Asia/Tokyo"

PHPでの記述例↓
==================================================
$now = strtotime("now");
echo date('Y-m-d H:i:s', $now) . " arg=$var[0]\n";
==================================================

【参考】
Windows+VMWareでLAMP環境を作る【その３】


�Cで複数のシェルスクリプトに分けて、クーロンで各々実行した理由ですが
たくさんのPHPの実行を時間的に集中させたらサーバーのCPUが跳ね上がってしまったからでした。

�Cの方法で処理を分散させてみたのですが、これで正しいかどうかはまだ疑問です。
まだまだ手探り状態です。。。

文字化け問題が解決したことで、やっとアプリづくりに入ることができました。
早速ですが、１万件のレコードをSQLに入れてみよう！


�@大量のレコードをRDSのテーブルに挿入する

INSERT INTO ……

を１万行も書けるかー！
とうことで、大量のレコードを簡単にテーブルに挿入する方法を探しました。
SQLの「LOAD DATA INFILE」が早そうです。

=======================================
● 構文
LOAD DATA INFILE "ファイル名" INTO TABLE テーブル名
FIELDS TERMINATED BY ',区切り文字' ENCLOSED BY '"';

● SQL
LOAD DATA LOCAL INFILE '[ファイル名].csv'　　← EC2の ~/ に置いた
REPLACE INTO TABLE [テーブル名]
CHARACTER SET utf8　　　　　　　　　← CSVファイルの中身もUTF-8で！
FIELDS TERMINATED BY ','　　　　　　　← カンマ（ , ）区切りです
OPTIONALLY ENCLOSED BY '"';　　　　　← 文字をテーブルに保存するときはダブルクォート（ " ）でお願いします
=======================================

【参考】
・大規模CSVをMySQLに入れる
・LOAD DATA INFILE構文：データのインポート
・[MySQL] CSV を使ってエクスポート・インポート

これを知らず、始めは INSERT文をコンソールにコピペしてましたが、手作業なので遅いし正確性が心配なので
止めてよかったです。

と思ったところで、「さて、どうやってCSVファイルをEC2にアップロードしよう」という新たな疑問が生じました。


�AEC2にSSHやFileZillaを使ってファイルをアップロードする

●コンソール画面からファイルをアップする　※SCPコマンドを使用

参考サイトを元に、EC2用に書き変えました↓

$ scp -P 22 -i [証明書] [CSVファイル] ec2-user@xx.xx.xx.xx:/home/ec2-user/
xxxxxxx.csv 100% 65KB 65.3KB/s 00:00 　　←これが表示されれば成功

【参考】ssh でファイル転送（SCP コマンド）


FTPソフト「FileZilla」を使うこともできます。

●設定
ホスト：ec2-user@xx.xx.xx.xx の「xx.xx.xx.xx」部分
ポート：空でOK
Protocol：SFTP
ログオンの種類：パスワードを訪ねる
ユーザー：ec2-user　※SSHでログインするときのユーザー
パスワード：空でOK
アカウント：空でOK

参考サイトにもありますが、SSHでログインするときに使っている秘密鍵を使用します。
※そのままでは使用できないため、変換する旨のアラートが表示されるのでOKを押します

メニュー > FileZilla > Preference の画面


【参考】【aws】ec2にfilezilla経由でファイルアップロード

これで無事CSVファイルの中身をRDSのテーブルに挿入することができました。


�BちょっとしたSQLの話

よく使うSQLをズラズラと。

●テーブル作成
CREATE TABLE [テーブル名] (
[カラム名] [型] [NOT NULL等のオプション],
[インデックス名](インデックスを付与するカラム名),
) DEFAULT CHARSET=utf8;　← 文字コードはUTF-8

●テーブル名変更
ALTER TABLE [旧テーブル名] RENAME TO [新テーブル名];

●カラム追加
ALTER TABLE [テーブル名] ADD [カラム名] [型] [NOT NULL等のオプション];

●カラムの型変更
ALTER TABLE [テーブル名] CHANGE COLUMN [旧カラム名] [新カラム名] [変えたい型] [NOT NULL等のオプション];
※新旧のカラム名は同じ

●インデックス確認
SHOW INDEX FROM [テーブル名];

●複合インデックスの付与　※条件で組み合わせて使うカラムがあるときに有効
CREATE INDEX [インデックス名] ON [テーブル名]([カラム1], [カラム2]);

●インデックス削除
ALTER TABLE [テーブル名] DROP INDEX [削除したいインデックス];

●SELECT文の結果をテーブルに挿入
INSERT INTO [テーブル名] ([カラム1], [カラム2], ....)
SELECT [カラム1], [カラム2], ...
FROM [テーブル名];

●特定の小数点以下を切り捨て　※123.4567 → 123.45
SELECT TRUNCATE([カラム名], 2) FROM [テーブル名];

【参考】
・CREATE TABLE構文
・MySQL 編13 - 数値関数、余り、四捨五入、切り捨て、切り上げ、数値書式
・SELECT結果をINSERT/UPDATE


準備が整ったので、今度こそアプリづくりに着手できる……はず！

PHPからRDSに接続してSQLを発行できたことだし、あとはアプリづくりに専念できる！
と思いきや、日本語をテーブルに挿入してみたら「???」と表示されてしまいました orz

これが、これまでで一番ハマった問題でした。

「UTF-8で日本語を表示したい」
これが今回の目的です。

まずは、「SET NAMES」というコマンドが有効っぽいと出てきたけど問題があるっぽい。

【参考】mysql + PHP の SET NAMES問題

ならば、「PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET `utf8`"」という命令がよさげ。
でもダメでした。

【参考】PHP, PDOでMySQL接続時の文字コード設定

次に、「charset=utf8」を設定すればよいという情報を発見。
指定しても変わらず……。

【参考】
・PDO_MYSQL DSN
・PHP 5.3.6以降（PDO）でMYSQLの文字化け対策（charsetを指定する）

EC2の /etc/my.cnf に設定を追記(↓)すればいけるという記事を見つけるもダメでした。
※現在もこの記述は残していますが
=======================================
[mysqld]
default-character-set = utf8

[mysqld_safe]
character-set-server = utf8

[mysql]
default-character-set = utf8

[mysqldump]
default-character-set = utf8
=======================================

EC2の設定を確認してみようという記事を発見。

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

UTF8以外の文字コードに設定されていることが確認できました。
でもこの参考サイトでの対処方法は「SET NAMES」という非推奨の方法。

【参考】PDOでmysqlに接続する時の文字コード
【参考】MySQLに日本語でINSERTできない場合

解決方法を探していたら、「RDSのパラメーターグループを変更」する必要があるらしい。

【参考】Amazon RDSのパラメータグループを変更する方法

パラメーターグループを変更するには、「Amazon RDS Command Line Toolkit」を使う必要があるらしい。
しかもJAVA環境が入っている必要もあるらしい。

くじけそうになりました orz

ということで、参考サイトを見ながらMacに Amazon RDS Command Line Toolkit をインストール。
幸い、JAVA環境はインストール済みでした。

環境設定（~/.bashrc ）に以下を追加。
=======================================
＃ JAVAの環境設定
JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/CurrentJDK/Home
export JAVA_HOME
PATH=$PATH:$JAVA_HOME/bin

# Amaozn Web Service Setting
export AWS_CREDENTIAL_FILE=/Users/[Macのユーザー名]/aws/credential.txt　←具体的には以下のとおり

# Amazon RDS CLT Setting
export AWS_RDS_HOME=/Users/[Macのユーザー名]/aws/RDSCli-1.14.001　← ToolKitを解凍した場所
PATH=$PATH:$AWS_RDS_HOME/bin
=======================================

「credential.txt」は、参考サイトにあった「アクセス証明書」にあった、
アクセスキーIDとシークレットキーの２つを記載して保存しました↓

=======================================
# Enter the AWS Keys without the < or >
# These can be found at http://aws.amazon.com under Account->Security Credentials
AWSAccessKeyId=[アクセスキーID]
AWSSecretKey=[シークレットキー]
※権限は 600
=======================================

ToolKitのインストールが出来たら、
次はAWSの管理画面に入り、RDSの設定変更のところで「Parameter Group」を追加します。

RDSの画面の左メニュに「Parameter Groups」があるので、それをクリックすると
現在のParameter Groupが表示されます。
デフォルトで１つ指定されているはずです。



これはToolKitでは変更できないようで、
新しくParameter Groupを作りUTF8設定をして、それをRDSに適用する
方法で解決しました。

Parameter Groupを新規に作る方法は、上記のAWS管理画面の指示どおりにすれば簡単にできました。

■注意点
作成ウィザードを進めていくと「DB Parameter Group Family:」を選択する画面になります。
そのときは、使用しているRDSのインスタンスと同じMySQLのバージョンを指定してください。
そうすることで、RDSのインスタンス > 右クリック > Modify > で表示される管理画面にて
「Parameter Group:」の選択肢に上記で作成した Parameter Group が選択できるようになります。
※異なったバージョンのMySQLを指定すると、選択肢に表示されません

これで準備が整いました（長い。。。


いよいよ、Amazon RDS Command Line Toolkit を使ってParameter Groupを変更します。
Macでコンソールを立ち上げて、ToolKitを実行しました。

実際に使ったコマンド↓
=======================================
rds-modify-db-parameter-group [ParameterGroup名] \
--aws-credential-file $AWS_CREDENTIAL_FILE \　　　　　←~/.bashrc で設定した「AWS_CREDENTIAL_FILE」
--region ap-northeast-1 \　　　　　　　　　　　　　　　←AZのインスタンスに合わせる
--parameters "name=character_set_client, value=utf8, method=immediate"

rds-modify-db-parameter-group [ParameterGroup名] \
--aws-credential-file $AWS_CREDENTIAL_FILE \
--region ap-northeast-1 \
--parameters "name=character_set_connection, value=utf8, method=immediate"

rds-modify-db-parameter-group [ParameterGroup名] \
--aws-credential-file $AWS_CREDENTIAL_FILE \
--region ap-northeast-1 \
--parameters "name=character_set_database, value=utf8, method=immediate"

rds-modify-db-parameter-group [ParameterGroup名] \
--aws-credential-file $AWS_CREDENTIAL_FILE \
--region ap-northeast-1 \
--parameters "name=character_set_results, value=utf8, method=immediate"

rds-modify-db-parameter-group [ParameterGroup名] \
--aws-credential-file $AWS_CREDENTIAL_FILE \
--region ap-northeast-1 \
--parameters "name=character_set_server, value=utf8, method=immediate"
=======================================

この後、RDSの該当するインスタンスを 右クリック > Reboot して再起動します。
Parameter Groupを確認して、以下のようになっていれば成功です！

振り返ると、MySQLのコマンドの先頭に「--default-character-set=utf8」を付けてます。
これも文字化け対策でした↓

$ mysql --default-character-set=utf8 -h [Endpoint] -P 3306 -u [RDSのインスタンスのユーザー名] -p

mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /rdsdbbin/mysql-5.6.12.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

【参考】
・AWSのRDSの文字化けに悩んだ時にパラメーター設定の為に見たサイトまとめ
・AWS RDS パラメータグループでMySQLの文字コードをUTF-8に設定

これで、文字化けが解消しました！
おつかれさまでしたー。

EC2からPHPを使って外部APIを使えたので、次は返ってきた値をDBに保存したいところです。
AWSでは、DBだけで独立したRDSというサービスがあるのでそれを利用しています。

今回のやることです。

�@PDO(PHP Data Object)を知る
�APDOを使ってRDS(MySQL)にアクセス
�BPDOを使ってクエリを発行
�CSQLにパラメーターを渡す

■前提条件
[Web] AWSを使ってみる �@EC2とRDSの立ち上げを済ませて、
コマンドラインでRDS(MySQL)に接続できる状態になっていること


�@PDO(PHP Data Object)を知る
PHP5から、MySQLを使うときに便利なクラスが登場しました。
「PDO」でググると詳しい説明が出てくるのでそちらを参照ください。

【参考】はじめてのPDO


�APDOを使ってRDS(MySQL)にアクセス
�BPDOを使ってクエリを発行
簡単な例を挙げます。
接続に必要な情報を揃えてから、new PDO() でMySQLにアクセスします。
それから query() を使ってSQLを発行します。

===========================================
<?php
try {
    // AWS管理画面ではMySQL5.6.12なので、charset=utf8 を設定する
    $dbs = "mysql:host=[RDSのインスタンスのEndpoint];dbname=[DB名];charset=utf8";
    $user = "[RDSのインスタンスのユーザー名]";
    $pass = "[RDSのインスタンスのパスワード]";
    $sql = "INSERT INTO [テーブル名] (columnNum,  columnStr) VALUES(10, 'テスト');";
    // PDOを使ってRDSに接続
    $pdo = new PDO($dbs, $user, $pass);
    // SQLを発行
    $pdo->query($sql);
    // 切断       
    $pdo = null;
} catch (PDOException $e) {
    die();
}
?>
===========================================

これだと固定のSQL文です。
たとえば「WHERE句」を柔軟に使えるようにするために条件にパラメーターを渡したいときは、
「query()」ではなくて「execute()」を使います。

ここで重要なのが、
EC2のデフォルトの設定では、PDOが使える設定になっていない
という点でした。

対処方法は、php.ini にPDOを有効にする設定を書き込むことでした。

まずは、PDOがインストールされているかチェック。
/usr/lib64/php/modules にpdo.so、pdo_mysql.soがあればOK。なければインストール↓
$ sudo yum -y install php-pdo

次にPDO用の設定を追記↓
$ sudo vim /etc/php.ini
;;;;
; Note: packaged extension modules are now loaded via the .ini files　　←こんな行があったのでその下に追記
; found in the directory /etc/php.d; these are loaded by default.
;;;;
extension=pdo.so
extension=pdo_mysql.so

【参考】
[AWS][PDO]AWSにMySQL用のPDOをインストールする


�CSQLにパラメーターを渡す
SELECT文を例に挙げます。
先ほどの「query()」が「execute()」に変わり、「bindValue()」を使ってパラメーターを渡しています。

===========================================
<?php
try {
    // AWS管理画面ではMySQL5.6.12なので、charset=utf8 を設定する
    $dbs = "mysql:host=[RDSのインスタンスのEndpoint];dbname=[DB名];charset=utf8";
    $user = "[RDSのインスタンスのユーザー名]";
    $pass = "[RDSのインスタンスのパスワード]";
    // :num のところがパラメーター
    $sql = "SELECT * FROM [テーブル名] WHERE columnNum = :num;";
    // PDOを使ってRDSに接続
    $pdo = new PDO($dbs, $user, $pass);
    // 先ほどのSQLを読み込んでおく
    $stmt = $pdo->prepare($sql);
    // SQLにパラメーターを渡す(整数なので、PDO::PARAM_INT)
    $stmt->bindValue(':num', 10, PDO::PARAM_INT);
    // SQLを発行
    $pdo->execute($sql);
    // SQLの結果を受け取る
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // チェック
    var_dump($result);
    // 切断       
    $pdo = null;
} catch (PDOException $e) {
    die();
}
?>
===========================================

これである程度柔軟なSQLを扱うことができるようになりました。

【参考】
・queryメソッド
・PDOの利用
・PDOStatement::bindValue


よかったね。
めでたし、めでたし。

とはなりませんでした！
一番ハマった、RDSの文字化け問題が次に待ち構えるのでした。。。