MySQL RouterはMySQLの読み取りと書き込みの分離を実装します

1. はじめに

MySQL Router は、以前の古いバージョンの SQL プロキシを置き換えるために MySQL によって公式に提供される軽量の MySQL ミドルウェアです。

MySQL Router はデータベースミドルウェアであるため、前のクライアントからの SQL 要求が書き込み要求であるか読み取り要求であるかを分析し、SQL 要求をマスターに送信するかスレーブに送信するか、またどのマスターまたはスレーブに送信するかを決定できる必要があります。このようにして、MySQL Router は MySQL の読み取りと書き込みの分離を実現し、MySQL リクエストの負荷を分散します。

したがって、MySQL Router の前提は、バックエンドが MySQL マスタースレーブレプリケーションを実装することです。

MySQL Router は非常に軽量で、異なるポートを介した単純な読み取り/書き込み分離のみを実装できます。読み取り要求のスケジュールアルゴリズムでは、デフォルトの rr (ラウンドロビン) のみを使用できます。より高度で複雑な機能はありません。したがって、MySQL Router を実装する場合は、バックエンド MySQL の高可用性を自分で構成する必要があります。高可用性を実現するには、Percona XtraDB Cluster、MariaDB Galera、またはMySQLの公式グループレプリケーションを使用することをお勧めします。他に選択肢がない場合は、MHAを使用して実現することもできます。

したがって、単純な MySQL Router の展開図は次のようになります。

この記事では、バックエンドに MySQL マスター/スレーブ高可用性がない場合に、MySQL Router を使用して読み取り/書き込み分離を実装します。バックエンドに MySQL 高可用性がある場合に読み取り/書き込み分離が実装されない理由について説明します。私の意見では、MySQL Router は単なるおもちゃです。機能が少ないだけでなく、アプリケーションコードで読み取り/書き込みに異なるポートを指定する必要があります (後述の構成ファイルの説明を参照)。実際の環境では誰も使用しません。

2. MySQLルーターを構成する

以下は実験環境です。

キャラクター名	ホストIP	MySQL バージョン	データステータス
MySQL ルーター	192.168.100.21	MySQL 5.7.22	なし
マスター	192.168.100.22	MySQL 5.7.22	新しいインスタンス
奴隷1	192.168.100.23	MySQL 5.7.22	新しいインスタンス
奴隷2	192.168.100.24	MySQL 5.7.22	新しいインスタンス

バックエンドの MySQL マスター/スレーブレプリケーションでは高可用性が実現されないため、書き込み操作を担当するマスターノードは 1 つだけです。

すべてのバックエンド MySQL ノードは新しくインストールされた MySQL インスタンスであるため、マスター/スレーブレプリケーションを直接有効にできます。既存データのマスター・スレーブレプリケーションの場合は、まずそれらが同期されていることを確認する必要があります。方法は次のとおりです。スレーブをマスターによって指定された座標に復元します。

2.1 MySQLルーターのインストール

MySQL Router のバイナリバージョンを次の場所からダウンロードします: https://dev.mysql.com/downloads/router/
rpm リポジトリ: http://repo.mysql.com/yum/mysql-tools-community/el/7/x86_64/

ここでは、MySQL Router 2.1.6 のバイナリバージョンが使用されます。

tar xf mysqlrouter-2.1.6-linux-glibc2.12-x86-64bit.tar.gz
mv mysqlrouter-2.1.6-linux-glibc2.12-x86-64bit /usr/local/mysqlrouter

そうなんです、とても簡単です。

バイナリパッケージを解凍すると、解凍されたディレクトリに次のファイルがあります。

[root@s1 mr]# ls
binデータにはlib実行共有が含まれる

bin ディレクトリには、MySQL Router のメインプログラムでもあるバイナリプログラム mysqlrouter が 1 つだけあります。

