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 テーブルのストレージフォーマット
以下の３つがある
- 静的 (固定長) テーブル
- 動的テーブル
- 圧縮テーブル
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関連のユーティリティプログラム
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）で指定可能。
しかし、このパラメータ、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を試す
ダンプユーティリティのオプションについては公式ドキュメントにあるのでそれを参照するのだが、事例が少ない。
書き方で戸惑うかもしれないので試してみて実行できた書き方を以下並べてみる。
第１引数が出力先のディレクトリ名になります。（/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.
...
チェック作業が事前に入ってる。
パフォーマンスの調整したい人に
並列数２スレッド、ダンプ中のデータ読み取りスループットのスレッドあたりの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でそこにアップできない？
できるんです！くうぅ〜〜〜！！
準備作業
オブジェクトストレージ（正確にはバケット）を作りましょう。(作り方はドキュメントをご参照ください)
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..aaaaaaaaxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Enter a tenancy OCID: ocid1.tenancy.oc1..aaaaaaaavyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
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のオブジェクトストレージのバケットに出力するにはこの２つのオプションが必要。
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パワー次第で上げたら良いんじゃないかなと思いますが、僕はお試しなんでデフォルトのままでやりました。
インポートのほうのパラメータはあんまり調べませんでしたが、パフォーマンスの設定以外は基本エクスポートでやっちゃう人が多いんじゃないかな？と思って今回は手を抜きましたｗ
さいごに
今回はMySQL ShellとOCI両方ちょっとずつ学ぶことができました。
インポートは若干駆け足気味ですが、まあそんなに難しくないかと思って。
MySQL Shellのダンプが使えるのは8.0.21限定ですし、MDSもまだまだ検証段階って人も多いと思います。
そんな段階だからこそ、触っていざというときにどっちも使えるようになっておけば、きっと将来役に立つこと間違い無しです（？）
MySQL Database Serviceを触るまでのお話
はじめに
MDS（MySQL Database Service）が東京リージョンにも来ました！
(^O^) <祝！
というわけで、朝4時に起こされたこともあり、時間もできたんで久々にOCIを触ってみたんですが、作りたいものが簡単に作れて楽しかったです。
ただ、公式チュートリアルにMDS関連がまだ無いこと、MDS系だとlefredさんのブログとかOracleのセミナー資料しか手順でわかりやすい情報が無いのでまだまだ敷居が微妙に高い可能性もあり、初見の人がどうするの？っていうのをざっくり流れで書いてみました。
でも、細かいところは前述の公式のチュートや記事を参考にしてね。
やりたいこと
要するに、１台どっからでもインターネット上から繋げられるようなVM立てて、そのマシン経由でMySQLに繋げられるイメージ。
マイPC--->インターネット--->OCI領域[VM(Linux)--->MySQL]
作り方(超ざっくり)
１．アカウント作成
まずはなにはともあれアカウントを作る
通常、ここからOracle Cloud Freeでアカウントが作れる。
30日間有効な$300のクレジットがもらえて、MySQL DBシステムは有料なのでそこから支払われる。
と言っても、普段Auroraとか触ってる人からすると安すぎて笑うくらい。
多くの人は30日じゃ使い切れないんじゃないかな。
また、Oracle主催のセミナーとか参加すると更に+$200で合計$500のクレジットとかになることがある（それ用のURLを教えてもらえる）。
そっちからだと通常はクレカ登録があるのだが、無いので楽ちんでもある。
注意点
アカウント作成時の注意点は以下の２つ
- クラウド・アカウント名を忘れない
- ホーム・リージョンを日本東部（東京）にしよう
クラウド・アカウント名はログイン時に使う。
ログイン時に使うのはクラウド・アカウント名、メールアドレス、パスワードの３つ
ホーム・リージョンはせっかく日本でMDS使えるようになったー！ってなったのに、アメリカにするのもどうなん？ってことで。
まあ、アッシュバーンは空いてるなんて話もあるんで、それでも良いって人は止めませんがｗ
２．ログイン
チュートリアル読みながらログインしてみてください。
ちなみにクラウド・アカウント名はテナンシー名とも言われるので、どっかで出てきたら脳内変換してやってください。
クラウド・アカウントの領域を内部で区画わけするコンパートメントって概念もあるけど、個人で使う分には無視でいいかも。
整理整頓好きな人は使ってみるのもいいかも（自分は一回作って、ちょっと触って削除した）
３．VCNの作成
２同様チュートリアル読みながら作成してみてください。
チュートリアルにもありますが、VCNウィザード使ったほうが楽です。VCN名考えることくらいしかやることありませんｗ
パブリック・サブネットもプライベート・サブネットも作ってくれます。
プライベート・サブネットの更新でエラーが出ることがありますが、プロビジョニングの再試行で普通に作れます。
４．インスタンスの作成
チュートリアルを参考に作成してみてください。
ここに用意されている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 ~]$
５．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
６．プライベート・サブネットのイングレス・ルールにMySQLポート追加
パブリックIPがこれから作るMySQL DBシステムに接続できるよう、先にプライベート・サブネットのイングレス・ルールにMySQLポートの3306と33060を追加しておきます。
この作業はMySQL DBシステム作ったあとでも問題ありません。
ポート番号もMySQL DBシステムを別の番号で作るのであれば臨機応変に変えてください。
ネットワーキング＞仮想クラウド・ネットワーク＞作ったVCN＞プライベート・サブネット＞プライベート・サブネットのセキュリティ・リスト という感じでクリックしていくと、先程作ったVCNに紐づくプライベート・サブネットのイングレス・ルールの追加画面が表示されます。
ここでイングレス・ルールの追加ボタンを押して、パブリック・サブネットをソースCIDRに、宛先ポート範囲に3306,33060を入れて追加ボタンを押せばOKです。
(ちなみにプライベート・サブネットの他のルールは消しておくのが吉です。無駄な穴は消しておきましょう)
山崎さんのスライドでもこの辺の話は記載されてますので参考にしてください。
７．MySQL DBシステムの作成
MySQL＞DBシステムをクリックすると作成画面に入ります。
MySQL DBシステムの作成ボタンを押して作ります。
作り方や機能の詳細についてはここからダウンロードできる資料のほうが詳しいので僕は手抜きで説明しませんｗ
出来上がるまで結構かかります。10分〜15分くらいかかることも。コーヒーでも淹れてゆっくり飲みながら待つのが吉です。
８．DBへ接続
MySQLクライアントやMySQL Shellで接続してみてください。
mysql -u(DBユーザ名) -p -h(DBのエンドポイント名) mysqlsh (DBユーザ名)@(DBのエンドポイント名)
ちゃんと入れてるのに MySQL Error 2003 (HY000): Can't connect to MySQL server が出るよーって人はイングレス・ルールを見直してみてください （入れるのはプライベート・サブネットのほうですよ！）
９．ローカルからDBを見てみたい？
そういう人はlefredさんのブログを読むと言いです。
SSHトンネルやWorkbenchでつなぐ方法があります。
（一つ前の記事にMySQL Router使うなんてなかなかトリッキーなのもありますが・・・）
コスト
一番小さいサイズとはいえ、数時間放置してて、おっといけね！と見てみたが、
安ぅ〜〜〜！
コストの見積もり、ここでできるみたいだけど、一番小さいやつ、デフォルトだと4400円くらいなんで、$300とか、ましてや$500とか最初にも書いたけどやっぱり使い切れない気がする。
サイズ上げて、ディスクも増やしてガンガン遊んでみるかー！（家族が時間をクレナイケド・・・）
さいごに
１時間もあればこれらの作業ができます。
しかも一回VCNやインスタンスを設定してしまえば、更に時間は短縮されるので、今からVM環境作ったりするよりは圧倒的に早いです。
自分も今日作った環境でこれから色々と遊んでいこうと思います。
追記
日本語のチュートリアルが無いってだけで、ちゃんとドキュメントにはある。
（これを朝見つけておきたかったなー）
シンキノウ×デ×シルキノウ (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の詳しい説明については適当にググってもらうとかすればいいんですが、ざっくり書くと
- MySQLのtarファイルゲットしてきて
- dbdeployerインストールして
- dbdeployer unpack mysql-xxxxxxxx-8.0.xx-linux-x86_64-minimal.tar.xz みたいな感じで解凍して
- 以下のようなコマンドでレプリケーション環境を作れます。
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 ]
検証開始
そして、２つのバージョンで比較するんですが、やることはかんたん。
- テーブル（t1）を作って
- テーブルに適当にデータ入れて
- 一応テーブルにデータ入ったこと確認して
- そのテーブルからデータコピーをCREATE TABLE ...SELECT構文でやって
- レプリカ側のほうにあるか確認
というだけの作業。
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が使えるようになったわけです。
リリースノートをみんなで眺めることで、そういう知見が多くの人の目に止まったんです。
素敵な会ですね！
この話以外にも気になる機能について色々語られてました。
非公式の何かがちょろっと配布されたりして、意外と録画されていないところも楽しい勉強会ｗ
みなさんも次回は参加してみては？
（今日はHunter×Hunter アニメ版のようなタイトルの付け方してみました）
クエリーキャッシュを有効にしたけりゃサーバー再起動
え？何当たり前のこと言ってるの？
そう思われた貴方、クエリキャッシュをちゃんと使うか使わないか最初に考えている偉い人ですね。
query_cache_typeをON（1）にするとクエリーキャッシュが有効になるんですが、これ、ドキュメントを読むと少なくともMySQL5.6以降は動的に変更（SET GLOBAL）できそうに見えます。
（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で廃止されたクエリーキャッシュをあとから有効にしたいとかそんなにニーズが無いから放置されてたのかもしれませんが…）
MySQL5.xではデッドロックだけど8.0では死なないよ
はじめに
タイトル通り、MySQL5.x系だとデッドロックになるんだけど、 MySQL8.0だとロック機構が変わってデッドロックにならないよ という組み合わせのお話。
僕はこの話をどっかで見た記憶が無かった（忘れた？）ので 教えてもらったとき結構驚いたんだけど、GA（8.0.11）では 既にこれがあったんで、まあ古い話なんだと思うし、 何をイマサラなのかもしれないので、ご存じの方は笑ってやってください。
ちなみに、REPEATABLE READでのお話なので、 READ COMMITTEDの場合は5系でもデッドロックにはなりまへん。
検証
今回やるのはこういうこと
- 最初のトランザクション（以下Tx1）でトランザクション開始、共有（S）ロックで全レコードを参照。
- 別のトランザクション（以下Tx2）でトランザクション開始、INSERTによる排他（X）ロック
- Tx1でINSERTによる排他（X）ロック
結論を先にいうと、3による結果がMySQL5.xではデッドロック発生（Tx1のINSERT死亡）、 MySQL8.0ではすんなりTx1のINSERTが通る（Tx2は待ちのまま）。
以下は自分で試してみたい人・どういうふうになるのか知りたい人のため。
準備
MySQL5.7(今回は.29を使いました)とMySQL8.0.11で以下のようなテーブルを作成する。
データベース作成
とりあえずいつものtestスキーマｗ
(root@127.0.0.1) [(none)]> create database test; Query OK, 1 row affected (0.01 sec)
testにログイン
(root@127.0.0.1) [(none)]> use test; Database changed
テーブル作成
とりあえずのtテーブル（とりあえずすぎるネーミング）
(root@127.0.0.1) [test]> CREATE TABLE `t` (
-> `id` int NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.08 sec)
1行インサート
(root@127.0.0.1) [test]> insert t select null; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 (root@127.0.0.1) [test]> select * from t; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
ここまでで準備はOK
MySQL5.x系（デッドロックになるパターン）
Tx1で共有ロック
$ mysql -uroot -pxxxxxx -h127.0.0.1 -P5729 test mysql: [Warning] Using a password on the command line interface can be insecure. … Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@127.0.0.1) [test]> begin; select * from t where id >=1 lock in share mode; Query OK, 0 rows affected (0.00 sec) +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
Tx2で排他ロック
$ mysql -uroot -pxxxxxx -h127.0.0.1 -P5729 test mysql: [Warning] Using a password on the command line interface can be insecure. … Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@127.0.0.1) [test]> begin; insert into t select null; Query OK, 0 rows affected (0.00 sec)
beginは通ったが、insertは待たされている。
Tx1でINSERTするとデッドロック発生
違和感はあるものの、MySQLのRRでのロックから考えると納得はする。（MySQLにマヒしてる？）
(root@127.0.0.1) [test]> insert into t select null; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
とはいえ、別トランザクションでロックかけられて、自分のトランザクションで知らずにデッドロックとかやはり悲しいものがある。
それがMySQL8.0では改善していた！
MySQL8.0系（デッドロックにならないパターン）
Tx1で共有ロック
$ mysql -uroot -pxxxxx -h127.0.0.1 -P8011 test mysql: [Warning] Using a password on the command line interface can be insecure. … Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@127.0.0.1) [test]> begin; select * from t where id >=1 lock in share mode; Query OK, 0 rows affected (0.00 sec) +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
Tx2で排他ロック
$ mysql -uroot -pxxxxx -h127.0.0.1 -P8011 test mysql: [Warning] Using a password on the command line interface can be insecure. … Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@127.0.0.1) [test]> begin; insert into t select null; Query OK, 0 rows affected (0.00 sec)
beginは通ったが、insertは待たされている。 ここまで全く同じだが、次が違う
Tx1ではTx2の排他ロックを気にしない
(root@127.0.0.1) [test]> insert t select null; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 (root@127.0.0.1) [test]> select * from t; +----+ | id | +----+ | 1 | | 3 | +----+ 2 rows in set (0.00 sec)
でも、ちゃんと「id=2」はTx2が抑えているのを考慮している！
Tx2は待ち疲れでタイムアウトｗ
でも、Tx1がいつまでもコミットしてくれないと、Tx2はタイムアウトしちゃいますｗ
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MySQL8.0のロック、だいぶ賢い！
逆に変わりすぎてて、こことかに明記されててもおかしくないと思ったんだが…。
追記（変わった理由）
この記事書いたとき気づいてなかったんだけど、innodb_autoinc_lock_modeがデフォルト値1から2に変わったことが理由ですね。
パラメータの差異確認して、かつtom__boさんのWeeklyのコメントも見ておそらくこれやなと思ってたけど、検証するのサボってましたｗ
innodb_autoinc_lock_modeについては第49回のMySQL道普請を参考にすると良いです。
MySQL5.7の段階でbinlog-formatがROWに変わってるのでinnodb_autoinc_lock_modeのデフォルトも変わってても良かった気がする。
