Help us understand the problem. What is going on with this article?

MySQL Routerを導入してみる

More than 3 years have passed since last update.

概要

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

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

使うツール

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

機能概要

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

本件では、スレーブの負荷分散としての使用を前提としているので、後者のモードを
img2.jpg
図のように接続元サーバーにセットアップする

セットアップ作業

本稿で使用したレシピはgithubに上げてある

リポジトリの追加

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

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

cd ~/itamae_cookbooks/repos
itamae local mysqlrepo.rb

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

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

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

cd ~/itamae_cookbooks/os_package
itamae local mysqlrouter.rb

動作検証

前準備

本件の検証では、

  • 192.168.56.105 と 192.168.56.107 で Mariadb(MySQL)を立ち上げ
  • 105 と 107 のそれぞれのサーバーに同じ接続用DB、そのDBに同じユーザーで同じパスワード、同じ権限を設定、同じ構造で同じ名前のテーブルを作成
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というレコード値を挿入
INSERT INTO test_tb(dat) VALUES ('105');
  • 107 側には 107というレコード値を挿入
INSERT INTO test_tb(dat) VALUES ('107');

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

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

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ページによると

  • 現状では、mysqlrouter自体の耐障害性構成オプションはないのでcron等で自分で障害耐性を上げる必要がある
  • パケットの検査の機能がない(これはSQL自体の検査機能なのか?どのnodeと接続通信しているのかの検査なのか? どちらを意味しているのか解りかねるが、多分どちらも機能もない)
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした