mita2 database life

主にMySQLに関するメモです

InnoDB FTSで検索できない文字があるときは innodb_ft_enable_stopword のせいかも

MySQL

InnoDB FTS シリーズが続きます。今回は innodb_ft_enable_stopword についてです。

mita2db.hateblo.jp

mita2db.hateblo.jp

TL; DR

  • ngram で、yakitori がヒットしない
mysql> SELECT * FROM fts_ngram;
+----+----------+
| id | c1       |
+----+----------+
|  1 | yakitori |
+----+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM fts_ngram WHERE MATCH(`c1`) 
> AGAINST('tori' IN BOOLEAN MODE);
Empty set (0.00 sec)
  • innodb_ft_enable_stopword が ON が原因
    • innodb_ft_enable_stopword は デフォルトでON
  • LIKE検索のような単純な文字列マッチを行う場合は、innodb_ft_enable_stopword = OFF にすべき

ストップワードとは何か

ストップワードは検索インデックスに登録されません。 MySQLでは、デフォルトのストップワードとして、このように英語の文章で頻出する単語があらかじめ登録されています。 人間は、（固有名詞など）特徴のあるキーワードで検索をします。以下のような頻出語を検索することはないため、インデックス化の対象外となっているわけです。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+
36 rows in set (0.00 sec)

実際に試してみます。There is an apple という文章に対して、FULLTEXTインデックスを作成します。

デフォルトのトークナイザはスペース区切りでトークナイズします。この場合は、there is an apple の４つにトークナイズされることになります。

しかし、インデックスに保存されたワードは apple と there のみになっています。 is と an は ストップワードに マッチ（完全一致）したため、無視されています。

mysql> CREATE TABLE `fts_defparser` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

mysql> INSERT INTO fts_defparser (c1) VALUES ('There is an apple');

mysql> SET GLOBAL innodb_ft_aux_table='t/fts_defparser';
mysql> SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE;
+-------+--------------+-------------+-----------+--------+----------+
| WORD  | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-------+--------------+-------------+-----------+--------+----------+
| apple |            2 |           2 |         1 |      2 |       12 |
| there |            2 |           2 |         1 |      2 |        0 |
+-------+--------------+-------------+-----------+--------+----------+
2 rows in set (0.01 sec)

apple は検索できますが、

mysql> SELECT * FROM fts_defparser WHERE MATCH(`c1`) AGAINST('apple' IN BOOLEAN MODE);
+----+-------------------+
| id | c1                |
+----+-------------------+
|  2 | There is an apple |
+----+-------------------+
1 row in set (0.00 sec)

an はインデックス化されなかったため、検索することができません。

mysql> SELECT * FROM fts_defparser WHERE MATCH(`c1`) AGAINST('an' IN BOOLEAN MODE);
Empty set (0.00 sec)

ngram の場合の挙動

ngram の場合の挙動は MySQL Server Team のブログ に以下のような記述があります。 要するにトークナイズした結果が、ストップワードに部分一致すると（ngramでなければ、完全一致でした）、そのワードはインデックスに登録されないと。

ストップワードの処理： stopwordsの処理も少し異なります。通常、トークン化された単語自体(完全一致)がストップワードテーブルにあるならば、その単語には全文検索インデックスは作成されません。しかしながら、n-gramパーサーの場合は、トークン化された単語がストップワードを含んでいないか確認し、含んでいればインデックスを作成しません。このように動作が異なる理由は、私たちのCJKは非常に多くの頻繁に使われる無意味な文字、単語、句読点を持っているからです。ストップワードに一致する文字が含まれているかを確認する方式を使うと、より役に立たないトークンを除去できます。

yakitori は ya ak ki it to or ri にトークナイズされます。 it to or はストップワードに完全一致していますが、ya ak ki ri もa や i に部分一致しているため、無視されてしまいました。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD WHERE VALUE IN ('a', 'it', 'to', 'i', 'or');
+-------+
| value |
+-------+
| a     |
| i     |
| it    |
| or    |
| to    |
+-------+
5 rows in set (0.00 sec)

では、innodb_ft_enable_stopword をOFFにして確認します。

