41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

適応ハッシュインデックスについて

はじめに

こないだのAdvent Calendar用の記事で適応ハッシュインデックス(AHI)について少し書いたんだけど、今日はそもそもそれってどうやって使ったり見たりするの?って話です。

適応ハッシュインデックスとは?(おさらい)

  • InnoDBのインデックスはクラスタ化されていて、主キーのリーフノードにデータが一緒に格納されている。
  • そのため、主キーによる検索は高速だが、セカンダリインデックスを用いた検索では、セカンダリインデックスから条件に合致するインデックスエントリの主キーの値を取得し、改めて主キーを検索するという2段階の検索が生じるため、やや遅い。
  • それに対し、バッファプール上にハッシュテーブルを動的に作成し、主キーの検索を効率化させるようにしたのが適応ハッシュインデックス。

詳しい説明については、下記のマニュアル参照

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.13.6 適応型ハッシュインデックス

有効化するには?

以下のシステム変数により、オンラインでON/OFFの切り替えが可能。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.12 InnoDB の起動オプションおよびシステム変数

デフォルトは?

ONです。

SET_VARヒント使える?(8.0の話)

使えません

SET_VARヒント→ MySQL :: MySQL 8.0 Reference Manual :: 8.9.3 Optimizer Hints 参照

モニタリングを有効化するには

innodb_monitor_enableにmodule_adaptive_hashを設定する。

mysql> SET GLOBAL innodb_monitor_enable='module_adaptive_hash';

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.14.6 InnoDB INFORMATION_SCHEMA メトリックテーブル

AHIをクエリで確認する

AHIを使っているかどうか

以下のようなクエリで確認することが可能

mysql> SELECT name, count FROM information_schema.INNODB_METRICS WHERE name LIKE '%adaptive%';

どの程度の適応ハッシュインデックスがバッファプールに存在するか

以下のようなクエリで確認することが可能

mysql> SELECT SUM(IS_HASHED = 'YES') HASHED_PAGES, COUNT(1) TOTAL_PAGES FROM information_schema.INNODB_BUFFER_PAGE;

但し、上記クエリは実行に負荷が高いので、本番環境で実行するのはおススメできない。(検証環境でやろう) また、出力された結果は、ハッシュそのもののサイズではなく、これはハッシュが作成されたページ数であるためあくまで参考情報。

INNODB_BUFFER_PAGE テーブルについては以下のドキュメント参照。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.29.16 INFORMATION_SCHEMA INNODB_BUFFER_PAGE テーブル

ヒット率

以下のようなクエリで確認することが可能

mysql> SELECT ahi_hits, ahi_miss, 100 * ahi_hits / ahi_miss AS ahi_hit_pct
         > FROM (SELECT
         > (SELECT count FROM information_schema.INNODB_METRICS WHERE NAME = 'adaptive_hash_searches') AS ahi_hits,
         > (SELECT count FROM information_schema.INNODB_METRICS WHERE NAME = 'adaptive_hash_searches_btree') AS ahi_miss) t;

とはいえ、有効にしたとしてもワークロード上、必ず早くなるというわけではないため、切り替えを行う場合は確認が必要。

INNODB_METRICS テーブルについては以下のドキュメント参照。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.29.19 INFORMATION_SCHEMA INNODB_METRICS テーブル

その他

B ツリーインデックスとハッシュインデックスの比較

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.3.8 B ツリーインデックスとハッシュインデックスの比較

を読もう。

おわりに

これも自分用のメモみたいなもんなんで殴り書きレベルです。

なんか新しく気づいたことあれば追記します。

読みにくかったらごめんなさい。

MyISAMってなんだろう?

はじめに

最近、MyISAMのこと調べる機会が多かったので、今までの人生でMyISAMテーブルを触ることなんてほぼ0だったし、MySQL的には今更な感じもするけどとりあえず書き出してみる。

(殴り書きに近い。あとで調べるときにとっかかりになればと思ってる)

なので、後日付け足していける感じの自分用のメモ扱い。

基本的にドキュメントは5.6以降大きな変更無いので、5.6ベースのもののリンクを貼っとく。

MyISAMとは

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2 MyISAM ストレージエンジン

  • MySQL5.5以降はInnoDBがデフォルトのストレージエンジン

MyISAM 起動オプション

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.1 MyISAM 起動オプション

キーに必要な容量

インデックスファイルのサイズは、キーを (key_length+4)/0.67 と計算し、すべてのキーに対してその値を合計して概算できます。
これは、すべてのキーがソート順に挿入され、テーブル内のキーが圧縮されていなときの最悪なケースです。
MyISAM テーブルでは、テーブルの作成時に PACK_KEYS=1 テーブルオプションを指定することで、数値のプリフィクスを圧縮することもできます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.2 キーに必要な容量

MyISAM テーブルのストレージフォーマット

以下の3つがある

  • 静的 (固定長) テーブル
  • 動的テーブル
  • 圧縮テーブル

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.3 MyISAM テーブルのストレージフォーマット

OPTIMIZE TABLEで再編成

MyISAM テーブルの場合、OPTIMIZE TABLE は次のように機能します。

* テーブルが行を削除または分割した場合は、そのテーブルを修復します。
* インデックスページがソートされていない場合は、それをソートします。
* テーブルの統計が最新でない (そのため、インデックスのソートによって修復を実行できない) 場合は、それを更新します。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.4 OPTIMIZE TABLE 構文

myisamchk -r でも可。

MyISAM テーブルの破損が起きるとき

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.4.1 MyISAM テーブルの破損

適切に閉じられなかったテーブルの問題

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.4.2 適切に閉じられなかったテーブルの問題

MyISAM テーブルの保守とクラッシュリカバリ

MySQL :: MySQL 5.6 リファレンスマニュアル :: 7.6 MyISAM テーブルの保守とクラッシュリカバリ

MyISAM テーブルの保守は、myisamchk が実行するものと似た操作を実行する SQL ステートメントを使用しても実行できます。
* MyISAM テーブルをチェックするには、CHECK TABLE を使用します。
* MyISAM テーブルを修復するには、REPAIR TABLE を使用します。
* MyISAM テーブルを最適化するには、OPTIMIZE TABLE を使用します。
* MyISAM テーブルを分析するには、ANALYZE TABLE を使用します。

MyISAMテーブルの修復

REPAIR TABLE を実行する。

myisamchk --recover tbl_nameも可

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.5 REPAIR TABLE 構文

myisamchk — MyISAM テーブルメンテナンスユーティリティー

その他MyISAM関連のユーティリティプログラム

myisam_ftdump — 全文インデックス情報の表示

myisamlog — MyISAM ログファイルの内容の表示

myisampack — 圧縮された読み取り専用の MyISAM テーブルの生成

mysqlhotcopy — データベースバックアッププログラム

バックアップ(というかテーブルコピーメイン)

自分の過去の記事↓

MySQLでのテーブルコピー(InnoDBとMyISAM) - 41から始めました

