TL;DR
11年ぶり(もうそんなに前なのか…)にmysqldがhttpを喋るようになる
MySQL :: MySQL 26.10 Reference Manual :: 7.5.10 MySQL REST Service Component
思ったより面倒だったので諦めるまでの経緯をメモ
MySQL Shell特有構文がどんなSQLに変換されてるのかをgeneral_logから調べると楽しい鴨（たぶんやらない）
MySQL 26.10.0 Early Access Is Here | mysql ではリリースノートしか出てなかったので、前提条件とか色々わからずドキュメントが出てやっと真面目に試せた機能。
特にこの MySQL :: MySQL 26.10 Reference Manual :: 7.5.10.1 Prerequisites が全然わからなかったので途中で投げ出していた。
なおこのPrerequisitesを読んでアカウントを作り始めると詰まるので、 MySQL :: MySQL 26.10 Reference Manual :: 7.5.10.2 Installing MySQL REST Service Component でやるのが良さげ。
### 「MySQL Shellで」ログインしてから
MySQL localhost SQL > CONFIGURE REST METADATA;
Query OK, 0 rows affected (1.6131 sec)
REST metadata configured successfully.
### ここから先はmysqlコマンドラインクライアントでもそのままmysqlshでもOKっぽい
mysql2610 19> INSTALL COMPONENT 'file://component_mysql_rest_service';
Query OK, 0 rows affected (0.001 sec)
MySQL localhost SQL > SHOW GLOBAL STATUS LIKE 'mysql_rest_service.%';
+----------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql_rest_service.current_http_port | 33443 |
| mysql_rest_service.current_io_worker_threads | 1 |
| mysql_rest_service.current_tls_cert | /usr/mysql/26.10.0/data/server-cert.pem |
| mysql_rest_service.current_tls_cipher | ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_CCM_SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES256-CCM:ECDHE-ECDSA-AES128-CCM |
| mysql_rest_service.current_tls_key | /usr/mysql/26.10.0/data/server-key.pem |
| mysql_rest_service.state | READY |
+----------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.0008 sec)
mysql2610 19> SELECT user, host, authentication_string, plugin, account_locked FROM mysql.user;
+----------------------------------+-----------+------------------------------------------------------------------------+-----------------------+----------------+
| user | host | authentication_string | plugin | account_locked |
+----------------------------------+-----------+------------------------------------------------------------------------+-----------------------+----------------+
| mysql_rest_service_admin | % | | caching_sha2_password | Y |
| mysql_rest_service_data_provider | % | | caching_sha2_password | Y |
| mysql_rest_service_dev | % | | caching_sha2_password | Y |
| mysql_rest_service_meta_provider | % | | caching_sha2_password | Y |
| mysql_rest_service_schema_admin | % | | caching_sha2_password | Y |
| mysql_rest_service_user | % | | caching_sha2_password | Y |
| mysql_task_admin | % | | caching_sha2_password | Y |
| mysql_task_user | % | | caching_sha2_password | Y |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | Y |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | Y |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | Y |
| mysql_mrs_data | localhost | | caching_sha2_password | Y |
| mysql_mrs_metadata | localhost | | caching_sha2_password | Y |
| root | localhost | | caching_sha2_password | N |
+----------------------------------+-----------+------------------------------------------------------------------------+-----------------------+----------------+
14 rows in set (0.000 sec)
mysql2610 19> SHOW DATABASES;
+-----------------------------+
| Database |
+-----------------------------+
| information_schema |
| mysql |
| mysql_rest_service_metadata |
| mysql_tasks |
| performance_schema |
| sys |
+-----------------------------+
6 rows in set (0.001 sec)
CREATE USERもCREATE ROLEも全部勝手にやってくれる。authentication_stringが空なので空パスワードかよと思ったけれどアカウントロックされてた。
俺のroot@localhostが空パスワードなのは仕様です（）
さてこれで
$ ss -ltpn | grep mysqld
LISTEN 0 128 0.0.0.0:33443 0.0.0.0:* users:(("mysqld",pid=3383608,fd=77))
LISTEN 0 4096 127.0.0.1:63610 0.0.0.0:* users:(("mysqld",pid=3383608,fd=18))
LISTEN 0 4096 *:62610 *:* users:(("mysqld",pid=3383608,fd=20))
33443がどうやらhttps用に空けているポートっぽい。
$ curl localhost:33443
curl: (52) Empty reply from server
そのままでは何も返ってこず。
どうやらエンドポイントとスキーマの紐づけをしないといけないので再びMySQL Shellへ。
MySQL localhost SQL > CREATE REST SERVICE /yoku0825;
Query OK, 0 rows affected (0.0067 sec)
REST SERVICE `/yoku0825` created successfully.
MySQL localhost SQL > SHOW CREATE REST SERVICE /yoku0825\G
*************************** 1. row ***************************
CREATE REST SERVICE: CREATE OR REPLACE REST SERVICE /yoku0825
OPTIONS {
"http": {
"allowedOrigin": "auto"
},
"headers": {
"Access-Control-Allow-Headers": "Content-Type, Authorization, X-Requested-With, Origin, X-Auth-Token",
"Access-Control-Allow-Methods": "GET, POST, PUT, DELETE, OPTIONS",
"Access-Control-Allow-Credentials": "true"
},
"logging": {
"request": {
"body": true,
"headers": true
},
"response": {
"body": true,
"headers": true
},
"exceptions": true
},
"includeLinksInResults": false,
"returnInternalErrorDetails": true
};
1 row in set (0.0063 sec)
MySQL localhost SQL > USE REST SERVICE /yoku0825
-> ;
Query OK, 0 rows affected (0.0000 sec)
Now using REST SERVICE `/yoku0825`.
MySQL localhost SQL > CREATE REST SCHEMA /yoku0826 FROM d1;
Query OK, 0 rows affected (0.0066 sec)
REST SCHEMA `/yoku0825/yoku0826` created successfully.
MySQL localhost SQL > SHOW CREATE REST SCHEMA /yoku0826\G
*************************** 1. row ***************************
CREATE REST SCHEMA : CREATE OR REPLACE REST SCHEMA /yoku0826 ON SERVICE /yoku0825
FROM `d1`
AUTHENTICATION NOT REQUIRED;
1 row in set (0.0068 sec)
MySQL localhost SQL > CREATE REST VIEW /t1 ON SCHEMA /yoku0826 AS t1;
Query OK, 0 rows affected (0.0082 sec)
REST VIEW `/yoku0825/yoku0826/t1` created successfully.
MySQL localhost SQL > SHOW CREATE REST VIEW /t1\G
*************************** 1. row ***************************
CREATE REST VIEW: CREATE OR REPLACE REST VIEW /t1
ON SERVICE /yoku0825 SCHEMA /yoku0826
AS `d1`.`t1` CLASS Yoku0825Yoku0826T1 {
num: num @KEY,
val: val
}
AUTHENTICATION REQUIRED;
1 row in set (0.0066 sec)
MySQL localhost SQL > ALTER REST SERVICE /yoku0825 PUBLISHED;
Query OK, 1 row affected (0.0141 sec)
publishすると /RESTサービス名/RESTスキーマ名/REST VIEW名 でデータが取れるようになりそう。
$ curl -sk https://localhost:33443/yoku0825/yoku0826/t1 | jq .
{
"message": "Unauthorized",
"status": 401
}
$ curl -sk 'https://localhost:33443/yoku0825/yoku0826/metadata-catalog/' | jq .
{
"items": [
{
"name": "/t1",
"links": [
{
"rel": "describes",
"href": "/yoku0825/yoku0826/t1"
},
{
"rel": "canonical",
"href": "/yoku0825/yoku0826/metadata-catalog/t1"
}
]
}
],
"limit": 25,
"offset": 0,
"hasMore": false,
"count": 1,
"links": [
{
"rel": "self",
"href": "/yoku0825/yoku0826/metadata-catalog/"
}
]
}
SHOW CREATE REST VIEW の結果で AUTHENTICATION REQUIRED になってるからかなと思ってDROPして作り直そうとか色々したんだけど、 CREATE REST VIEW の時点で AUTHENTICATION NOT REQUIRED みたいなのは最初から指定できないみたい。
なので足すだけ足す。
MySQL localhost SQL > CREATE REST ROLE read_only;
Query OK, 0 rows affected (0.0085 sec)
REST ROLE `read_only` created successfully.
MySQL localhost SQL > GRANT REST READ ON SCHEMA /yoku0825 TO read_only;
Query OK, 0 rows affected (0.0074 sec)
GRANT to `read_only` added successfully.
MySQL localhost SQL > CREATE REST AUTH APP aaa VENDOR MRS;
Query OK, 0 rows affected (0.0036 sec)
REST AUTH APP `aaa` created successfully.
MySQL localhost SQL > CREATE REST USER 'bbb'@'aaa' IDENTIFIED BY 'VeryVerySecret1!';
Query OK, 0 rows affected (0.0102 sec)
REST USER `:"bbb"@"aaa"` created successfully.
MySQL localhost SQL > GRANT REST ROLE read_only TO 'bbb'@'aaa';
Query OK, 0 rows affected (0.0075 sec)
GRANT ROLE to `bbb`@`aaa` added successfully.
MySQL localhost SQL > ALTER REST SERVICE /yoku0825 ADD AUTH APP 'aaa';
Query OK, 1 row affected (0.0083 sec)
AUTH APP, USERはどうやらかっ飛ばせないっぽい(AUTH APPなしのUSER作ろうとすると怒られる）
最後の ADD AUTH APP 'xx' に気が付かなくてかなりハマった。
作ったアカウントでどうcurlに繋げるかはこうっぽい
$ curl -sk -X POST -d '{"username":"bbb", "nonce":"blahblahblah", "authApp": "aaa", "sessionType": "bearer"}' 'https://localhost:33443/yoku0825/authentication/login'
{"session":"7055341b-134a-4b61-3fc4-c0bbc0bb3fc4","iterations":5000,"nonce":"blahblahblah^F}:E36vt9bR","salt":[74,140,150,53,78,92,144,180,235,196,144,64,103,163,232,252,227,49,109,170]}
が、俺にはそんな認証を頑張るアレはないのでここで力尽きたのであった。
