2019年9月22日日曜日

MySQL table_encryption_privilege_check パラメータの挙動

前回の記事ではdefault_table_encryption パラメータについて触れました。 default_table_encryptionでは、ENCRYPTION句を省略した場合の挙動を制御することができます。

今回は、table_encryption_privilege_check パラメータについて確認してみます。 このパラメータを有効化すると、データベース(スキーマ)のENCRYPTIONと異なるENCYRPTIONをテーブル作成時に指定できなくなります。


mysql> SHOW CREATE DATABASE sbtest;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| sbtest   | CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='Y' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> CREATE TABLE t3 (pk SERIAL PRIMARY KEY) ENCRYPTION='N';
ERROR 3826 (HY000): Table encryption differ from its database default encryption, and user doesn't have enough privilege.



同様に、データベースのENCRYPTIONを無効にした場合、デーブル作成時に「ENCRYPTION=Y」を指定できなくなります。


mysql> SHOW CREATE DATABASE sbtest;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| sbtest   | CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t4 (pk SERIAL PRIMARY KEY) ENCRYPTION='N';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t5 (pk SERIAL PRIMARY KEY) ENCRYPTION='Y';
ERROR 3826 (HY000): Table encryption differ from its database default encryption, and user doesn't have enough privilege.


データの暗号化を強制することが以下の運用を行うことで実現できます。
  • table_encryption_privilege_check をONにしておく
  • DBAがENCRYPTION=Yを指定したデータベースを作成する
  • 開発者にはそのデータベースにのみ権限を付与する


暗号化強制機能がこのような仕組みで実現されるとは想像していなかったです。
データベースとユーザを分けることで、暗号化強制の有無を切り替えられますし、なかなか便利な実装だと思いました。

2019年9月15日日曜日

MySQL Casual Talks vol.12 で発表してきた

MySQL Casual Talks vol.12 で発表してきた。




MySQLに入れるとしたらどれぐらいの容量が目安かアンケートしたりしてみました。 結果は、結構ばらけました。。。もうちょっと傾向が出ることを期待してたのですが。。。

1つのDBがどれだけ食えるかは、容量だけでなくワークロードの内容やら他の要素も大いに関係します。 一概には言えないというのが結果にも現れたかなと思います。




会場を提供してくださったLINEさん、tomboさんありがとうございました!

2019年9月9日月曜日

MySQL default_table_encryption パラメータの挙動

MySQL 8.0 で default_table_encryption パラメータが追加されてました。
「テーブルの暗号化をデフォルトで有効にするオプションかな?」と思って、CREATE TABLEしてみるも変わらず・・・

マニュアル見ると、データベースとテーブルスペースに効くと書いてありました・・・。名前が紛らわしい・・・。


Defines the default encryption setting applied to schemas and general tablespaces when they are created without specifying an ENCRYPTION clause.

このパラメータを有効化した状態で、ENCRYPTIONオプションを省略してCREATE DATABASEすると、ENCRYPTION='Y'が暗黙的に追加されます。

mysql> SHOW VARIABLES LIKE 'default_table_encryption';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| default_table_encryption | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE tde;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE DATABASE tde;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                               |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| tde      | CREATE DATABASE `tde` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='Y' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


テーブルのENCRYPTION指定を省略した場合は、データベースのENCRYPTIONに従う仕様です。結果として、テーブルの暗号化をデフォルトとすることができます。

mysql> CREATE TABLE tde.test (pk SERIAL PRIMARY KEY, c1 VARCHAR(255));
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tde.test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `pk` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
1 row in set (0.00 sec)


default_table_encryption パラメータはENCRYPTION指定を省略した場合の挙動を変更します。明示的にENCRYPTION='N'を指定すれば、暗号化しないテーブルを作成できます。暗号化を強制する機能ではありません。
とはいえ、暗号化をし忘れた(ENCRYPTIONを指定してなかった)というケースが防げるだけでも、十分有用だと思います。


暗号化を強制したい場合は、table_encryption_privilege_check という別のパラメータが関連してきます。
つづく。。。

2019年8月27日火曜日

MySQL Technology Cafe #5 に参加してきた

前回の、MySQL Technology Cafe #4 に続き、#5 に参加してきました。


タイムテーブル

 

時間 内容 登壇者
18:00-18:25 受付 -
18:25-18:30 はじめに MySQL GBU
18:30-19:15 MySQL 8.0 の便利機能とSQL標準 MySQL テクニカルアナリスト 木村明治 氏
19:30-19:40 MySQL Binlog のパケットと自作クライアント 高野周哉 氏
19:40-20:30 ネットワーキング -
20:45 撤収 -

 

