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"
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がパフォーマンス状態を取得するなど管理用途などで利用出来そうなので時間をとって追加で確認して見ます。