2021-02-12

MySQL 5.7 Database Administratorを取ってきた（半年前の話）　

最近MySQL全く触れてないけど、久々に。

「オラクルマスター取得した」はたまに目にするんですけど、「MySQL認定資格とってきました」って話をネットで漁ってもsakaikさんのブログしかひっかからないので「ちょっとやってみるか」と思い取得してきました。（半年前）

自分が受験したときは再受験キャンペーンをやっていたので1度目は勉強なしでどこまでいけるか腕試しで、だめだったら勉強しようくらいな気持ちで受験した記憶です。

当時はMySQLの運用はたまに隣でちょこちょこみていて、クエリーチューニングだったり、sql_modeをいじったりしてた時期だったと思います。そして受験結果は50%くらい…で不合格。（記憶が曖昧ですがそれくらいだったはず）

普段使ってない機能や知らないオプションのところがだめだめで落ちました。

2回目はある程度ドキュメントを読んでから受験したのでちゃんと合格できました！

8.0.21で盛り上がってるとこ申し訳ないんですが、MySQL 5.7DatabaseAdministrator合格しました。（今更…
— lhfukamachi (@lhfukamachi) 2020年7月14日

これから受験しようと考えている人へ

需要がなさそうではありますが、一応私がどうやって勉強したかを残しておきます。

私の場合は2回目の受験は受験日2日前くらいに思い出したかのように勉強したので、実質勉強時間は10時間くらいです。とはいえ、当時MySQLはちょこちょこ触っていたので多少はカバーされていたかもしれません。と言っても本当に基本的な運用しかしておらず、ちょっと込み入った質問をきかれても答えられない状態でした。（これは今も同じ）

やったこと１：サンプル問題を解く　

サンプル問題が公開されていて、まずこれを解いてどれくらい全体を知ってるか、現状を把握しました。あんまり詳しくないなってところは後でドキュメントを読もうと思ってメモしたくらいです。

やったこと２：YouTubeを見る

幸いにもOracleさんが試験対策セミナーをYouTubeに公開していて、それを3回くらいみた記憶です。「へーこんな機能あったんだ」とか新たな発見もありました。ここでも不安なところをメモしておいて後ほどドキュメント読む感じにしました。

www.youtube.com

やったこと３：ドキュメントを読む

日本語の試験対策本は（私の知ってる限り）ないのでドキュメントベースで勉強しました。といっても先程の不安な所や知らなかった所、前回ダメダメだった所を読んでメモして気になったものは実際の動作をMySQL5.7で確認する感じです。ドキュメントを読んでて「あれ？この機能バージョン8.0から追加されなかったっけ？っっってよく見たらドキュメント8.0じゃないか！」ってことが2度ほどあったので読み始める前にドキュメントのバージョンが5.7のものであるかどうかを確認することを強くおすすめします。（URLに5.7が入っているかどうかで判断）

MySQL :: MySQL 5.7 Reference Manual

その他

受験勉強したときには利用しておりませんが、詳解MySQL 5.7やエキスパートのためのMySQL[運用+管理]トラブルシューティングガイド、実践ハイパフォーマンスMySQLあたりは昔に読んでおりました。そのあたりも事前にもう一度読んでおけばもうちょっと得点上がってたかもです。またはドキュメントを通しで読んでおくのも良いと思います。

最後に

普段の運用ではある程度テンプレート化されたMySQLを運用していたのでデフォルトでは設定されていない独自の設定や、普段利用しない機能についても知るいいきっかけになりました。

今年も再受験キャンペーンを実施しているみたいですので受験する人のためになればと思い書いた次第でした。

2020-12-08

foreign_key_checksの挙動を知る

この記事はMySQL Advent Calendar 2020 - Qiitaの8日目の記事です。昨日はmita2さんの MySQL 8.0 でも utf8mb4_general_ci を使い続けたい僕らは - mita2 database lifeでした。

