2019年5月6日月曜日

MySQL 8の新機能 RESOURCE GROUP と 権限


RESOURCE GROUPはざっくり言うと、セッションごとに利用できるCPUを制限したり固定できる機能です。以前、試した記事はこちら
意図せず、CPUリソースを制限されてしまうことのないよう、RESOURCE GROUPと権限の関係を調べてみました。

結論

  • RESOURCE GROUPを作成するには、RESOURCE_GROUP_ADMIN 権限が必要
  • RESOURCE GROUPを適用するには、RESOURCE_GROUP_USER 権限が必要
    • RESOURCE_GROUP_USER 権限を持っていれば、他人のセッションのリソースグループも変更できる
    • RESOURCE_GROUP_USER権限を与えるかどうかは慎重に考えたほうがよさそう

検証

 

作成

 

mysql> CREATE RESOURCE GROUP OneCPU TYPE=USER VCPU=0;
ERROR 1227 (42000): Access denied; you need (at least one of) the RESOURCE_GROUP_ADMIN privilege(s) for this operation

 

適用

 

何も権限がない状態。 

 

mysql> SET RESOURCE GROUP OneCPU;
ERROR 1227 (42000): Access denied; you need (at least one of) the RESOURCE_GROUP_ADMIN OR RESOURCE_GROUP_USER privilege(s) for this operation
 

 

testuser に RESOURCE_GROUP_USER を与える。

mysql> GRANT RESOURCE_GROUP_USER ON *.* TO testuser@'%';
Query OK, 0 rows affected (0.01 sec)


$ mysql -utestuser -p
mysql> SET RESOURCE GROUP OneCPU;
Query OK, 0 rows affected (0.00 sec)


testuserがtestuser以外のセッションに対して、RESOURCE GROUPを適用できるかチェック。

適用する対象は(SHOW PROCESSLISTで表示される) Connection Idではなく、Thread_id で指定する必要がある点に注意。
performance_schema から確認できる。


mysql> SELECT THREAD_ID, PROCESSLIST_USER, RESOURCE_GROUP  FROM performance_schema.threads
       WHERE PROCESSLIST_ID IS NOT NULL;
+-----------+------------------+----------------+
| THREAD_ID | PROCESSLIST_USER | RESOURCE_GROUP |
+-----------+------------------+----------------+
|        43 | NULL             | SYS_default    |
|        45 | NULL             | SYS_default    |
|        50 | NULL             | SYS_default    |
|        88 | testu2           | USR_default    |
|        89 | testuser         | USR_default    |
|        90 | root             | USR_default    |
+-----------+------------------+----------------+
6 rows in set (0.00 sec)


mysql> SET RESOURCE GROUP OneCPU FOR 88;
Query OK, 0 rows affected (0.00 sec)



mysql> SET RESOURCE GROUP OneCPU FOR 90;
Query OK, 0 rows affected (0.00 sec)



RESOURCE_GROUP_USER 権限を持っていれば、誰のセッションであろうと、リソースグループを適用できる仕様みたい。

2019年5月1日水曜日

MySQLの管理者用ネットワークインタフェース(administrative network interface)を試す


MySQL 8.0.14 から DB管理者用 に追加でネットワークインタフェースを設けることができるようになりました。

このように、接続あふれを起こしているときでも、管理者用IFであれば接続できます。

$ mysql -P3306 -uroot -pPass%word123 -h db01
ERROR 1040 (HY000): Too many connections


# 3307の管理者用IFに接続
$ mysql -P3307 -uroot -pPass%word123 -h db01
mysql> 


利用するには、admin_port と admin_address を設定します。ポート番号(admin_port)だけでなく、LISTENするIPアドレス(admin_address)も指定する必要があります。

[mysqld]
admin_address=db01
admin_port=3307

admin_address を省略したり、ワイルドカードを使うことはできないようです。
ポート番号だけ、変えられれば十分なんだけどな。。。
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_admin_address
If this variable is not specified at startup, the server maintains no administrative interface.
The value cannot specify a wildcard address format (*, 0.0.0.0, or ::)
このポートにはSERVICE_CONNECTION_ADMIN権限を持っていないと接続できません。