mysql> SET innodb_ft_enable_stopword = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> OPTIMIZE TABLE fts_ngram;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| t.fts_ngram | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| t.fts_ngram | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.23 sec)

mysql> SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE ORDER BY POSITION;
+------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| ya   |            2 |           2 |         1 |      2 |        0 |
| ak   |            2 |           2 |         1 |      2 |        1 |
| ki   |            2 |           2 |         1 |      2 |        2 |
| it   |            2 |           2 |         1 |      2 |        3 |
| to   |            2 |           2 |         1 |      2 |        4 |
| or   |            2 |           2 |         1 |      2 |        5 |
| ri   |            2 |           2 |         1 |      2 |        6 |
+------+--------------+-------------+-----------+--------+----------+
7 rows in set (0.01 sec)

mysql>  SELECT * FROM fts_ngram WHERE MATCH(`c1`) AGAINST('tori' IN BOOLEAN MODE);
+----+----------+
| id | c1       |
+----+----------+
|  1 | yakitori |
+----+----------+
1 row in set (0.00 sec)

yakitoriが検索できるようになりました！

Mroonga から InnoDB FTS への乗り換えを考えてみた

MySQL

このエントリーはMySQL Casual Advent Calendar 2019 の7日目です。 実は、毎年 12 /7 日書いてます。

mita2db.hateblo.jp

mita2db.hateblo.jp

--

昨日は、@SHINOHARATTT さんでした。 ポケモンを題材にして論理設計を学ぶ　というエントリーでした。楽しく学べて良いです！

qiita.com

--

本エントリーではMroonga と InnoDB FTS の比較を軽くしてみたいと思います。

InnoDB FTS に乗り換えるモチベーション

  1. 耐障害性を高めたい
    mroonga ストレージエンジンは クラッシュセーフではありません。運が悪いと障害時に直前にコミットした内容が失われる可能性があります。 ラッパーモードでもテーブル本体はInnoDBなため、クラッシュセーフですが、インデックスはクラッシュセーフではありません。 障害時にテーブルとインデックスの整合性が崩れ「テーブルにはデータがあるのに、全文検索でSELECTするとヒットしない」という状況になりえます*1。InnoDB FTS であればクラッシュセーフです。

  2. Group Replication への準備
    MySQL Group Replication は InnoDB のみをサポートします。GR構成を利用するには、mroongaを脱却する必要があります。とはいえ、GR構成でマスターを運用しつつ、スレーブだけ mroonga のストレージエンジンを使うという回避方法もあります。

mroonga を使い続けるモチベーション

  1. パフォーマンス
    mroongaとInnoDB FTSの性能特性には大きな違いがあります。 以下の数値は @ktou さんのスライドから、お借りしたものです。このテストケースでは mroonga のほうが圧倒的に高速です。 どちらが速いかはケースバイケースだと思いますが、InnoDB FTSだとパフォーマンス要件を満たせないケースも十分あり得ます。 f:id:mita2db:20191206154558p:plain

  2. 豊富なトークナイザ
    InnoDB FTSはシンプルなN-gramかMecabによるトークナイズしかサポートしていません。 mroongaは多くのトークナイザをサポートしており、要件に応じてノイズの少ない検索結果を得ることができます。 実際に比較してみた例を、この後に記載しています。

  3. NATURAL LANGAGE MODE で結果セット（SELECT結果）が変化する
    Mroonga と InnoDB FTS ではスコアの計算方法が違います。 また、スコアだけでなく、マッチするレコード数も変わってしまいます。 これについては、また、別のエントリーで書きたいと思います。

  4. 豊富な検索オプション
    以下は、mroonga のスニペットの例です*2。検索語句にマッチした文字列の前後に、任意の文字列を付加できます。 スニペット機能を使うことで、マッチした文字列をハイライトする機能が簡単に実装できます。