MySQL 8.0 の便利機能とSQL標準


MySQL サポート担当の木村さんのお話!
あまり取り上げられない、MySQL 8 の新機能の紹介。


  • C言語の非同期API
    • 8.0.16でサポート
    • C言語のAPIの使い方を知りたければ、mysqldump.ccのソースが参考になる
    • 従来の同期APIの名前に  _nonblocking をつけた非同期APIがサポートされた
    • ポーリングするタイプの素朴な非同期API
  • EXPLAINのTREE表示
    • 8.0.16でサポートされたが、Experimental扱いなので注意
      • 将来フォーマットが変わるかもしれない
    • EXPLAIN FORMAT=TREE を指定すると某DBみたいな実行計画が出力される
  • パスワード再利用ポリシー
    • X世代前のパスワードはNGとすることができるように
  •  新しいCollation、utf8mb4_0900_bin
    • 8.0.17からサポート
    • 従来は 'a' = 'a ' (a+スペース) だったが、このCollationは 'a' != 'a ' になる
    • @tmtms さんのスライドが詳しい
--
MySQL と SQL標準の関わり

  • SQL標準で定められた機能でMySQLが最近サポートしたもの
    • CTE
      • RECURSIVE句はMySQLでは省略できない
      • 再帰が cte_max_recursion_depth を超えるとエラーになる→デフォルトは1000
    • Window Function
    • CHECK句
    • Generated Column
  • MySQLの機能がベースとなってSQL標準に取り込まれたり業界標準になったもの 
    • GROUP_CONCAT 
    • LIMIT句
      • SQL標準では FETCH FIRST n ROWS という形式でサポート
    • REPLACE, INSERT ... ON DUPLICATE ...
    • INSERT ... VALUES (), (), () バルクインサート

MySQL Binlog のパケットと自作クライアント

 

高野さん

バイナリログから更新内容をリアルタイムに読み出すために、独自にRubyでクライアントを書いたお話でした!すごい!

バイナリログを使った、Pub/Sub型のシステムはニーズはそれなりにありそうなものの、なかなかデファクトとなる手法が定まらない分野の1つだと感じてます。自分も過去に、MySQL Binlog Events ライブラリを試したりしました。


  • wireshark/tcpdump でMySQLのクライアントサーバのやりとりをみてみる!
    •  
       
    • https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase.html
  • MySQLのパケットのフォーマット 
    • 最初の4バイトはパケット長

  • Server Greeting Packet
    • パスワード認証時にパスワードからハッシュ生成するために使うSaltが含まれている
  • Ruby と TCP SocketでMySQLのサーバからバイナリログを読み取るクライアントを書いた
    • Pub/Subみたいな非同期処理に活用
    • 接続したら、最初に SET master_binlog_checksum=OFF にしないとエラーになる

その他

  •  MySQL Casual Talk vol.12 が 9/13 にあります
    • https://mysql-casual.connpass.com/event/142689/
  •  db tech showcase 2019 もMySQLのセッションが盛りだくさん
    • https://www.db-tech-showcase.com/dbts/tokyo 

2019年8月3日土曜日

プロシージャの中身はPerconaだとスロークエリログに落ちる

プロシージャの中身のクエリはスロークエリログに落ちない の続き。

Vanilla MySQLだとプロシージャの中身がログに落ちませんが、Percona Server ではスロークエリログが拡張されていて、ログに落ちます。

log_slow_sp_statements で出力の有無を設定します。デフォルトではプロシージャの中身をスロークエリログに出力する設定になってます。

mysql> SHOW GLOBAL VARIABLES LIKE 'log_slow_sp_statements';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| log_slow_sp_statements | ON    |
+------------------------+-------+
1 row in set (0.00 sec)



試してみます。



mysql> DELIMITER //
mysql> CREATE PROCEDURE sample()
    -> BEGIN
    ->   SELECT SLEEP(10);
    -> END
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql>
mysql> SET GLOBAL slow_query_log =on;
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL long_query_time = 0;
Query OK, 0 rows affected (0.00 sec)


mysql> CALL sample;
+-----------+
| SLEEP(10) |

+-----------+

Vanilla MySQL だとこうだったのが

/usr/sbin/mysqld, Version: 8.0.15 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2019-04-03T06:54:09.547179Z
# User@Host: root[root] @ localhost []  Id:    30
# Query_time: 10.001274  Lock_time: 0.000376 Rows_sent: 1  Rows_examined: 0
SET timestamp=1554274439;
call test.sample();