mysqldumpでのバックアップの場合

--lock-tablesまたは -lを使ってダンプするすべてのテーブルをダンプ前にロック(READ LOCK)する。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

DELETE構文

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.2 DELETE 構文

MyISAMテーブルの項目を参照

DELETE ステートメントの速度

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.2.3 DELETE ステートメントの速度

MyISAM テーブル内の個々の行を削除するために必要な時間は、インデックスの数に正確に比例します。
行をもっと速く削除するには、key_buffer_size システム変数を増やして、キーキャッシュのサイズを大きくできます。
MyISAM テーブルからすべての行を削除するには、TRUNCATE TABLE tbl_name の方が DELETE FROM tbl_name より速くなります。
切り捨て操作はトランザクションセーフではありません。
アクティブなトランザクションやアクティブなテーブルロックの途中で試みるとエラーが発生します。

MyISAMからInnoDB

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.6.4 MyISAM から InnoDB へのテーブルの変換

漢(オトコ)のコンピュータ道: MyISAMからInnoDBへ切り替えるときの注意点

大きな違いはロッキングとデータの堅牢性。

MyISAMはテーブルロック、原子性の非担保による壊れやすさ。

MyISAMの最適化

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.6 MyISAM テーブルの最適化  

パーティション関連の話

MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.6 パーティショニングの制約と制限

パラメータ一覧

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

key_buffer_size

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

値を増やすことで、すべての読み取りおよび複数の書き込みのインデックス処理を改善できます。
システムの主な機能が MyISAM ストレージエンジンを使用して MySQL を実行する場合、マシンの合計メモリーの 25% がこの変数の許容可能な値です。
ただし、値を大きくし過ぎると (マシンの合計メモリーの 50% 超)、システムのページングが始まってきわめて低速になることがあります。
これは MySQL がデータ読み取りのためのファイルシステムキャッシュの実行をオペレーティングシステムに依存しているためで、ファイルシステムキャッシュのためにいくらかの空きを残しておく必要があります。
また、MyISAM に追加して使用するほかのストレージエンジンのメモリー要件も考慮します。

MyISAMテーブルの最大サイズ

MySQL :: MySQL 5.6 リファレンスマニュアル :: D.10.3 テーブルサイズの制限

これ調べてみたら、可変長カラムを含む場合のみっぽい。

カラムが固定長オンリーの場合はこの限界サイズを超えて、可変長カラムの場合の約カラム長倍になるという。

詳しく知りたい人はmi_create.ccあたりを読んでみるといいです。

GTIDとMyISAM

混ぜるな危険。

クラッシュ時はソースとレプリカの整合性が崩れてレプリケーションが停止する。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.3.4 GTID ベースレプリケーションの制約

 GTID を使用する場合、MyISAM などの非トランザクションストレージエンジンを使用するテーブルへの更新は、InnoDB などのトランザクションストレージエンジンを使用するテーブルへの更新と同じステートメントまたはトランザクションで実行できません。
この制約は、非トランザクションストレージエンジンを使用するテーブルへの更新とトランザクションストレージエンジンを使用するテーブルへの更新が、同じトランザクション内に混在していると、複数の GTID が同じトランザクションに割り当てられる可能性があるためです。

統計情報について

MyISAMはインデックスのファイルサイズと格納されているレコード数を管理しているだけ→実質的には統計情報と呼べる情報は管理してない。

なぜならMyISAMには、対象のインデックスエントリがそのインデックス内で何番目のレコードかということを即座に返す機能があるため、インデックスの統計情報を維持する必要がないから。

内部一時テーブルストレージエンジン

MySQL5.6だと以下の通り

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.4.4 MySQL が内部一時テーブルを使用する仕組み

場合により、サーバーはクエリーの処理中に内部一時テーブルを作成します。
それらのテーブルは、メモリー内に保持して、MEMORY ストレージエンジンによって処理したり、ディスク上に格納して、MyISAM ストレージエンジンによって処理したりできます。
サーバーは最初にインメモリーテーブルとして内部で一時テーブルを作成し、それが大きくなりすぎた場合に、それをディスク上テーブルに変換することがあります。

MySQL5.7ではサーバーがディスク上の内部一時テーブルを管理するために使用するストレージエンジンとしてMyISAMがパラメータ(internal_tmp_disk_storage_engine)で指定可能。

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_internal_tmp_disk_storage_engine

しかし、このパラメータ、8.0.16で削除される。理由はInnoDBに固定となったため。

一方で、internal_tmp_mem_storage_engineというパラメータもできる。こちらは名前がややこしいが別物かつエンジンはTempTable or MEMORY)

MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables

おわりに

他にもMyISAMってこんな特徴、問題あるよ!って話あれば教えてください。

TRUNCATE TABLEがバッファプールを読んでいる

本エントリはMySQL Advent Calendar 2020 の13日目です。

今日はちょっとだけMySQL8.0にアップグレードしとくと良いよ、というネタです。

TRUNCATEよりもDROPとCREATEのほうが性能が良いか?

はい、MySQL5.7以前まではDROPと&CREATE のほうがTRUNCATE TABLEよりも高速なんです。

なぜならこんなバグが5.7以前にはあるからです。

要約すると、MySQL 5.7以前のInnoDBでは、TRUNCATE TABLEを実行した際、ファイルを再作成する以外にも、バッファプールのスキャンが発生するというものです。

ただし、適応型ハッシュインデックスがOFFの場合には、スキャンは生じません。

このバグ、MySQL8.0で改善されました。

どういうときに困るのか?

大きなバッファプールを持っていて、TRUNCATE対象のテーブル情報がそこに乗っているとき。

テーブルの再作成+バッファープールのスキャンが走るので、テーブルがでかければでかいほど遅くなるってわけです。

この令和の時代、MySQL Serverでさすがにinnodb_buffer_pool_size=128MBのデフォルトサイズでいつまでも運用してる会社さんはそうそういないでしょう。

皆さん最低100GBのメモリくらいは積んでますよね?

ですから、皆さん全員困ってると思ってですね・・・おや、誰か来たようだ。

対応方法

  • TRUNCATE TABLEをやめて、DROP TABLE+CREATE TABLEにする。
  • innodb_adaptive_hash_indexをOFFにして、適応型ハッシュインデックスを無効にする。
  • MySQL8.0にアップグレードする。

バッファプールを小さくすることは他の処理への影響もあるのでやらないこと。(もちろん、OMMKillerが発生するような設定の場合は別)

innodb_adaptive_hash_indexをOFFにするのは動的に行えます。

なんでMySQL8.0では直ったの?

MySQL 8.0では、内部的にTRUNCATEテーブルがDROPとCREATEを連続して行うのと等価の操作に置き換えられ、バッファプールを読まなくなったからです。

https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html