mysql> SELECT id, text, mroonga_snippet(text, 8, 2, 'ascii_general_ci', 1, 1, '...', '...<br>', 'fulltext', '<span class="w1">', '</span>', 'MySQL', '<span class="w2">', '</span>', 'search', '<span calss="w3">', '</span>') FROM snippet_test WHERE MATCH(text) AGAINST ('+fulltext' IN BOOLEAN MODE);
+----+-------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | text                                                                                            | mroonga_snippet(text, 8, 2, 'ascii_general_ci', 1, 1, '...', '...<br>', 'fulltext', '<span class="w1">', '</span>', 'MySQL', '<span class="w2">', '</span>', 'search', '<span calss="w3">', '</span>') |
+----+-------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | An open-source fulltext search engine and column store.                                         | ...<span class="w1">fulltext</span>...<br>... <span calss="w3">search</span> ...<br>                                                                                                                   |
|  2 | An open-source storage engine for fast fulltext search with MySQL.                              | ...<span class="w1">fulltext</span>...<br>... <span calss="w3">search</span> ...<br>                                                                                                                   |
|  3 | Tritonn is a patched version of MySQL that supports better fulltext search function with Senna. | ...f <span class="w2">MySQL</span> ...<br>...<span class="w1">fulltext</span>...<br>                                                                                                                   |
+----+-------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

スニペット以外にも、「痒い所に手が届く」機能がmroongaには多くあります。

トークナイザ比較

以下のようなテストデータで、代表的なトークナイザの結果比較を行います。

mysql> SELECT * FROM bigram_fts ;
+----+------------------------------------------------+
| id | c1                                             |
+----+------------------------------------------------+
|  1 | yoku0825さん、おはようございMySQL！                |
+----+------------------------------------------------+
2 rows in set (0.00 sec)

Mroonga TokenBigram vs InnoDB FTS ngram + ngram_token_size = 2

TokenBigram は mroonga のデフォルトのトークナイザです。Bigramは２文字ずつに区切ってトークナイズします。

Mroonga の TokenBigram と同じ２文字区切りにするため、ngram_token_size は2を設定しています。

InnoDB FTS の場合： ※ マッチした部分に * でマークしています。

mysql> SELECT * FROM bigram_fts WHERE MATCH(`c1`) AGAINST('SQ' IN BOOLEAN MODE);
+----+------------------------------------------------+
| id | c1                                             |
+----+------------------------------------------------+
|  1 | yoku0825さん、おはようございMy*SQ*L！              |
+----+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM bigram_fts WHERE MATCH(`c1`) AGAINST('82' IN BOOLEAN MODE);
+----+------------------------------------------------+
| id | c1                                             |
+----+------------------------------------------------+
|  1 | yoku0*82*5さん、おはようございMySQL！              |
+----+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM bigram_fts WHERE MATCH(`c1`) AGAINST('5さ' IN BOOLEAN MODE);
+----+------------------------------------------------+
| id | c1                                             |
+----+------------------------------------------------+
|  1 | yoku082*5さ*ん、おはようございMySQL！              |
+----+------------------------------------------------+
1 row in set (0.00 sec)

Mroonga の場合：

