レプリケーションとシャーディング、MySQLでレプリケーションの張り方

この記事は、 MySQL Casual Advent Calendar 2017 4日目の記事です。

1日前の記事はこちら→MySQL 8.0でカジュアルにウインドウ関数(rank, dense_rank) | キムラデービーブログ

僕の記事は、超初心者向けに、レプリケーションとシャーディングについての説明と、MySQLでのレプリケーションの張り方を解説します。

レプリケーションとシャーディングの違い

レプリケーションとシャーディングはともにDBの負荷分散の方法ですが、両者は微妙に異なります。ごっちゃにならないようにしましょう。

レプリケーションは、中身が全く同じDBを数台用意して、負荷分散する方法です。 一方でシャーディングはデータを数台のDBに分割することで負荷分散する方法です。 それぞれについて細かく見ていきます。

レプリケーション

レプリケーションは、中身が同じDBを複数台用意して負荷分散する方法です。レプリケーションの場合、masterと呼ばれるサーバーが1台、slaveと呼ばれるサーバーが複数台存在する構成となります。一般的にDBは更新系のクエリより参照系のクエリの方が多く流れてきます。これを利用した負荷分散方法がレプリケーションです。

図では仮にmasterを1台、slaveを2台用意しています。masterとslaveの間では「レプリケーション」を行います。「レプリケーション」を行うと、slaveはmasterのデータを常にコピーし続けようとします。

更新系のクエリは常にmasterに発行するようにします。slaveはmasterの状態を監視していて、masterが更新されるとslaveも同じようにデータが更新されます。つまり、slaveは常にmasterと同じ状態になります。参照系のクエリはslaveに向けます。更新系クエリは少ないが参照系クエリが多いDBの場合、slaveを増やしていくことでslave1台あたりの負荷を下げることができます。ただし、masterは基本的に1台しか存在できないので、更新系クエリが多いDBの場合はこの構成は難しくなってきます。

シャーディング

シャーディングは、DBを垂直分割して負荷分散を行う方法です。例えば以下の図のように、ユーザーIDの偶数・奇数でデータを分割します。

これはレプリケーションよりも直感的な負荷分散方法かもしれません。レプリケーションとは違い、更新系クエリが多いDBでも負荷分散ができます。が、欠点が多い分割方法となります。

サーバーの台数を増やしづらい

図では2台のDBの分割していますが、これを3台に増やそうと思った場合を考えます。レプリケーションの場合は、データをコピーしたslaveを追加するだけなのです。しかしシャーディングの場合はデータを振り分け直す必要があるため、簡単にサーバーの台数を増やせません。

結局両方のDBサーバーにクエリを投げなければいけない時がある

例えば、「ユーザーID100〜200のユーザーのデータ」を集めて来るとします。 当然ながらこの中には奇数ID・偶数ID両方混ざっていますから、 SELECT * FROM user WHERE 100 < id AND id <= 200; のようなクエリを2台のDBに投げてアプリケーション側で集計する必要があります。このように分割出来ないクエリも存在してきてしまうので、分割のしかたは慎重に吟味する必要があります。

実際にレプリケーションを張ってみる

実際にレプリケーションを張るコマンドを紹介しておわりにします。以下のようなレプリケーションを張るとします。

GTIDではないレプリケーション

GITD（Global Transaction ID）レプリケーションというのがMySQL 5.6で追加されたのですが、それい以前のバージョンのMySQLでレプリケーションを張る方法です。

レプリケーション用ユーザーを作成する

レプリケーションは、「slaveがmasterの状態を監視して、masterにクエリが発行されたらslaveでも同じクエリを発行してやる」という方法でmasterとslaveの同期を取っています。まずはslaveはmasterの状態を見るためのユーザーをmaster上に作成します。仮に repl ユーザーを作成するとします。

master(192.168.10.1) にて、rootユーザーで以下のクエリを発行します。

mysql> CREATE USER 'repl'@'192.168.10.2' IDENTIFIED BY 'replpass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.10.2';

ユーザーが追加されると思います。

mysql> SELECT user,host FROM mysql.user WHERE user = 'repl';
+------+---------------------------+
| user | host                      |
+------+---------------------------+
| repl | 192.168.10.2/255.255.255.255   |
+------+---------------------------+

masterのバイナリログのファイルとポジションを確認する

master で以下のクエリを発行して確認します。

mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000011 |   117823 |              |                  | ed5098b4-8312-11e7-982d-005056b9a2e9:1-287 |
+------------------+----------+--------------+------------------+--------------------------------------------+

File と Position のセットをメモります。

slaveとmasterの間でレプリケーションを張る

ここまでメモった情報を使って、 slave で以下のクエリを発行させることでレプリケーションを張ることができます。rootユーザーで発行してください。

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=117823, MASTER_HOST='192.168.10.1', MASTER_USER='repl', MASTER_PASSWORD='replpass';

MASTER_LOG_FILE, MASTER_LOG_POS はそれぞれ File Position の値、 MASTER_HOST は masterサーバーのホスト、MASTER_USER, MASTER_PASSWORD はmasterで作ったレプリケーション用ユーザーとパスワードになります。

slave で以下のクエリを発行するとレプリケージョンの状態が見られます。

mysql> SHOW SLAVE STATUS\G

CHANGE MASTER TO はレプリケーションの設定をするだけで、まだレプリケーションは開始していません。レプリケーションを開始するには以下のクエリを発行してください。

mysql> START SLAVE;

レプリケーションが開始されます。確認してみましょう。

mysql> SHOW SLAVE STATUS\G

問題なくレプリケーションが走っていれば、 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates といったように Slave_SQL_Running_State に何か書いてあると思います。このメッセージの場合はmasterに発行された更新は全て取り込まれていることを意味しています。

レプリケーションを一旦停止させるには

mysql> STOP SLAVE;

とすれば良いです。

レプリケーションの設定を消す場合は

mysql> RESET SLAVE;

とします。

試しにレプリケーションされているか確認してみる

レプリケーションされているか確認してみましょう。例えば、masterで CREATE DATABASE してみます。

-- masterにて
mysql> CREATE DATABASE hogehoge;

レプリケーションされているslaveにも同じDBができているはずです。

-- slaveにて
mysql> SHOW DATABASES;

GTIDレプリケーションの場合

GTIDレプリケーション自体の説明はまた別でしようかと思いますが、MySQL 5.6からは、GTIDレプリケーションというものを使うのが一般的です。基本的な手順はGTIDでない場合と同じですが、 CHANGE MASTER TO の部分だけが異なります。

mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.1', MASTER_USER='repl', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;

File や Position を指定せずとも、これだけでいい感じにレプリケーションを貼ってくれます。

まとめ

超初心者向けになってしまいましたが、 個人でアプリケーションを開発したりしているとなかなか使わないレプリケーションの張り方についてなど書きました。個人サーバーにvagrantやdockerでDBを2台立ててみて試してみてください。

SQLアンチパターン