MySQL で年月(yyyy/mm)のデータ型と PERIOD_ADD/DIFF

いわゆる請求データみたいなやつで請求年月のような yyyy/mm の値を表すために DATE 側を使うか整数型で yyyymm みたいにするか。

DATE 型で日付を 1 固定で持っていたとして、例えば請求年月が 2018/01 という条件で検索するとき 請求年月 = 2018/01/01 だと意味的におかしい気がする、請求年月 between 2018/01/01 and 2018/01/31 でなければならない。

そういう余計なことを考えなくていいようにするために yyyymm 形式の整数で持っておいて 請求年月 = 201801 とするのはアリだと思います。

ただ、例えば期間のデータで日割りが必要がないので yyyy/mm ~ yyyy/mm のように年月だけで持たせておくと、日付計算することも考えると整数型だと都合が悪いです。DATE型で持っていたほうが DATE_ADD とかがそのまま使えて便利です。

ただ、その場合 2018/01/01 ~ 2018/03/01 などとなるのは意味的におかしいので、2018/01/01 ~ 2018/03/31 とするべきだと感じます。

ただ、同じ、本当は業務的には年月なんだけど技術的な都合でDATE型にしている値でも、期間の FROM なのか TO なのかによって DB への格納方法が異なるのには違和感があります。

ただ、そもそも本当は業務的には「期間」というデータであってそれを技術的な都合でFROMとTOに分割しているのだと考えるとどっちてもいいんじゃないという気もする(どっちにしても業務的な意味とDBMSでの格納方法に齟齬がある)。


なお、2018/01/01 ~ 2018/03/31 のような形式で格納するようにすると、下記のような不安しか感じないコードを書いてしまうかもしれない。

select date_add(cast('2018/03/31' as date), interval 1 month)

なお、予想に反して?、期待したとおりの 2018/04/30 を返します。

なお、PHP の似たようなコードは予想通り?、期待に反して下記のような結果になります。

var_dump((new DateTime('2018/03/31'))->modify('+1 month')->format('Y/m/d'));
// string(10) "2018/05/01"
var_dump((new DateTime('2018/03/31'))->add(new DateInterval('P1M'))->format('Y/m/d'));
// string(10) "2018/05/01"

と思ってたらこんなのがあった。

SELECT PERIOD_ADD(200812, 1);
/* 200901 */

SELECT PERIOD_DIFF(200903, 200811);
/* 4 */

わざわざこんな関数が用意されているぐらいなので、MySQL で年月を表すデータ型は整数で良い?・・のかもしれない。

Doctrine や Eloquent や CakePHP はいかにして差分更新を実現しているか

Doctrine や Eloquent や CakePHP などの ORM でDBからフェッチしたエンティティの一部の属性だけ変更して保存したとき、テーブルの行全体が更新されるわけではなく、変更した一部の属性だけが更新されますが、それがどう実装されているか気になったので調べたメモ。

Eloquent

Laravel の Eloquent はエンティティ(モデル)が POPO ではないので、エンティティ自身にいろいろ情報が詰め込まれています。

モデルの HasAttributes トレイトで、

フェッチしたときの元の値を保持していて、

元の値との比較で更新すべき属性のリストを得ます。

CakePHP

Laravel の Eloquent と同じく、エンティティが POPO ではないのでエンティティ自身にいろいろ情報が詰め込まれています。

(実際に試してはいないんですけど)、EntityTrait トレイトで DB からフェッチしてきてから変更や追加されたプロパティの一覧を持っていて、

更新時に Entity から変化のあったプロパティだけ取り出して SQL を作ります。

Doctrine

Symfony などで使われる Doctrine はエンティティが POPO なので Eloquent や CakePHP のようにエンティティにいろいろ詰め込むことは出来ないはずですが?

EntityManager の中の UnitOfWork で、

$originalEntityData という、DBからフェッチした元の Entity の値を保持していて、

保存時に、フェッチしたときの元の値と Entity の値を比較して更新するセットを導出しています。

ので、 Entity 自体は POPO のままで、比較による差分での部分更新を実現していました。なかなか面白いですね、POPO なエンティティを扱う ORM は同じような実装になっているものなのでしょうか。

zend-db

zend-db の TableGateway や RowGateway は見た感じ差分更新のようなことは行われていなさそうです。RowGateway をフェッチして一部の属性を変更して save すると変更していない属性も含めて全部更新されそうです(試していない)。

さいごに

差分更新が出来ないと特定の状況ですごく不自然な動きになるように思います。

例えば、ユーザーというエンティティがあって、ユーザーの一部の属性だけ(氏名だけ、とか、メールアドレスだけ、とか)編集するフォームがあって、次のように処理していたとします。

  • Repository からユーザーのエンティティを取得
  • リクエストから値を取り出してエンティティの属性に反映
  • Repository でエンティティを DB に保存

ユーザーの氏名だけ変更数フォームと、メールアドレスだけ変更するフォームがあって、[A] は氏名のみを編集するリクエスト、[B] はメールアドレスを編集するリクエストです。この2つのリクエストが次のような順番で処理されると・・

  • [A] Repository からユーザーのエンティティを取得
  • [A] リクエストからメールアドレスを取り出してエンティティの属性に反映
  • [B] Repository からユーザーのエンティティを取得
  • [B] リクエストから氏名を取り出してエンティティの属性に反映
  • [A] Repository でエンティティを DB に保存
  • [B] Repository でエンティティを DB に保存

最後の段で差分更新が行われていないと [A] によるメールアドレスの変更は [B] による氏名の変更によって上書きされるので残りません。ですが [B] としては氏名だけ変更するフォームで操作しただけなので [A] に問い詰められても氏名しか変更してないので知らんがなです。

差分更新ができれば [A] によるメールアドレスの変更も [B] による氏名の変更も両方残ります。

DBからフェッチした時点で FOR UPDATE なロックしとけば大丈夫ですけど、これだけのために FOR UPDATE は過剰? でもないか??

あるいは、フォームにあわせた特定の属性だけ更新するメソッドをリポジトリに設けたり、

$this->userRepo->updateEmail($user->id, $user->email);

うーん、ログインユーザーの権限によって更新できる属性が異なる、などという仕様だったりすると「特定の属性だけ編集するフォーム」の「特定」が可変になるので破綻します。

では、更新する属性をリポジトリのメソッドで指定してみたり、

$this->userRepo->save($user, ['email']);

うーん、ありかな?