$ mysql -P3307 -uappuser -pPassword%123 -h db01
ERROR 1227 (42000): Access denied; you need (at least one of) the SERVICE_CONNECTION_ADMIN privilege(s) for this operation



実は、この機能を利用しなくても、特権ユーザであれば、max_connections+1までコネクションを貼ることができます。

# 非特権ユーザ
$ mysql -P3306 -uappuser -pPass%word123 -h db01
ERROR 1040 (08004): Too many connections

# root なら接続できる
$ mysql -P3306 -uroot -pPass%word123 -h db01
mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 3     |
+-----------------+-------+
1 row in set (0.01 sec)

# max_connections=3 に対して、4本目のセッションが貼れる
mysql> show processlist;
+----+-----------------+------------+------+---------+------+-------------------------------+------------------+
| Id | User            | Host       | db   | Command | Time | State                         | Info             |
+----+-----------------+------------+------+---------+------+-------------------------------+------------------+
|  5 | event_scheduler | localhost  | NULL | Daemon  |  238 | Waiting on empty queue        | NULL             |
|  9 | system user     |            | NULL | Connect |  238 | executing                     | NULL             |
| 12 | system user     |            | NULL | Query   |  238 | Slave has read all relay log; | NULL             |
| 32 | appuser         | db01:60348 | NULL | Sleep   |   71 |                               | NULL             |
| 33 | appuser         | db01:60350 | NULL | Sleep   |   69 |                               | NULL             |
| 34 | appuser         | db01:60352 | NULL | Sleep   |   67 |                               | NULL             |
| 36 | root            | db01:60356 | NULL | Query   |    0 | starting                      | show processlist |
+----+-----------------+------------+------+---------+------+-------------------------------+------------------+
7 rows in set (0.00 sec)


あくまでも、プラス1本だけなので、もう1個セッションを貼ろうとすると、拒否られる

$ mysql -P3306 -uroot -pPass%word123 -h db01
ERROR 1040 (HY000): Too many connections



トラブルシュートしていて、管理者が複数セッションを貼りたいというケースはよくあるので、admin_address/admin_port は役に立ちそうです

2019年4月25日木曜日

mysql_config_editor は シャープを含むパスワードをうまく扱えない

mysql_config_editor は ログイン情報を管理するためのコマンドです。
~/.mylogin.cnf に難読化されたIDやパスワード、接続先の情報が記録されます。


$ strings .mylogin.cnf
#Xu*|


難読化されているので、中身を確認するには、my_print_defaults コマンドで表示する必要があります。

$ my_print_defaults client -s
--user=root
--password=Pass
--host=localhost

 
さて、このコマンドなんですが、シャープを含む文字列をうまく扱えないバグがあります。
Pass#word123 というパスワードが Pass になってしまっています。
シャープは設定ファイルではコメントを示します。
設定ファイルに保存するときに、うまくエスケープしてない実装になってしまっているんでしょうね。。。

$ mysql_config_editor set --host=localhost --user=root --password
Enter password: Pass#word123

$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

$ my_print_defaults client -s
--user=root
--password=Pass
--host=localhost


バグレポもだいぶ昔に上がってました。まだ修正されてないみたいです。

https://bugs.mysql.com/bug.php?id=74691

2019年4月13日土曜日

MySQL 8の新機能 LATERAL句によるSQLチューニング

MySQL 8.0.14でLATERAL句がサポートされました。
一瞬、LITERALに空目しましたが、LATERAL(側面)でした。

LATERAL句を試してみます。

サンプルとしてこのようなテーブルを扱います。
従業員の一覧(employees)と、各従業員の特定の期間の月額給与(salaries)のテーブルです。