In MySQL 5.7 and earlier, on a system with a large buffer pool and innodb_adaptive_hash_index enabled, a TRUNCATE TABLE operation could cause a temporary drop in system performance due to an LRU scan that occurred when removing the table's adaptive hash index entries (Bug #68184). The remapping of TRUNCATE TABLE to DROP TABLE and CREATE TABLE in MySQL 8.0 avoids the problematic LRU scan.

ところで適応型ハッシュインデックス(AHI)って何?

一部のインデックス値のアクセス頻度が高い場合、バッファプール上にハッシュテーブルを動的に作成し、主キーの検索を効率化させるInnoDBエンジンの機能です。

更には、セカンダリインデックスによる検索はその過程で主キーによる等価比較が行われますが、AHIは主キーの検索を効率化させているので、セカンダリインデックスによる検索が多いワークロードだと検索効率が上がる可能性も大いにあります。

一方で、これが複数の並列結合などの負荷が高いワークロードではロックが競合の原因となったり、LIKE 演算子と % ワイルドカードを使用したクエリは恩恵が得られないといったことがあり、5.6以降のアーキテクチャ改善によりこれがONになっているメリットは過去のバージョンと比べると徐々に減っています。

とはいえ、デフォルトはONであり、ONとなっていることで高速化している可能性もあるので、知らずに使っていた人もOFFにする場合は事前に検証が必要な機能です。

(これ、地味にパフォーマンスに影響する意外と重要な機能ですよね)

詳しくはドキュメントをご確認下さい。

AHIでおまけ

Perconaのピーター・ツァイツェフさんがAHI関連で面白い記事を2本載せているので参考に載せておきます。

一つは古いですが、MySQL5.7.11の段階で色々なデータで検証したものです。 www.percona.com データが非常に多くなった場合、sysbenchで検証した結果としては効率が落ちてはいるものの、MySQLのドキュメントにある通り、ワークロードに応じて使える場合もあるのでちゃんと確認してON/OFFしようねという内容になっています。

もう一つはAuroraでReaderノードでは無効なAHIをWriterノードで有効にしたら同じクエリがWriterでは高速だったという話です。 www.percona.com

今現在、Auroraでどのような設定になっているのか知らないのですが、innodb_adaptive_hash_indexがデフォルトでは無効だったらしく、かつReaderノードではAHIを有効化できなかったが、Writerノードでは変更できたので変えてみたら高速になったので注意してねとのこと。

(そういえば、AuroraがMySQL8.0をComing soon的な話を聞きましたが、クエリキャッシュはどうするんだろう?)

明日は?

明日はupdraftさんの

今日は、DBで日付にはDATE型?VARCHAR型どっちの日。 - 今日はなにの日。

です。

お楽しみに!

MySQL ShellのダンプとMDSへのインポート

はじめに

今回はずっと触りたかったMySQL Shellのダンプをやりつつ、 最後は前回作ったMDS相手にインポートしてみようかと思います。

MDSにインポートするためにはociクライアントの設定や、オブジェクトストレージが必要なので、それらを途中で設定していきます。

準備

1. 手元のdbdeployerで作成したMySQL8.0.21の環境に、公式が出しているテスト用データベースたちを適当に入れていきます。

自分はgit cloneで取得しましたが、zip形式でもダウンロードは可能です。

$ git clone https://github.com/datacharmer/test_db.git

2. MySQL Shellももちろんインストールしておいてください

自分のところの環境はUbuntuとOCI側はCentOSですので、以下のとおりです。

Ubuntu

$ sudo dpkg -i /PATH/version-specific-package-name.deb -- 今時点だとmysql-apt-config_0.8.15-1_all.debが最新の様子
$ sudo apt-get update
$ sudo apt-get install mysql-shell

CentOS(前回やりましたが、一応転記)

$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
$ sudo yum install mysql-shell

3. 1で取得したスキーマ達をローカルの8.0.21にインポート

$ cd test_db/
$ mysql -uroot -p -h127.0.0.1 -P8021 < employees.sql
$ mysql -uroot -p -h127.0.0.1 -P8021 < test_employees_md5.sql
$ mysql -uroot -p -h127.0.0.1 -P8021 < test_employees_sha.sql
$ mysql -uroot -p -h127.0.0.1 -P8021 < sakila/sakila-mv-schema.sql
$ mysql -uroot -p -h127.0.0.1 -P8021 < sakila/sakila-mv-data.sql
$ 
$ # 作成したスキーマを確認(testはdbdeployerで自動で作っている)
$ mysql -uroot -p -h127.0.0.1 -P8021

(root@127.0.0.1) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

MySQL ShellのdumpInstanceを使っていく

ダンプ取得で使えるものとしてdumpInstanceとdumpSchemasがあるけど、その差はスキーマ(データベース)単位でやるか否かだけの話。

dumpInstanceのほうが自分はよく使いそうだし、2つの差も引数としてスキーマ指定が絡むだけっぽいのでdumpInstanceを触っていく

dumpInstanceを試す

ダンプユーティリティのオプションについては公式ドキュメントにあるのでそれを参照するのだが、事例が少ない。

書き方で戸惑うかもしれないので試してみて実行できた書き方を以下並べてみる。

第1引数が出力先のディレクトリ名になります。(/tmp/dumpdirとか)

上書きしようとするとエラーになるので、

excludeSchemas

指定したスキーマをダンプから除外します。

