またしてもcgoで作りました。
https://github.com/shigenobu/mysql_ws_split
前回作成した、ws_parse_url関数との違いは、
引数が2つあることですが、意外とこれがはまりました。
MySQLって、splitする関数がなくて（まあ、あったとしてもどうするんだという話）、
splitして、JSON配列に展開しちゃえば、そこから加工しやすくなるだろうという算段です。
以上
カテゴリー「mariadb」
PHPのparse_url関数のような動作をするMySQLのUDFをcgoで作成しました。
https://github.com/shigenobu/mysql_ws_parse_url
（参考）PHPのparse_url関数
https://www.php.net/manual/ja/function.parse-url.php
この関数のポイントは、クエリー形式のパラメータをキー名でとれるところです。
最近はアクセスログをDBに保存して、そのまま分析に使うなどをすることもあり、MySQL側で一気に加工できれば楽だなと思いつくりました。
これ以外にも作成したものがいくつかあるので、そのうち公開しようと思います。
なお、動作確認したのはMariaDB10.4のみなので、もしお使いになる場合は自身の環境での動作確認をお願いします。
以上
MariaDB10.4.7から、text/blobについても、桁数を指定しないでINDEXが作成できるようになった。
以下のリンクの下のほうに書いてあった。
https://jira.mariadb.org/browse/MDEV-20918
しかし、UNIQUE KEYの場合、これによる弊害が現時点では発生する。
10.3まではエラーで抑止してくれたものの、10.4からはスルーして、HASHインデックスとして作成しまうのだ。
つまり、InnoDBにおいては、現状有効でないHASHインデックスが形だけ定義されるということである。
実際に例を挙げてみます。
MariaDB [test]> create table dummy (
-> c_tinytext tinytext,
-> c_text text,
-> c_mediumtext mediumtext,
-> c_longtext longtext,
-> cu_tinytext tinytext,
-> cu_text text,
-> cu_mediumtext mediumtext,
-> cu_longtext longtext,
-> key k1 (c_tinytext),
-> key k2 (c_text),
-> key k3 (c_mediumtext),
-> key k4 (c_longtext),
-> unique key u1 (cu_tinytext),
-> unique key u2 (cu_text),
-> unique key u3 (cu_mediumtext),
-> unique key u4 (cu_longtext)
-> ) Engine=InnoDB;
Query OK, 0 rows affected, 3 warnings (0.016 sec)
waringがあるものの、問題なくテーブルが作成できてしまった。
念のためwarningを見てみる。
MariaDB [test]> show warnings; +-------+------+----------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------+ | Note | 1071 | Specified key was too long; max key length is 3072 bytes | | Note | 1071 | Specified key was too long; max key length is 3072 bytes | | Note | 1071 | Specified key was too long; max key length is 3072 bytes | +-------+------+----------------------------------------------------------+ 3 rows in set (0.000 sec)
３つしかない。次にshow create tableを見てみる。
MariaDB [test]> show create table dummy;
+-------+-----------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------+
| dummy | CREATE TABLE dummy (
c_tinytext tinytext DEFAULT NULL,
c_text text DEFAULT NULL,
c_mediumtext mediumtext DEFAULT NULL,
c_longtext longtext DEFAULT NULL,
cu_tinytext tinytext DEFAULT NULL,
cu_text text DEFAULT NULL,
cu_mediumtext mediumtext DEFAULT NULL,
cu_longtext longtext DEFAULT NULL,
UNIQUE KEY u1 (cu_tinytext) USING HASH,
UNIQUE KEY u2 (cu_text) USING HASH,
UNIQUE KEY u3 (cu_mediumtext) USING HASH,
UNIQUE KEY u4 (cu_longtext) USING HASH,
KEY k1 (c_tinytext(255)),
KEY k2 (c_text(768)),
KEY k3 (c_mediumtext(768)),
KEY k4 (c_longtext(768))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------+
1 row in set (0.001 sec)
なんと、UNIQUE KEYが、自動的にHASH INDEXに変更されてしまった。
念のため、INDEXの状態を見てみる。
MariaDB [test]> show index from dummy;
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dummy | 0 | u1 | 1 | cu_tinytext | A | NULL | NULL | NULL | YES | HASH | | |
| dummy | 0 | u2 | 1 | cu_text | A | NULL | NULL | NULL | YES | HASH | | |
| dummy | 0 | u3 | 1 | cu_mediumtext | A | NULL | NULL | NULL | YES | HASH | | |
| dummy | 0 | u4 | 1 | cu_longtext | A | NULL | NULL | NULL | YES | HASH | | |
| dummy | 1 | k1 | 1 | c_tinytext | A | 0 | 255 | NULL | YES | BTREE | | |
| dummy | 1 | k2 | 1 | c_text | A | 0 | 768 | NULL | YES | BTREE | | |
| dummy | 1 | k3 | 1 | c_mediumtext | A | 0 | 768 | NULL | YES | BTREE | | |
| dummy | 1 | k4 | 1 | c_longtext | A | 0 | 768 | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.002 sec)
やはり、UNIQUE KEYだけHASH INDEXになっている。
この状態において、残念ながらUNIQUE KEYの検索時のインデックスは効きません。HASH INDEXだから等価なら効くのかと思いきや、InnDBなんで、フルスキャンになってしまいます。ご注意ください。
これ、見落とすと思う。少なくとも今までMySQLに触ってきた人なら。
ポジティブに考えるなら、InnoDBでもHASH INDEXを近いうちにサポートするということなのだろうか？とも考えられなくはない。10.5で導入されるのだろうか？
UNIQUE制約を張る以上、等価検索が往々にして行われるので、InnoDBのHASH INDEXサポートは個人的には歓迎かなと。
以上
MariaDB10.3から導入されたsystem versioned table（正確にはTemporal Data Tablesというらしい）ですが、一見するととても便利な機能ですが、ちょっと注意が必要です。
（ひとつめ）
CREATE時にSQLモードの影響を受ける。
SQLモードに「NO_ZERO_DATE」（もしくはTRADITIONAL）が入っていて、「0000-00-00」などの無効な日時を弾く定義を行っているとCREATEできない。（当然ALTER時も影響を受けます）
MariaDB [test]> select @@sql_mode; +------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +------------------------------------------------------------------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------------------------------------------------------------------------------------------------------------------------------------------------+
MariaDB [test]> create table t_sv (
-> user_id int not null,
-> name varchar(32) not null,
-> primary key (user_id)
-> ) with system versioning;
ERROR 1067 (42000): Invalid default value for 'row_start'
とまあ、こんな感じです。
当方の環境では、「NO_ZERO_DATE」と「TRADITIONAL」を落とすことで、CREATE可能になりました。
（ふたつめ）
row_endというカラム（レコードが切り替えられた日時であり、上のDDLだと勝手にそのような名前で作成される）は実はPRIMARY KEYである。
show create table の結果だと、
MariaDB [test]> show create table t_sv; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t_sv | CREATE TABLEt_sv(user_idint(11) NOT NULL,namevarchar(32) NOT NULL, PRIMARY KEY (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
となるのですが、show index の結果をみると、
MariaDB [test]> show index from t_sv; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t_sv | 0 | PRIMARY | 1 | user_id | A | 0 | NULL | NULL | | BTREE | | | | t_sv | 0 | PRIMARY | 2 | row_end | A | 0 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
というように、user_idとrow_endでPRIMARY KEY となっている。
show create table 上は出てこないのに、不思議。
ちまみに、information_schema.statistics のテーブルにも出てきているので、アプリケーション上で自動的にPRIMARY KEY の判定を行って、何かをしている場合は注意が必要です。
しかし、実は例外もあって、以下のようにそもそもPRIMARY KEY がないテーブルだとrow_endにPRIMARY KEY が設定されない。
CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME INTERVAL 1 WEEK (
PARTITION p0 HISTORY,
PARTITION p1 HISTORY,
PARTITION p2 HISTORY,
PARTITION pcur CURRENT
);
これまた超絶不思議。おそらくはclustered indexに自動的に決定されることが起因しているのかもしれない。
試しにxをPRIMARY KEY に変更したら以下のようなエラーがでた。
MariaDB [test]> alter table t add primary key (x); ERROR 4119 (HY000): Not allowed for system-versionedtest.t. Change @@system_versioning_alter_history to proceed with ALTER.
どうやら変更できないみたい。
（みっつめ）
公式にも書いてありますが、mysqldumpを使った時に、現在のレコードのみが出力されます。
つまり、mysqldumpではsystem versioned table のバックアップはできません。
そのほか、
- 同じデータであっても隠しカラムの値のため、CHECKSUM の値が同じにならない
- データを変更してなくても、ON UPDATEが走る（row_startとかが変わるから）
といった特徴もあります。
安易に使うと、意外なところでハマりそうな気がしますが、社内システムなど限られた環境で扱う分は、威力を発揮しそうな感じがします。
system versioned table はトランザクションIDの履歴対応やら、10.4でアプリケーションタイムをサポートするなど、今後も改良が加えられていくと思いますので、個人的には期待してます。
以上
MariaDB10.3で導入されたSequenceですが、
便利な反面、注意しなければいけないことが2つありましたので、
記載しておきます。
（ひとつめ）
テーブルのdefault値にすると、名前次第でmysqldumpから復元できない。
これは以前書いた記事の話です。
たとえば、以下のようなテーブルシーケンスを作成します。
MariaDB [test]> create sequence t_user_seq increment by 0;
MariaDB [test]> create table t_user (
-> user_id int not null,
-> name varchar(32) not null,
-> seq_no bigint not null unique default nextval(t_user_seq),
-> primary key (user_id)
-> );
この状態で、mysqldumpすると、以下のようにテーブルの定義が先に来てしまう関係上、リストアで失敗します。
-- -- Table structure for table `t_user` -- : : : -- -- Table structure for table `t_user_seq` --
これは名前順で出力されてしまう仕様上、t_user_seqが後に定義されてしまうため、リストアに失敗するという現象をおこします。
（ふたつめ）
show create table 中に、シーケンスの定義にスキーマ（データベース）名が含まれてしまう。
MariaDB [test]> show create table t_user; +--------+----------------------+ | Table | Create Table | +--------+----------------------+ | t_user | CREATE TABLE `t_user` ( `user_id` int(11) NOT NULL, `name` varchar(32) NOT NULL, `seq_no` bigint(20) NOT NULL DEFAULT nextval(`test`.`t_user_seq`), PRIMARY KEY (`user_id`), UNIQUE KEY `seq_no` (`seq_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +--------+----------------------+
test というスキーマ名が入ってきてしまっています。
これにより、別スキーマに、
create table ~ like test.t_user;
とかしてしまうと、シーケンスの参照がコピー元となってしまいます。
うっかりやりそうなミスなので、ご注意ください。
以上
MariaDB10.4でhistgramの収集がデフォルトでONになりました。
（もちろんANALYZE TABLEを実行したときですけど）
10.3では、
select @@use_stat_tables; +-------------------+ | @@use_stat_tables | +-------------------+ | NEVER | +-------------------+
でしたが、
10.4では、
select @@use_stat_tables; +------------------------+ | @@use_stat_tables | +------------------------+ | PREFERABLY_FOR_QUERIES | +------------------------+
となり、ANALYZE TABLE 時に、特定のクエリーを加えると、histgramの収集が行われます。
ANALYZE TABLE 時にhistgramを収集するための書き方としては、
analyze table ${tableName} persistent for all;
のようになります。詳しくは公式を参考ください。
で、実際にやってみました。
まずは、以下のようなテーブルに大量にデータを突っ込んでおきます。
MariaDB [test]> create table user ( id integer not null auto_increment, name varchar(64), money_free integer, money_paid integer, primary key (id) );
MariaDB [test]> select count() from user; +----------+ | count() | +----------+ | 16777214 | +----------+
最小値と最大値をとっておきます。
MariaDB [test]> select min(money_free), max(money_free) from user; +-----------------+-----------------+ | min(money_free) | max(money_free) | +-----------------+-----------------+ | 1 | 100 | +-----------------+-----------------+
histgramを収集していない状態で、money_freeを条件として、フィルターされる行数を見てみます。
MariaDB [test]> explain extended select * from user where money_free between 30 and 31; +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 16305730 | 100.00 | Using where | +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
わかりにくいですが、filteredの値が100となっており、全くフィルターされない状態となっています。
次に、ANALYZE TABLE を実行してみます。
MariaDB [test]> analyze table user persistent for all; +-----------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------+---------+----------+-----------------------------------------+ | test.user | analyze | status | Engine-independent statistics collected | | test.user | analyze | status | OK | +-----------+---------+----------+-----------------------------------------+ 2 rows in set (36.498 sec)
結構時間かかりましたね。フルスキャンなので、公式にも記載があるように、slaveのみで実施するなどにして、サービスに影響が無いよう配慮する必要があります。
なお、バイナリログに記載されるようなので、masterで実施すると、slaveにも伝播してしまいますので、お気をつけ下さい。（flush localで回避できる模様）
そして、再度同じクエリーで、explainを実行してみます。
MariaDB [test]> explain extended select * from user where money_free between 30 and 31; +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 16777214 | 1.56 | Using where | +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
おおー、filteredが激減しましたね。
なお、ヒストグラムの情報は以下のテーブルに書かれるようです。
- mysql.table_stats
- mysql.column_stats
- mysql.index_stats
MySQL8のように、ヒストグラムを削除するコマンドはないようなので、直接DELETEしちまってもいいような。。
削除したら、flush tablesを打っておくとよいのかな。。。（削除に限らず作成／更新したら？）
このようにヒストグラムを更新することで、INDEXがないカラムに対しても、JOIN時に評価対象行が減るので、非常にパフォーマンスがアップすることが期待されます。
もちろん、join_bufferを増やしたり、optimizer_switchを調整したりといったことも合わせて必要ですが、OLAP向けにもInnoDBでもこれまで以上に頑張れそうですね。
以上
久し振りの投稿。
MariaDBを10.3から10.4にupgradeして、色々検証中。
まずは、INSTANTの拡張について記載します。
検証したバージョンは、10.4.8-communityです。
10.3で導入されたINSTANT機能ですが、10.4になって以下のように拡張されています。
- ALTER TABLE … ADD COLUMN（末尾への追加、10.3で導入）
- ALTER TABLE … DROP COLUMN（カラム削除、ただしINDEXなし）
- ALTER TABLE … MODIFY COLUMN（カラム変更）
- Reordering Columns（順序変更、つまりafterが使える）
- Changing the Data Type of a Column（varcharは最大まで、varbinaryは255まで、text型はダメ）
- Changing a Column to NULL（NULL許可へ変更）
- Adding a New ENUM Option（ENUM値の追加）
- Adding a New SET Option（SET値の追加）
- Removing System Versioning from a Column（system versionedの外し）
- ALTER TABLE … ALTER COLUMN（カラム修正）
- Setting a Column’s Default Value（デフォルト値の設定）
- Removing a Column’s Default Value（デフォルト値の削除）
- ALTER TABLE … CHANGE COLUMN（カラム名変更）
- ALTER TABLE … DROP INDEX and DROP INDEX（Secodary INDEX削除）
- ALTER TABLE … DROP FOREIGN KEY（外部キー削除）
- ALTER TABLE … AUTO_INCREMENT=…（AUTOインクリメント値再設定）
- ALTER TABLE … PAGE_COMPRESSED=1 and ALTER TABLE … PAGE_COMPRESSION_LEVEL=…（1のみ）
- ALTER TABLE … DROP CONSTRAINT（制約の削除）
- ALTER TABLE … RENAME TO and RENAME TABLE …（テーブル名変更）
（参考）
https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-the-instant-alter-algorithm/
※varcharの拡張は、utf8mb4環境で、最大まで一瞬でした。
※varbinaryについては公式に記載ありませんが、255までなら一瞬でした。256からはCOPYとなるようです。
いやー数が多いですね。
ここには記載がありませんが、virtualの生成列も一瞬で生成可能なので、これもINSTANTとしていいのかなと。
で、INSTANTを発動させるためには、DDLを発行する前に、
SET SESSION alter_algorithm='INSTANT';
をやると、INSTANT以外のものはエラーとなるのですが、
MariaDB [test]> select @@alter_algorithm; +-------------------+ | @@alter_algorithm | +-------------------+ | DEFAULT | +-------------------+
の状態であれば、INSTANT可能なDDLであれば、INSTANTが選択されるようです。
ただ、これ明示的な記載がないのが気になるところですが。。
（参考）
https://mariadb.com/kb/en/library/server-system-variables/#alter_algorithm
いくつか実験してみました。
以下のようなテーブルに大量にデータ入れてみます。
MariaDB [test]> create table user (
id integer not null auto_increment,
name varchar(64),
money_free integer,
money_paid integer,
primary key (id)
);
MariaDB [test]> select count() from user; +----------+ | count() | +----------+ | 16777214 | +----------+
これまでは、INSTANTが機能されると、
SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column';
の結果がインクリメントされるのですが、
MariaDB [test]> alter table user modify column name varchar(8192); Query OK, 0 rows affected (0.017 sec)
などのvarchar拡張やら、enum追加などは、innodb_instant_alter_columnの値が増えませんでした。
これは、どうやら内部的には10.2でサポートされた拡張については、innodb_instant_alter_columnをインクリメントしないという意図的な動きのようです。
https://jira.mariadb.org/browse/MDEV-20801
ここまで広い範囲をカバーしてもらえると、DDL変更の運用負荷がとても下がりますね。
残っている箇所が、INDEX関係なので、相当難しいと思いますが、今後も期待ですね。
以上
MariaDBに特化した、データベースの差分検出／反映ツールを作ってみました。
https://github.com/shigenobu/magentadesk
こちらのツールは、同一筐体内に存在する2つのデータベース（スキーマ）の差分を検出し、検出した差分結果を使って、反映対象先データベースにデータをコピーするものとなります。
以下のMariaDB特有機能をふんだんに使ってしまっているため、MySQLでは動きません。
- Dynamic Column
- Except syntax
- CTE
- Sequence
使いどころとしては、webサービスなどで、staging環境で確認が取れたデータを、production環境に反映するといったシチュエーションとなるかと。
staging環境とproduction環境の筐体を同一にしておき、productionはそこからレプリケーションでproduction専用のDBサーバに配るような形がよいかなと思います。
日本語のマニュアルは以下となります。
https://github.com/shigenobu/magentadesk/blob/master/README.ja.md
JAVAの単体実行バイナリをreleaseページからDLしてもられば使えます。
依存を極小化したので、わずか1.5MBのバイナリとなり、取り回しも楽かと。
そのほか、過去に作成したものですが、JAVAのTCPサーバ／クライアント、UDPサーバ／クライアントのMAVENライブラリも紹介させてください。
（redchest）
https://github.com/shigenobu/redchest
websocketライクなインタフェースで設計しており、NIO2のTCP実装を使っています。Linuxで動かせば、epollのシステムコールで処理します。特徴としては、断線などにも対応すべく、最終受信からN秒で接続を破棄するように作り込んでいます。注意点として、広域（日本⇔ヨーロッパなど）で使う場合、epollのシステムコールの制約なのか、ごく稀にMTUより小さい受信バッファサイズであっても、さらに分割される場合があります。（C/C++でも同様の現象確認済）その際は、次回受信で不足分が取得できますので、前回メッセージを一時的にセッションに蓄積するなどの工夫が必要です。
（blueshelf）
https://github.com/shigenobu/blueshelf
こちらもwebsocketライクなインターフェースで設計した、NIOのUDP実装を使っています。Linuxならselectのシステムコールですね。特徴としては、複数ポートでlistenすることで、多重のIOを実現しています。そもそも、UDPには多重IOの技術はあまり不要かと思いますが、複数ポートを使うことで、複数のIOをうまく使い、メニーcore環境におけるパフォーマンス向上を目指しました。こちらも、最終受信からN秒で、「切断」のような扱いにしています。
以上
MariaDB10.4.6がGAとしてリリースされました。（2019年6月18日）
10.3からやや遅れたそうですが、ほぼ1年、おめでとうございます。
今回は、10.3と比べると、MariaDB Server関連の機能的な追加はすくないように見えます。
そのかわり、Galeraがバージョン4になっており、こちらが今回の目玉のように見えます。
Percona XtraDB Cluster 5.7のGalera3より一歩先行した感じでしょうか。
変更点の概要は公式ページを参照ください。
https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-104/
これから諸々検証していければと思います。
以上
githubが開発しているOrchestoratorについて、
以前の記事で、MariaDBに対応していない？と記載してしまったが、
実際に試してみたところ、MariaDBのGTIDレプリケーションにも対応していたので、
その備忘録を記載します。
なお、検証したバージョンは以下となります。
- CentOS7.5
- MariaDB10.3.11
- Orchestorator3.0.13
１．MariaDBのセットアップ
master、slave×2の準同期レプリケーションを構築します。
構成は以下の通り。
----------------------------------------
master
IP:192.168.35.11
----------------------------------------
| |
|(semi sync) |(semi sync)
| |
-------------------- --------------------
slave slave
IP:192.168.35.12 IP:192.168.35.13
-------------------- --------------------
設定は以下の通り。
# マルチソースをやる際に必要かもしれないので、レプリケーションクラスターで共通の数字
gtid_domain_id=11
# レプリケーションクラスター間で一意の数字（IPアドレスの下2桁など）
server_id={{ server_id }}
# 名前解決ができない環境向けに、自身のノードのIPアドレスを設定しておく
report_host={{ ip_addr }}
# とりあえず0だが、動的に変更する
read_only=0
# 安定のrow
binlog_format=row
# 毎コミットごとにバイナリログに書き出す
sync_binlog=1
# フェイルオーバー時にslaveが昇格しても大丈夫なように
log_slave_updates=1
# レプリケーション安全設定
relay_log_recovery=1
# 大きなリレーログへの対応
slave_max_allowed_packet=1G
# タイムアウト長めに
slave_net_timeout=3600
# slaveのSQLスレッドの並列化
slave_parallel_threads={{ cpu_num }}
# 厳格モード
gtid_strict_mode=1
# 以下、準同期設定（MariaDB10.3では、plugin_load_addは不要）
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1500
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_wait_point=AFTER_SYNC
そして、レプリケーションを開始しておく。
２．Orchestoratorの設定
IPアドレス192.168.35.10のOrchestorator専用ノードを立てる。
orchestorator.conf.jsonの内容は以下の通り。
{
"Debug": false,
"EnableSyslog": false,
"ListenAddress": ":3000",
"MySQLTopologyUser": "adminuser",
"MySQLTopologyPassword": "adminpassword",
"BackendDB": "sqlite",
"SQLite3DataFile": "/usr/local/orchestrator/orchestrator.sqlite3",
"DiscoverByShowSlaveHosts": true,
"InstancePollSeconds": 5,
"UnseenInstanceForgetHours": 240,
"SnapshotTopologiesIntervalHours": 0,
"InstanceBulkOperationsWaitTimeoutSeconds": 10,
"HostnameResolveMethod": "default",
"MySQLHostnameResolveMethod": "@@report_host",
"SkipBinlogServerUnresolveCheck": true,
"ExpiryHostnameResolvesMinutes": 60,
"RejectHostnameResolvePattern": "",
"ReasonableReplicationLagSeconds": 10,
"ProblemIgnoreHostnameFilters": [],
"VerifyReplicationFilters": false,
"ReasonableMaintenanceReplicationLagSeconds": 20,
"CandidateInstanceExpireMinutes": 60,
"DetectClusterAliasQuery": "SELECT @@report_host",
"PromotionIgnoreHostnameFilters": [],
"DetectSemiSyncEnforcedQuery": "",
"URLPrefix": "",
"StatusEndpoint": "/api/status",
"StatusSimpleHealth": true,
"StatusOUVerify": false,
"AgentPollMinutes": 60,
"UnseenAgentForgetHours": 6,
"StaleSeedFailMinutes": 60,
"SeedAcceptableBytesDiff": 8192,
"PseudoGTIDPattern": "",
"PseudoGTIDPatternIsFixedSubstring": false,
"PseudoGTIDMonotonicHint": "asc:",
"DetectPseudoGTIDQuery": "",
"BinlogEventsChunkSize": 10000,
"SkipBinlogEventsContaining": [],
"ReduceReplicationAnalysisCount": true,
"FailureDetectionPeriodBlockMinutes": 60,
"RecoveryPeriodBlockSeconds": 3600,
"RecoveryIgnoreHostnameFilters": [],
"RecoverMasterClusterFilters": [
"*"
],
"RecoverIntermediateMasterClusterFilters": [
"*"
],
"OnFailureDetectionProcesses": [],
"PreFailoverProcesses": [],
"PostFailoverProcesses": [
"echo -e 'Failed server is {failedHost}:{failedPort}.\nSuccessed server is {successorHost}:{successorPort}.' | mail -s 'Complete Orchestrator Failover' yourname@sample.com"
],
"PostUnsuccessfulFailoverProcesses": [
"echo -e 'Failed server is {failedHost}:{failedPort}.' | mail -s 'Failed Orchestrator Failover' yourname@sample.com"
],
"PostMasterFailoverProcesses": [
"/usr/local/bin/detect_orchestrator_failover.py -u adminuser -p adminpassword -H {successorHost} -P {successorPort}"
],
"PostIntermediateMasterFailoverProcesses": [],
"CoMasterRecoveryMustPromoteOtherCoMaster": true,
"DetachLostSlavesAfterMasterFailover": true,
"ApplyMySQLPromotionAfterMasterFailover": true,
"MasterFailoverDetachSlaveMasterHost": false,
"MasterFailoverLostInstancesDowntimeMinutes": 0,
"PostponeSlaveRecoveryOnLagMinutes": 0
}
※adminuserは、レプリケーションクラスターに対してアクセス可能なsuperユーザとする。
ここでのポイントは、
"MySQLHostnameResolveMethod": "@@report_host"
"DetectClusterAliasQuery": "SELECT @@report_host"
である。
これにより、Orchestoratorはレプリケーションクラスターの状態取得をIPアドレスで行うことができるようになる。
この後、ひとまずOrechstoratorにレプリケーションクラスターのmasterノードを登録する。masterを登録しておけば、slaveも自動的に発見してくれる。
なお、登録方法は、WebGUIやらAPIなどであるが、ここでは割愛。
３．フェイルオーバー時のMariaDBパラメータの動的設定処理の配備
上記の設定の
"PostMasterFailoverProcesses"
の部分の
/usr/local/bin/detect_orchestrator_failover.py
を以下の感じで実装する。
なお、以下の処理は、masterのフェイルオーバー完了時に呼び出される。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import os
import sys
import traceback
import datetime
import re
import argparse
import MySQLdb
parser = argparse.ArgumentParser(description = "detect orchestrator failover.")
parser.add_argument("-u", type=str, help = "(required) mysql user", required=True)
parser.add_argument("-p", type=str, help = "(required) mysql pass", required=True)
parser.add_argument("-H", type=str, help = "(required) master host", required=True)
parser.add_argument("-P", type=str, help = "(required) master port", required=True)
args = parser.parse_args()
mysql_user = args.u
mysql_pass = args.p
master_host = args.H
master_port = args.P
fp = None
master_conn = None
master_c = None
try:
# log
log_path = '/path_to_your_dir/detect_orchestrator_failover.log'
fp = open(log_path, 'a')
# connect master
master_conn = MySQLdb.connect(
user=mysql_user,
passwd=mysql_pass,
host=master_host,
port=int(master_port),
db='mysql'
)
master_c = master_conn.cursor(MySQLdb.cursors.DictCursor)
fp.write('[{}]new master is {}:{}.\n'.format(datetime.datetime.now(), master_host, master_port))
# set master
master_queries = []
master_queries.append('set global read_only = 0')
for sql in master_queries:
master_c.execute(sql)
fp.write('[{}]new master query is {}.\n'.format(datetime.datetime.now(), sql))
# get slaves
sql = "show slave hosts"
master_c.execute(sql)
rows = master_c.fetchall()
if isinstance(rows, tuple):
for row in rows:
slave_conn = None
slave_c = None
try:
slave_host = row['Host']
slave_port = row['Port']
# connect slave
slave_conn = MySQLdb.connect(
user=mysql_user,
passwd=mysql_pass,
host=slave_host,
port=int(slave_port),
db='mysql'
)
slave_c = slave_conn.cursor(MySQLdb.cursors.DictCursor)
fp.write('[{}]new slave is {}:{}.\n'.format(datetime.datetime.now(), slave_host, slave_port))
# set slave
slave_queries = []
slave_queries.append('set global read_only = 1')
for sql in slave_queries:
slave_c.execute(sql)
fp.write('[{}]new slave query is {}.\n'.format(datetime.datetime.now(), sql))
except:
fp.write('[{}]{}\n'.format(datetime.datetime.now(), traceback.format_exc()))
finally:
if slave_c is not None:
slave_c.close()
if slave_conn is not None:
slave_conn.close()
except:
fp.write('[{}]{}\n'.format(datetime.datetime.now(), traceback.format_exc()))
finally:
if master_c is not None:
master_c.close()
if master_conn is not None:
master_conn.close()
if fp is not None:
fp.close()
ここまでやっておくことにより、
- IPアドレスベースでOrchestorator上からレプリケーションクラスターの認識／操作が行われる
- フェイルオーバー時にslaveのread_onlyを1に変更できる
ということができる。
ちなみに、Orchestoratorでは、
masterのダウンに対する振舞のみがhookとして設定できるようなので、
slaveを追加した場合に自動的にread_onlyを設定するといったことは出来無いように見えます。（たぶん）
マニュアルが細かいので、利用の際はがんばってマニュアルを読んでみてください。
https://github.com/github/orchestrator/tree/master/docs
（参考）
https://qiita.com/rerorero/items/1f06cc8db9c469191289
https://www.s-style.co.jp/blog/2018/11/2875/以上