AWS LambdaでAmazon RDS for MySQLへ接続する(Node.js 4.3 + KMSで暗号化したMySQL接続パスワードをkms.decryptで復号化してMySQLヘ接続 + バッチ実行をSNS通知する) ref: http://qiita.com/na0AaooQ/items/ff9ab6ce9831236b3ea6

example.js

var AWS = require('aws-sdk');
var mysql = require('mysql');

// KMSにより暗号化したMySQL接続用のパスワード文字列
var kmsEncyptedToken = "CiC*********(KMSで暗号化したMySQL接続用のパスワード文字列)***********UI=";

// 接続先のMySQLサーバ情報
var mysql_host = "example-rds-mysql-server.carvmoii2uds.ap-northeast-1.rds.amazonaws.com";
var mysql_user = "example_user";
var mysql_dbname = "exampledb";
var mysql_password = "";

var sns_topic_arn = "arn:aws:sns:ap-northeast-1:*******************:example-lambda-mysql-sns";

exports.handler = function(event, context){

    // 実行するSQL文
    var sql = "SELECT * FROM exampledb.example_table WHERE insert_date <= date_sub(curdate(), interval 2 week)";

    if (kmsEncyptedToken && kmsEncyptedToken !== "<kmsEncryptedToken>") {

        var encryptedBuf = new Buffer(kmsEncyptedToken, 'base64');
        var cipherText = {CiphertextBlob: encryptedBuf};

        var kms = new AWS.KMS({ region: 'ap-northeast-1' });
        var sns = new AWS.SNS({ region: 'ap-northeast-1' });

        // KMSにより暗号化したパスワード文字列の復号化
        kms.decrypt(cipherText, function (err, data) {
            if (err) {

                console.log("CipherText Decrypt error: " + err);
                context.fail(err);

            } else {

                mysql_password = data.Plaintext.toString('ascii');

                var connection = mysql.createConnection({
                    host     : mysql_host,
                    user     : mysql_user,
                    password : mysql_password,
                    database : mysql_dbname
                });

                console.log("MySQL Server Name: " + mysql_host);
                console.log("MySQL User Name: " + mysql_user);
                console.log("MySQL Database Name: " + mysql_dbname);
                console.log("MySQL Exec SQL: " + sql);

                // MySQLデータベースへの接続
                connection.connect();

                // MySQLデータベースでSQL実行
                connection.query(sql, function(err, rows, fields) {
                    if (err) {

                        console.log("MySQL Select Error");
                        context.fail(err);
                        sns.publish({
                            Message: 'Lambda Function Error',
                            Subject: 'Lambda Function Error',
                            TopicArn: sns_topic_arn
                        }, function(err, data){
                            if(err) throw err;
                            else context.fail('SNS Publish Error');
                        });

                        throw err;

                    } else {

                    console.log("MySQL Select Success");
                    console.log(rows);
                    console.log(fields);
                    }
                });

                connection.end(function(err) {

                    sns.publish({
                        Message: 'Lambda Function Success',
                        Subject: 'Lambda Function Success',
                        TopicArn: sns_topic_arn
                    }, function(err, data){
                        if(err) throw err;
                        else context.fail('SNS Publish Error');
                    });

                    context.done();
                });
            }
        });

    } else {

        context.fail("kmsEncyptedToken has not been set.");

    }

    console.log('end');

};

file0.txt

[ec2-user@example-server ~]$ hostname
example-server
[ec2-user@example-server ~]$ mysql -u root -p -h example-rds-mysql-server.carvmoii2uds.ap-northeast-1.rds.amazonaws.com
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON exampledb.* TO example_user@'192.168.0.0/255.255.0.0' IDENTIFIED BY 'exam
ple_password';
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM exampledb.example_table;
Query OK, 6 rows affected (0.00 sec)

mysql> SELECT * FROM exampledb.example_table;
Empty set (0.00 sec)