mysql> SELECT * FROM employees LIMIT 5;
+--------+-<snip>-+-----------+
| emp_no | <snip>-| last_name |
+--------+-<snip>-+-----------+
|  10001 | <snip>-| Facello   |
|  10002 | <snip>-| Simmel    |
|  10003 | <snip>-| Bamford   |
|  10004 | <snip>-| Koblick   |
|  10005 | <snip>-| Maliniak  |
+--------+-<snip>-+-----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM salaries LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)


さて、このテーブルから、各従業員の初任給の支給日と額を1回のSQLで取り出したいとします。

サブクエリで書くと、非常にわかりやすく書けます。

mysql> SELECT emp_no, last_name,
    ->  (SELECT MIN(from_date) FROM salaries
    ->                        WHERE salaries.emp_no = employees.emp_no) AS first_salary_date,
    ->  (SELECT salary         FROM salaries
    ->                        WHERE salaries.emp_no = employees.emp_no
    ->                          AND from_date = first_salary_date) AS first_salary
    -> FROM employees;
+--------+------------------+-------------------+--------------+
| emp_no | last_name        | first_salary_date | first_salary |
+--------+------------------+-------------------+--------------+
|  10001 | Facello          | 1986-06-26        |        60117 |
|  10002 | Simmel           | 1996-08-03        |        65828 |
|  10003 | Bamford          | 1995-12-03        |        40006 |
|  10004 | Koblick          | 1986-12-01        |        40054 |
|  10005 | Maliniak         | 1989-09-12        |        78228 |
|  10006 | Preusig          | 1990-08-05        |        40000 |
|  10007 | Zielinski        | 1989-02-10        |        56724 |
<snip>
300024 rows in set (6.99 sec)


実行計画を見ると、employees テーブルの各行に対して、salariesテーブルから、初任給の支給日と額を分けて読み取っています。
1人の従業員に対して、salariesテーブルを2回読み取っていて(DEPENDENT SUBQUERYが2回)、無駄に感じます。

+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------------+--------+----------+-------------+
| id | select_type        | table     | type   | possible_keys | key     | key_len | ref                             | rows   | filtered | Extra       |
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------------+--------+----------+-------------+
|  1 | PRIMARY            | employees | ALL    | NULL          | NULL    | NULL    | NULL                            | 299600 |   100.00 | NULL        |
|  3 | DEPENDENT SUBQUERY | salaries  | eq_ref | PRIMARY       | PRIMARY | 7       | employees.employees.emp_no,func |      1 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | salaries  | ref    | PRIMARY       | PRIMARY | 4       | employees.employees.emp_no      |      9 |   100.00 | Using index |
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------------+--------+----------+-------------+






 salariesテーブルから、支給日と額を同時に読み出して高速化を測りたくなりますが・・・

mysql> SELECT emp_no, last_name,
    ->  (SELECT from_date, salary FROM salaries
    ->                            WHERE salaries.emp_no = employees.emp_no
    ->                            ORDER BY from_date
    ->                            LIMIT 1) AS first_salary_date
    -> FROM employees;
ERROR 1241 (21000): Operand should contain 1 column(s)


サブクエリは単一のカラムのみを返す必要があり、思い通りにいきません。


mysql> SELECT emp_no, last_name,
    -> first_pay.from_date,
    -> first_pay.salary
    -> FROM employees,
    ->  (SELECT from_date, salary FROM salaries
    ->                           WHERE salaries.emp_no = employees.emp_no
    ->                        ORDER BY from_date LIMIT 1 ) AS first_pay;
ERROR 1054 (42S22): Unknown column 'employees.emp_no' in 'where clause'



FROMのほうでもダメです。FROM句では他のテーブルのカラムを参照することができないのです。

 
これが、LATERAL句を使うとFROMに書けるようになります。
salariesテーブルの読み取り回数が減ったぶん、実行時間が 6.99s→3.40s と速くなっています。

mysql> SELECT employees.emp_no, employees.last_name,
    -> first_pay.from_date,
    -> first_pay.salary
    -> FROM employees,
    -> LATERAL
    ->  (SELECT from_date, salary FROM salaries
    ->                        WHERE salaries.emp_no = employees.emp_no
    ->                        ORDER BY from_date LIMIT 1 ) AS first_pay;