例)testスキーマを除外

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir",{excludeSchemas:["test"]})
Acquiring global read lock
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Preparing data dump for table `sakila`.`actor`
Writing DDL for schema `sakila`
Writing DDL for view `sakila`.`customer_list`
Writing DDL for view `sakila`.`actor_info`
Writing DDL for view `sakila`.`film_list`
Data dump for table `sakila`.`actor` will be chunked using column `actor_id`
Preparing data dump for table `sakila`.`address`
Data dump for table `sakila`.`address` will be chunked using column `address_id`
Preparing data dump for table `sakila`.`category`
Data dump for table `sakila`.`category` will be chunked using column `category_id`
Preparing data dump for table `sakila`.`city`
Data dump for table `sakila`.`city` will be chunked using column `city_id`
Preparing data dump for table `sakila`.`country`
Data dump for table `sakila`.`country` will be chunked using column `country_id`
Preparing data dump for table `sakila`.`customer`
Data dump for table `sakila`.`customer` will be chunked using column `customer_id`
Preparing data dump for table `sakila`.`film`
Data dump for table `sakila`.`film` will be chunked using column `film_id`
Preparing data dump for table `sakila`.`film_actor`
Data dump for table `sakila`.`film_actor` will be chunked using column `actor_id`
Preparing data dump for table `sakila`.`film_category`
Data dump for table `sakila`.`film_category` will be chunked using column `film_id`
Preparing data dump for table `sakila`.`film_text`
Writing DDL for view `sakila`.`nicer_but_slower_film_list`
Data dump for table `sakila`.`film_text` will be chunked using column `film_id`
Preparing data dump for table `sakila`.`inventory`
Data dump for table `sakila`.`inventory` will be chunked using column `inventory_id`
Preparing data dump for table `sakila`.`language`
Writing DDL for view `sakila`.`sales_by_film_category`
Data dump for table `sakila`.`language` will be chunked using column `language_id`
Preparing data dump for table `sakila`.`payment`
Writing DDL for view `sakila`.`sales_by_store`
Writing DDL for view `sakila`.`staff_list`
Data dump for table `sakila`.`payment` will be chunked using column `payment_id`
Preparing data dump for table `sakila`.`rental`
Data dump for table `sakila`.`rental` will be chunked using column `rental_id`
Preparing data dump for table `sakila`.`staff`
Writing DDL for table `sakila`.`actor`
Data dump for table `sakila`.`staff` will be chunked using column `staff_id`
Preparing data dump for table `sakila`.`store`
Data dump for table `sakila`.`store` will be chunked using column `store_id`
Preparing data dump for table `employees`.`departments`
Data dump for table `employees`.`departments` will be chunked using column `dept_no`
Preparing data dump for table `employees`.`dept_emp`
Data dump for table `employees`.`dept_emp` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`dept_manager`
Writing DDL for table `sakila`.`address`
Data dump for table `employees`.`dept_manager` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`employees`
Writing DDL for table `sakila`.`category`
Data dump for table `employees`.`employees` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`salaries`
Data dump for table `employees`.`salaries` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`titles`
Data dump for table `employees`.`titles` will be chunked using column `emp_no`
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing DDL for table `sakila`.`city`
Writing DDL for table `sakila`.`country`
Writing DDL for table `sakila`.`customer`
Writing DDL for table `sakila`.`film`
Writing DDL for table `sakila`.`film_actor`
Writing DDL for table `sakila`.`film_category`
Writing DDL for table `sakila`.`film_text`
Writing DDL for table `sakila`.`inventory`
Writing DDL for table `sakila`.`language`
Writing DDL for table `sakila`.`payment`
Writing DDL for table `sakila`.`rental`
Writing DDL for table `sakila`.`staff`
Writing DDL for table `sakila`.`store`
Writing DDL for schema `employees`
Writing DDL for view `employees`.`current_dept_emp`
Writing DDL for view `employees`.`dept_emp_latest_date`
Writing DDL for table `employees`.`departments`
Writing DDL for table `employees`.`dept_emp`
Writing DDL for table `employees`.`dept_manager`
Writing DDL for table `employees`.`employees`
Writing DDL for table `employees`.`salaries`
Writing DDL for table `employees`.`titles`
Data dump for table `sakila`.`actor` will be written to 1 file
Data dump for table `sakila`.`category` will be written to 1 file
Data dump for table `sakila`.`address` will be written to 1 file
Data dump for table `sakila`.`country` will be written to 1 file
Data dump for table `sakila`.`customer` will be written to 1 file
Data dump for table `sakila`.`city` will be written to 1 file
Data dump for table `sakila`.`film_actor` will be written to 1 file
Data dump for table `sakila`.`film` will be written to 1 file
Data dump for table `sakila`.`film_category` will be written to 1 file
Data dump for table `sakila`.`film_text` will be written to 1 file
Data dump for table `sakila`.`language` will be written to 1 file
Data dump for table `sakila`.`payment` will be written to 1 file
Data dump for table `sakila`.`inventory` will be written to 1 file
Data dump for table `sakila`.`rental` will be written to 1 file
Data dump for table `sakila`.`store` will be written to 1 file
Data dump for table `sakila`.`staff` will be written to 1 file
Data dump for table `employees`.`departments` will be written to 1 file
Data dump for table `employees`.`dept_manager` will be written to 1 file
Data dump for table `employees`.`dept_emp` will be written to 1 file
Data dump for table `employees`.`employees` will be written to 1 file
Data dump for table `employees`.`salaries` will be written to 1 file                                            
Data dump for table `employees`.`titles` will be written to 1 file                                               
1 thds dumping - 100% (3.97M rows / ~3.93M rows), 733.12K rows/s, 26.80 MB/s uncompressed, 7.02 MB/s compressed  
Duration: 00:00:05s                                                                                            
Schemas dumped: 2                                                                                              
Tables dumped: 22                                                                                              
Uncompressed data size: 144.53 MB                                                                              
Compressed data size: 37.75 MB                                                                                 
Compression ratio: 3.8                                                                                         
Rows written: 3966288                                                                                          
Bytes written: 37.75 MB                                                                                        
Average uncompressed throughput: 26.31 MB/s                                                                    
Average compressed throughput: 6.87 MB/s 

確かにtestスキーマに対してはバックアップを取得していないですね。

最初だけは出力結果を上記のように表示しましたが、長いんであとは省略。

基本コマンド・オプション実行例だけだしておきます。

excludeTables

任意のテーブルをダンプから除外します。

DDLだけとかデータだけとかそういうオプションはありません、とのこと。

ちなみにmysql.apply_status、 mysql.general_log、 mysql.schema、とmysql.slow_log tablesあたりのデータだけは勝手に除外されるようです。

例)test.test02テーブルを除外

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir2",{excludeTables:["test.test02"]})

ユーザ情報を除外

ユーザ関連の情報を除外します。これはcreate userやrole,grantとかそういったものを移行先に持っていかないようにするやつですね。

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir3",{users:false})

メタ定義のみ

いわゆるmysqldumpでいう--no-dataですね。DDLだけ取得するやつです。

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir4",{ddlOnly:true})

データオンリー

いわゆるmysqldumpでいう--no-create-infoですね。データだけ取得するやつです。

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir5",{dataOnly:true})

ドライラン

要するに実際に作りはしないけど、やったらどうなるかな?ってやつ。

試しに既に作成済のダンプディレクトリに出力しようとした→ドライランなのにエラー(なかなか便利そう)

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir5",{excludeSchemas:["test"],dryRun:true})
Util.dumpInstance: Cannot proceed with the dump, '/tmp/dumpdir5' already exists at the target location /tmp/dumpdir5. (ArgumentError)

testスキーマ抜きでやってみる→実際は出力されない(OK)

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir6",{excludeSchemas:["test"],dryRun:true})
/tmp$ ll 
合計 92
drwxrwxrwt 20 root    root    4096 10月  4 06:29 ./
drwxr-xr-x 26 root    root    4096  9月 23 09:59 ../
drwxr-x---  2 (user) (user) 4096 10月  4 06:21 dumpdir5/
srwxrwxrwx  1 (user) (user)    0 10月  4 06:11 mysql_sandbox8021.sock=
...

/tmp/dumpdir6がないですね。

mdsに持っていけるかチェック