MRP(Meal RePlacement:食事代替品)を食してみたメモ

MRP(Meal RePlacement:食事代替品)をいくつか食してみたメモ。

自宅での食事ではこの類のものは食しておらず、職場で昼飯や晩飯を食べるときだけ食しています。また、昼飯は同僚何人かと弁当を注文することがあり、それが注文できるときには食していません(3人以上で注文しないといけないので希望者が少ないと注文できない)。晩飯は残業するときですが残業したくないです。

なので、この類のものを食す頻度はそれほど多くはありません。週に2~3回程度です。

コスパの比較

ざっくりとしたコストの比較。栄養とかはよくわからないので、カロリー量と1食あたりの価格のみ比較しています。価格はさっき見たときの価格、まとめ買いで安くなったりはするけれどもとりあえず最小購入単位、海外系は iHerb で買ってます。

カロリー量とは無関係に小分けされている1パック(COMP DRINK は半分)を1食としているので、1食あたりと1kcalあたりの価格に相関はありません。1食のカロリー量が少なすぎるような気がするのですが、食事のすべてを MRP に置き換えているわけではないので問題ないと思います。

品名 価格 カロリー 価格/食 価格/kcak
COMP POWDER 5,000 円 400 kcal x 12 417 円/食 1.04 円/kcak
COMP GUMI 5,000 円 400 kcal x 10 500 円/食 1.25 円/kcak
COMP DRINK 7,800 円 1000 kcal x 6 650 円/食 1.30 円/kcak
BASE PASTA quick 3,540 円 364 kcal x 6 590 円/食 1.62 円/kcak
Myoplex (EAS) 5,333 円 300 kcak x 20 265 円/食 0.88 円/kcak
RAW MEAL (Garden of Life) 2,712 円 240 kcak x 7 387 円/食 1.61 円/kcak

雑感

RAW MEAL(これが正式名なのかどうかよくわからない)はだいぶ昔に買ったのですが、Myoplex の方が飲みやすくてコスパも良かったので、1回買ったきりです。これだけはパックで小分けになっていないので備え付けのスコップ2杯で1食にしています。

Myoplex は同じ粉系でも RAW MEAL や COMP POWDER とかと比べて甘くて飲みやすいです。RAW MEAL はどうだったかもう忘れましたが COMP POWDER はなにかしら味をつけないと辛かったです。Myoplex はそのままでも OK でした。

COMP POWDER は素では飲めたものではなかったのでスムージーで味をつけて飲んでいました。ただいろいろなレビューを見ていると素でも大丈夫な人も居るようなので好みによるようです。

BASE PASTA quick は手軽だと聞いて試してみたのですが、飯!と思ってから食べるまでに電子レンジでチンする時間が必要なので、あまりお手軽感はありませんでした。レンジでチンするのと容器を振ったり洗ったりするののどちらが手間かは人によりけりだと思います。

COMP GUMI も手軽といえば手軽でしたけど、これを1食分を一気に食べるのはかなり辛いです。昼飯や晩飯の代替には向かなそうです。間食の感覚で少しずつ食べるものなのでしょうけど。

手軽さでは COMP DRINK が最強でした。開けて注いで飲むだけだし、2食目はパックから直でグビグビ飲めます。味も COMP POWDER を水に溶かしたものと比べれば飲みやすく、そのままグビグビいけます(最近 COMP POWDER を食していないので今はどうなのかわかりませんが)。

COMP DRINK のデメリットは、コスパがそんなに良くないのと、1パックで2食分なのに開封後はお早めにお召し上がる必要があるとこです。後述の通りこの類のものを食すのは不定期なので、計画的に2食食べる、というのがしにくいです。金曜日の夜とかに食すと翌週まで食す機会無いですし。500ml 版があればいいんですけどね。

まとめ

COMP DRINK が一番手軽で飲みやすく、メイン MRP (食事代替品)になってます。

ただ、前述の通り開封後の取扱が要注意なので、2回食す計画が練られないとき用に Myoplex も買っています。

COMP GUMI はなんか用途が違う気がします。BASE PASTA quick は自分にはあいませんでした。粉系は Myoplex が一番飲みやすくて良いです。

監視ツールはいかにしてカウンター値のチェックを行なうのか

1年ぐらい前に諸事情により調べたメモ。

監視ツールでリソース情報とかのメトリクスに対して、○○を超えたら、みたいな閾値のチェックを設ける場合、元の値がディスク使用率とかロードアベレージのようなそのままの値が取れるものなら良いのですが(いわゆる GAUGE 値)、CPU 使用率や Traffic などは普通はカウンター値として取れるので(いわゆる COUNTER 値)、前回値からの差分に対して閾値のチェックをかける必要があります。

Nagios

そういう機能は無い。というかプラグイン自体がアラートを判断するので Nagios 的にはメトリクスに対する閾値チェックという概念がない。そういうのはプラグイン側で実装する。

プラグインで COUNTER 値をチェックするのはちょっと工夫が必要。

例えば、vmstat を 1 秒間実行してその結果をチェックするとか、/proc/stat をどこかに保存しておいて前回値との差分値をチェックする、とかです。

Sensu

チェックプラグインは Nagios と同じ仕様なので、基本は Nagios と同じ。

がしかし、Sensu で取得したメトリクスを時系列データベースに保存し、チェックプラグインで時系列データベースに問い合わせてチェックすることができます(Nagios でも eventhandler でパフォーマンスデータを時系列データベースに保存しておけば同じことはできると思うけど)。

Munin

少し前のバージョンでは Perl の Storable モジュール?でホストごとに記録されているステートファイルに、前回値と今回値が記録されています。

https://github.com/munin-monitoring/munin/blob/2.0.25/master/lib/Munin/Master/LimitsOld.pm#L319-L321

my $state_file = sprintf ('%s/state-%s-%s.storable', $config->{dbdir}, $hash->{group}, $host);
DEBUG "[DEBUG] state_file: $state_file";
my $state = munin_read_storable($state_file) || {};

https://github.com/munin-monitoring/munin/blob/2.0.25/master/lib/Munin/Master/LimitsOld.pm#L350-L351

my $rrd_filename = munin_get_rrd_filename($field);
my ($current_updated_timestamp, $current_updated_value) = @{ $state->{value}{"$rrd_filename:42"}{current} || [ ] };
my ($previous_updated_timestamp, $previous_updated_value) = @{ $state->{value}{"$rrd_filename:42"}{previous} || [ ] };

