RでMySQLのデータベースのデータを取り出して解析に利用。

    From Wiki

R言語(あーるげんご)はオープンソース・フリーソフトウェアの統計解析向けの
プログラミング言語及びその開発実行環境である。
R言語はニュージーランドのオークランド大学のRoss Ihakaと
Robert Clifford Gentlemanにより作られた。
現在ではR Development Core Team(S言語開発者であるJohn M. Chambersも参画)
によりメンテナンスと拡張がなされている。

との事

Rをインストール後、以下のパッケージはインストール済み。
> install.packages(“DBI”)
> install.packages(“RMySQL”)

※ MySQL5.6.21を利用しているので、コンパイルするときにパスの設定を調整してインストール。

[admin@CentOS01 ~]$ sudo -s 
[sudo] password for admin: 
[root@CentOS01 admin]# R

R version 3.1.1 (2014-07-10) -- "Sock it to Me"
Copyright (C) 2014 The R Foundation for Statistical Computing
Platform: x86_64-redhat-linux-gnu (64-bit)

R は、自由なソフトウェアであり、「完全に無保証」です。 
 一定の条件に従えば、自由にこれを再配布することができます。 
 配布条件の詳細に関しては、'license()' あるいは 'licence()' と入力してください。 

R は多くの貢献者による共同プロジェクトです。 
 詳しくは 'contributors()' と入力してください。 
 また、R や R のパッケージを出版物で引用する際の形式については 
 'citation()' と入力してください。 

 'demo()' と入力すればデモをみることができます。 
 'help()' とすればオンラインヘルプが出ます。 
 'help.start()' で HTML ブラウザによるヘルプがみられます。 
 'q()' と入力すれば R を終了します。 

 以下にエラー load(name, envir = .GlobalEnv) : 
   空の (0 バイトの) 入力ファイルです 
 起動準備中です -  警告メッセージ: 
 .RData に保存されたデータを復帰できません  
> library(RMySQL) 
 要求されたパッケージ DBI をロード中です 
 警告メッセージ: 
In dyn.load(file, DLLpath = DLLpath, ...) : 

> dbconnector <- dbConnect(dbDriver("MySQL"),dbname="test", user="admin", password="password")
> test.table <- dbGetQuery(dbconnector, "select * from Personal_Info")
> dbDisconnect(dbconnector)
[1] TRUE
> names(test.table)
[1] "id"   "name"
> class(test.table)
[1] "data.frame"
> test.table
  id                                                             name
1  1 D8F991170C3468696E4D963AE4A8E2A1D6404D7066F30A5D0419C0EC80D0602B
2  2                                                     This is test
3  3                                           ????????audit.log?????
> 

R


MySQL暗号化 Community EditionとEnterprise

“AES 128 MySQL 4.0.2~”

SET @key_str = SHA2('password',512);
SELECT HEX(AES_ENCRYPT("AES暗号化-個人情報01",@key_str)) into @AES_ENC;
SELECT @AES_ENC;
SELECT AES_DECRYPT(UNHEX(@AES_ENC),@key_str);

“AES 256 MySQL 5.6.17~ “

SELECT @@session.block_encryption_mode;
SET block_encryption_mode = 'aes-256-cbc';
SELECT @@session.block_encryption_mode;
SET @key_str = SHA2('password',512);
SELECT @key_str;
SET @init_vector = RANDOM_BYTES(16);
SET @crypt_str = HEX(AES_ENCRYPT("AES暗号化-個人情報01",@key_str,@init_vector));
SELECT @crypt_str;
SELECT AES_DECRYPT(UNHEX(@crypt_str),@key_str,@init_vector);

“RSAを同じセッションで確認。MySQL 5.6.21~”
こちらはEnterpriseのみで利用可能なPlugin (鍵をPublicとPrivateに分ける事が可能)

SELECT CREATE_ASYMMETRIC_PRIV_KEY('RSA', 1024) INTO @priv_key;
SELECT CREATE_ASYMMETRIC_PUB_KEY('RSA', @priv_key) INTO @pub_key;
SELECT ASYMMETRIC_ENCRYPT('RSA','プライベート鍵にて暗号化しています', @priv_key) INTO @enc_priv;
SELECT ASYMMETRIC_ENCRYPT('RSA','公開鍵にて暗号化しています', @pub_key) INTO @enc_pub;
SELECT ASYMMETRIC_DECRYPT('RSA', @enc_pub, @priv_key);
SELECT ASYMMETRIC_DECRYPT('RSA', @enc_priv, @pub_key);

―以下メモ―
■暗号化された文字列の長さの確認
The length of crypt_str can be calculated using this formula:
16 * (trunc(string_length / 16) + 1)

root@localhost > SELECT 16 * (TRUNCATE(100/16,1) + 1);
+-------------------------------+
| 16 * (TRUNCATE(100/16,1) + 1) |
+-------------------------------+
|                         115.2 |
+-------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT 16 * (TRUNCATE(100/16,0) + 1);
+-------------------------------+
| 16 * (TRUNCATE(100/16,0) + 1) |
+-------------------------------+
|                           112 |
+-------------------------------+
1 row in set (0.00 sec)

root@localhost > 


root@localhost > select LENGTH("AES暗号化-個人情報01");
+---------------------------------------+
| LENGTH("AES暗号化-個人情報01")        |
+---------------------------------------+
|                                    27 |
+---------------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT 16 * (TRUNCATE(27/16,0) + 1);
+------------------------------+
| 16 * (TRUNCATE(27/16,0) + 1) |
+------------------------------+
|                           32 |
+------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT LENGTH(@crypt_str);
+--------------------+
| LENGTH(@crypt_str) |
+--------------------+
|                 64 |
+--------------------+
1 row in set (0.00 sec)