Percona Server ではこう。
Stored_routine の項目にプロシージャの名前が記載され、プロシージャの中のSQLがログに記載されてます。

/usr/sbin/mysqld, Version: 8.0.15-6 (Percona Server (GPL), Release 6, Revision 63abd08). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2019-08-03T12:54:22.490311Z
# User@Host: root[root] @ localhost []  Id:     9
# Schema: t  Last_errno: 0  Killed: 0
# Query_time: 10.000860  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 57
# Stored_routine: t.sample
use t;
SET timestamp=1564836852;
SELECT SLEEP(10);



プロシージャの中身のクエリはスロークエリログに落ちない

とあるMySQLのデータベースで、性能劣化した疑いのあるプロシージャがありました。
プロシージャには複数のSELECTやINSERTが含まれています。

どのSQLが遅かったか突き止める方法はあるのでしょうか?
プロシージャに含まれている、各SQLはスロークエリログに落ちるんでしょうか?

試してみます。

テスト用に遅いプロシージャを作ります。

DELIMITER //
CREATE PROCEDURE sample()
BEGIN
  SELECT SLEEP(10);
END
//
DELIMITER ;


ログを出します。

mysql> SET GLOBAL slow_query_log = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL long_query_time = 0;
Query OK, 0 rows affected (0.00 sec)

スロークエリログにはプロシージャの中身は表示されませんでた。。。

/usr/sbin/mysqld, Version: 8.0.15 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2019-04-03T06:54:09.547179Z
# User@Host: root[root] @ localhost []  Id:    30
# Query_time: 10.001274  Lock_time: 0.000376 Rows_sent: 1  Rows_examined: 0
SET timestamp=1554274439;
call test.sample();


Rows_examinedの値はどうなるんだろうか?

DELIMITER //
CREATE PROCEDURE sample2()
BEGIN
  SELECT * FROM mysql.user;
  SELECT * FROM mysql.user;
END
//
DELIMITER ;

このSELECT文は一回あたり、5行スキャンする

# Time: 2019-04-03T07:02:03.693124Z
# User@Host: root[root] @ localhost []  Id:    32
# Query_time: 0.000284  Lock_time: 0.000127 Rows_sent: 5  Rows_examined: 5
SET timestamp=1554274923;
select * from mysql.user;

ログには二回分(合計10)がちゃんと記録された。Row_examined の値は正しく合計を表していると。

# Time: 2019-04-03T07:04:31.383244Z
# User@Host: root[root] @ localhost []  Id:    36
# Query_time: 0.000693  Lock_time: 0.000000 Rows_sent: 10  Rows_examined: 10
SET timestamp=1554275071;
CALL samitani.sample2();


まとめ:

  • プロシージャの中身のどこが遅いかはスロークエリログからわからない。
    • 中のSQLを1つ1つ実行してみるしかなさそう
  • Rows_examined は合計が記録される。


つづく。。。

2019年7月31日水曜日

サーバサイドプリペアドステートメントはperformance_schemaに記録されない

あるクエリがperformance_schemaに記録されなかった。
原因を調べていて1つ記録されない条件を見つけたのでメモ。

まず、問題ないパターン。

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-07-30 10:40:47 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT SQL_TEXT FROM events_statements_history;
+-------------------------------------------------------------------+
| SQL_TEXT                                                          |
+-------------------------------------------------------------------+
| NULL                                                              |

<snip>
|
| NULL                                                              |
| CALL sys.ps_truncate_all_tables(FALSE)                            |
| SELECT NOW()                                                      |
| NULL                                                              |
+-------------------------------------------------------------------+
10 rows in set (0.00 sec)



「SELECT NOW()」がしっかり、performance_schemaに記録されてますね。
しかし、サーバサイド プリペアドステートメントを使うと・・・

mysql> PREPARE stmt FROM 'SELECT "PREPARE"';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
+---------+
| PREPARE |
+---------+
| PREPARE |
+---------+
1 row in set (0.00 sec)

mysql> SELECT SQL_TEXT FROM events_statements_history;
+-------------------------------------------------------------------+
| SQL_TEXT                                                          |
+-------------------------------------------------------------------+
| NULL                                                              

<snip>
| NULL                                                              |
| CALL sys.ps_truncate_all_tables(FALSE)                            |
| PREPARE stmt FROM ...                                             |
| EXECUTE stmt                                                      |
+-------------------------------------------------------------------+
10 rows in set (0.00 sec)





(゚д゚)あらやだ! クエリの部分が「...」になってしまったワ。
ファンクションやプロシージャの中のSQLも記録されませんから、それと同じと思えば、違和感ないか?

子ネタでした(つづく?)