MySQL Routerを導入してみる

# 概要
レプリケーション構成のmariadb(MySQL)での、スレーブ側を直接接続せずに、接続ツールを介して接続することで、冗長性を確保するための施策を試してみる。

完全に接続を接続ツールに任せて、シングル構成のように見せかけるツールもあるが、クラスター構成ではない、マスター/スレーブ構成だと、問題が生じる場合(データ伝播遅延、トランザクション中データ参照不能、スレーブ構成では使用すべきでないSQLの修正)があるので、マスター/スレーブの接続切り替えに関してはアプリコードで確実対応することにして、複数のスレーブに対する接続をツールを使うこととする。

# 使うツール
 MySQL Router
   MySQLのオフィシャルで配布しているツール、きちんとGAリリースされている。
   (以前、別の名前で同じようなツールがあったが、それが名前が変わったのか？)

#  機能概要

 - リストの最初の候補に接続、接続サーバーが障害時に次候補に自動的に切り替えるモード
 - 単純なラウンドロビンでリスト全部に順次接続する、リストサーバー障害時には、自動的に切り離すモード

 本件では、スレーブの負荷分散としての使用を前提としているので、後者のモードを
![img2.jpg](https://qiita-image-store.s3.amazonaws.com/0/137052/0be2d9b4-25cf-3dc3-de3f-412fe984f6ac.jpeg)
図のように接続元サーバーにセットアップする

# セットアップ作業

[本稿で使用したレシピはgithubに上げてある](https://github.com/hirotaka-tajiri/itamae_cookbooks)

## リポジトリの追加
```ruby:mysqlrepo.rb
execute "add mysql.repo" do
    action :run
    command "rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm"
end
```

(管理サーバーに対して実行)

```shell-session
cd ~/itamae_cookbooks/repos
itamae local mysqlrepo.rb
```

## MySQL Router と mariadb(mysql)クライアントツールのインストール

```ruby:mysqlrouter.rb
[
    "mariadb",
    "mysql-router"
].each{| pkg |
    package pkg do
        action :install
    end
}

template "/etc/mysqlrouter/mysqlrouter.ini" do
    action :create
    source "files/mysqlrouter.ini" 
    owner  "mysql"
    group  "mysql"
    mode   "755"
end

directory "/var/log/mysqlrouter" do
    action :create
    owner  "mysql"
    group  "mysql"
    mode   "777"
end

service "mysqlrouter.service" do
    action [:enable, :start]
end
```

(管理サーバーに対して実行)

```shell-session
cd ~/itamae_cookbooks/os_package
itamae local mysqlrouter.rb
```

# 動作検証

### 前準備

本件の検証では、

- 192.168.56.105 と 192.168.56.107 で Mariadb(MySQL)を立ち上げ
- 105 と 107 のそれぞれのサーバーに同じ接続用DB、そのDBに同じユーザーで同じパスワード、同じ権限を設定、同じ構造で同じ名前のテーブルを作成

```sql:
CREATE DATABASE balancingtest;
CREATE USER balancinguser IDENTIFIED BY 'xxxxxx';
GRANT ALL PRIVILEGES ON balancingtest.* TO 'balancinguser'@'%';
FLUSH PRIVILEGES;
USE balancingtest;
CREATE TABLE test_tb(dat VARCHAR(8));
```
- 105 側には 105というレコード値を挿入

```sql:
INSERT INTO test_tb(dat) VALUES ('105');
```

- 107 側には 107というレコード値を挿入

```sql:
INSERT INTO test_tb(dat) VALUES ('107');
```

### 検証作業１(バランシングの検証)

接続->SELECT->切断、接続->SELECT->切断、接続->SELECT->切断、接続->SELECT->切断を繰り返す

```shell-session
mysql -ubalancinguser -Dbalancingtest -pxxxxxx -h127.0.0.1 -P3306

SELECT dat FROM test_tb;

(Ctrl + C)
```
を繰り返すと

    105、107、105、107

とのデータを得ることができる


### 検証作業2(縮退動作の検証)

接続->SELECT->切断、接続->SELECT->切断、105側のサーバーの停止、接続->SELECT->切断、接続->SELECT->切断 という作業を行う


    105、107、107、107

との縮退動作を確認することができる

### 検証作業3(全サーバーDOWNの検証)

検証作業2の状態から、接続->SELECT->切断、107側のサーバーも停止、接続->SELECT->切断 という作業を行う

    107、ERROR 2003 (HY000): Can't connect to MySQL server

との全停止なので接続エラーを返すことが確認できる

### 検証作業4(障害復旧時の検証)
検証作業2の状態から、接続->SELECT->切断、105側サーバーの復旧、接続->SELECT->切断、接続->SELECT->切断、接続->SELECT->切断、接続->SELECT->切断 という作業を行う

    107、107、105、107、105

と自動的にバランシングリストに復帰させられる
(この自動復旧動作については、きちんとマニュアルに記載してあった)

## 総括

- 接続オーバーヘッド(レイテンシー)問題を全く考えないとすると、ロードバランスの動作は、希望通りの動きをしてくれる。
- また、障害時に自動的に縮退モードで、障害サーバーを切り離す動作についても問題がない。
- ただ、自動復旧については、賛否のどちらともいえる。
- なんらかの軽い断で、復旧時にデータに問題がない場合には、自動復旧で構わないが、重い障害などで大幅でデータ齟齬が発生し、復旧作業が必要な場合には、リストから切り離しのままが良い場合などあると思われる。その場合に、自動復旧されるとサービス障害となりかねない。
- そういう場合には、一度mysqlrouterのリストから障害サーバーを外し、mysqlrouterデーモンを再起動させ、強制的にリストから外しているうちにデータ復旧作業を行い、データ復旧後に、再度mysqlrouterのリストに追加して、mysqlrouterデーモンを再起動させロードバランスに戻すという作業を行う必要があるのではないかと思われる

### 現状(2016/12/7)時点での注意点
[マニュアルのFAQページによると](http://dev.mysql.com/doc/mysql-router/en/mysql-router-faq.html)

- 現状では、mysqlrouter自体の耐障害性構成オプションはないのでcron等で自分で障害耐性を上げる必要がある
- パケットの検査の機能がない(これはSQL自体の検査機能なのか?どのnodeと接続通信しているのかの検査なのか? どちらを意味しているのか解りかねるが、多分どちらも機能もない)