エラーのオンパレードの模様…

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir6",{excludeSchemas:["test"],dryRun:true, ocimds: true})
Checking for compatibility with MySQL Database Service 8.0.21
ERROR: User root@localhost is granted restricted privileges: RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID
ERROR: User R_DO_IT_ALL@% is granted restricted privileges: RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID
ERROR: User R_POWERFUL@% is granted restricted privileges: RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID
NOTE: Database sakila had unsupported ENCRYPTION option commented out
ERROR: Function sakila.get_customer_balance - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Function sakila.inventory_held_by_customer - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Function sakila.inventory_in_stock - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Procedure sakila.film_in_stock - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Procedure sakila.film_not_in_stock - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Procedure sakila.rewards_report - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
NOTE: Database employees had unsupported ENCRYPTION option commented out
ERROR: Trigger sakila.customer_create_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.ins_film - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.upd_film - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.del_film - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.payment_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.rental_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.actor_info - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.customer_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.film_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.nicer_but_slower_film_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.sales_by_film_category - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.sales_by_store - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.staff_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View employees.current_dept_emp - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View employees.dept_emp_latest_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
Compatibility issues with MySQL Database Service 8.0.21 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL.
Util.dumpInstance: Compatibility issues were found (RuntimeError)

どうやら今のところMDSに持っていくためには変換が必要=compatibilityっていうオプションが必要な模様

compatibilityを使う

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir6",{excludeSchemas:["test"],dryRun:true,ocimds:true,compatibility:["strip_definers", "strip_restricted_grants"]})
Checking for compatibility with MySQL Database Service 8.0.21
NOTE: User root@localhost had restricted privileges (RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID) removed
NOTE: User R_DO_IT_ALL@% had restricted privileges (RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID) removed
NOTE: User R_POWERFUL@% had restricted privileges (RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID) removed
NOTE: Database sakila had unsupported ENCRYPTION option commented out
NOTE: Function sakila.get_customer_balance had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Function sakila.inventory_held_by_customer had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Function sakila.inventory_in_stock had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Procedure sakila.film_in_stock had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Procedure sakila.film_not_in_stock had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Procedure sakila.rewards_report had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Database employees had unsupported ENCRYPTION option commented out
NOTE: Trigger sakila.customer_create_date had definer clause removed
NOTE: Trigger sakila.ins_film had definer clause removed
NOTE: Trigger sakila.upd_film had definer clause removed
NOTE: Trigger sakila.del_film had definer clause removed
NOTE: Trigger sakila.payment_date had definer clause removed
NOTE: Trigger sakila.rental_date had definer clause removed
NOTE: View sakila.actor_info had definer clause removed
NOTE: View sakila.customer_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.film_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.nicer_but_slower_film_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.sales_by_film_category had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.sales_by_store had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.staff_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View employees.current_dept_emp had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View employees.dept_emp_latest_date had definer clause removed and SQL SECURITY characteristic set to INVOKER
Compatibility issues with MySQL Database Service 8.0.21 were found and repaired. Please review the changes made before loading them.
...

チェック作業が事前に入ってる。

パフォーマンスの調整したい人に

並列数2スレッド、ダンプ中のデータ読み取りスループットのスレッドあたりの1秒あたりの最大バイト数を10MBに抑えるような場合

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir7",threads:2,maxRate:"10M"})

他にも

まだオプションはあるんだけど、あんまり使わなそうなやつは今回省略w

知りたい方はドキュメントを熟読して僕に教えてください。

MDSに直接入れたい

ローカルにあるダンプ結果をOCIだとどうやって上げるんだろう?

というので、調べたところ、オブジェクトストレージというものがありました。(AWSで言うところのS3かな)

これ作って、そこにアップしたらええんやな?

いや、待てよ。直接MySQL Shellでそこにアップできない?

できるんです!くうぅ〜〜〜!!

準備作業

  1. オブジェクトストレージ(正確にはバケット)を作りましょう。(作り方はドキュメントをご参照ください)

  2. OCI クライアントをインストールしましょう。(これもインストールの仕方はドキュメントを参照してください)

とはいえ、OCI クライアントはちょっとハマったので簡単にメモを残しておきます

インストール

$ sudo bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)" --accept-all-defaults

oci cliのconfig作成

$ oci setup config

こんな感じのやり取りがある

    This command provides a walkthrough of creating a valid CLI config file.

    The following links explain where to find the information required by this
    script:

    User API Signing Key, OCID and Tenancy OCID:

        https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#Other

    Region:

        https://docs.cloud.oracle.com/Content/General/Concepts/regions.htm

    General config documentation:

        https://docs.cloud.oracle.com/Content/API/Concepts/sdkconfig.htm


Enter a location for your config [/home/(user)/.oci/config]: 
Config file: /home/(user)/.oci/config already exists. Do you want add a profile here? (If no, you will be prompted to overwrite the file) [Y/n]: n
File: /home/(user)/.oci/config already exists. Do you want to overwrite (Removes existing profiles!!!)? [y/N]: y
Enter a user OCID: ocid1.user.oc1..aaaaaaaa7oboyrvb4bqaeunr4jns7yetr5y5746noimtap2lxo5rtgq7phsq
Enter a tenancy OCID: ocid1.tenancy.oc1..aaaaaaaav7xgcb3nvajmwss3fleubvt4w3fysrtacd5icp6qopwaib3nmjwa
Enter a region (e.g. ap-chiyoda-1, ap-chuncheon-1, ap-hyderabad-1, ap-melbourne-1, ap-mumbai-1, ap-osaka-1, ap-seoul-1, ap-sydney-1, ap-tokyo-1, ca-montreal-1, ca-toronto-1, eu-amsterdam-1, eu-frankfurt-1, eu-zurich-1, me-jeddah-1, sa-saopaulo-1, uk-gov-cardiff-1, uk-gov-london-1, uk-london-1, us-ashburn-1, us-gov-ashburn-1, us-gov-chicago-1, us-gov-phoenix-1, us-langley-1, us-luke-1, us-phoenix-1, us-sanjose-1): ap-tokyo-1
Do you want to generate a new API Signing RSA key pair? (If you decline you will be asked to supply the path to an existing key.) [Y/n]: Y
Enter a directory for your keys to be created [/home/(user)/.oci]: 
Enter a name for your key [oci_api_key]: 
Public key written to: /home/(user)/.oci/oci_api_key_public.pem
Enter a passphrase for your private key (empty for no passphrase): 
Private key written to: /home/(user)/.oci/oci_api_key.pem
Fingerprint: 1e:2b:f5:83:93:93:9e:03:85:05:66:7f:b1:f8:ac:ca
Config written to /home/(user)/.oci/config


    If you haven't already uploaded your API Signing public key through the
    console, follow the instructions on the page linked below in the section
    'How to upload the public key':

        https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#How2

公開鍵をアップロードする

このドキュメントを見ながらユーザー>公開キーの追加で公開鍵を登録します。

公開鍵はこんな感じで見れるかと。

cat /home/(user)/.oci/oci_api_key_public.pem

トークンの検証

oci iam region list

を実行して、以下のように出たらOK