共有ディレクトリにはサンプル設定ファイルとサンプル SysV スタイルの起動スクリプトがありますが、残念ながらスクリプトは Debian プラットフォームに基づいているため、Red Hat シリーズで使用する前に変更してインストールする必要があります。そこで、後で CentOS で SysV スクリプトを作成しました。

[root@s1 mr]# ls share/doc/mysqlrouter/
ライセンス.txt README.txt sample_mysqlrouter.conf sample_mysqlrouter.init

最後に、メインプログラムを PATH 環境変数に追加します。

echo "PATH=$PATH:/usr/local/mysqlrouter/bin" >/etc/profile.d/mysqlrouter.sh
chmod +x /etc/profile.d/mysqlrouter.sh
ソース /etc/profile.d/mysqlrouter.sh

2.2 MySQLルーターの起動とテスト

以下は、上記の実験環境用の設定ファイルです。マスターノードは192.168.100.22:3306 1 つだけです。書き込みノード (マスター) が複数ある場合は、ノードをカンマで区切ります。設定ファイルについては後ほど説明します。

[デフォルト]
config_folder = /etc/mysqlrouter
ログ記録フォルダ = /usr/local/mysqlrouter/log
ランタイムフォルダ = /var/run/mysqlrouter

[ロガー]
レベル = INFO

[ルーティング:スレーブ]
バインドアドレス = 192.168.100.21:7001
宛先 = 192.168.100.23:3306,192.168.100.24:3306
モード = 読み取り専用
接続タイムアウト = 1

[ルーティング:マスター]
バインドアドレス = 192.168.100.21:7002
宛先 = 192.168.100.22:3306
モード = 読み書き
接続タイムアウト = 2

次に、MySQL Router が配置されているマシンに上記で使用したディレクトリを作成します。

シェル> mkdir /etc/mysqlrouter /usr/local/mysqlrouter/log /var/run/mysqlrouter

これにより、MySQL Router が起動してサービスが提供されます (開始する前に、バックエンドの MySQL がマスター/スレーブレプリケーションで構成されていることを確認してください)。

[root@s1 mr]# mysqlrouter &
[1] 16122

監視ステータスを確認します。ここで監視される 2 つのポート 7001 と 7002 は、フロントエンドが MySQL ルーターに接続するために使用されます。これらは、フロントエンドから送信された SQL 要求を受信し、読み取りおよび書き込みルールに従って SQL 要求をバックエンドの MySQL マスターノードとスレーブノードにルーティングするために使用されます。

[root@s1 mr]# netstat -tnlp
アクティブなインターネット接続（サーバーのみ）
プロトコル 受信Q 送信Q ローカルアドレス 外部アドレス 状態 PID/プログラム名 
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 1231/proxysql    
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 1231/proxysql    
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1152/sshd        
tcp 0 0 192.168.100.21:7001 0.0.0.0:* LISTEN 16122/mysqlrouter
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2151/マスター      
tcp 0 0 192.168.100.21:7002 0.0.0.0:* LISTEN 16122/mysqlrouter
tcp6 0 0 :::22 :::* 1152/sshdをリッスン        
tcp6 0 0 ::1:25 :::* LISTEN 2151/マスター

ログを表示します:

[root@s1 mr]# cat /usr/local/mysqlrouter/log/mysqlrouter.log 
2018-07-07 10:14:29 INFO [7f8a8e253700] [routing:slaves] が開始されました: 192.168.100.21:7001 でリッスン中; 読み取り専用

2018-07-07 10:14:29 INFO [7f8a8ea54700] [routing:masters] が開始されました: 192.168.100.21:7002 でリッスン中; 読み取り/書き込み

最後にテストします。テストする前に、バックエンドマスターの MySQL ルーターノードに認証を与えて接続を許可します。接続は 2 つのスレーブノードに複製されます。

mysql> 'P@ssword1!' で識別される root@'192.168.100.%' に *.* のすべての権限を許可します。