mysql> SELECT * FROM bigram_mrn WHERE MATCH(`c1`) AGAINST('SQ' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql> SELECT * FROM bigram_mrn WHERE MATCH(`c1`) AGAINST('82' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql> SELECT * FROM bigram_mrn WHERE MATCH(`c1`) AGAINST('5さ' IN BOOLEAN MODE);
Empty set (0.00 sec)

InnoDB FTS では LIKE検索のように、単に文字列が存在していれば、SELECT結果に含まれます。 一方、Mroongaでは検索にマッチしませんでした。

この差は、MroongaのTokenBigramの以下の仕様に由来します。

バイグラムでトークナイズする。ただし、連続したアルファベット・連続した数字・連続した記号はそれぞれ1つのトークンとして扱う。そのため、3文字以上のトークンも存在する。これはノイズを減らすためである。

「MySQL」や「0825」であればどちらのストレージエンジンでもマッチします。

Mroonga TokenBigramSplitSymbolAlphaDigit vs InnoDB FTS ngram + ngram_token_size = 2

Mroonga の TokenBigramSplitSymbolAlphaDigit の説明には以下のように記載があります。 このトークナイザであれば、Mroonga と InnoDB FTS の結果は一致しそうです。

バイグラムでトークナイズする。 TokenBigramSplitSymbolAlpha に加えて、連続した数字も特別扱いせずに通常のバイグラムの処理を行う。つまり、すべての字種を特別扱いせずにバイグラムの処理を行う。

念のため、試してみます。

Mroonga の場合：

mysql> SELECT * FROM bigram_mrn_alphadigit WHERE MATCH(`c1`) AGAINST ('SQ' IN BOOLEAN MODE);
+----+------------------------------------------------+
| id | c1                                             |
+----+------------------------------------------------+
|  1 | yoku0825さん、おはようございMySQL！            |
+----+------------------------------------------------+
1 row in set (0.03 sec)

mysql> SELECT * FROM bigram_mrn_alphadigit WHERE MATCH(`c1`) AGAINST ('82' IN BOOLEAN MODE);
+----+------------------------------------------------+
| id | c1                                             |
+----+------------------------------------------------+
|  1 | yoku0825さん、おはようございMySQL！            |
+----+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM bigram_mrn_alphadigit WHERE MATCH(`c1`) AGAINST ('5さ' IN BOOLEAN MODE);
+----+------------------------------------------------+
| id | c1                                             |
+----+------------------------------------------------+
|  1 | yoku0825さん、おはようございMySQL！            |
+----+------------------------------------------------+
1 row in set (0.00 sec)

TokenBigramSplitSymbolAlphaDigit をつかっているケースでは、InnoDB FTS の ngram と同一の結果になることが確認できました。

まとめ

ということで、mroonga から InnoDB FTS に変更することを検討してみた結果でした。 mroongaのデフォルトの TokenBigram の挙動が InnoDB FTS とは異なるため、注意が必要そうです。

明日のAdvent Calendar は、@kk2170さんです。 去年はMySQLでクリスマスケーキを焼いてらっしゃいました。今年はどんな内容か楽しみです。

*1:その場合、テーブルをリビルドし、インデックスを再作成することで解決します

*2:mroonga 公式マニュアルより

InnoDB FULLTEXT Search の ngram_token_size

MySQL

ngram_token_size パラメータは InnoDB FTS の ngram パーサーの設定です。ngram_token_size に指定した文字数ごとに文章をトークナイズします。

疑問

  • ngram_token_size をインデックス作成後から変更すると、どうなるのか？
  • あり得るパターン
    1. CREATE INDEX した時の ngram_token_size をインデックスが保持しており、変更後も既存のインデックスは影響を受けない
    2. ngram_token_size 変更前後で、１つのインデックスに異なるトークンサイズのトークンが存在してしまう

試してみます

mysql> SET GLOBAL ngram_token_size=3;
ERROR 1238 (HY000): Variable 'ngram_token_size' is a read only variable

おっと、、、ngram_token_size はオンラインで変更できなかった、、、 この時点で、パターン2臭がする、、、

まず、token_size=2 で インデックスを作ります。

mysql> SHOW GLOBAL VARIABLES LIKE 'ngram_token%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO fts.fts_check (col1) VALUES('にぐらむ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| ぐら   |            6 |           6 |         1 |      6 |        3 |
| にぐ   |            6 |           6 |         1 |      6 |        0 |
| らむ   |            6 |           6 |         1 |      6 |        6 |
+--------+--------------+-------------+-----------+--------+----------+
3 rows in set (0.00 sec)

ngram_toke_size=2、2文字区切りで、トークナイズされていることが確認できました。 次に、ngram_toke_size=3 にして、データを追加します。

mysql> SHOW GLOBAL VARIABLES LIKE 'ngram_token%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 3     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO fts.fts_check (col1) VALUES('トリグラム');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| ぐら   |            6 |           6 |         1 |      6 |        3 |
| にぐ   |            6 |           6 |         1 |      6 |        0 |
| らむ   |            6 |           6 |         1 |      6 |        6 |
+--------+--------------+-------------+-----------+--------+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE;
+-----------+--------------+-------------+-----------+--------+----------+
| WORD      | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-----------+--------------+-------------+-----------+--------+----------+
| グラム    |            8 |           8 |         1 |      8 |        6 |
| トリグ    |            8 |           8 |         1 |      8 |        0 |
| リグラ    |            8 |           8 |         1 |      8 |        3 |
+-----------+--------------+-------------+-----------+--------+----------+
3 rows in set (0.00 sec)

パターン2 でした！新しく登録したレコードは設定変更の影響を受けて３文字区切りでトークナイズされてる。 １つのインデックスに２文字区切りと、３文字区切りでトークナイズされた結果が混ざってしまっています。

念の為、検索して、確認してみます。

mysql> SHOW GLOBAL VARIABLES LIKE 'ngram_token%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 3     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM fts_check WHERE MATCH(col1) AGAINST (+'にぐ' IN BOOLEAN MODE);
+----+--------------+
| id | col1         |
+----+--------------+
|  1 | にぐらむ     |
+----+--------------+
1 row in set (0.00 sec)

-- ngram_token_size =  3 なので、「にぐら」でインデックスから検索する。
-- インデックスには「にぐ」しかないのでヒットしない。
mysql> SELECT * FROM fts_check WHERE MATCH(col1) AGAINST (+'にぐら' IN BOOLEAN MODE);
Empty set (0.01 sec)

-- インデックスに「トリ」はないのでヒットしない
mysql> SELECT * FROM fts_check WHERE MATCH(col1) AGAINST (+'トリ' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql> SELECT * FROM fts_check WHERE MATCH(col1) AGAINST (+'トリグ' IN BOOLEAN MODE);
+----+-----------------+
| id | col1            |
+----+-----------------+
|  4 | トリグラム      |
+----+-----------------+
1 row in set (0.00 sec)

まとめ

  • ngram_token_size を変更したら、インデックスを再作成しないと、既存のインデックスに反映されない
mysql> OPTIMIZE TABLE fts_check;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| fts.fts_check | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| fts.fts_check | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.05 sec)
  • そして、スキーマやテーブルごとにngram_token_sizeを変更することはできない

MySQL admin_address にワイルドカードは指定不可

MySQL

先日書いたエントリーについて、

mita2db.hateblo.jp

MySQL Weekly で tom__bo さんから頂いた、感想へのフィードバック（と補足）です。

mysql-weekly.hatenablog.com

僕がやりたかったこと

  • 接続あふれしている時でも、DBAは管理者アカウントで接続できるようにしたい
  • しかし、管理者アカウントは増やしたくない
    • 今ある、root@localhost だけを使いたい
    • SERVICE_CONNECTION_ADMIN 権限を持ったユーザを追加で作ることも避けたい
  • また、管理者アカウントはローカル接続のみとしたい

結論

  • skip_network_resolve = OFF + admin_address = 127.0.0.1 で実現可

admin_address はワイルドカードが使えない

tom__bo さんからワイルドカードを使うのはどうか？というアイデアを貰いましたが、残念ながらadmin_address はワイルドカードを受け入れてくれません。。。

If admin_address is specified, its value must satisfy these requirements:

The value must be a single IPv4 address, IPv6 address, or host name.

The value cannot specify a wildcard address format (*, 0.0.0.0, or ::). https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

フィードバックありがとうございます

HAツールがtoo many connectionsに巻き込まれないようにとか考えると接続自体は*(すべて)で受け付けて通常のアカウント認証に任せるのではだめなのかなと思ったりもした。

DBA以外が管理IFを活用するという視点はなかったです 😲 MySQL 8.0 になって細かく権限を指定できるようになったし、アカウントを増やさないという拘りは捨ててもいいかもしれない。

tom__bo さんフィードバックありがとうございます！

MySQLで管理用IFを使いつつ特権ユーザをローカルアカウントのみに絞りたい

MySQL

以前、接続が溢れているとき（too many connectionsエラー発生時）でも接続可能にする Administrative Network Interface（管理NW IF） の概要をブログに書きました。この時は、特権アカウントの接続元との関連は書かなかったのですが、特権アカウントの接続元をローカルのみに絞りたい場合、少し考えないといけないことがありました。

mita2db.hateblo.jp

特権アカウントはローカル接続のみとしたい

特権アカウントはローカル接続のみとしている(特権で接続するためにDBサーバへのSSHログインを前提とする)場合に、どのように管理用IFを設定すれば良いか試してみました。

ダメな設定

# /etc/my.cnf
admin_port=3307
admin_address=192.168.1.100

まず、このようにadmin_addressにDBサーバのIPアドレスを指定した場合、Admin IFである3307ポートへのアクセスはリモートアクセスのみになります。

# ローカルの3307ポートはLISTENされてない
$ mysql -uroot -P3307 -h127.0.0.1
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

結果として、(root@'192.168.1.X'のような)リモートアクセス可能な特権アカウントを用意せざるを得ず、要件を満たせません。

要件を満たす設定

admin_address にループバックIF(127.0.0.1)を指定することで、ローカルからの接続が可能になります。127.0.0.1の代わりにlocalhostでも問題ありません。

# /etc/my.cnf
admin_port=3307
admin_address=127.0.0.1
$ mysql -uroot
ERROR 1040 (HY000): Too many connections

# 3307から接続すると、too many connections が出ている状況でも接続可能になっている
$ mysql -uroot -P3307 -h127.0.0.1
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

root@localhostユーザを利用した接続になっていますね。 この設定であれば、追加でリモートアクセス可能な特権アカウントは不要です。これで、要件を満たせそう・・・

と思ったのですが、これは skip_name_resolve が OFF になっていることが前提でした。 接続元ホスト127.0.0.1が名前解決され localhost になるため、root@localhost ユーザ が利用されます。

skip_name_resolve = ON のケース

skip_name_resolve を有効にしている場合は、root@127.0.0.1 ユーザを追加する必要があります。

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1';

root@localhostはUNIXドメインソケット経由の接続のために残す必要があります。 root@localhost と root@127.0.0.1 の２つの特権アカウントが存在する形になり、若干不便ですね・・・

まとめ

Administrative Network Interface を利用しつつ、特権ユーザはローカルホストからのみ接続許可としたい場合：

  1. skip_name_resolve = OFF + admin_address=127.0.0.1

  2. skip_name_resolve = ON + admin_address=127.0.0.1 + root@127.0.0.1 ユーザ

日本MySQLユーザ会 望年LT大会2019 と Casual Talks 開催告知

MySQL

はてなブログに引っ越して、初めての記事です。

先日、MyNA 忘年LT大会に参加してきました。 keny_lala さんが、MySQL クイズやったりしてて、面白かったです。 sakaik さん、企画ありがとうございます。来年もよろしくお願いします！

sakaik.hateblo.jp

自分はConnector/Node.js のドライバを試して、ハマったところを、LTしました。

www.slideshare.net

Connector / Node.js でこういうエラーが出た時は「URIのIDとパスワードを指定している部分に不正な文字が含まれている」という意味です。

$ node tutorial.js
Error: Invalid userinfo segment

このように接続子のURIにエンコードしていない文字（この場合は#）があると、このエラーがでます。

mysqlx.getSession('mysqlx://appuser:Password#a123@hostname:33060')

以下のようにエンコードしなければなりません。よくよく考えれば当たり前のことなのですが、userinfo って何を指しているのかわからず、小一時間悩んでしまいました。。。

mysqlx.getSession('mysqlx://appuser:Password%23a123@hostname:33060')

--

さて、MySQL Casual Talks vol.13 を 1/28（火）に開催します！ 登壇者募集中です。mita2 までご連絡ください。

mysql-casual.connpass.com

mita2db メモは、はてなブログに引っ越してきました

諸事情あって、blogger から はてなブログに引越ししました。 MySQL Geek な人たち、はてなブログ民が多い！

tmtms.hatenablog.com

sakaik.hateblo.jp

tombo2.hatenablog.com

next4us-ti.hatenablog.com

blog.kamipo.net

sh2.hatenablog.jp

rkajiyama.hatenablog.com

hiroi10.hatenablog.com

do-aki.hatenablog.jp

kenken0807.hatenablog.com

soudai.hatenablog.com