{
  "data": [
    {
      "key": "AMS",
      "name": "eu-amsterdam-1"
    },
    {
      "key": "BOM",
      "name": "ap-mumbai-1"
    },
    {
      "key": "DXB",
      "name": "me-dubai-1"
    },
    ...
    {
      "key": "YYZ",
      "name": "ca-toronto-1"
    },
    {
      "key": "ZRH",
      "name": "eu-zurich-1"
    }
  ]
}

さっそくOCIへ吐き出してみる

osBucketNameとosNamespace

OCIのオブジェクトストレージのバケットに出力するにはこの2つのオプションが必要。

osBucketNameは文字通り、オブジェクトストレージのバケット名、osNamespaceでバケット名前空間を指定します。

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("testbackup",{ocimds:true,compatibility:["strip_definers", "strip_restricted_grants"],"osBucketName": "bucket-hogehoge", "osNamespace": "xxxxxxx"})

osNamespaceは以下のコマンドで取得するか、オブジェクトストレージ>バケットの詳細>ネームスペースで確認するといいです。

oci os ns get

MDSにインポート

事前準備

OCIにインスタンスを一つ立てて、そっちにもOCI クライアントをインストールしましょう。

インポート

そしてMySQL Shellで以下のように実行すればインポートできます

 MySQL  xxxxxx:3306 ssl  JS >util.loadDump("testbackup", {osBucketName: "bucket-hogehoge", osNamespace: "xxxxxxx"})

並列度はインスタンスのCPUパワー次第で上げたら良いんじゃないかなと思いますが、僕はお試しなんでデフォルトのままでやりました。

インポートのほうのパラメータはあんまり調べませんでしたが、パフォーマンスの設定以外は基本エクスポートでやっちゃう人が多いんじゃないかな?と思って今回は手を抜きましたw

さいごに

今回はMySQL ShellとOCI両方ちょっとずつ学ぶことができました。

インポートは若干駆け足気味ですが、まあそんなに難しくないかと思って。

MySQL Shellのダンプが使えるのは8.0.21限定ですし、MDSもまだまだ検証段階って人も多いと思います。

そんな段階だからこそ、触っていざというときにどっちも使えるようになっておけば、きっと将来役に立つこと間違い無しです(?)

MySQL Database Serviceを触るまでのお話

はじめに

MDS(MySQL Database Service)が東京リージョンにも来ました!

(^O^) <祝!

というわけで、朝4時に起こされたこともあり、時間もできたんで久々にOCIを触ってみたんですが、作りたいものが簡単に作れて楽しかったです。

ただ、公式チュートリアルにMDS関連がまだ無いこと、MDS系だとlefredさんのブログとかOracleのセミナー資料しか手順でわかりやすい情報が無いのでまだまだ敷居が微妙に高い可能性もあり、初見の人がどうするの?っていうのをざっくり流れで書いてみました。

でも、細かいところは前述の公式のチュートや記事を参考にしてね。

やりたいこと

要するに、1台どっからでもインターネット上から繋げられるようなVM立てて、そのマシン経由でMySQLに繋げられるイメージ。

マイPC--->インターネット--->OCI領域[VM(Linux)--->MySQL]

作り方(超ざっくり)

1.アカウント作成

まずはなにはともあれアカウントを作る

通常、ここからOracle Cloud Freeでアカウントが作れる。

30日間有効な$300のクレジットがもらえて、MySQL DBシステムは有料なのでそこから支払われる。

と言っても、普段Auroraとか触ってる人からすると安すぎて笑うくらい。

多くの人は30日じゃ使い切れないんじゃないかな。

また、Oracle主催のセミナーとか参加すると更に+$200で合計$500のクレジットとかになることがある(それ用のURLを教えてもらえる)。

そっちからだと通常はクレカ登録があるのだが、無いので楽ちんでもある。

注意点

アカウント作成時の注意点は以下の2つ

  • クラウド・アカウント名を忘れない
  • ホーム・リージョンを日本東部(東京)にしよう

クラウド・アカウント名はログイン時に使う。

ログイン時に使うのはクラウド・アカウント名、メールアドレス、パスワードの3つ

ホーム・リージョンはせっかく日本でMDS使えるようになったー!ってなったのに、アメリカにするのもどうなん?ってことで。

まあ、アッシュバーンは空いてるなんて話もあるんで、それでも良いって人は止めませんがw

2.ログイン

チュートリアル読みながらログインしてみてください。

ちなみにクラウド・アカウント名はテナンシー名とも言われるので、どっかで出てきたら脳内変換してやってください。

クラウド・アカウントの領域を内部で区画わけするコンパートメントって概念もあるけど、個人で使う分には無視でいいかも。

整理整頓好きな人は使ってみるのもいいかも(自分は一回作って、ちょっと触って削除した)

3.VCNの作成

2同様チュートリアル読みながら作成してみてください。

チュートリアルにもありますが、VCNウィザード使ったほうが楽です。VCN名考えることくらいしかやることありませんw

パブリック・サブネットもプライベート・サブネットも作ってくれます。

プライベート・サブネットの更新でエラーが出ることがありますが、プロビジョニングの再試行で普通に作れます。

4.インスタンスの作成

チュートリアルを参考に作成してみてください。

ここに用意されているOSのイメージがデフォルトでは使え、OWindows Server以外はAlways Free対象です。

とりあえず、自分はCentOS7とOracle Linux7.8の2つを作ってみました。

シェイプ、ネットワークおよびストレージ・オプションの表示を開くと先程作ったVCNのパブリックサブネットからIPがデフォルトで割り振られているのがわかります。

接続については

  • SSHキー・ペアの生成
  • 公開キー・ファイルの選択
  • 公開キーの貼付け

がありますが、自分は公開キーを貼り付けてます。(どうせ使うPC決まってるし)

作成されたパブリックIPを使ってsshでつなぎます(以下イメージです)

接続例

$ ssh opc@xxx.xxx.xxx.xxx
The authenticity of host 'xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx)' can't be established.
ECDSA key fingerprint is SHA256:pKpvubZvr4MeMfHVHMzRJxMhRlIdpJqimTdiPD64qLk.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'xxx.xxx.xxx.xxx' (ECDSA) to the list of known hosts.
[opc@tkyk7704vm-c7 ~]$ 

5.MySQLクライアント等を入れとく

mysqlshやmysqlクライアントでDBにつなごうと思っているので、それらをインストールしておきます。

$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
$ sudo yum install mysql-shell
$ sudo yum install mysql-community-client

6.プライベート・サブネットのイングレス・ルールにMySQLポート追加

パブリックIPがこれから作るMySQL DBシステムに接続できるよう、先にプライベート・サブネットのイングレス・ルールにMySQLポートの3306と33060を追加しておきます。

この作業はMySQL DBシステム作ったあとでも問題ありません。

ポート番号もMySQL DBシステムを別の番号で作るのであれば臨機応変に変えてください。