書き込みポートである MySQL Router のポート 7002 に接続します。マスター/スレーブの高可用性は構成されていないため、書き込み可能かどうかを確認する簡単なテストで十分です。

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7002 -e '@@server_idを選択します;'
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
+-------------+
| @@サーバーID |
+-------------+
| 110 |
+-------------+

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7002 -e 'データベースmytestを作成します;'
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7002 -e 'データベースを表示;'
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
+--------------------+
| データベース |
+--------------------+
| 情報スキーマ |
|mysql |
| マイテスト |
| パフォーマンススキーマ |
|システム|
+--------------------+

次に、各スレーブノードをテストして、rr スケジューリングアルゴリズムの読み取り要求の負荷分散を実現できるかどうかを確認します。

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7001 -e '@@server_idを選択します;' 
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
+-------------+
| @@サーバーID |
+-------------+
| 120 |
+-------------+

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7001 -e '@@server_idを選択します;'
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
+-------------+
| @@サーバーID |
+-------------+
| 130 |
+-------------+

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7001 -e 'データベースを表示;'
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
+--------------------+
| データベース |
+--------------------+
| 情報スキーマ |
|mysql |
| マイテスト |
| パフォーマンススキーマ |
|システム|
+--------------------+

当然ながら、検査結果は正常です。

MySQL Router は非常にシンプルであるように見えます。実際、非常にシンプルです。適切な構成ファイルを提供するだけで、すべて設定完了です。それでは、MySQL Router の設定ファイルについて説明しましょう。

3. MySQLルーター設定ファイルの説明

MySQL Router の設定ファイルも非常にシンプルで、設定が必要な項目は多くありません。

デフォルトでは、mysql ルーターはインストールディレクトリで "mysqlrouter.conf" を検索し、ホームディレクトリで ".mysqlrouter.conf" を検索します。バイナリプログラムの mysqlrouter コマンドで "-c" または "--config" を使用して、構成ファイルを手動で指定することもできます。

MySQL ルーターの設定ファイルはフラグメント形式であり、よく使用されるフラグメントは [DEFAULT]、[logger]、[routing:NAME] の 3 つです。フラグメント名は大文字と小文字が区別され、単一行の「#」または「;」コメントのみがサポートされます。行の途中または行末のコメントはサポートされません。

上記の例の設定ファイルを見てみましょう。

[デフォルト]
config_folder = /etc/mysqlrouter
ログ記録フォルダ = /usr/local/mysqlrouter/log
ランタイムフォルダ = /var/run/mysqlrouter

[ロガー]
レベル = INFO

[ルーティング:スレーブ]
バインドアドレス = 192.168.100.21:7001
宛先 = 192.168.100.23:3306,192.168.100.24:3306
モード = 読み取り専用
接続タイムアウト = 1

[ルーティング:マスター]
バインドアドレス = 192.168.100.21:7002
宛先 = 192.168.100.22:3306
モード = 読み書き
接続タイムアウト = 2

1.DEFAULTフラグメントの構成。

[DEFAULT]セクションでは通常、設定ファイルのディレクトリ、ログのディレクトリ、および MySQL ルーターが実行されているディレクトリ (pid ファイルなど) が設定されます。

例えば：

[デフォルト]
config_folder=/etc/mysqlrouter #追加の設定ファイルディレクトリを指定します。このディレクトリ内のすべての conf ファイルが読み込まれます。logging_folder=/usr/local/mysqlrouter/log #ログディレクトリを指定します。ログファイル名は mysqlrouter.log です。
runtime_folder=/var/run/mysqlrouter #ランタイムディレクトリを指定します。デフォルトは /run/mysqlrouter です。

2.ロガーフラグメントの構成。

[logger]セクションには、ログレベルを設定するオプションが 1 つだけあります。

[ロガー]
レベル=debug # デバッグ、情報（デフォルト）、警告、エラー、致命的、大文字と小文字を区別しない

3.ルーティングフラグメントの構成。

[routing:NAME]は、さまざまなルーティングインスタンスを設定する MySQL ルーターの主要部分です。NAME には任意の名前を付けることができます。 [routing:slaves] 、 [routing:masters]など。

