MySQL HTTP Plugin確認

by

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

Posted in: DB, MYSQL, インストール, 設定, 運用 ⋅ Tagged: , ,

Comments are closed.

Post Navigation