ネットワーキング>仮想クラウド・ネットワーク>作ったVCN>プライベート・サブネット>プライベート・サブネットのセキュリティ・リスト という感じでクリックしていくと、先程作ったVCNに紐づくプライベート・サブネットのイングレス・ルールの追加画面が表示されます。

ここでイングレス・ルールの追加ボタンを押して、パブリック・サブネットをソースCIDRに、宛先ポート範囲に3306,33060を入れて追加ボタンを押せばOKです。

f:id:next4us-ti:20200926173024p:plain

(ちなみにプライベート・サブネットの他のルールは消しておくのが吉です。無駄な穴は消しておきましょう)

山崎さんのスライドでもこの辺の話は記載されてますので参考にしてください。

7.MySQL DBシステムの作成

MySQL>DBシステムをクリックすると作成画面に入ります。

MySQL DBシステムの作成ボタンを押して作ります。

作り方や機能の詳細についてはここからダウンロードできる資料のほうが詳しいので僕は手抜きで説明しませんw

出来上がるまで結構かかります。10分〜15分くらいかかることも。コーヒーでも淹れてゆっくり飲みながら待つのが吉です。

8.DBへ接続

MySQLクライアントやMySQL Shellで接続してみてください。

mysql -u(DBユーザ名) -p -h(DBのエンドポイント名)

mysqlsh (DBユーザ名)@(DBのエンドポイント名)

ちゃんと入れてるのに MySQL Error 2003 (HY000): Can't connect to MySQL server が出るよーって人はイングレス・ルールを見直してみてください (入れるのはプライベート・サブネットのほうですよ!)

9.ローカルからDBを見てみたい?

そういう人はlefredさんのブログを読むと言いです。

SSHトンネルやWorkbenchでつなぐ方法があります。

(一つ前の記事にMySQL Router使うなんてなかなかトリッキーなのもありますが・・・)

コスト

一番小さいサイズとはいえ、数時間放置してて、おっといけね!と見てみたが、

f:id:next4us-ti:20200926193225p:plain

安ぅ〜〜〜!

コストの見積もり、ここでできるみたいだけど、一番小さいやつ、デフォルトだと4400円くらいなんで、$300とか、ましてや$500とか最初にも書いたけどやっぱり使い切れない気がする。

サイズ上げて、ディスクも増やしてガンガン遊んでみるかー!(家族が時間をクレナイケド・・・)

さいごに

1時間もあればこれらの作業ができます。

しかも一回VCNやインスタンスを設定してしまえば、更に時間は短縮されるので、今からVM環境作ったりするよりは圧倒的に早いです。

自分も今日作った環境でこれから色々と遊んでいこうと思います。

追記

日本語のチュートリアルが無いってだけで、ちゃんとドキュメントにはある。

docs.cloud.oracle.com

(これを朝見つけておきたかったなー)

シンキノウ×デ×シルキノウ (CREATE TABLE ... SELECT)

Myリノベ 8.0.21に参加

先日MySQL Release note でわいわい言う勉強会 8.0.21」に参加してまいりました。

今回も最新のMySQL8.0.21のリリースノートをみんなで見ながら、思いつくままに語る会で、その中で僕は相変わらずROM専ですが参加しておりました。

流れとしては坂井さんがオープニングトーク、次にまつさんがJSON_VALUE()のLTを語ってくださり、その他の機能についてはリリースノートを見ながら…という感じでした。

いずれyoutubeあたりに動画が上がると思いますので、全体が知りたい方はtwitterやslackでチェックしてるとよいかと。

8.0.21からはCREATE TABLE ... SELECT気軽に使えるぜ!

さて、その会の中でyoku0825さんがつぶやいたCREATE TABLE ... SELECTの以下の話、マイナーかもしれないけど結構驚きの変更だと思うんですよね。

On storage engines that support atomic DDL, the CREATE TABLE ... SELECT statement is now logged as one transaction in the binary log when row-based replication is in use.
Previously, it was logged as two transactions, one to create the table, and the other to insert data.
With this change, CREATE TABLE ... SELECT statements are now safe for row-based replication and permitted for use with GTID-based replication.
For more information, see Atomic Data Definition Statement Support.

見たことないですか?

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

GTIDを使う人は頭に叩き込まれてるはずなんです。

  • 行ベースで
  • GTID使ってる場合
  • CREATE TABLE ... SELECTはできないって

だって、ドキュメントにもあるんだから。

みんな5.6以降、GTIDを使う人はCREATE TABLE ... SELECT使わないぞ!と心に誓ってたはずなんです。

でも、

MySQL8.0.21からはフツーにレプリカに反映されるようになったんです。

試してみた

作業概要

環境構築

僕はdbdeployer使ってます。とてもかんたんに環境作れますよ。

dbdeployerの詳しい説明については適当にググってもらうとかすればいいんですが、ざっくり書くと

  1. MySQLのtarファイルゲットしてきて
  2. dbdeployerインストールして
  3. dbdeployer unpack mysql-xxxxxxxx-8.0.xx-linux-x86_64-minimal.tar.xz みたいな感じで解凍して
  4. 以下のようなコマンドでレプリケーション環境を作れます。
dbdeployer deploy replication 8.0.xx \
    --custom-role-name=R_POWERFUL \
    --custom-role-privileges='ALL PRIVILEGES' \
    --custom-role-target='*.*' \
    --custom-role-extra='WITH GRANT OPTION' \
    --default-role=R_POWERFUL \
    --bind-address=0.0.0.0 \
    --remote-access='%' \
    --gtid \
    --db-user=admin \
    --db-password=xxxxx

こんな感じで2つのソースレプリカ形式のDBのペアを用意しました。

$ dbdeployer sandboxes
 rsandbox_8_0_20          :   master-slave           8.0.20   [21021 31021 21022 31022 21023 31023 ]
 rsandbox_8_0_21          :   master-slave           8.0.21   [21122 31122 21123 31123 21124 31124 ]

検証開始

そして、2つのバージョンで比較するんですが、やることはかんたん。

  1. テーブル(t1)を作って
  2. テーブルに適当にデータ入れて
  3. 一応テーブルにデータ入ったこと確認して
  4. そのテーブルからデータコピーをCREATE TABLE ...SELECT構文でやって
  5. レプリカ側のほうにあるか確認

というだけの作業。

GTIDの確認

こんな感じでGTIDモードはいずれもONです

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21122 -e "show variables like 'gtid_mode'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
$ mysql -uroot -pxxxxx -h127.0.0.1 -P21021 -e "show variables like 'gtid_mode'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+

MySQL8.0.20