ルーティング構成セクションで設定できるオプションは次のとおりです。

（１） bind_addressとbind_port

bind_address と bind_port は、mysql ルーターがフロントエンド SQL 要求をリッスンするアドレスとポートです。 MySQL Router ではポートは必須ですが、bind_address を使用してIP:PORT形式で指定できるため、bind_port でバインドする必要はありません。
ルーティングルールではアドレス監視命令を 1 つだけ設定できますが、「0.0.0.0」を使用するとホスト上のすべてのアドレスを監視できます。リスンアドレスが指定されていない場合、デフォルトのリスンアドレスは 127.0.0.1 になります。

さらに、リスニングアドレスは destinations ディレクティブによって指定されたリストに表示できません。

次に例を示します。

[ルーティング:スレーブ]
バインドポート = 7001
[ルーティング:スレーブ]
バインドアドレス = 192.168.100.21
バインドポート = 7001
[ルーティング:スレーブ]
バインドアドレス = 192.168.100.21:7001

一般的に言えば、異なるポートを介して読み取り/書き込みの分離を実装することは良い考えではありません。最大の理由は、これらの接続ポートをアプリケーションコードで指定する必要があることです。ただし、MySQL Router は読み取りと書き込みの分離をこの方法でのみ実現できるため、MySQL Router はおもちゃとしてのみ使用する必要があります。

（２） destinations

ルーティングルールの転送先をHOST:PORTの形式で定義します。HOST は IP またはホスト名のいずれかになります。複数の転送先はカンマで区切られます。ターゲットリストが複数のスレーブとして定義されている場合。

[ルーティング:スレーブ]
バインドアドレス = 192.168.100.21:7001
宛先 = 192.168.100.23:3306,192.168.100.24:3306
[ルーティング:マスター]
バインドアドレス = 192.168.100.21:7002
宛先 = 192.168.100.22:3306,192.168.100.100:3306

（３） mode

MySQL ルーターには、読み取り専用モードと読み取り/書き込みモードの 2 つのモードがあります。これら 2 つの方法により、異なる転送スケジューリング方法が生成されます。

読み取り/書き込みに設定します。これは、マスターの高可用性を実現するために、宛先をマスターに設定する場合によく使用されます。

スケジュール方法: MySQL Router が初めてクライアント要求を受信すると、その要求を宛先リストの最初の宛先に転送します。2 回目にクライアント要求を受信すると、やはり最初の宛先に転送します。2 番目の宛先は、最初の宛先に接続できない場合にのみ接続されます (MySQL サービスがシャットダウンまたはダウンしている場合など)。すべての宛先に接続できない場合、MySQL Router は中断されます。このスケジュール方法は「先着順」と呼ばれます。
ターゲットに接続すると、MySQL Router はそれをキャッシュし、次にリクエストを受信したときにターゲットに転送します。キャッシュ対象なので、MySQL Routerを再起動すると無効になるということになります。
したがって、MySQL Router を通じて読み取りと書き込みの分離を実装する場合は、複数のマスターを設定し、パフォーマンスの優れたマスターを宛先リストの最初の位置に配置して、他のマスターをバックアップマスターとして後ろに配置することができます。

読み取り専用に設定され、MySQL 読み取り要求の負荷分散を実現するために宛先をスレーブに設定する場合によく使用されます。

スケジューリング方法: MySQL ルートはクライアント要求を受信すると、宛先リストの最初の宛先から逆方向に (ラウンドロビンで) ポーリングします。最初の要求は最初の宛先に転送され、2 番目の要求は 2 番目の宛先に転送され、最後の宛先に転送された後の次の要求は最初の宛先に転送されます。最初のターゲットが利用できない場合、ルーターはターゲットが利用可能になるまで、次のターゲットを順番にチェックします。すべてのターゲットが利用できない場合、MySQL ルーターは中断されます。
使用できないターゲットは一時的に分離され、MySQL Router はそれらのステータスを継続的にチェックし、再び使用可能になったときにターゲットリストに再度追加します。