実は今まで、外部キー制約をガッツリ使ったサービスを担当したことなくて、「親子関係のテーブルだ」くらいにしか思ってなかったんですが、いざ触ってみるといろいろ知らないことがあったのでこれを機会に調べてみました。調べているとsysytem変数にforeign_key_checksというものがあるらしく、これを無効にすると、全体的に外部キー制約が無効になるらしいですね。なのでforeign_key_checksをON/OFFしながら色々実行してみます。環境はCentOS7、MySQL8.0.21でInnoDB前提です。なお利用するテーブルは下記

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `user_id` int NOT NULL,
  `name` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `user_id` int DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `t1_ibfk_1` (`user_id`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

SET GLOBAL/SESSION

foreign_key_checksはGLOBAL/SESSIONともに利用できるようです。 foreign_key_checks=0をSESSIONで実行したときはそのセッションのみが外部キー参照を無効にできます。

mysql1> SELECT @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)
mysql1> insert into t1 values (1,1, now());
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`reference_test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)

mysql2> SET SESSION foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)
mysql2> insert into t1 values (1,1, now());
Query OK, 1 row affected (0.01 sec)

親テーブルでトランザクションを開始してSELECTすると子テーブルがDROPできない

たとえ、foreign_key_checks=0にしてもトランザクションを開始して親テーブル(user)を参照している状態でもう一方のコネクションで子テーブル（t1）を削除しようとしても待たされます。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
//親テーブルを参照
mysql> SELECT * FROM user;
Empty set (0.00 sec)

mysql> SET SESSION foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE t1;
## ここで待たされる

SHOW PROCESSLISTで確認するとメタデータロック待ちっぽいですね。もちろん親テーブル側のトランザクションが終了すると削除されるます。

> show processlist;
+--------+-----------------+-----------+----------------+---------+--------+---------------------------------+------------------+
| Id     | User            | Host      | db             | Command | Time   | State                           | Info             |
+--------+-----------------+-----------+----------------+---------+--------+---------------------------------+------------------+
|      5 | event_scheduler | localhost | NULL           | Daemon  | 530894 | Waiting on empty queue          | NULL             |
| 100172 | root            | localhost | reference_test | Sleep   |    391 |                                 | NULL             |
| 100173 | root            | localhost | reference_test | Query   |     34 | Waiting for table metadata lock | DROP TABLE t1    |
| 100178 | root            | localhost | NULL           | Query   |      0 | starting                        | show processlist |
+--------+-----------------+-----------+----------------+---------+--------+---------------------------------+------------------+
4 rows in set (0.00 sec)

なお子テーブルにINDEXの追加やtruncateは問題なくできました。

子テーブルでトランザクションが開始されていると親テーブルにINDEXが追加できない

foreign_key_checks=0で子テーブル(t1)をトランザクションを開始して参照している状態で親テーブル(user)に外部キー参照をされていないカラムをINDEX追加しようとしてもMDLになります。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
// 子テーブルを参照
mysql> SELECT * FROM t1;
+----+---------+---------------------+
| id | user_id | updated             |
+----+---------+---------------------+
|  1 |       1 | 2020-12-08 01:55:52 |
+----+---------+---------------------+
1 row in set (0.00 sec)

mysql> SET SESSION foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE user ADD index idx_name(name);
ここで待たされる

ちなみにuserテーブルに対してALTER TABLE/DROP TABLEともにできなかったのですが、TURNCATEはできました。この挙動、実は以前は出くわしてこれをきっかけに障害を起こしてしまったことあります…関係ないカラムなのに…と思い、何が起きたのかわからなくてyoku0825さんに助けてもらったのはいい思い出です。

まとめ

foreign_key_checksは外部キーを無効/有効にすることができるシステム変数ですが、そういうもんだと思って利用すると意外なところではまったりするかもしれないですね。このあたり挙動を理解して運用していく必要がありそうです。

参考： https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_foreign_key_checks

日々の覚書: MySQL 8.0 vs 外部キー制約 vs ALTER TABLEでメタデータロック待ちになったら疑うこと

2020-05-25

CLion on macOSでMySQLをデバッグする

最近MySQLのdebug buildをMacでする機会が多くなったのでメモがてら残しておく。 $HOME配下にmysql ディレクトリを作ってmysql_sourceにコードを、mysql_dataにdatadirとかバイナリを置く構成にする。最初brewとかつかってなにかいれた記憶があるけど、忘れてしまった。。。

とりあえずビルドするまで

$ wgetとかでboost付きのソースコードを取得

$ tar xvf mysql-boost-8.0.20.tar.gz

--　ビルド作業はworkで行う。
$ mkdir work

$ cd work

$ cmake \
-DCMAKE_INSTALL_PREFIX=${HOME}/mysql/mysql_data/mysql-8.0.20 \
-DMYSQL_DATADIR=${HOME}/mysql/mysql_data/mysql-8.0.20/data \
-DSYSCONFDIR=${HOME}/mysql/mysql_data/mysql-8.0.20 \
-DMYSQL_UNIX_ADDR=${HOME}/mysql/mysql_data/mysql-8.0.20/data/mysql.sock \
-DWITH_DEBUG=1  \
-DWITH_BOOST=${HOME}/mysql/mysql_source/mysql-8.0.20/boost ../mysql_source/mysql-8.0.20

$ time make -j 4

$ time make install -j4

自分の環境だとだいたい30分くらい

わーい
```
$ time make -j 4
real24m0.747s
user83m16.501s
sys7m34.630s
```
— lhfukamachi (@lhfukamachi) 2020年4月18日

初期化

$ ./mysqld --basedir=${HOME}/mysql/mysql_data/mysql-8.0.20 --datadir=${HOME}/mysql/mysql_data/mysql-8.0.20/data --initialize-insecure --user=${USER}

CLionの設定からデバッグまで

CLionを起動してソースコードを開く。このあと Preference > CMakeでCMake optionとかの設定

-DCMAKE_INSTALL_PREFIX=${HOME}/mysql/mysql_data/mysql-8.0.20
-DMYSQL_DATADIR=${HOME}/mysql/mysql_data/mysql-8.0.20/data
-DSYSCONFDIR=${HOME}/mysql/mysql_data/mysql-8.0.20
-DMYSQL_UNIX_ADDR=${HOME}/mysql/mysql_data/mysql-8.0.20/data/mysql.sock
-DWITH_DEBUG=1
-DWITH_BOOST=${HOME}/mysql/mysql_source/mysql-8.0.20/boost

f:id:hidemifukamachi:20200525232450p:plain

右上の実行Debug Configureからmysqldを選択し　実行の引数の設定をする。

--basedir=${HOME}/mysql/mysql_data/mysql-8.0.20 --datadir=${HOME}/mysql/mysql_data/mysql-8.0.20/data

このときにBefore launchからビルドを外して、都度ビルドしないようにしておく。あとはデバッグでmysqldを起動し、do_commandとかにブレークポイントをおいて止まってくれるか確認する。

注意：2020年5月現在、macbook air 2020ではCLionがクラッシュする。

https://youtrack.jetbrains.com/issue/JBR-2310

VMオプションとかを変えて凌ぐしかなさそう…

2019-12-16

MySQLのHELP構文ってやつ

この記事は MySQL Advent Calendar 2019の16日目です。昨日はdupont-kedamaさんのMySQL5.5からMySQL8.0にマイグレーションしたゆるい話でした。

MySQLにはHELP構文というものがあって、構文とかをサクッと調べられるということは聞いたことあったけど、ちゃんと使ったことがなかったのでこの際に調べてみた。

dev.mysql.com

基本構文は下記。

mysql> HELP '<検索文字列>'

mysql スキーマのhelp関連のテーブルを返しているらしい。

mysql> show tables like 'help%';
+-------------------------+
| Tables_in_mysql (help%) |
+-------------------------+
| help_category           |
| help_keyword            |
| help_relation           |
| help_topic              |
+-------------------------+
4 rows in set (0.00 sec)

このあたりかな。

コンテンツ一覧を見るにはHELP 'contents', 型の一覧を見るにはHELP 'data_types'を利用する。

HELP 'contents'を実行した時に出力されるコンテンツを選んで実行するとさらに項目を選ぶように言われる。

例えばDROP ROLEの項目を見たいとしたら、

mysql> HELP 'contents'                                                                                                                             
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Components
   Compound Statements
   Contents
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

mysql> HELP 'Account Management'
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ALTER RESOURCE GROUP
   ALTER USER
   CREATE RESOURCE GROUP
   CREATE ROLE
   CREATE USER
   DROP RESOURCE GROUP
   DROP ROLE
   DROP USER
   GRANT
   RENAME USER
   REVOKE
   SET DEFAULT ROLE
   SET PASSWORD
   SET RESOURCE GROUP
   SET ROLE

mysql> HELP 'DROP ROLE'
Name: 'DROP ROLE'
Description:
Syntax:
DROP ROLE [IF EXISTS] role [, role ] ...

DROP ROLE removes one or more roles (named collections of privileges).
To use this statement, you must have the global DROP ROLE or CREATE
USER privilege. When the read_only system variable is enabled, DROP
ROLE additionally requires the CONNECTION_ADMIN or SUPER privilege.
(以下略)

HELP DROP からでも辿れるっぽい

> HELP DROP
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER TABLE
   ALTER TABLESPACE
   DEALLOCATE PREPARE
   DROP DATABASE
   DROP EVENT
   DROP FUNCTION
   DROP FUNCTION UDF
   DROP INDEX
   DROP PREPARE
   DROP PROCEDURE
   DROP RESOURCE GROUP
   DROP ROLE
   DROP SCHEMA
   DROP SERVER
   DROP SPATIAL REFERENCE SYSTEM
   DROP TABLE
   DROP TABLESPACE
   DROP TRIGGER
   DROP USER
   DROP VIEW

mysql> HELP DROP ROLE 
Name: 'DROP ROLE'
Description:
Syntax:
DROP ROLE [IF EXISTS] role [, role ] ...
(以下略)

地味に便利だ。

その他にも組み込み関数とかもサクッと調べてられて引数とか忘れたときに便利。

mysql> > HELP format_bytes
Name: 'FORMAT_BYTES'
Description:
FORMAT_BYTES(count)

Given a numeric byte count, converts it to human-readable format and
returns a string consisting of a value and a units indicator. The
string contains the number of bytes rounded to 2 decimal places and a
minimum of 3 significant digits. Numbers less than 1024 bytes are
represented as whole numbers and are not rounded.

URL: https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html

Examples:
mysql> SELECT FORMAT_BYTES(512), FORMAT_BYTES(18446644073709551615);
+-------------------+------------------------------------+
| FORMAT_BYTES(512) | FORMAT_BYTES(18446644073709551615) |
+-------------------+------------------------------------+
|  512 bytes        | 16.00 EiB                          |
+-------------------+------------------------------------+

ただ、完全一致じゃないと表示はしてくれないらしい。

mysql> help format_byte

Nothing found
Please try to run 'help contents' for a list of all accessible topics

「%」を使ったワイルドカード検索はできるのでこっちを使えばよいのかな。

mysql> help %ormat_byte%
Name: 'FORMAT_BYTES'
Description:
FORMAT_BYTES(count)
(以下略)

今後は困ったら積極的に使っていきたい。

2019-02-27

いい加減MySQL8.0のRoleを理解する。その１

最近権限付与周りの依頼が来たときにMySQL8.0だった場合にROLEを理解してないのでそっと隣の人か斜め前の人にやってもらうことが多々あるのでいい加減ROLEを理解する。たぶんその３くらいまでやる。

参考にするのはこちらの公式ドキュメント

MySQL :: MySQL 8.0 Reference Manual :: 6.3.4 Using Roles

ROLEを作成してみる

まずロールの追加、削除はCREATE,DROPでできる。当たり前だけどHOST名とかはいらない

mysql>CREATE ROLE test_role;
Query OK, 0 rows affected (0.01 sec)

mysql>DROP ROLE test_role;
Query OK, 0 rows affected (0.01 sec)

GRANTとREVOKEで権限の割当や取消ができる。とりあえずSELECTを与えてみる。

mysql>CREATE ROLE select_role;
Query OK, 0 rows affected (0.00 sec)

mysql>GRANT SELECT on d1.* to select_role;
Query OK, 0 rows affected (0.00 sec)

なるほど。ほぼユーザー作成と権限付与をするときと一緒か。

実際に作って権限を与えてみる。

// とりあえずユーザーを作って。
mysql>CREATE USER fukamachi@localhost identified with mysql_native_password by '******';
Query OK, 0 rows affected (0.01 sec)

mysql>GRANT 'select_role' to fukamachi@localhost;
Query OK, 0 rows affected (0.00 sec)

できたっぽい。付与されたROLEを確認するには今まで通りSHOW GRANTSで確認すればいいっぽい

mysql>SHOW GRANTS for fukamachi@localhost;
+----------------------------------------------------+
| Grants for fukamachi@localhost                     |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `fukamachi`@`localhost`      |
| GRANT `select_role`@`%` TO `fukamachi`@`localhost` |
+----------------------------------------------------+
2 rows in set (0.00 sec)

select_roleが割り当てられてる。ログインできるか確認する。

$ MYSQL_PWD='******' mysql --defaults-file=/etc/mysql/my.cnf -ufukamachi
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 8.0.14 MySQL Community Server - GPL
(省略)

できた。実際にSELECTしてみる。

mysql>use d1;
ERROR 1044 (42000): Access denied for user 'fukamachi'@'localhost' to database 'd1'
mysql>SELECT * FROM d1.t1 limit 10;
ERROR 1142 (42000): SELECT command denied to user 'fukamachi'@'localhost' for table 't1'

…できない…わからない…

調べてみると、どうも権限を付与しただけはROLEが有効になるわけではない。SET ROLEが必要らしい。とりあえずSET ROLEして有効化してみる。

（さっき作ったfukamachiユーザーでログイン）
mysql> SET ROLE select_role;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM d1.t1 limit 10;
+----+------------+
| id | value      |
+----+------------+
|  1 | C8vAt5qSyl |
|  2 | 15mtFfSGuz |
|  3 | 96ZFZ0451W |
|  4 | ZHM6qMQRXI |
|  5 | xMkqCVFgOT |
|  6 | FWBCUry1FW |
|  7 | Uoa5Y1sOPK |
|  8 | DoCkXtvZSj |
|  9 | JdiF94D1t2 |
| 10 | dwqV6ykt54 |
+----+------------+
10 rows in set (0.00 sec)

できた。

もちろんinsertはできない。

mysql> INSERT INTO d1.t1(value) VALUES ('insert_test');
ERROR 1142 (42000): INSERT command denied to user 'fukamachi'@'localhost' for table 't1'

とりあえず今日はここまで。

2019-01-23

Oracle11gでのLIMIT,OFFSETするクエリをMySQL8のwindow関数で利用してみる

諸事情でWindow関数を触る機会があって、ちょっと前にyokuさんに教えてもらったWindow関数ネタを思い出したので１つ。

Oracle11gにはMySQLでおなじみ？のLIMIT,OFFSETがない。

そこで（いろいろやり方はあるけど）window関数を使ってあらわしたりする。

例えば id(number), value(varchar2) のテーブルならこんな感じ。

SELECT * FROM 
  (
     SELECT row_number() over order by id asc) as row_number,
                   value 
     FROM t1
  ) a
WHERE a.row_number between 500 and 600;

MySQL8からwindow関数が追加され、row_number()とかもつかえるのでこのクエリをそのままMySQLで使えるか検討してみる。環境はMySQL8.0.13。データは前回のブログで使った100万行くらい入ってるシンプルなテーブルのやつ

mysql  [d1]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `value` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql  [d1]> SELECT COUNT(1) FROM t1;
+----------+
| COUNT(1) |
+----------+
|  1011000 |
+----------+
1 row in set (0.15 sec)

mysql  [d1]> SELECT * FROM
    ->   (
    ->      SELECT row_number() over (order by id) as row_no,
    ->                    value
    ->      FROM t1
    ->      )  a
    -> WHERE a.row_no between 500 and 600;
+--------+------------+
| row_no | value      |
+--------+------------+
|    500 | 1zwujbeNGW |
|    501 | qz8df1RHcc |
|    502 | XKH92CeXGa |
|    503 | zWc7JQeOH0 |
|    504 | AkwB5KClKD |
|    505 | rP1ZUAOc80 |
~~

|    595 | oDdB7ZR1jD |
|    596 | kqUuhKOELK |
|    597 | o4mAu0y2KG |
|    598 | 5e0EbD9OXW |
|    599 | yUyRUrOy9d |
|    600 | 5J4wVaIKOJ |
+--------+------------+
101 rows in set (0.91 sec)

お、ちゃんと出力された。SQL的には（当たり前だけど）問題ないっぽい。これでOracleのテーブルをMySQLに移行するときのSQLの書き換える量がへりますね。（めでたしめでたし。

ただ、0.91 secはやっぱ遅い気がする。

mysql  [d1]> SELECT id, value FROM t1 ORDER BY id LIMIT 101 OFFSET 499;
+-----+------------+
| id  | value      |
+-----+------------+
| 500 | 1zwujbeNGW |
| 501 | qz8df1RHcc |
| 502 | XKH92CeXGa |
| 503 | zWc7JQeOH0 |
| 504 | AkwB5KClKD |
| 505 | rP1ZUAOc80 |
| 506 | WJSjD0NWJb |
| 507 | 3j6lvvGYvH |
~~
| 598 | 5e0EbD9OXW |
| 599 | yUyRUrOy9d |
| 600 | 5J4wVaIKOJ |
+-----+------------+
101 rows in set (0.00 sec)

。。。ですよね〜。

ということでOFFSETを深くしてprofilingをとってみる。

mysql  [d1]> SELECT * FROM
    ->     (
    ->       SELECT row_number() over (order by id) as row_no,
    ->                    value
    ->       FROM t1
    ->     )  a
    ->  WHERE a.row_no between 1000000 and 1000010;
+---------+------------+
| row_no  | value      |
+---------+------------+
| 1000000 | 5UGdDjX6sI |
| 1000001 | 1BmxMSJMNO |
| 1000002 | fRA4G96KoZ |
| 1000003 | 0PEhFBERn3 |
| 1000004 | x9N5k3eez5 |
| 1000005 | KeIyt1RHX5 |
| 1000006 | BaDGNWrvPj |
| 1000007 | hnpbYAQtKW |
| 1000008 | XI1a2RAy0G |
| 1000009 | ZmnI8XobRb |
| 1000010 | N6XXZnw3mo |
+---------+------------+
11 rows in set (1.01 sec)

mysql  [d1]> show profile;
+----------------------------+----------+
| Status                     | Duration |
+----------------------------+----------+
| starting                   | 0.000550 |
| checking permissions       | 0.000024 |
| Opening tables             | 0.000390 |
| init                       | 0.000034 |
| System lock                | 0.000066 |
| optimizing                 | 0.000010 |
| optimizing                 | 0.000007 |
| statistics                 | 0.000054 |
| preparing                  | 0.000033 |
| Creating tmp table         | 0.000058 |
| statistics                 | 0.000023 |
| preparing                  | 0.000015 |
| executing                  | 0.000032 |
| Sending data               | 0.000015 |
| executing                  | 0.000004 |
| Sending data               | 0.000008 |
| Creating sort index        | 0.973423 |
| end                        | 0.000040 |
| query end                  | 0.000014 |
| waiting for handler commit | 0.000030 |
| query end                  | 0.000017 |
| removing tmp table         | 0.028293 |
| query end                  | 0.004465 |
| removing tmp table         | 0.000023 |
| query end                  | 0.000015 |
| closing tables             | 0.000041 |
| freeing items              | 0.000094 |
| cleaning up                | 0.000094 |
+----------------------------+----------+
28 rows in set, 1 warning (0.00 sec)

Warning (Code 1287): 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead
mysql  [d1]> SELECT id, value FROM t1 ORDER BY id LIMIT 11 OFFSET 999999;
+---------+------------+
| id      | value      |
+---------+------------+
| 1000000 | 5UGdDjX6sI |
| 1000001 | 1BmxMSJMNO |
| 1000002 | fRA4G96KoZ |
| 1000003 | 0PEhFBERn3 |
| 1000004 | x9N5k3eez5 |
| 1000005 | KeIyt1RHX5 |
| 1000006 | BaDGNWrvPj |
| 1000007 | hnpbYAQtKW |
| 1000008 | XI1a2RAy0G |
| 1000009 | ZmnI8XobRb |
+---------+------------+
10 rows in set (0.22 sec)

mysql [d1]>  SELECT id, value FROM t1 ORDER BY id LIMIT 11 OFFSET 999999;
+---------+------------+
| id      | value      |
+---------+------------+
| 1000000 | 5UGdDjX6sI |
| 1000001 | 1BmxMSJMNO |
| 1000002 | fRA4G96KoZ |
| 1000003 | 0PEhFBERn3 |
| 1000004 | x9N5k3eez5 |
| 1000005 | KeIyt1RHX5 |
| 1000006 | BaDGNWrvPj |
| 1000007 | hnpbYAQtKW |
| 1000008 | XI1a2RAy0G |
| 1000009 | ZmnI8XobRb |
| 1000010 | N6XXZnw3mo |
+---------+------------+
11 rows in set (0.19 sec)

mysql  [d1]> show profile;
+----------------------------+----------+
| Status                     | Duration |
+----------------------------+----------+
| starting                   | 0.000265 |
| checking permissions       | 0.000019 |
| Opening tables             | 0.000135 |
| init                       | 0.000022 |
| System lock                | 0.000065 |
| optimizing                 | 0.000011 |
| statistics                 | 0.000046 |
| preparing                  | 0.000031 |
| Sorting result             | 0.000009 |
| executing                  | 0.000008 |
| Sending data               | 0.189200 |
| end                        | 0.000031 |
| query end                  | 0.000009 |
| waiting for handler commit | 0.000016 |
| query end                  | 0.000014 |
| closing tables             | 0.000020 |
| freeing items              | 0.000038 |
| cleaning up                | 0.000048 |
+----------------------------+----------+
18 rows in set, 1 warning (0.00 sec)

Warning (Code 1287): 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead

window関数の利用するするほうはCreating tmp tableしてCreating sort indexしてるがこのsort indexが重いらしい。件数がすくないとたいしたことないかもしれないけど無難にlimitに書き換えてあげたほうがよいかもしれない。

（書いてから気づいたけどbetweenじゃなくて普通にid > ? and id <= ?ってすればよかった）

2018-12-16

MTSを使ってクエリをロールバックする。

この記事はMySQL Casual Advent Calendar 2018の16日目です。

元ネタはlefredさんのこちらのスライド FOSDEM MySQL & Friends Devroom, February 2018 MySQL Point-in-Time Recovery like a Rockstar

ざっくりいうと、マルチスレッドスレーブ(以下MTS)を使ってbinlogを読み込ませてPITRを早くするというものです。検証環境はCentOS7、MySQL8.0.13。

例えばこんな感じなテーブル。

 fuki1 [d1]> SELECT * FROM t1 limit 10;
+----+------------+
| id | value      |
+----+------------+
|  1 | C8vAt5qSyl |
|  2 | 15mtFfSGuz |
|  3 | 96ZFZ0451W |
|  4 | ZHM6qMQRXI |
|  5 | xMkqCVFgOT |
|  6 | FWBCUry1FW |
|  7 | Uoa5Y1sOPK |
|  8 | DoCkXtvZSj |
|  9 | JdiF94D1t2 |
| 10 | dwqV6ykt54 |
+----+------------+
10 rows in set (0.01 sec)
 
fuki1 [d1]> SELECT COUNT(1) FROM t1;
+----------+
| COUNT(1) |
+----------+
|  1010000 |
+----------+
1 row in set (0.15 sec)

このテーブルにこんな更新を全件にかけてしまったとします。

fuki1 [d1]> UPDATE t1 SET value = 'lhfukamachi';
Query OK, 1010000 rows affected (14.48 sec)
Rows matched: 1010000  Changed: 1010000  Warnings: 0

fuki1 [d1]> SELECT * FROM t1 limit 10;
+----+-------------+
| id | value       |
+----+-------------+
|  1 | lhfukamachi |
|  2 | lhfukamachi |
|  3 | lhfukamachi |
|  4 | lhfukamachi |
|  5 | lhfukamachi |
|  6 | lhfukamachi |
|  7 | lhfukamachi |
|  8 | lhfukamachi |
|  9 | lhfukamachi |
| 10 | lhfukamachi |
+----+-------------+
10 rows in set (0.03 sec)

UPDATE t1 SET value = 'lhfukamachi'によってvalueカラムが全て'lhfukamachi'に書き換わってしまいました。その後もデータが来る想定でこのテーブルに1000件ほどデータを追加します（ついでに他のテーブルにも更新します。

fuki1 [d1]> select count(1) FROM t1;
+----------+
| count(1) |
+----------+
|  1011000 |
+----------+
1 row in set (0.13 sec)

通常PITRをする時は、クエリが実行される以前のフルバックアップ + バイナリログを用いてmysqlbinlogコマンドを使って特定のクエリのポジションを飛ばしてデータを再構築するやり方が一般的かと思います。 MTS（というか、レプリケーション）を使ったやり方の場合はbinlogをあたかもrelaylogとして読ませてやるやり方らしいです。

（下記記事の「レプリケーション機能を使用してリカバリする」を参考）第46回特定テーブルをロールバックする方法：MySQL道普請便り｜gihyo.jp … 技術評論社

とりあえず現状のgtid_executedを確認してみます。

fuki1 [(none)]> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 11025939
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 7d2772a5-8987-11e8-a6a6-0201858272ab:1-128919
1 row in set (0.03 sec)

次に、問題のクエリをbinlogから探し出してGTIDを調べます。

$ /usr/local/mysql8012/bin/mysqlbinlog -vv mysql-bin.000002 | grep -A10 -B20 'lhfukamachi' 
(省略)
SET @@SESSION.GTID_NEXT= '7d2772a5-8987-11e8-a6a6-0201858272ab:123919'/*!*/;
# at 9448700
#181216 13:28:00 server id 19355  end_log_pos 9448789 CRC32 0xd73ed147  Query   thread_id=30269 exec_time=15    error_code=0
SET TIMESTAMP=1544934480/*!*/;
BEGIN
/*!*/;
# at 9448789
#181216 13:28:00 server id 19355  end_log_pos 9448908 CRC32 0x71786abb  Query   thread_id=30269 exec_time=15    error_code=0
SET TIMESTAMP=1544934480/*!*/;
UPDATE t1 SET value = 'lhfukamachi'
/*!*/;
# at 9448908
#181216 13:28:00 server id 19355  end_log_pos 9448939 CRC32 0x4314fe86  Xid = 91082
COMMIT/*!*/;
# at 9448939
#181216 13:28:54 server id 19355  end_log_pos 9449014 CRC32 0x3e0275c5  GTID    last_committed=30003    sequence_number=30004   rbr_only=no     original_committed_timestamp=1544934534697601 immediate_commit_timestamp=1544934534697601      transaction_length=317
# original_commit_timestamp=1544934534697601 (2018-12-16 13:28:54.697601 JST)
# immediate_commit_timestamp=1544934534697601 (2018-12-16 13:28:54.697601 JST)
/*!80001 SET @@session.original_commit_timestamp=1544934534697601*//*!*/;
SET @@SESSION.GTID_NEXT= '7d2772a5-8987-11e8-a6a6-0201858272ab:123920'/*!*/;

問題のクエリのGTIDはこれっぽい。

7d2772a5-8987-11e8-a6a6-0201858272ab:123919

ここで一旦MySQLを止めて、binlogを退避後、過去のコールドバックアップをもってきます。

$ MYSQL_PWD='********' /usr/local/mysql8013/bin/mysqladmin --defaults-file=/data/mydatabase/my.cnf -uroot shutdown
$ cp -p  /data/mydatabase/mysql-bin.index /data/tmp/relaylog_backup/mysql-relay.index
$ cp -p  /data/mydatabase/mysql-bin.000001 /data/tmp/relaylog_backup/mysql-relay.000001
$ cp -p  /data/mydatabase/mysql-bin.000002 /data/tmp/relaylog_backup/mysql-relay.000002
$ vim mysql-relay.index
~
`mysql-bin.*` を`mysql-relay*`に書きなおしましょう。
~

// 過去のコールドバックアップの解凍
# zstd -dc -p2 mydatabase.20182214.tar.zst | tar xv
// 現在のdatadirを名前を変えて退避
# mv /data/mydatabase  /data/_mydatabase
// datadirに過去のコールドバックアップを置き直す。
# mv mydatabase /data/mydatabase

続いて過去のbinlogをrelaylogとして設置します

$ cp -p /data/tmp/relaylog_backup/mysql-relay.* /data/mydatabase/

あとはMySQLを起動後、RESET MASTERしてから除外するクエリのGTIDをそえて、GTID_PURGEDを設定します。

fuki1 [(none)]> SELECT @@gtid_executed;
+----------------------------------------------+
| @@gtid_executed                              |
+----------------------------------------------+
| 7d2772a5-8987-11e8-a6a6-0201858272ab:1-93916 |
+----------------------------------------------+
1 row in set (0.00 sec)

fuki1 [(none)]> RESET MASTER;
Query OK, 0 rows affected (0.06 sec)

fuki1 [d1]> SET GLOBAL GTID_PURGED='7d2772a5-8987-11e8-a6a6-0201858272ab:1-93916,7d2772a5-8987-11e8-a6a6-0201858272ab:123919'; 
Query OK, 0 rows affected (0.08 sec)

fuki1 [mysql]> SET GLOBAL server_id = 1;
Query OK, 0 rows affected (0.00 sec)

fuki1 [mysql]> SET GLOBAL SLAVE_PARALLEL_TYPE = 'LOGICAL_CLOCK';
Query OK, 0 rows affected (0.00 sec)

fuki1 [mysql]> SET GLOBAL SLAVE_PARALLEL_WORKERS = 3;
Query OK, 0 rows affected (0.00 sec)

CHANGE MASTER構文とSTART SLAVE sql_threadを実施してreloylog（もともとbinlogだったやつ）の読み込み開始

fuki1 [(none)]> CHANGE MASTER TO master_host='dummy',
    -> relay_log_file = 'mysql-relay.000001',
    -> relay_log_pos= 4;
Query OK, 0 rows affected (0.04 sec)

fuki1 [(none)]> START SLAVE sql_thread;
Query OK, 0 rows affected (0.10 sec)

おぉ。innotopのトランザクションをみてると確かに3 threadで実行されてる！！

History  Versions  Undo  Dirty Buf  Used Bufs  Txns  MaxTxnTime  LStrcts
     51                      2.67%      3.78%     8       00:00

ID  User        Host  Txn Status  Time   Undo  Query Text
12  system use        ACTIVE (PR  00:00     2
13  system use        ACTIVE (PR  00:00     2
14  system use        ACTIVE (PR  00:00     2

完了後データを確認してみるとたしかにgtid_purgedで設定したクエリはとばされて読み込みが完了してます。

fuki1 [d1]> SELECT * FROM t1 limit 10;
+----+------------+
| id | value      |
+----+------------+
|  1 | C8vAt5qSyl |
|  2 | 15mtFfSGuz |
|  3 | 96ZFZ0451W |
|  4 | ZHM6qMQRXI |
|  5 | xMkqCVFgOT |
|  6 | FWBCUry1FW |
|  7 | Uoa5Y1sOPK |
|  8 | DoCkXtvZSj |
|  9 | JdiF94D1t2 |
| 10 | dwqV6ykt54 |
+----+------------+
10 rows in set (0.01 sec)

fuki1 [d1]> SELECT @@gtid_executed;
+-----------------------------------------------+
| @@gtid_executed                               |
+-----------------------------------------------+
| 7d2772a5-8987-11e8-a6a6-0201858272ab:1-128919 |
+-----------------------------------------------+
1 row in set (0.00 sec)

fuki1 [d1]> select count(1) FROM t1;
+----------+
| count(1) |
+----------+
|  1011000 |
+----------+
1 row in set (0.13 sec)

こんな感じでMTSを利用してPITRができるようです。すごい！本来は速度検証してどれくらい早くなるかまでやりたかったけど更新データ量が少なくてそこまで至らなかった感じです。。。:bow: 多分自分が本番で実際することはないですが、こういうやり方もあるよーって感じで勉強になりました。

以上です。

そろばんのしょ（第3版第2刷）

雑魚い見習いDBAの日々学んだことや、どーでもいいことを。基本は自分の備忘録。

MySQL 5.7 Database Administratorを取ってきた（半年前の話）

これから受験しようと考えている人へ

やったこと１：サンプル問題を解く

やったこと２：YouTubeを見る

やったこと３：ドキュメントを読む

その他

最後に

foreign_key_checksの挙動を知る

foreign_key_checksの挙動を知る

SET GLOBAL/SESSION

親テーブルでトランザクションを開始してSELECTすると子テーブルがDROPできない

子テーブルでトランザクションが開始されていると親テーブルにINDEXが追加できない

まとめ

CLion on macOSでMySQLをデバッグする

とりあえずビルドするまで

CLionの設定からデバッグまで

注意：2020年5月現在、macbook air 2020ではCLionがクラッシュする。

MySQLのHELP構文ってやつ

いい加減MySQL8.0のRoleを理解する。その１

ROLEを作成してみる

Oracle11gでのLIMIT,OFFSETするクエリをMySQL8のwindow関数で利用してみる

MTSを使ってクエリをロールバックする。