300024 rows in set (3.40 sec)


+----+-------------------+------------+------+---------------+---------+---------+----------------------------+--------+----------------------------+
| id | select_type       | table      | type | possible_keys | key     | key_len | ref                        | rows   | Extra                      |
+----+-------------------+------------+------+---------------+---------+---------+----------------------------+--------+----------------------------+
|  1 | PRIMARY           | employees  | ALL  | NULL          | NULL    | NULL    | NULL                       | 299600 | Rematerialize (<derived2>) |
|  1 | PRIMARY           | <derived2> | ALL  | NULL          | NULL    | NULL    | NULL                       |      2 | NULL                       |
|  2 | DEPENDENT DERIVED | salaries   | ref  | PRIMARY       | PRIMARY | 4       | employees.employees.emp_no |      9 | Using filesort             |
+----+-------------------+------------+------+---------------+---------+---------+----------------------------+--------+----------------------------+



ちなにみ、このクエリはMySQL 8で新たにサポートされたWINDOW関数を使って書くこともできます。
今回のケースの場合、LATERALのほうが高速でした。

mysql> SELECT
    ->   DISTINCT
    ->   emp_no,
    ->   last_name,
    ->   FIRST_VALUE(from_date) OVER w,
    ->   FIRST_VALUE(salary) OVER w
    -> FROM
    -> (
    -> SELECT
    -> employees.emp_no,
    -> employees.last_name,
    -> salaries.from_date,
    -> salaries.salary
    -> FROM employees INNER JOIN salaries USING (emp_no)
    -> ) x
    -> WINDOW w AS (PARTITION BY x.emp_no ORDER BY x.from_date);
300024 rows in set (14.12 sec)


MySQL 8で新しい書き方が増えて、便利になった一方、パターンが増えて悩む。。。

2019年4月5日金曜日

SHOW PROCESSLISTのHostがパーセントになるケース

プロシージャでDEFINERを指定して作成すると、Hostの覧が%になる。以下詳細。

まず、通常のケース。Id 37のセッションは正しく root が localhost から接続していると表示される。

mysql> SELECT CONNECTION_ID( );
+------------------+
| CONNECTION_ID( ) |
+------------------+
|               37 |
+------------------+
1 row in set (0.00 sec)


mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
| Id | User            | Host      | db   | Command | Time   | State                    | Info             |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 605477 | Waiting on empty queue   | NULL             |
<snip>
| 37 | root            | localhost | t    | Sleep   |     15 |                          | NULL             |
| 38 | root            | localhost | NULL | Query   |      0 | starting                 | SHOW PROCESSLIST |
| 39 | root            | localhost | NULL | Sleep   |    635 |                          | NULL             |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
6 rows in set (0.00 sec)


ここで別のユーザを作成し、DEFINERを指定したプロシージャを作成する。
デフォルトのSQL SECURITYはDEFINERのため、このSQLは呼び出したユーザではなく、DEFINERで指定したユーザ(つまり、user1)の権限で実行される。

mysql> CREATE USER user1@'%' IDENTIFIED BY 'Password@123';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT EXECUTE ON t.* TO  user1@'%';

Query OK, 0 rows affected (0.01 sec)

 
mysql> DELIMITER //
mysql> CREATE DEFINER = user1@'%' PROCEDURE t.sample()
BEGIN
  SELECT CURRENT_USER();
  SELECT SLEEP(10);
END
//
mysql> DELIMITER ;


さきほどのセッション(Id 37)でこのプロシージャを呼びだすと、Hostがパーセントになる。
User もuser1に変わる。プロシージャのDEFINERの権限で実行されているので、こういう表示になるんだろう。
TRIGGERやFUNCTIONもたぶん同じ動きになる。

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
| Id | User            | Host      | db   | Command | Time   | State                    | Info             |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 604842 | Waiting on empty queue   | NULL             |
<snip>
| 37 | user1           | %         | t    | Query   |      2 | User sleep               | SELECT SLEEP(10) |
| 38 | root            | localhost | NULL | Sleep   |     58 |                          | NULL             |
| 39 | root            | localhost | NULL | Query   |      0 | starting                 | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
6 rows in set (0.00 sec)