ソース(マスター)側

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21021 test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.20-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> CREATE TABLE `t1` (
    ->    `id` int(11) NOT NULL AUTO_INCREMENT,
    ->    PRIMARY KEY (`id`)
    ->  ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.04 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.02 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

(root@127.0.0.1) [test]> CREATE TABLE t2 select * from t1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
(root@127.0.0.1) [test]> select * from t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

ERROR 1786が出ますね。

当然ソース・レプリカいずれのインスタンスにもt2テーブルは作られていません。

これを8.0.21でもやります。

MySQL8.0.21

ソース(マスター)側

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21122 test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> CREATE TABLE `t1` (
    ->    `id` int(11) NOT NULL AUTO_INCREMENT,
    ->    PRIMARY KEY (`id`)
    ->  ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.02 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

(root@127.0.0.1) [test]> CREATE TABLE t2 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@127.0.0.1) [test]> select * from t2;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

エラーが出ない!

んじゃ、レプリカ側はどうなってるかな?

レプリカ(スレーブ)側

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21123 test
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

(root@127.0.0.1) [test]> select * from t2;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

できてるぅ!ヒャッホー!

binlogの比較

GITDがONだと比較できないので、OFFにしてmysqlbinlogで覗いてみる。

MySQL8.0.20

# at 9418
#200814 18:50:04 server id 100  end_log_pos 9543 CRC32 0xccceb3e3   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1597398604/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `t2` (
  `id` int NOT NULL DEFAULT '0'
)
/*!*/;
# at 9543
#200814 18:50:04 server id 100  end_log_pos 9622 CRC32 0xf3e5f383   Anonymous_GTID  last_committed=38   sequence_number=39  rbr_only=yes    original_committed_timestamp=1597398604099299   immediate_commit_timestamp=1597398604099299 transaction_length=283
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1597398604099299 (2020-08-14 18:50:04.099299 JST)
# immediate_commit_timestamp=1597398604099299 (2020-08-14 18:50:04.099299 JST)
/*!80001 SET @@session.original_commit_timestamp=1597398604099299*//*!*/;
/*!80014 SET @@session.original_server_version=80020*//*!*/;
/*!80014 SET @@session.immediate_server_version=80020*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 9622
#200814 18:50:04 server id 100  end_log_pos 9697 CRC32 0x796b6528   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1597398604/*!*/;
BEGIN
/*!*/;
# at 9697
#200814 18:50:04 server id 100  end_log_pos 9745 CRC32 0x1fde1b11   Table_map: `test`.`t2` mapped to number 112
# at 9745
#200814 18:50:04 server id 100  end_log_pos 9795 CRC32 0xc4bf3987   Write_rows: table id 112 flags: STMT_END_F

BINLOG '
TF42XxNkAAAAMAAAABEmAAAAAHAAAAAAAAEABHRlc3QAAnQyAAEDAAABAQARG94f
TF42Xx5kAAAAMgAAAEMmAAAAAHAAAAAAAAEAAgAB/wABAAAAAAIAAAAAAwAAAIc5v8Q=
'/*!*/;
# at 9795
#200814 18:50:04 server id 100  end_log_pos 9826 CRC32 0xbc8b4b0a   Xid = 71
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

MySQL8.0.21

# at 9496
#200814 18:47:28 server id 100  end_log_pos 9639 CRC32 0xe24b1dc8   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1597398448/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `t2` (
  `id` int NOT NULL DEFAULT '0'
) START TRANSACTION
/*!*/;
# at 9639
#200814 18:47:28 server id 100  end_log_pos 9687 CRC32 0x4972ee4a   Table_map: `test`.`t2` mapped to number 112
# at 9687
#200814 18:47:28 server id 100  end_log_pos 9737 CRC32 0x56fa2558   Write_rows: table id 112 flags: STMT_END_F

BINLOG '
sF02XxNkAAAAMAAAANclAAAAAHAAAAAAAAEABHRlc3QAAnQyAAEDAAABAQBK7nJJ
sF02Xx5kAAAAMgAAAAkmAAAAAHAAAAAAAAEAAgAB/wABAAAAAAIAAAAAAwAAAFgl+lY=
'/*!*/;
# at 9737
#200814 18:47:28 server id 100  end_log_pos 9768 CRC32 0xa09930d6   Xid = 71
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

明らかに8.0.21のほうが短いね。

読み解いたわけじゃないけど、雰囲気は感じる。

所感

リリースノートではサラッと一行で書かれてるけど、結構な変更だと思いません?

GTIDが無効な場合でもクラッシュ時とかにレプリカにはテーブルはあるけどデータが無いとか無くなるわけだし、

GTIDが有効な場合でもエラーにならずにCREATE TABLE ... SELECTが使えるようになったわけです。

リリースノートをみんなで眺めることで、そういう知見が多くの人の目に止まったんです。

素敵な会ですね!

この話以外にも気になる機能について色々語られてました。

非公式の何かがちょろっと配布されたりして、意外と録画されていないところも楽しい勉強会w

みなさんも次回は参加してみては?

(今日はHunter×Hunter アニメ版のようなタイトルの付け方してみました)

クエリーキャッシュを有効にしたけりゃサーバー再起動

え?何当たり前のこと言ってるの?

そう思われた貴方、クエリキャッシュをちゃんと使うか使わないか最初に考えている偉い人ですね。

query_cache_typeをON(1)にするとクエリーキャッシュが有効になるんですが、これ、ドキュメントを読むと少なくともMySQL5.6以降は動的に変更(SET GLOBAL)できそうに見えます。

f:id:next4us-ti:20200719174422p:plain

5.5でもできそうに見える)

ところが、有効なのを無効にはできるんですが、無効な状態を有効にはできないんです。

試してみた

my.cnfでまず有効にした状態で起動してみます。

$ grep query_cache_type ./msb_5_7_30/my.sandbox.cnf
query_cache_type=1
$ ./msb_5_7_30/restart
stop /home/takiida/sandboxes/msb_5_7_30
. sandbox server started
$ mysql -uroot -pxxxxx -h127.0.0.1 -P5730
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

有効な状態から無効に変更することはSET GLOBAL句で可能です。

(root@127.0.0.1) [(none)]> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.01 sec)

(root@127.0.0.1) [(none)]> set global query_cache_type = 0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [(none)]> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set (0.01 sec)

(root@127.0.0.1) [(none)]> ^DBye

ところが

クエリキャッシュを無効にして再起動します。

$ grep query_cache_type ./msb_5_7_30/my.sandbox.cnf
query_cache_type=0
$ ./msb_5_7_30/restart
stop /home/takiida/sandboxes/msb_5_7_30
. sandbox server started
$ mysql -uroot -pxxxxx -h127.0.0.1 -P5730
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

無効な状態から有効にしようとすると怒られる!

(root@127.0.0.1) [(none)]> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> set global query_cache_type = 1;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

query_cache_typeを1にするんならサーバー再起動しろや!と。

クエリキャッシュ、最初から有効か、無効の環境しか使ったこと無いから知らんかった。

ドキュメントが微妙ですね…

ドキュメント的に優しくないので直るといいなぁ、と思うのでちょっとプッシュしておこうかと思います。 (まあ、MySQL8.0で廃止されたクエリーキャッシュをあとから有効にしたいとかそんなにニーズが無いから放置されてたのかもしれませんが…)