mysql> INSERT INTO exampledb.example_table( id, insert_date) VALUES( 1, '2016-03-10 12:30:00' );
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO exampledb.example_table( id, insert_date) VALUES( 2, '2016-03-20 12:30:00' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO exampledb.example_table( id, insert_date) VALUES( 3, '2016-03-30 13:40:00' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO exampledb.example_table( id, insert_date) VALUES( 4, '2016-04-05 14:10:00' );
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO exampledb.example_table( id, insert_date) VALUES( 5, '2016-04-10 22:00:00' );
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM exampledb.example_table;
+------+---------------------+
| id   | insert_date         |
+------+---------------------+
|    1 | 2016-03-10 12:30:00 |
|    2 | 2016-03-20 12:30:00 |
|    3 | 2016-03-30 13:40:00 |
|    4 | 2016-04-05 14:10:00 |
|    5 | 2016-04-10 22:00:00 |
+------+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM exampledb.example_table WHERE insert_date <= date_sub(curdate(), interval 2 week);
+------+---------------------+
| id   | insert_date         |
+------+---------------------+
|    1 | 2016-03-10 12:30:00 |
|    2 | 2016-03-20 12:30:00 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql>

file1.txt

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Stmt1448696327000",
            "Effect": "Allow",
            "Action": [
                "kms:*"
            ],
            "Resource": [
                "arn:aws:kms:ap-northeast-1:**************:key/KMSの暗号化キーのARNを指定する"
            ]
        }
    ]
}

file10.txt

START RequestId: 442b53cf-ff43-11e5-9dc8-2123a6cf68bb Version: $LATEST
2016-04-10T17:40:08.926Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    end
2016-04-10T17:40:10.127Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    MySQL Server Name: example-rds-mysql-server.carvmoii2uds.ap-northeast-1.rds.amazonaws.com
2016-04-10T17:40:10.183Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    MySQL User Name: example_user
2016-04-10T17:40:10.183Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    MySQL Database Name: exampledb
2016-04-10T17:40:10.183Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    MySQL Exec SQL: SELECT * FROM exampledb.example_table WHERE insert_date <= date_sub(curdate(), interval 2 week)
2016-04-10T17:40:10.346Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    MySQL Select Success
2016-04-10T17:40:10.346Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    [ RowDataPacket { id: 1, insert_date: Thu Mar 10 2016 12:30:00 GMT+0000 (UTC) },
  RowDataPacket { id: 2, insert_date: Sun Mar 20 2016 12:30:00 GMT+0000 (UTC) } ]
2016-04-10T17:40:10.567Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    [ FieldPacket {
    catalog: 'def',
    db: 'exampledb',
    table: 'example_table',
    orgTable: 'example_table',
    name: 'id',
    orgName: 'id',
    charsetNr: 63,
    length: 12,
    type: 3,
    flags: 0,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true },
  FieldPacket {
    catalog: 'def',
    db: 'exampledb',
    table: 'example_table',
    orgTable: 'example_table',
    name: 'insert_date',
    orgName: 'insert_date',
    charsetNr: 63,
    length: 19,
    type: 12,
    flags: 128,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true } ]
END RequestId: 442b53cf-ff43-11e5-9dc8-2123a6cf68bb
REPORT RequestId: 442b53cf-ff43-11e5-9dc8-2123a6cf68bb  Duration: 2670.49 ms    Billed Duration: 2700 ms    Memory Size: 128 MB Max Memory Used: 30 MB  

file2.txt

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Stmt1460306102000",
            "Effect": "Allow",
            "Action": [
                "sns:Publish"
            ],
            "Resource": [
                "arn:aws:sns:ap-northeast-1:***********:example-lambda-mysql-sns"
            ]
        }
    ]
}

file3.txt

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "logs:CreateLogGroup",
        "logs:CreateLogStream",
        "logs:PutLogEvents"
      ],
      "Resource": "arn:aws:logs:*:*:*"
    },
    {
      "Effect": "Allow",
      "Action": [
        "ec2:CreateNetworkInterface",
        "ec2:DescribeNetworkInterfaces",
        "ec2:DetachNetworkInterface",
        "ec2:DeleteNetworkInterface"
      ],
      "Resource": "*"
    }
  ]
}

file4.txt

[ec2-user@example-server ~]$ hostname
example-server
[ec2-user@example-server ~]$ sudo yum -y update


[ec2-user@example-server ~]$ uname -a
Linux example-server 4.4.5-15.26.amzn1.x86_64 #1 SMP Wed Mar 16 17:15:34 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
[ec2-user@example-server ~]$