root@localhost > 

■HEXでサイズが2倍になっているので、UNHEXしてあげるとサイズが想定通りであると確認出来る。

root@localhost > SELECT LENGTH(UNHEX(@crypt_str));
+---------------------------+
| LENGTH(UNHEX(@crypt_str)) |
+---------------------------+
|                        32 |
+---------------------------+
1 row in set (0.00 sec)

root@localhost > 

MySQL5.7のMySQL HTTP PluginにてMySQLに直接HTTPクライアントでアクセス出来るので、
基本的な動作をマニュアルを見ながら確認してみました。
DBはmysql-5.7.5
MySQL Labs :: MySQL HTTP Plugin

1.先ずは、LabsからダウンロードしてMySQL5.7とプラグインをインストール

mysql> INSTALL PLUGIN myhttp SONAME 'libmyhttp.so';
Query OK, 0 rows affected (0.14 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+--------------+---------+
| Name                       | Status   | Type               | Library      | License |
+----------------------------+----------+--------------------+--------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL         | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL         | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL         | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| myhttp                     | ACTIVE   | DAEMON             | libmyhttp.so | GPL     |
+----------------------------+----------+--------------------+--------------+---------+
43 rows in set (0.00 sec)

mysql> 


mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='myhttp'\G
*************************** 1. row ***************************
           PLUGIN_NAME: myhttp
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: DAEMON
   PLUGIN_TYPE_VERSION: 50705.0
        PLUGIN_LIBRARY: libmyhttp.so
PLUGIN_LIBRARY_VERSION: 1.5
         PLUGIN_AUTHOR: Andrey Hristov, Ulf Wendel
    PLUGIN_DESCRIPTION: HTTP Plugin for MySQL
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
1 row in set (0.06 sec)

mysql> 

2. my.cnfをマニュアルを見て編集

myhttp_default_mysql_user_name = http_sql_user
myhttp_default_mysql_user_passwd = sql_secret
myhttp_default_mysql_user_host = 127.0.0.1

3. myhttpデータベースを検証用に作成しテスト用テーブルとユーザーを作成

USE `myhttp`;

CREATE TABLE `simple` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col_a` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `simple` VALUES (1,'Hello'),(2,' '),(3,'world!');
CREATE TABLE `sql_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col_char` char(127) NOT NULL,
`col_null` char(1) DEFAULT NULL,
`col_date` date NOT NULL,
`col_decimal` decimal(5,2) NOT NULL,
`col_float` float NOT NULL,
`col_bigint` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sql_types` VALUES (1,'CHAR(127)',NULL,'2014-08-21',123.45,0.9999,9223372036854775807);
INSERT INTO `sql_types` VALUES (2,'CHAR(127)',NULL,'2014-08-22',678, -1.11,-9223372036854775800);

CREATE TABLE `dojo_jsonp` (
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`version` int(10) unsigned DEFAULT '1',
`dojo_blob` blob,
`dojo_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`dojo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `dojo_jsonp` VALUES ('2014-08-22 07:11:43',1,'{\"first_name\": \"Ulf\",\"last_name\": \"Wendel\", \"email\": \"ulf.wendel@example.com\"}',1),
('2014-08-22 07:12:04',1,'{\"first_name\": \"Andrey\", \"last_name\": \"Hristov\",\"email\": \"andrey.hristov@example.com\"}',2);

CREATE TABLE `dojo_jsonp_fields` (
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT '',
`email` varchar(255) DEFAULT '',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`version` int(10) unsigned DEFAULT '1',
`dojo_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`dojo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `dojo_jsonp_fields` VALUES ('Andrey','Hristov','andrey.hristov@example.com','2014-08-22 07:27:23',1,1);
INSERT INTO `dojo_jsonp_fields` VALUES ('Ulf','Wendel','ulf.wendel@example.com','2014-08-22 07:30:37',1,2);

CREATE TABLE `no_primary_key` (
`id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `no_primary_key` VALUES (1),(2),(3);

CREATE TABLE `compound_primary_key` (
`col_a` int(11) NOT NULL,
`col_b` int(11) NOT NULL,
PRIMARY KEY (`col_a`,`col_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `compound_primary_key` VALUES (1,1),(1,2),(1,3),(2,1);


CREATE USER 'http_sql_user'@'192.168.56.0/255.255.255.0' IDENTIFIED WITH mysql_native_password;
SET old_passwords = 0;
SET PASSWORD FOR 'http_sql_user'@'192.168.56.0/255.255.255.0' = PASSWORD('sql_secret');
GRANT ALL ON myhttp.* TO 'http_sql_user'@'192.168.56.0/255.255.255.0';

4.CurlでDBにアクセスしてみて、SELECTやINSERTが出来る事を確認。

curl --user basic_auth_user:basic_auth_passwd --url "http://192.168.56.108:8080/sql/myhttp/SELECT+%2A+FROM+simple"
curl --user basic_auth_user:basic_auth_passwd --url "http://192.168.56.108:8080/sql/myhttp/INSERT+INTO+simple%28col_a%29+VALUES+%28%27Yippie%27%29"

curl

root@localhost > select * from simple;
+----+--------+
| id | col_a  |
+----+--------+
|  1 | Hello  |
|  2 |        |
|  3 | world! |
|  4 | Yippie |
+----+--------+
4 rows in set (0.00 sec)

root@localhost > 

直接、DBにアクセスする事でDBAがパフォーマンス状態を取得するなど管理用途などで利用出来そうなので時間をとって追加で確認して見ます。

ちなみにGENERALログはこんな感じになります。
general-log