DEFINERとSQL SECURITYとは


DEFINERとSQL SECURITY DEFINERを指定することで、プロシージャを呼び出したユーザとは別のユーザでプロシージャを動かすことができる。
権限では制御できないような、細かいアクセス制御を行いたい場合に活用できる。

例えば、テーブルのアクセス権限は与えずプロシージャのEXECUTE権限だけを与える。
プロシージャの中で、見せるべきレコードのみをSELECTするといった使い方。

-- adminはt1をSELECTできる権限を持っている
mysql> CREATE DEFINER = admin@'localhost' PROCEDURE t.show_record()
SQL SECURITY
DEFINER
BEGIN
-- 大事なレコードは見せない
  SELECT * FROM t1 WHERE is_secret = 0;
END

-- オペレータにはshow_recordの権限だけを与えるて大事なデータは見せない
mysql> GRANT EXECUTE ON t.show_record() TO 'operator'@%';


※ MySQL Community Edition 8.0.15 で試しました。

2019年3月28日木曜日

mysql コマンドを使ってシェルスクリプトを綺麗に書くコツ

mysql コマンドは -e で実行するSQLを指定できる。
セミコロンで区切れば複数のSQLを同時に実行することもできる。

$ mysql -e 'select 1; select 2'
+---+
| 1 |
+---+
| 1 |
+---+
+---+
| 2 |
+---+
| 2 |
+---+

-e を複数指定して、これでもいける。複数行に分けて書く時に自然。

$ mysql -e 'select 1;' \
>       -e 'select 2'

-e で指定した文字列を単に結合して実行しているようで、セミコロンをなくしてしまうと文法エラーになる。

$ mysql -e 'select 1 ' \
>       -e 'select 2'
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for
the right syntax to use near 'select 2' at line 1

ということは、逆にこういう書き方も可能ということがわかった。

$ mysql \
> -e 'SELECT *' \
> -e '  FROM mysql.user' \
> -e ' WHERE User = "root"'

シェルスクリプトが綺麗に書けそうです。

2019年2月25日月曜日

mysqldumpで出力されるINSERT文の最大長は?

mysqldumpの --extended-insert オプションを利用すると、出力されるINSERT文がバルクINSERTの形式になる。
まとめてINSERTすることで、書き込み回数を減らし、importにかかる時間を短縮するための仕組み。

どこまで長いINSERT文が生成されるんだろうか?
まさか、10Gのテーブルをdumpしたら、10Gの巨大なINSERT文ができる・・・ということはないよね・・・

結論としては、net_buffer_lengthに収まるように調整される。
マニュアルにも記載がある。

クライアント/サーバー通信用のバッファーの初期サイズ。複数行の INSERT ステートメント (--extended-insert オプションまたは --opt オプションを使用する場合など) を作成する場合、mysqldumpnet_buffer_length までの長さの行を作成します。この変数を増加させる場合は、MySQL サーバー内の net_buffer_length 変数が少なくともこの大きさであることを確認してください。

試してみる。
mysqldumpはクライアントのため、mysqldumpを実行するサーバのclientセクションに書く必要がある。
$ cat .my.cnf
[client]
net_buffer_length=16K

$ mysqldump  --triggers --routines --events --single-transaction --set-gtid-purged=OFF sbtest > dump16k.txt


$ cat .my.cnf
[client]
net_buffer_length=32K

$ mysqldump  --triggers --routines --events --single-transaction --set-gtid-purged=OFF sbtest > dump32k.txt
うん、16Kと32Kで比較すると、INSERT文の数がだいたい半分になってる。
$ wc -l dump*.txt
    12523 dump16k.txt
     6246 dump32k.txt
    18769 total