[ec2-user@example-server ~]$ sudo yum install nodejs npm --enablerepo=epel

[ec2-user@example-server ~]$ node -v
v0.10.42
[ec2-user@example-server ~]$ npm -v
1.3.6
[ec2-user@example-server ~]$

file5.txt

[ec2-user@example-server ~]$ mkdir /home/ec2-user/example-lambda-mysql-select
[ec2-user@example-server ~]$
[ec2-user@example-server ~]$ cd /home/ec2-user/example-lambda-mysql-select
[ec2-user@example-server example-lambda-mysql-select]$ pwd
/home/ec2-user/example-lambda-mysql-select
[ec2-user@example-server example-lambda-mysql-select]$ npm install mysql

file6.txt

[ec2-user@example-server example-lambda-mysql-select]$ vi /home/ec2-user/example-lambda-mysql-select/example.js

file7.txt

[ec2-user@example-server example-lambda-mysql-select]$ pwd
/home/ec2-user/example-lambda-mysql-select
[ec2-user@example-server example-lambda-mysql-select]$ zip -r example-lambda-mysql-select.zip .

[ec2-user@example-server example-lambda-mysql-select]$ ls -lrta /home/ec2-user/example-lambda-mysql-select/
total 272
drwxrwxr-x  3 ec2-user ec2-user   4096 Apr 11 00:55 node_modules
-rw-rw-r--  1 ec2-user ec2-user   2649 Apr 11 00:55 example.js
drwx------ 21 ec2-user ec2-user   4096 Apr 11 00:55 ..
-rw-rw-r--  1 ec2-user ec2-user 258434 Apr 11 00:56 example-lambda-mysql-select.zip
drwxrwxr-x  3 ec2-user ec2-user   4096 Apr 11 00:56 .
[ec2-user@example-server example-lambda-mysql-select]$

file8.txt

[ec2-user@example-server example-lambda-mysql-select]$ aws s3 cp /home/ec2-user/example-lambda-mysql-select/example-lambda-mysql-select.zip s3://適当なS3バケット/
upload: ./example-lambda-mysql-select.zip to s3://適当なS3バケット/example-lambda-mysql-select.zip
[ec2-user@example-server example-lambda-mysql-select]$

file9.txt

START RequestId: 442b53cf-ff43-11e5-9dc8-2123a6cf68bb Version: $LATEST
2016-04-10T17:40:08.926Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    end
2016-04-10T17:40:10.127Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    MySQL Server Name: example-rds-mysql-server.carvmoii2uds.ap-northeast-1.rds.amazonaws.com
2016-04-10T17:40:10.183Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    MySQL User Name: example_user
2016-04-10T17:40:10.183Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    MySQL Database Name: exampledb
2016-04-10T17:40:10.183Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    MySQL Exec SQL: SELECT * FROM exampledb.example_table WHERE insert_date <= date_sub(curdate(), interval 2 week)
2016-04-10T17:40:10.346Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    MySQL Select Success
2016-04-10T17:40:10.346Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    [ RowDataPacket { id: 1, insert_date: Thu Mar 10 2016 12:30:00 GMT+0000 (UTC) },
  RowDataPacket { id: 2, insert_date: Sun Mar 20 2016 12:30:00 GMT+0000 (UTC) } ]
2016-04-10T17:40:10.567Z    442b53cf-ff43-11e5-9dc8-2123a6cf68bb    [ FieldPacket {
    catalog: 'def',
    db: 'exampledb',
    table: 'example_table',
    orgTable: 'example_table',
    name: 'id',
    orgName: 'id',
    charsetNr: 63,
    length: 12,
    type: 3,
    flags: 0,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true },
  FieldPacket {
    catalog: 'def',
    db: 'exampledb',
    table: 'example_table',
    orgTable: 'example_table',
    name: 'insert_date',
    orgName: 'insert_date',
    charsetNr: 63,
    length: 19,
    type: 12,
    flags: 128,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true } ]
END RequestId: 442b53cf-ff43-11e5-9dc8-2123a6cf68bb
REPORT RequestId: 442b53cf-ff43-11e5-9dc8-2123a6cf68bb  Duration: 2670.49 ms    Billed Duration: 2700 ms    Memory Size: 128 MB Max Memory Used: 30 MB