（4） connect_timeout

MySQL Router が宛先に接続する際のタイムアウト。デフォルト値は 1 秒です。値の範囲は 1 ～ 65536 です。待ち時間が長くなりすぎないように、できるだけ小さい値を設定するようにしてください。
読み取り/書き込みモードでは、プライマリマスターが使用できないと判断されてバックアップマスターに接続されないように、タイムアウト期間を少し長めに設定できます。
読み取り専用モードでは、宛先リストがポーリングされるため、誤判定が発生しても影響はそれほど大きくなく、タイムアウト期間を少し短く設定できます。

（５）その他の選択肢

使用するプロトコル、リクエストの最大数など、その他の指示も設定できますが、デフォルト値を設定する必要はありません。これらはすべて、MySQL Router が MySQL と組み合わせて最適化したオプションであり、それ自体がすでに完璧です。

設定ファイルには、おおよそ次の内容が含まれています。設定後、最初にデフォルトセグメントに関係するディレクトリを作成することを忘れないでください。その後、mysql router を起動して、読み取り/書き込み分離サービスを提供できます。

4. MySQL Router用のSysVスクリプトを提供する

MySQL Router はメインプログラム (bin ディレクトリの mysqlrouter) のみを提供し、プログラムは停止オプションなしでのみ起動できるため、プロセスを強制終了するには kill コマンドのみを使用できます。

MySQL Router にはサンプルの起動スクリプトも用意されており、 $basedir/share/doc/mysqlrouter/sample_mysqlrouter.initにあります。ただし、このスクリプトは Debian プラットフォームに基づいており、CentOS でいくつかの設定とインストールが必要なので、使用せず、大まかなスクリプトを自分で作成してください。

シェル> vim /etc/init.d/mysqlrouter
#!/bin/bash

# chkconfig: -78 30
# 説明: MySQL ルーターの起動/停止

デーモン=/usr/local/mysqlrouter
proc=$DAEMON/bin/mysqlrouter
DAEMON_OPTIONS="-c ${DAEMON}/mysqlrouter.conf"

. /etc/init.d/functions

始める（） {
    [ -e /var/lock/subsys/mysqlrouter ]の場合;
        アクション「MySQL ルータは動作しています」/bin/false
    それ以外
        $proc $DAEMON_OPTIONS & &>/dev/null
        戻り値=$?
        エコー
    [ $retval -eq 0 ]の場合;
             /var/lock/subsys/mysqlrouter をタッチします
        アクション「MySQL ルーターを起動しています」/bin/true
        それ以外
        echo "MySQL ルーターの起動に失敗しました"
        フィ
    フィ
}
    
停止（） {
    [ -e /var/lock/subsys/mysqlrouter ]の場合;
        キルオール$proc
        戻り値=$?
        エコー
        [ $retval -eq 0 ]の場合;
            rm -f /var/lock/subsys/mysqlrouter
            アクション「MySQL ルーターを停止しています」/bin/true
        フィ
    それ以外
        アクション「MySQL ルータが動作していません」/bin/false
    フィ
}

状態（） {
    [ -e /var/lock/subsys/mysqlrouter ]の場合;
        echo "MySQL ルータが実行中です"
    それ以外
        echo "MySQL ルータは実行されていません"
    フィ
}

ケース「$1」
    始める）
        始める
        睡眠1
        ;;
     停止）
        停止
        睡眠1
        ;;
    再起動）
        停止
        始める
        睡眠1
        ;;
    状態）
        状態
        ;;
    *)
        echo "使用法: $0 {start|stop|status|restart}"
        戻り値=1
        ;;
エサック

$retval を終了

次に実行権限を付与します。

シェル> chmod +x /etc/init.d/mysqlrouter

これで、MySQL Router を使用して MySQL の読み取り/書き込み分離を実装する方法についての記事は終了です。MySQL Router の読み取り/書き込み分離の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: