ことさら−古都プログラマーの更級日記

京都でお寺を回りながら御朱印集めをしていたり、LoLをしたり試合を見に行ったりしているエンジニアのブログです。技術的なはなしとか日常的なはなし、カメラやLoLや競馬の話も書きます。右メニューに検索やらカテゴリーやらがあるので、見たい記事だけ見てね!

MySQL 5.7のマルチソースレプリケーションを活用する - 無停止でシャーディングを解消

はじめに

自己紹介

背景

昔々あるところに、以下のような構成のサーバーがありました

  • Webサーバー(アプリケーションサーバー)とDBサーバーからなる。
  • DBに入っているデータはユーザーIDでシャーディングされている。*2
  • どのデータがどのシャードに入っているかはアプリケーション(つまりコードで)管理されている。

f:id:yoshiki_utakata:20171214183537p:plain

このサーバは以下の問題を抱えています

  • MySQLのバージョンが古い(MySQL 5.1とか)
  • シャーディングするほどデータが多いわけではない(シャーディングする必要がない)
    • むしろシャーディングすることにより、特定のクエリは全てのDBに投げてアプリケーション層で集計とかそういうことをするハメになっている。
  • 古い物理サーバーなので仮想サーバーにのせかえたい

そこで以下を行いたいと思います

  • MySQLのバージョンを上げたい(最新は5.7.18とか)
  • シャーディングしていたのを1台にまとめる
  • GTIDレプリケーションに切り替える
  • 無停止でこれらを行う

MySQL5.7で追加されたマルチソースレプリケーションなどを活用し、これらを実現させたので、今回はその過程を書きたいと思います。

どうやるか(この記事で解説すること)

  1. MySQLのマルチソースレプリケーションを活用して無停止シャーディング解消
  2. Webサーバー→DBサーバーの向き先を変更
  3. MySQL 5.7.6 から可能になった GTID ローリング有効化を使い無停止でGTIDレプリケーションに切り替える

MySQL 5.7 のマルチソースレプリケーション機能を使ってシャーディング解消

マルチソースレプリケーションとは

MySQL 5.6 までのレプリケーション

  • Slaveに対してMasterは1台でした

f:id:yoshiki_utakata:20171214184942p:plain

MySQL 5.7 から実装されたマルチソースレプリケーション

  • 1つのSlaveが複数のMasterを持つ事ができるようになりました。
  • レプリケーションは「Channel」で区別されます。
  • slaveサーバーが MySQL 5.7 である必要があります。(Masterは5.7じゃなくていい)

f:id:yoshiki_utakata:20171214185309p:plain

どうやってシャーディングを”無停止で”解消するのか

まず、新しいMySQL 5.7のサーバーを用意します。

f:id:yoshiki_utakata:20171214185717p:plain

マルチソースレプリケーションを利用して、3台のDBサーバーから1台の新DBサーバーにレプリケーションを貼ります。

f:id:yoshiki_utakata:20171214190056p:plain

Webサーバー→DBサーバーの向き先を変え、旧DBサーバー→新DBサーバーのレプリケーションを切ります。

f:id:yoshiki_utakata:20171214190138p:plain

順番に具体的に説明していきます。

新しいMySQL 5.7のサーバーを用意する

新DBサーバーを用意し、MySQL 5.7をインストール。あらかじめMaster-Slave構成を組んでおきます。 このときに重要なのが、GTIDレプリケーションを切っておくことです。

旧DBサーバーはMySQLのバージョンが古いため、GTIDレプリケーションの機能がありません。つまり、旧DBサーバー→新DBサーバーのレプリケーションは、バイナリログポジションによるレプリケーション(非GTIDレプリケーション)にするしかありません。新Master→新Slaveの間だけGTIDレプリケーションにするといった中途半端なことはできませんので、一旦GTIDレプリケーションをオフにする必要があります。

GTIDレプリケーションが有効になっているかどうかは以下のクエリでわかります。

mysql> SELECT @@GLOBAL.GTID_MODE;
+--------------------+
| @@GLOBAL.GTID_MODE |
+--------------------+
| ON                 |
+--------------------+
1 row in set (0.00 sec)

普通はONになっていますのでOFFにします。 GTID_MODE には ON, ON_PERMISSIVE, OFF_PERMISSIVE, OFF の4つの状態があり、順々にしか切り替えられないので順々に切り替えます。新masterDB, 新slaveDB両方ともGTIDレプリケーションOFF にしておきます。