このステートファイルは RRA ファイルを更新するときに一緒に更新されます。

https://github.com/munin-monitoring/munin/blob/2.0.25/master/lib/Munin/Master/UpdateWorker.pm#L52

my $state_file = sprintf ('%s/state-%s.storable', $config->{dbdir}, $path); 
DEBUG "[DEBUG] Reading state for $path in $state_file";
$self->{state} = munin_read_storable($state_file) || {};

:


my $last_updated_timestamp = $self->_update_rrd_files(\%service_config, \%service_data);

:

DEBUG "[DEBUG] Writing state for $path in $state_file";
munin_write_storable($state_file, $self->{state});

最新版だと SQLite に変わっていました。

https://github.com/munin-monitoring/munin/commit/ba5306d148f04269b3b6bcb61c43e2f1fb34375e

Cacti

Cacti 単体にそういう機能はなくて thold プラグインを使う必要があります。ので thold-v0.5.0 を見てみました。

DB の thold_data テーブルの lastread,lasttime,oldvalue 辺りが前回値を持っています。

select lastread, lasttime, oldvalue from thold_data \G
/*
lastread: 5.4033
lasttime: 2017-04-26 12:55:13
oldvalue: 3309610
*/

oldvalue が生の値、lastread が計算によって求められたチェック対象の値です。

この値は Cacti の poller_output というフックポイントの処理で更新されます。たぶん RRA ファイルを更新するときに呼ばれるフックポイントです。

# setup.php
api_plugin_register_hook('thold', 'poller_output', 'thold_poller_output', 'includes/polling.php');

# polling.php
function thold_poller_output ($rrd_update_array) {
    // ..snip..
    db_execute("UPDATE thold_data SET tcheck=1, lastread='$currentval',
        lasttime='" . date("Y-m-d H:i:s", $currenttime) . "',
        oldvalue='" . $item[$t_item['name']] . "'
        WHERE rra_id = " . $t_item['rra_id'] . "
        AND data_id = " . $t_item['data_id']);
}

それにしてもアレなコードだわ・・・

Prometheus

コードを見るまでもなく、アラートに PromQL(Prometheus の時系列データベースに問い合わせる DSL)が使える時点で、都度時系列データベースに問い合わせているのは確定的に明らか。

また、データの取得の間隔とアラートのチェックの間隔が別々に設定できるので、メトリクスの取得とアラートの取得は独立して動いている、たぶん。

Zabbix

わからん。

まとめ

Munin や Cacti は RRA ファイルを読んでいるのかと思ったけどそんなことはなかった。パフォーマンス的にそれだと辛いのだと思う。

Graphite や InfluxDB のようなそれっぽい時系列データベースを使っていれば、アラートのチェックの都度、時系列データベースに問い合わせるのでも良いのかもしれない。

ただ、データの取得と、アラートのチェックと、可視化のための時系列データの保存、はなるべく疎な方がきれいな気がするので、Cacti や Munin 風の方法が良い気もする。

MySQL Group Replication 素振り

公式のドキュメントを読みながら素振りしました。

グループレプリケーションでは、グループのメンバシップ管理、ノードの障害検出、追加ノードの同期、などが自動で行われます。一方でアプリケーションからの接続先をルーティングするような機能はないため、アプリケーションがクラスタのどのノードに接続するかの制御には別のなにかが必要です(MySQL Router とか HAProxy とか)。

Configuration

グループレプリケーションのための my.cnf の抜粋です。

# GTID レプリケーションに関する設定
# もちろん server_id はノードごとに固有の値が必要
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

# 8.0.3 以前なら必要なオプション(それ以降ならデフォルト)
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

# ここからグループレプリケーションに関する設定

# グループレプリケーションするなら XXHASH64 でなければならない
# 8.0.2 以降ならデフォルト
transaction_write_set_extraction=XXHASH64

# グループレプリケーションのプラグインをロード
plugin_load=group_replication.so

# 適当な方法で生成した UUID を指定する
# グループの名前になって GTID もこの UUIDから作られる
group_replication_group_name="470b4daf-6c34-415f-97ad-68cf76ef24e7"

# ノードの起動時にグループレプリケーションを自動的に開始しない
group_replication_start_on_boot=off

# グループレプリケーションのための自ノードのアドレスとポート番号
group_replication_local_address= "192.168.88.11:6606"

# グループのメンバーの一覧
group_replication_group_seeds= "192.168.88.11:6606,192.168.88.12:6606,192.168.88.13:6606"

# グループレプリケーションの開始時に新たなグループを作らない
group_replication_bootstrap_group=off

# MySQL 8.0 のユーザーはデフォルトで caching_sha2_password 認証なので準備が面倒
# だけどこれを設定しておけば面倒がなくなる、ただし中間者攻撃に脆弱
group_replication_recovery_get_public_key=ON

plugin_load=group_replication.so を指定しているのでサーバ起動時にグループレプリケーションのプラグインがロードされます。

後から INSTALL PLUGIN する場合は group_replication_* パラメータは loose-group_replication_* のように loose- をつけておかないと存在しないパラメータのエラーで起動がコケます。

MySQL 8.0 で作成されたユーザーはデフォルトで caching_sha2_password という認証方法を使うため、グループレプリケーションではいろいろ準備が必要なようなのですが、group_replication_recovery_get_public_key=ON を指定すればその準備が省略できます。ただし中間者攻撃に対して脆弱になります。

default_authentication_plugin=mysql_native_password とかでデフォルトの認証方法を変えておくか、ユーザーを作成するときに mysql_native_password を明示しておいても良いのかもしれません、通信経路が安全であることが間違いないなら。

docker-compose

docker-compose.yml

version: '3.4'

services:
  sv01:
    image: mysql:8
    container_name: sv01
    hostname: sv01
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      MYSQL_DATABASE: test
      MYSQL_PS1: "sv01> "
    networks:
      mysql:
        ipv4_address: 192.168.88.11
    command:
      - --server_id=1
      - --gtid_mode=ON
      - --enforce_gtid_consistency=ON
      - --binlog_checksum=NONE
      - --relay_log=relay-bin
      - --transaction_write_set_extraction=XXHASH64
      - --plugin-load=group_replication.so
      - --group_replication_group_name=470b4daf-6c34-415f-97ad-68cf76ef24e7
      - --group_replication_start_on_boot=off
      - --group_replication_local_address=192.168.88.11:6606
      - --group_replication_group_seeds=192.168.88.11:6606,192.168.88.12:6606,192.168.88.13:6606
      - --group_replication_bootstrap_group=off
      - --group_replication_recovery_get_public_key=ON

  sv02:
    image: mysql:8
    container_name: sv02
    hostname: sv02
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      MYSQL_DATABASE: test
      MYSQL_PS1: "sv02> "
    networks:
      mysql:
        ipv4_address: 192.168.88.12
    command:
      - --server_id=2
      - --gtid_mode=ON
      - --enforce_gtid_consistency=ON
      - --binlog_checksum=NONE
      - --relay_log=relay-bin
      - --transaction_write_set_extraction=XXHASH64
      - --plugin-load=group_replication.so
      - --group_replication_group_name=470b4daf-6c34-415f-97ad-68cf76ef24e7
      - --group_replication_start_on_boot=off
      - --group_replication_local_address=192.168.88.12:6606
      - --group_replication_group_seeds=192.168.88.11:6606,192.168.88.12:6606,192.168.88.13:6606
      - --group_replication_bootstrap_group=off
      - --group_replication_recovery_get_public_key=ON

  sv03:
    image: mysql:8
    container_name: sv03
    hostname: sv03
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      MYSQL_DATABASE: test
      MYSQL_PS1: "sv03> "
    networks:
      mysql:
        ipv4_address: 192.168.88.13
    command:
      - --server_id=3
      - --gtid_mode=ON
      - --enforce_gtid_consistency=ON
      - --binlog_checksum=NONE
      - --relay_log=relay-bin
      - --transaction_write_set_extraction=XXHASH64
      - --plugin-load=group_replication.so
      - --group_replication_group_name=470b4daf-6c34-415f-97ad-68cf76ef24e7
      - --group_replication_start_on_boot=off
      - --group_replication_local_address=192.168.88.13:6606
      - --group_replication_group_seeds=192.168.88.11:6606,192.168.88.12:6606,192.168.88.13:6606
      - --group_replication_bootstrap_group=off
      - --group_replication_recovery_get_public_key=ON

  sv04:
    image: mysql:8
    container_name: sv04
    hostname: sv04
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      MYSQL_DATABASE: test
      MYSQL_PS1: "sv04> "
    networks:
      mysql:
        ipv4_address: 192.168.88.14
    command:
      - --server_id=4
      - --gtid_mode=ON
      - --enforce_gtid_consistency=ON
      - --binlog_checksum=NONE
      - --relay_log=relay-bin
      - --transaction_write_set_extraction=XXHASH64
      - --plugin-load=group_replication.so
      - --group_replication_group_name=470b4daf-6c34-415f-97ad-68cf76ef24e7
      - --group_replication_start_on_boot=off
      - --group_replication_local_address=192.168.88.14:6606
      - --group_replication_group_seeds=192.168.88.11:6606,192.168.88.12:6606,192.168.88.13:6606
      - --group_replication_bootstrap_group=off
      - --group_replication_recovery_get_public_key=ON

networks:
  mysql:
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 192.168.88.0/24

docker-compose で開始してコンテナに入ります。

docker-compose up -d sv01 sv02 sv03

docker-compose exec sv01 mysql
docker-compose exec sv02 mysql
docker-compose exec sv03 mysql

ユーザーの作成とバイナリログのリセット

プラグインがロードされていることを確認します。

/* [sv01/sv02/sv03] */
show plugins;

レプリケーションユーザーを作ります。

/* [sv01/sv02/sv03] */
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

レプリケーションチャンネル group_replication_recovery に↑で作成したユーザーの資格情報を使うように設定します。

/* [sv01/sv02/sv03] */
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

docker-compose up でデータベースが初期化された時点でバイナリログにいろいろ書き込まれているので、そのままだとレプリケーションを開始したときに競合するので、バイナリログをリセットしておきます。

/* [sv01/sv02/sv03] */
RESET MASTER;

なお、レプリケーションユーザーの作成などの、すべてのノードで個別に実行するためレプリケーションされたくないクエリは set sql_log_bin = 0|1 とかでバイナリログの ON/OFF を制御しつつクエリを実行するのが普通のようです。ただ、初回のセットアップでは「すべてのノードでいろいろ準備→RESET MASTER→レプリケーション開始」の方がわかりみがあるきがするので、いつもそうしてます。

グループレプリケーションの開始

グループを作成してグループレプリケーションを開始します。この作業は最初の1台だけで行います。

/* [sv01] */
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

グループのメンバーを確認します。この時点では sv01 の1台しかありません。

/* [sv01] */
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+-------------+
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+-------------+-------------+--------------+-------------+
| sv01        |        3306 | ONLINE       | PRIMARY     |
+-------------+-------------+--------------+-------------+

残りのノードでも開始します。このとき group_replication_bootstrap_group=ON を指定しません。指定すると同じ名前の別のグループが作成されてしまいます。

/* [sv02/sv03] */
START GROUP_REPLICATION;

グループのメンバーを確認します。うまくグループに参加できれいればどのノードで実行しても同じ結果が返ります。最初のノードである sv01 がプライマリになっています。

/* [sv01/sv02/sv03] */
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+-------------+
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+-------------+-------------+--------------+-------------+
| sv01        |        3306 | ONLINE       | PRIMARY     |
| sv03        |        3306 | ONLINE       | SECONDARY   |
| sv02        |        3306 | ONLINE       | SECONDARY   |
+-------------+-------------+--------------+-------------+

sv01 で適当にデータを入れてみます。

/* [sv01] */
USE test;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
INSERT INTO t1 VALUES (1, 'Luis');

他のノードにレプリケーションされています。

/* [sv02/sv03] */
USE test;
select * from t1;

プライマリ以外のノードは読み込み専用になってるので更新のトランザクションはエラーになります。

/* [sv02/sv03] */
INSERT INTO t1 VALUES (2, 'xx');
/* ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement */

グループにインスタンスを追加

新しくノードを追加します。

docker-compose up -d sv04
docker-compose logs -f sv04
docker-compose exec sv04 mysql

プラグインがロードされていることを確認します。

/* [sv04] */
show plugins;

レプリケーションユーザーを作って、バイナリログをリセットして、グループレプリケーションを開始します。

/* [sv04] */

/* ユーザー作成 */
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

/* レプリケーションチャンネルに資格情報を設定 */
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

/* バイナリログをリセット */
RESET MASTER;

/* グループを開始 */
START GROUP_REPLICATION;

新しいノードが追加されるとランダムに選ばれたグループのメンバー(ドナー)からデータの同期が行われ(リカバリプロセス)、同期が完了するとオンラインのメンバーとして使用できるようになります。

/* [sv04] */

/* メンバーの一覧表示 */
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;

/* データの確認 */
USE test;
select * from t1;

運用中のグループにインスタンスを追加

リカバリプロセスは MySQL の通常のレプリケーションを利用して行われます。なので必要なバイナリログがグループのすべてのメンバーで既にパージされているとリカバリプロセスは失敗します。

グループのすべてのメンバからバイナリログをパージします。

/* [sv01/sv02/sv03] */
flush logs;
purge binary logs before now();
show binlog events;

新しいインスタンスを開始して、

docker-compose rm -fsv sv04
docker-compose up -d sv04
docker-compose logs -f sv04
docker-compose exec sv04 mysql
/* [sv04] */
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
RESET MASTER;
START GROUP_REPLICATION;

ログに次のようなものがたくさん出力されます。

[ERROR] [MY-010557] [Repl] Error reading packet from server for channel 'group_replication_recovery': Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. To find the missing transactions, see the master's error log or the manual for GTID_SUBTRACT. (server_errno=1236)
[ERROR] [MY-013114] [Repl] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. To find the missing transactions, see the master's error log or the manual for GTID_SUBTRACT.', Error_code: MY-013114

メンバーのリストを見てみると、追加はされていますが MEMBER_STATERECOVERING のままです。

/* [sv04] */
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+-------------+
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+-------------+-------------+--------------+-------------+
| sv04        |        3306 | RECOVERING   | SECONDARY   |
| sv01        |        3306 | ONLINE       | PRIMARY     |
| sv03        |        3306 | ONLINE       | SECONDARY   |
| sv02        |        3306 | ONLINE       | SECONDARY   |
+-------------+-------------+--------------+-------------+

しばらくと MEMBER_STATEERROR になります。

/* [sv04] */
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+-------------+
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+-------------+-------------+--------------+-------------+
| sv04        |        3306 | ERROR        |             |
+-------------+-------------+--------------+-------------+

他のメンバーからは見えなくなります。

/* [sv01] */
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+-------------+
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+-------------+-------------+--------------+-------------+
| sv01        |        3306 | ONLINE       | PRIMARY     |
| sv03        |        3306 | ONLINE       | SECONDARY   |
| sv02        |        3306 | ONLINE       | SECONDARY   |
+-------------+-------------+--------------+-------------+

グループレプリケーションを開始した直後の、すべてのバイナリログがまだ残っている状態でインスタンスを追加するようなときは別として、運用中のグループにノードを追加するときは追加前に mysqldump 的なことが必要です。

公式のドキュメントだと MySQL Enterprise Backup を使用する例が記載されていましたが、mysqldump でも良いと思うし、データディレクトリの rsync(auto.cnf に注意)でも大丈夫だと思います。

試しに mysqldump してみます。

# 新しいインスタンスを開始
docker-compose rm -fsv sv04
docker-compose up -d sv04
docker-compose logs -f sv04

# レプリケーションユーザーの作成やバイナリログのリセット
docker-compose exec -T sv04 mysql <<'SQL'
  CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
  GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
  CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
  RESET MASTER;
SQL

# 別のノードからダンプして新しいノードにインポート
docker-compose exec -T sv03 mysqldump \
    --all-databases --single-transaction --triggers --routines --events |
  docker-compose exec -T sv04 mysql

# コンテナに入る
docker-compose exec sv04 mysql

show master statusExecuted_Gtid_Set が他のノードと同じになっていれば大丈夫です。

/* [sv04] */
show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 |      151 |              |                  | 470b4daf-6c34-415f-97ad-68cf76ef24e7:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+

グループレプリケーションを開始できます。

/* [sv04] */
START GROUP_REPLICATION;
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+-------------+
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+-------------+-------------+--------------+-------------+
| sv04        |        3306 | ONLINE       | SECONDARY   |
| sv01        |        3306 | ONLINE       | PRIMARY     |
| sv03        |        3306 | ONLINE       | SECONDARY   |
| sv02        |        3306 | ONLINE       | SECONDARY   |
+-------------+-------------+--------------+-------------+

IPアドレスのホワイトリスト

MySQL の設定で group_replication_group_seeds にグループのインスタンスのアドレスを羅列していますが、別にグループのすべてのインスタンスが羅列されている必要はありません。

この設定は、インスタンスでグループレプリケーションを開始したときに、どこからグループの情報を取得するかの設定です。なのでグループ内の生きているメンバーのいずれかが指定されていれば大丈夫です。なので、グループへのインスタンスの追加は group_replication_group_seeds をいじらなくてもできます。

一方、グループに参加できるアドレスのホワイトリストの設定もあって(group_replication_ip_whitelist)、そこに含まれないアドレスのインスタンスはグループのインスタンスとの通信が拒否されます。ただし、デフォルトでサーバの I/F のサブネットが追加されるので、複数のサブネットにインスタンスが分散されるとかではない限りはデフォルトのままで良さそうです。

ノードの故障

おもむろにプライマリノードを強制終了します。

docker-compose kill -s KILL sv01

他のノード(sv02 とか)で次のようにログが表示されます。

[Warning] [MY-011493] [Repl] Plugin group_replication reported: 'Member with address sv01:3306 has become unreachable.'
[Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: sv01:3306'

メンバの一覧を観てみると、sv01 がなくなって sv04 がプライマリになってました。

/* [sv02] */
SELECT * FROM performance_schema.replication_group_members;
/*------------+-------------+--------------+-------------+
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+-------------+-------------+--------------+-------------+
| sv04        |        3306 | ONLINE       | PRIMARY     |
| sv03        |        3306 | ONLINE       | SECONDARY   |
| sv02        |        3306 | ONLINE       | SECONDARY   |
+-------------+-------------+--------------+------------*/

Monitoring Group Replication

グループのメンバやレプリケーションの詳細は下記のようにパフォーマンススキーマから得られます。

/* グループのメンバに関する情報 */
select * from performance_schema.replication_group_member_stats \G
select * from performance_schema.replication_group_members;

/* レプリケーションチャンネルに関する情報 */
select * from performance_schema.replication_connection_status \G
select * from performance_schema.replication_applier_status;

レプリケーションチャンネルには次の用途の2つが作成されています。

  • group_replication_recovery
    • 分散リカバリフェーズ(リカバリプロセス)で使用される
    • ノードを追加したときや故障ノードが復帰したときの同期用
  • group_replication_applier
    • グループで実行されたトランザクションを適用するために使用される
    • 要するに平時のトランザクションのレプリケーション

グループのメンバに関するパフォーマンステーブルのビューについて。

  • replication_group_member_stats
    • メンバーごとの待機中のトランザクション数とか処理済トランザクション数とかの統計
    • 特定のメンバーが遅れているとかでキューに溜まってるのを監視したりするのに使える
  • replication_group_members
    • メンバーのステータス

replication_group_members で表示されるメンバーのステータス。

  • ONLINE
    • 同期されている
  • RECOVERING
    • リカバリプロセス中
  • OFFLINE
    • メンバーはグループに属していない
    • プラグインがロード済でグループレプリケーションか開始していないとき
  • ERROR
    • リカバリプロセスやトランザクションの適用中にエラー
  • UNREACHABLE
    • メンバーがネットワーク的に到達不能

サービス監視とかするなら ONLINE 以外はなにかしら異常とみなして良さそうです。

Deploying in Multi-Primary or Single-Primary Mode

グループレプリケーションにはシングルプライマリモードとマルチプライマリモードがあります。デフォルトはシングルプライマリです。

グループのメンバーの中でシングルとマルチは混在できません。切り替えるためにはグループレプリケーション自体を再構成する必要があります。

マルチプライマリモードにすると、次のようなステートメントのチェックが行われるようになります。

  • トランザクションが SERIALIZABLE 分離レベルで実行されるとコミットが失敗する
  • CASCADE の外部キーを持つテーブルに対してトランザクションを実行するとコミットに失敗する

これらのチェックは group_replication_enforce_update_everywhere_checksFALSE にすると無効にできます。もちろん無効にするとノード間でデータ不整合が起こる可能性が生じると思います。

Single-Primary Mode

デフォルトのシングルプライマリモードでは、グループのただ一つのメンバーだけがプライマリで、それ以外は super-read-only=ON が設定されて読み取り専用になります。

プライマリがコケたときに新しいプライマリの選出は group_replication_member_weight で重みつけできます。ただしグループ内で MySQL のバージョンに差異があると下位メジャーバージョンのものから優先的に選択されます。

プライマリがコケた後、クライアントアプリケーションを再ルーティングする前に、新しいプライマリがレプリケーションに関係するリレーログを適応しきるのを待ったほうが良いです。これは普通のレプリケーションでも同じで、リレーログが適応し切る前にクライアントを新しいプライマリにルーティングすると、クライアントからの更新とリレーログによる更新が競合する可能性があります。

プライマリとなっているメンバーの検索は performance_schema.replication_group_members テーブルの MEMBER_ROLE を見ればわかります。

Tuning Recovery

新規ノードの追加や故障ノードが復帰するとき、グループのメンバからランダムで1つが選ばれて、そのノードからデータを取得する。この処理はリカバリプロセスと呼ばれて、選ばれたノードはドナーと呼ばれる。

ドナーへの接続が失敗したときは自動的に別のドナーが選択されて接続が再試行される。接続のリトライの限界に達するとリカバリプロセスはエラーで終了する。

リカバリプロセスでは単なる接続エラーだけではなく、いろいろなエラーを検出して別のドナーで再試行が行われる。

  • パージされたデータ
    • ドナーでリカバリに必要なデータが既にパージされている
  • 重複データ
    • 新しいノードが既に持っているデータと、ドナから同期されるデータとが競合したとき
    • 他のドナーには切り替えずにエラーにすることも考えられるけど、不均質なグループではあるメンバーは競合するトランザクションを持っていて、あるメンバーは競合するトランザクションを持っていない可能性があるため、このエラーが発生したときも他のドナーで再試行されます
  • その他のエラー
    • リカバリスレッドがコケたとき・・えーとまあなんかエラー?です

リカバリプロセスは普通のMySQLレプリケーションの実装に依存しているため、ここで説明した再試行とは別に、普通のMySQLレプリケーションとしての再試行も行われることがある。

リカバリの再試行回数は group_replication_recovery_retry_count パラメータで設定できる。

group_replication_recovery_reconnect_interval で再試行のインターバルを設定できる。再試行の都度、毎回インターバルが挟まれるわけではなく、すべてのドナーで一通り失敗したときだけインターバルが挟まれて次のドナー(一度失敗している)で再試行される。

Network Partitioning

クオラムのためにグループの過半数が生きている必要がある。ただしサーバがグループから自発的に去ったときはグループのメンバに去ることが伝えられるので、その時点でグループが再構成されるのでクオラム数も再計算される。

クオラム低下による停止からの復帰の方法。まず、生きてるノードでアドレスを確認して、

/* 生きてるすべてのノードで確認 */
SELECT @@group_replication_local_address;

グループのメンバシップを強制的に変更する。

SET GLOBAL group_replication_force_members="192.168.88.11:6606,192.168.88.12:6606";

Group Replication Requirements

グループレプリケーションに使用するサーバの要件。

  • InnoDB ストレージエンジン
    • トランザクション前提です
  • 主キー
    • トランザクションの競合の検出のために主キーが必要です
  • IPv4 ネットワーク
    • IPv6 は未対応です
  • ネットワークパフォーマンス
    • それなりに太い帯域とそれなりに低いレイテンシが前提です

制限

認証プロセス?(トランザクションのコミット時にグループのメンバで合意を得るプロセス)ではギャップロックが利用できない(InnoDB の外部ではギャップロックに関する情報を利用できないため)。なのでトランザクション分離レベルには READ COMMITTED を使うことをおすすめする(たぶん要するにトランザクションがグループに伝播されるときにトランザクションが発生したノード以外ではギャップロックが利かないということだと思います。それで困るのはマルチプライマリのときだけだと思うのでシングルプライマリなら関係ないように思います)。

LOCK TABLESGET_LOCK() は使用できない(これもシングルプライマリなら関係ないように思います。プライマリでは利きますよね? というか LOCK TABLES とか GET_LOCK() とかなんて普段使わないですね。。。)。

マルチプライマリモードでは SERIALIZABLE 分離レベルはサポートしていない。

マルチプライマリモードでは同じオブジェクトに対して異なるサーバで DDL と DML が同時に実行されると DDL の競合が検出されないリスクがある?

マルチプライマリモードでは CASCADE を指定した外部キー制約をサポートしていない。カスケード操作が行われるときに検出できない競合が生じることがあるため。なのでマルチプライマリモードでは group_replication_enforce_update_everywhere_checks を有効にすることをおすすめする。シングルプライマリモードなら問題ない。

グループ内での複製に5秒以上を要するような通信があるとグループ内の通信の失敗を引き起こす可能性がある。LOAD DATA INFILE などで使用するファイルを小さく分割するなどの対応が必要。

マルチプライマリモードでは SELECT .. FOR UPDATE でデッドロックすることがある。これはグループのメンバー間でロックが共有されないため。 (SELECT .. FOR UPDATE は普通にデッドロックする可能性はあると思うのだけど・・どういうこと?)

グローバルレプリケーションフィルタは使用できない。一部のメンバーでトランザクションをフィルタするとグループが一貫のある状態で同期できなくなるため。グループ外のサーバとのレプリケーションなどのグループレプリケーションに直接関係しないレプリケーションチャンネルになら使用できる。 (いわゆる replicate-do-db とかのことだと思われる)

さいごに

GTID レプリケーションと mysqlfailover に毛が生えたようなものかと思ってたんですけど

とかを見るに単純にバイナリログを転送しているだけではなさそうです。ただシングルプライマリだと実質単純にバイナリログを転送しているだけになりそうな気もするので、グループレプリケーションの最大の旨味はマルチプライマリモードですかね? 最もノードのヘルスチェックやフェイルオーバーの自動化の面だけでも十分メリットあると思うので今後使えそうなら使っていきたいかも。

それと、グループ全体をシャットダウンすると group_replication_bootstrap_group が再び必要になります。これはちょっと面倒ですね。いわゆる本番環境なら止めることはないので良いですけど、検証環境とかで上げたり下げたりすることがあると運用が面倒そうです。

mysqldump で default-character-set とか hex-blob とか

とある MySQL のダンプファイルをインポートしようとしたところ、次のような警告が表示されました。

Warning (Code 1300): Invalid utf8 character string: 'FFFFFF'

インポートに失敗しているわけではないですがやや気持ち悪いです。

原因

blob とかのバイナリを含むテーブルを mysqldump して mysql でインポートしようとしたためです。

create table t (x blob, z text);
insert into t values (x'FFFF0123456789ABCDEF', 'あいうえお');
select hex(x), z from t;
+----------------------+-----------------+
| hex(x)               | z               |
+----------------------+-----------------+
| FFFF0123456789ABCDEF | あいうえお      |
+----------------------+-----------------+
mysqldump test t > dump.sql
mysql test < dump.sql
#=> Warning (Code 1300): Invalid utf8 character string: 'FFFF01'

バイナリを含むテーブルを普通に mysqldump すると文字列リテラルにバイナリのバイトシーケンスがそのまま出力されます(エスケープとかされないという意味)。

そのようなダンプファイルを mysql に流すと、リテラルの中身を utf8 として読もうとして↑のような警告になります(エラーになることもあったっけ?)。

もちろん SQL 的にエスケープが必要なものはエスケープされます(シングルクオートとか)。

insert into t values (x'302739', 'あいうえお');
mysqldump test t | less
#=> INSERT INTO `t` VALUES ('0\'9','あいうえお');

解決方法(hex-blob)

mysqldump--hex-blob オプションを付けるのがスタンダードな解決方法です。

$ mysqldump --help | grep -A1 -- --hex-blob
  --hex-blob          Dump binary strings (BINARY, VARBINARY, BLOB) in
                      hexadecimal format.

このオプションを付けてダンプするとバイナリ部分は16進の形式でダンプされます。

INSERT INTO `t` VALUES (0xFFFF0123456789ABCDEF,'あいうえお');

解決方法(default-character-set)

リテラルの部分を utf8 として解釈しようとするから警告が出ているわけなので、インポート時に --default-character-set=binary などと指定する方法も考えられます。が・・・これはうまくいきません。

mysql test --default-character-set=binary < dump.sql
#=> Warning (Code 1300): Invalid utf8 character string: 'FFFF01'

なぜなら普通に mysqldump するとダンプファイルの先頭のあたりに SET NAMES utf8 が出力されるためです。

無理やり削ってやれば大丈夫です。

sed '/SET NAMES/d' dump.sql | mysql test --default-character-set=binary

ただし、この方法はダンプファイルに出力されている文字のエンコーディングと、データベースの(テーブルの(カラムの))エンコーディングが一致している前提が必要です。もっとも、今日日はどちらも utf8 とか utf8mb4 などで揃っているだろうのでまず問題は無いと思いますが。

あるいはダンプ時に --default-character-set=binary を指定しても良いです。

mysqldump --default-character-set=binary test t > dump.sql
mysql test < dump.sql

あるいはダンプ時に SET NAMES を出力しないオプションもあります。これなら mysql--default-character-set=binary を活かせられます。

mysqldump --skip-set-charset test t > dump.sql
mysql --default-character-set=binary test < dump.sql

mysqldump の default-character-set とは

そもそも mysqldump に指定する --default-character-set とはいったいなんなのか・・これはダンプファイルに出力される文字エンコーディングです。

例えば次のように複数の文字エンコーディングが混在しているようなテーブルで、

create table t (
    s text charset sjis,
    e text charset ujis,
    u text charset utf8
);

insert into t values ('あ', 'あ', 'あ');
select * from t;

元の文字エンコーディングがなんであったとしても mysqldump--default-character-set で指定したエンコーディングに変換されて出力されます。

mysqldump --default-character-set=sjis test t | grep INSERT | nkf -Sw
#=> INSERT INTO `t` VALUES ('あ','あ','あ');
mysqldump --default-character-set=ujis test t | grep INSERT | nkf -Ew
#=> INSERT INTO `t` VALUES ('あ','あ','あ');
mysqldump --default-character-set=utf8 test t | grep INSERT
#=> INSERT INTO `t` VALUES ('あ','あ','あ');

--default-character-set=binary なら素のまま出力されます(ので化けてる、空文字に見えてる部分には印字不可能なバイトシーケンスがあります)。

mysqldump --default-character-set=binary test t | grep INSERT | nkf -Sw
#=> INSERT INTO `t` VALUES ('あ','、「','縺);
mysqldump --default-character-set=binary test t | grep INSERT | nkf -Ew
#=> INSERT INTO `t` VALUES ('','あ',');
mysqldump --default-character-set=binary test t | grep INSERT
#=> INSERT INTO `t` VALUES ('','','あ');

さいごに

バイナリを含むテーブルのダンプには --hex-blob を付けましょう。~/.my.cnf に書いても可。

--default-character-set=binary は個人的にオススメできません。本来テキストファイルであるはずのファンプファイルにバイナリのバイトシーケンスが含まれるのは違和感がありすぎます。--default-character-set=binary を使うことで無駄な変換が行われず、複数のエンコーディングを同時に使っている状況なら意義があることもあるかもしれませんが、今日日はデータベースも mysqldumputf8 で統一されているでしょう。もしどうしてもおかしなエンコーディングを使う必要があるなら(sjis とか)それこそ BLOB とかでバイナリで放り込めばいいんじゃないでしょうか。

MySQL の GTID レプリを replicate-do-db でフィルタすると欠番になる?

スレーブ側で--replicate-do-や--replicate-ignore-などのルールを使ってフィルタリングをすると、GTIDに欠番ができて、連番が連続しなくなるため、SHOW SLAVE STATUSの出力が大変なことになってしまう。GTIDを用いるときは、フィルタリングしないのほうが無難である。

漢(オトコ)のコンピュータ道: MySQLレプリケーションの運用が劇的変化!!GTIDについて仕組みから理解する

replicate-do-db などでスレーブでフィルタしていると GTID に欠番が生じてスレーブの Executed_Gtid_Set がすごいことになる。ということだと思うのですが、GTID はトランザクション単位で採番されるものの replicate-do-db とかはステートメント単位とかだと思うので(1つのトランザクションの中にフィルタされる更新とされない更新が混ざることがある)、直感的にはトランザクションの一部がフィルタされたときはマスターとスレーブでトランザクションの内容に差異が生じて、全部フィルタされたときも GTID が欠番にはならずに空のトランザクションになりそうな気がしたので、試してみました。

試したバージョンは次の通り。

mysqld --version
/usr/sbin/mysqld  Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)

docker-compose.yml

version: '3.4'

services:
  sv01:
    image: mysql:8
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      MYSQL_DATABASE: test
    networks:
      mysql:
        ipv4_address: 192.168.88.11
    command:
      - --default_authentication_plugin=mysql_native_password
      - --skip-name-resolve
      - --character-set-server=utf8
      - --innodb-file-per-table
      - --log-bin=mysql-bin
      - --sync-binlog=1
      - --relay-log=relay-bin
      - --log-slave-updates
      - --skip-slave-start
      - --binlog-format=row
      - --replicate-do-db=test
      - --slave-exec-mode=IDEMPOTENT
      - --master-info-repository=TABLE
      - --relay-log-info-repository=TABLE
      - --relay-log-recovery=ON
      - --gtid-mode=ON
      - --enforce-gtid-consistency
      - --server-id=1

  sv02:
    image: mysql:8
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      MYSQL_DATABASE: test
    networks:
      mysql:
        ipv4_address: 192.168.88.12
    command:
      - --default_authentication_plugin=mysql_native_password
      - --skip-name-resolve
      - --character-set-server=utf8
      - --innodb-file-per-table
      - --log-bin=mysql-bin
      - --sync-binlog=1
      - --relay-log=relay-bin
      - --log-slave-updates
      - --skip-slave-start
      - --binlog-format=row
      - --replicate-do-db=test
      - --slave-exec-mode=IDEMPOTENT
      - --master-info-repository=TABLE
      - --relay-log-info-repository=TABLE
      - --relay-log-recovery=ON
      - --gtid-mode=ON
      - --enforce-gtid-consistency
      - --server-id=2

networks:
  mysql:
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 192.168.88.0/24

docker-compose で環境を立ち上げる。

docker-compose up
docker-compose exec sv01 bash
docker-compose exec sv02 bash

レプリケーション用のユーザーを作成。

# [sv01/sv02]
mysql mysql -v <<'SQL'
CREATE USER 'repl'@'%' IDENTIFIED BY 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
SQL

バイナリログがわかりやすくなるように reset master しとく。

# [sv01/sv02]
mysql mysql -v <<'SQL'
reset master;
SQL

レプリケーションを開始。

# [sv02]
mysql mysql -v <<'SQL'
change master to
  MASTER_HOST = '192.168.88.11',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'pass',
  MASTER_AUTO_POSITION = 1;
start slave;
SQL

--replicate-do-db=test により test データベース以外はスレーブでフィルタされるようになっているので、適当に別のデータベースを作るなどしてマスターを更新します。

/* [sv01] */
use test
create table t (id int not null primary key, no int not null);
insert into t values (1, 111);

/* [sv01/sv02] */
select * from t;

/* [sv01] */
create database hoge;
use hoge
create table h (id int not null primary key, no int not null);
insert into h values (1, 111);

/* [sv01] */
use test
insert into t values (2, 222);

GTID を見てみます。

/* [sv01/sv02] */
show master status \G
show slave status \G

見た感じ欠番が生じてる雰囲気はありません。次のように mysqlbinlog を見比べてみても、

mysqlbinlog mysql-bin.000001 --include-gtids=6930f785-7376-11e8-9b24-0242c0a8580b:5 --base64-output=DECODE-ROWS -v

sv01

BEGIN
/*!*/;
# at 1197
#180619  5:22:14 server id 1  end_log_pos 1245 CRC32 0xb5b056b1         Table_map: `hoge`.`h` mapped to number 107
# at 1245
#180619  5:22:14 server id 1  end_log_pos 1289 CRC32 0xe5c002ce         Write_rows: table id 107 flags: STMT_END_F
### INSERT INTO `hoge`.`h`
### SET
###   @1=1
###   @2=111
# at 1289
#180619  5:22:14 server id 1  end_log_pos 1320 CRC32 0xf366fedf         Xid = 122
COMMIT/*!*/;

sv02

BEGIN
/*!*/;
# at 1265
#180619  5:22:14 server id 1  end_log_pos 1332 CRC32 0x7b096e01         Query   thread_id=17    exec_time=0     error_code=0
SET TIMESTAMP=1529385734/*!*/;
COMMIT

スレーブでフィルタされた処理は空のトランザクションとなって記録されているようです。

ので replicate-do-db などを用いてフィルタしてもスレーブで GTID に欠番がでることはなさそうです。

さいごに

参考にした記事はだいぶ古く(3年半ぐらい前)、バージョンも 5.6 とかなので、今回試した 8.0.11 だとその辺の事情も変わってるのかもしれません。

現代は GTID レプリケーションと replicate-do-db などを併用しても問題ない、と思います。