mysql> SET GLOBAL gtid_mode = 'ON_PERMISSIVE';
Query OK, 0 rows affected (0.00 sec)
  
mysql> SET GLOBAL gtid_mode = 'OFF_PERMISSIVE';
Query OK, 0 rows affected (0.00 sec)
  
mysql> SET GLOBAL gtid_mode = 'OFF';
Query OK, 0 rows affected (0.01 sec)
  
mysql> SELECT @@GLOBAL.GTID_MODE;
+--------------------+
| @@GLOBAL.GTID_MODE |
+--------------------+
| OFF                |
+--------------------+
1 row in set (0.00 sec)

この後、通常通り 新Master-新Slaveのレプリケーションを貼って下さい。

旧DBサーバーからデータをdump&新DBサーバーにリストア

mysqldump コマンドを使ってdumpして普通にrestoreするのですが、すこしコツがいります。まずdumpは以下のように行います。

# シャード1
$ mysqldump -u root -p db_name table_name1 table_name2 --master-data=2 --single-transaction > /tmp/records1.sql

# シャード2以降
$ mysqldump -u root -p db_name table_name1 table_name2 --master-data=2 --single-transaction --no-create-info > /tmp/records2.sql

まず、 --master-data=2 が重要です。 --master-data はMasterでdumpしてSlaveでリストアする時に利用するオプションです。 1 を指定するとdumpしたsqlにchange mater文が自動的に挿入されます。2 を指定すると、コメントアウトした状態でchange master文が挿入されます。

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000644', MASTER_LOG_POS=528589829;

マルチソースレプリケーションをしたい場合、change master するのと同時に「チャンネル」を指定しないといけないため、このchange master文はそのまま利用できません。しかし、MASTER_LOG_POSの値は欲しいため、コメントアウトした状態で出力してもらいます。

次に、--no-create-info です。シャード2以降は --no-create-info を指定しています。これはシャード1をrestoreした段階で既にテーブルが作成されているためです。これを入れないとシャード2をrestoreする際にシャード1で作成されたデータがdropされてしまいます。

dumpできたら、1から順にリストアしていきます。

$ mysql -h new-masterdb -u user_name -p db_name < /tmp/records1.sql
$ mysql -h new-masterdb -u user_name -p db_name < /tmp/records2.sql

旧master DBサーバーから新master DBサーバーへのマルチソースレプリケーション

さて、先程dump時に出力されたchange master文があると思いますが、

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000644', MASTER_LOG_POS=528589829;

これを元にマルチソースレプリケーションを貼っていきます。

-- シャード1からのレプリケーション
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000644', MASTER_LOG_POS=528589829, MASTER_HOST='old_master_db_shard1', MASTER_USER='rep', MASTER_PASSWORD='xxx' FOR CHANNEL 'shard1';

-- シャード2からのレプリケーション
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000xxx', MASTER_LOG_POS=xxxxxxxxx, MASTER_HOST='old_master_db_shard2', MASTER_USER='rep', MASTER_PASSWORD='xxx' FOR CHANNEL 'shard2';


-- 確認
mysql> show master status\G

-- レプリケーション開始
mysql> start slave;

-- 確認
mysql> show master status\G

FOR CHANNEL がポイントです。チャンネルを指定することで複数Masterからのレプリケーションが可能になります。これがマルチソースレプリケーションです。それぞれのレプリケーションはチャンネル名で区別されます。チャンネル名はかぶらないようにしてください。show slave status してみましょう。複数のMasterからレプリケーションされていることがわかります。

これで、先程示した以下の図の状態になりました。

f:id:yoshiki_utakata:20171214190056p:plain

Webサーバーから新DBサーバーに向き先を変更する

コードを書きかえるなどして向き先を変えただけです。割愛します。これで無停止で切り替えができました。

旧DBサーバー→新DBサーバーへのレプリケーションを切る

新DBサーバーMasterで reset slave all するだけです。

これで以下の図の状態になりました。

f:id:yoshiki_utakata:20171214190138p:plain

今回のようなマルチソースレプリケーションを活用したシャーディング解消時の注意点

複数のMasterから一つのSlaveにレプリケーションする場合は、複数MasterからのupdateによってSlaveでコンフリクトが発生しないように注意する必要があります。

マルチソースレプリケーションOKな例

例えば、旧サーバーで entry というテーブルが user_id でシャーディングされていたとします。entry_id が Primary key だとします。

以下のように、各シャードで被らないように entry_id が与えられていれば、DB名とtable名が被っていても問題ありません。

-- シャード1 db:entry_db, select * from entry;
+----------+---------+
| entry_id | user_id |
+----------+---------+
| 1        | 1       |
+----------+---------+

-- シャード2 db:entry_db, select * from entry;
+----------+---------+
| entry_id | user_id |
+----------+---------+
| 2        | 2       |
+----------+---------+

マルチソースレプリケーションされたSlaveのレコードは以下のようになります。

-- db:entry_db, select * from entry;
+----------+---------+
| entry_id | user_id |
+----------+---------+
| 1        | 1       |
| 2        | 2       |
+----------+---------+

マルチソースレプリケーションNGな例

OKな例と同様のテーブル構成で、以下のように entry_id が被ってしまっている場合、

-- シャード1 db:entry_db, select * from entry;
+----------+---------+
| entry_id | user_id |
+----------+---------+
| 1        | 1       |
+----------+---------+

-- シャード2 db:entry_db, select * from entry;
+----------+---------+
| entry_id | user_id |
+----------+---------+
| 1        | 2       |
+----------+---------+

マルチソースレプリケーションしていた場合、Primary Key制約によりどちらか一方のレコードしかinsertすることができません。こういった場合にはマルチソースレプリケーションは出来ません。

もちろんDBやテーブルが違う場合は問題ありませんが、シャーディングを解消するために同じDBかつ同じテーブルに複数のmasterからレプリケーションを張る場合は気をつける必要があります。*3

無停止GTIDレプリケーション有効化

これで旧DBサーバーとの関係は切れたため、GTIDレプリケーションを有効化できます。MySQL 5.7.6 以前は、GTIDレプリケーションを有効にするために、一旦レプリケーションを止めないといけませんでしたが、MySQL 5.7.6 からは、無停止でGTIDレプリケーションを有効にできます。手順は簡単ですが、MasterとSlaveでごちゃごちゃ操作する必要があるため、間違えないようにしてください。手順は

  1. Slaveを gtid_mode = ON_PERMISSIVE にする
  2. Master を gtid_mode = ON にする
  3. Saster を gtid_mode = ON にする

です。

まず Slave を gtid_mode = ON_PERMISSIVE にする

-- 最初はOFFになっているはず
mysql> select @@global.gtid_mode;
 
mysql> SET GLOBAL gtid_mode = 'OFF_PERMISSIVE';
 
mysql> SET GLOBAL gtid_mode = 'ON_PERMISSIVE';

-- ONになっているはず
mysql> select @@global.gtid_mode;

-- レプリケーションは続いたまま
mysql> show slave status\G

Master を ON まで変更する

-- 最初はOFFになっているはず
mysql> select @@global.gtid_mode;
 
mysql> SET GLOBAL gtid_mode = 'OFF_PERMISSIVE';
  
mysql> SET GLOBAL gtid_mode = 'ON_PERMISSIVE';
  
mysql> SET GLOBAL gtid_mode = 'ON';

-- ONになっているはず
mysql> select @@global.gtid_mode;
 
-- レプリケーションは続いたまま
mysql> show slave status\G

最後に Slave を ON に変更する

-- ON_PERMISSIVEになっている
mysql> select @@global.gtid_mode;

mysql> SET GLOBAL gtid_mode = 'ON';

-- ONになったはず
mysql> select @@global.gtid_mode;

-- レプリケーションは続いたまま
mysql> show slave status\G

何故GTIDレプリケーションを有効化するのか

主に障害発生時の復旧をラクにできるといったメリットがありますが、長くなりすぎるので詳しい説明はここでは割愛します。以下の記事が参考になります。

bizstation.hatenablog.com

おわりに

MySQL 5.7 のマルチソースレプリケーション活用事例と、GTIDローリング有効化について書きました。

マルチソースレプリケーションについては、まだ実装されたてなため、痒い所に手が届かなかったり、バグっぽいものがあったりしますが( start slave for channel とかでチャンネルごとにレプリケーションを開始/停止できたら便利なのになー、できそうなのになー、と思うけどできない)これからきっと便利なものになっていくでしょう。

どういった場面で活用できるか難しい機能ですが、実務では使える場面もちょいちょいあると思います。ぜひ活用してみてください。

*1:https://twitter.com/nico_nico_talk/status/941210221451423744 この機能は僕が実装しました。タグのコピーも実装中ですのでしばらくお待ち下さい...

*2:シャーディングについては http://yoshiki-utakata.hatenablog.com/entry/2017/12/04/104639 あたりにまとめました

*3:下手にidを振るのはアンチパターンだということがわかりましたね...