Zabbix MySQLスレーブサーバ（レプリケーション）の監視設定メモ

MySQLを、マスター/スレーブ構成で運用している時に、監視しておきたいのが、レプリケーションが正常に動作していることです。マスターサーバになにか障害が発生した場合は、即座にサービスに影響が出るため、すぐ気が付けますが、スレーブサーバでレプリケーションが停止していたり、遅延が発生していることは、なかなか気が付けないことが多く、データ不整合が発覚して、はじめて気づくこともあります。そこで今回は、Zabbix で MySQLのレプリケーション状態を監視する方法をご紹介したいと思います。

サーバ構成
MySQL レプリケーションの仕組み
レプリケーション状態の確認用コマンド
1. SHOW SLAVE STATUS
2. SHOW MASTER STATUS
Zabbix で監視する項目
Zabbixエージェント用の MySQLユーザの作成
1. パスワードファイルの作成
2. 確認
レプリケーション状態監視用のユーザーパラメータの追加
MySQL スレーブサーバ監視テンプレートの作成
MySQL マスターサーバ監視テンプレートの作成
1. アイテムの作成
ホストの作成
作成した監視テンプレートの動作確認
トリガーの動作確認

サーバ構成

MySQL マスター/スレーブサーバの設定内容は「MySQL 5.6 マスター/スレーブサーバの設定メモ」をご参照ください。また、MySQL マスター/スレーブサーバには、Zabbix-agent をインストールしておきます。

Zabbixサーバの、インストールや初期設定については、以下の記事をご参照ください。
Zabbix 2.4 を yumでインストール（CentOS6.5）
Zabbix 1-1. ユーザーの作成とアクション（メール通知）の設定

MySQL レプリケーションの仕組み

Zabbix の設定をする前に、レプリケーションの仕組みについて、簡単に確認しておきましょう。レプリケーション機能の中核を担うのが、マスターとの通信を担当する「スレーブ I/Oスレッド」と、スレーブDBの更新を担当する「スレーブ SQLスレッド」です。このどちらかが停止してしまうと、レプリケーションが停止し、データ不整合（マスターDBとスレーブDBの内容が異なる）が発生します。

バイナリログ
　マスターDBの更新情報が記録されます。この更新情報が、以下の仕組みを使って、スレーブサーバに転送され、スレーブのDBに適用されます。

マスタースレッド
　スレーブ I/0スレッドからの要求を受け付けて、バイナリログをスレーブに転送します。

スレーブ I/0スレッド
　マスターへ接続し、バイナリログを受け取り、リレーログという中間的なログに記録します。

スレーブ SQLスレッド
　リレーログ（転送されたマスターDBの更新情報）を読取って、スレーブDBを更新します。

レプリケーション状態の確認用コマンド

SHOW SLAVE STATUS

スレーブサーバ上で「SHOW SLAVE STATUS」を実行することによって、スレーブの詳細な状態が確認できます。コマンドを実行する際、末尾を通常の「;」ではなく「\G」とすると、見やすく表示してくれます。

「Slave_IO_Running」が、スレーブ I/0スレッドの状態、「Slave_SQL_Running」が、スレーブ SQLスレッドの状態です。また「Seconds_Behind_Master」で、スレーブ SQLスレッドによる遅延を確認できます。

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000003
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 284
        Relay_Master_Log_File: mysqld-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 622
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 6a4a225b-db14-11e4-9cc6-080027651ae2
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0

1 row in set (0.00 sec)

SHOW MASTER STATUS

マスターサーバ上で「SHOW MASTER STATUS」を実行すると、マスター上のバイナリログのファイル名、位置などが確認できます。詳細は後述しますが、SHOW SLAVE STATUS の表示と比較することによって、スレーブI/0スレッドによる遅延を調べることができます。

mysql> SHOW MASTER STATUS\G

*************************** 1. row ***************************
             File: mysqld-bin.000003
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

1 row in set (0.00 sec)

Zabbix で監視する項目

どのレプリケーション状態を、Zabbixで監視するかを整理しておきます。

スレーブ IOスレッドの状態

SHOW SLAVE STATUS コマンドの「Slave_IO_Running」の値が「Yes」であれば、スレーブ IOスレッドが正常に動作しています。停止していると、この値が「No」または「Connecting」になります。

スレーブ IOスレッドが停止する原因は、マスターサーバとの接続エラーによるものです。具体的には、マスターサーバの3306番ポートのブロック、UUIDの重複、CHANGE MASTER TOで指定するパスワードの間違いなどが挙げられます。

スレーブ SQLスレッドの状態

SHOW SLAVE STATUS コマンドの「Slave_SQL_Running」の値が「Yes」であれば、スレーブ SQLスレッドが正常に動作しています。停止していると、この値が「No」になります。

レプリケーションでのトラブルで、一番多いのが、このスレーブ SQLスレッドの停止です。原因は、バイナリログの破損や、データ不整合によるものです。こうなってしまった場合は、マスターDBをダンプして、スレーブで読み込むといった、リストア作業が必要になります。運用が開始していると正直かなり大変な作業です。(^^;)

スレーブ IOスレッドによる遅延

マスターサーバで SHOW MASTER STATUS コマンドを実行して、現在のバイナリログファイル名「File」と、位置「Position」の値を確認し、スレーブサーバで実行した SHOW SLAVE STATUS コマンドの「Master_Log_File」と「Read_Master_Log_Pos」の値と比較することによって確認できます。

・比較する項目
　File <-比較-> Master_Log_File
　Position <-比較-> Read_Master_Log_Pos

スレーブ SQLスレッドによる遅延

先にも書きましたが、SHOW SLAVE STATUS コマンドの「Seconds_Behind_Master」の値で確認できます。

まとめると、Zabbix で監視すべき具体的な項目は以下になります。
・スレーブサーバ（SHOW SLAVE STATUS コマンド）
　Slave_IO_Running
　Slave_SQL_Running
　Master_Log_File
　Read_Master_Log_Pos
　Seconds_Behind_Master

・マスターサーバ（SHOW MASTER STATUS コマンド）
　File
　Position

Zabbixエージェント用の MySQLユーザの作成

ここで作成したユーザとパスワードを使って、SHOW SLAVE STATUS / SHOW MASTER STATUS コマンドを実行し、出力結果を Zabbixで取得します。このユーザは、これらのコマンドが実行できればよいので、最小の権限「REPLICATION CLIENT」を設定しておきましょう。マスター/スレーブサーバそれぞれで、この MySQLユーザを作成してください。

mysql -u root -p

GRANT REPLICATION CLIENT ON *.* TO 'zabbixagent'@'localhost' IDENTIFIED BY '＜パスワード＞';

パスワードファイルの作成

続いて、この MySQLユーザ用のパスワードファイルを作成します。

Zabbixユーザのホームディレクトリを作成

mkdir /var/lib/zabbix
chmod 700 /var/lib/zabbix
chown zabbix:zabbix /var/lib/zabbix

パスワードファイルの作成
vim /var/lib/zabbix/.my.cnf

[mysql]
user=zabbixagent
password="＜パスワード＞"

※＜パスワード＞は、必ずクオートしておきます。

パスワードを書いてありますので、zabbix ユーザのみが読み書きできるように、パーミッションを設定しておきましょう。

chown zabbix:zabbix /var/lib/zabbix/.my.cnf
chmod 600 /var/lib/zabbix/.my.cnf

確認

作成したユーザで SHOW SLAVE STATUS / SHOW MASTER STATUS コマンドを実行できるかを確認しておきます。各コマンドの出力が表示されればOKです。

スレーブサーバで確認

HOME=/var/lib/zabbix mysql -e "SHOW SLAVE STATUS\G"

マスターサーバで確認

HOME=/var/lib/zabbix mysql -e "SHOW MASTER STATUS\G"

レプリケーション状態監視用のユーザーパラメータの追加

Zabbix で、スレーブサーバからは、SHOW SLAVE STATUS コマンドの出力結果を、マスターサーバからは、SHOW MASTER STATUS コマンドの出力結果を取得できるように、Zabbixエージェントの設定ファイルに、ユーザーパラメータを追加します。

スレーブサーバ

vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
最終行に下記を追加

UserParameter=mysql.slave.status[*],HOME=/var/lib/zabbix mysql -e "SHOW SLAVE STATUS\G" | grep " ""$1": | awk '{print $$2}'

このユーザーパラメータは、Zabbixサーバで「mysql.slave.status[＜フィールド名＞]」アイテムキーを実行すると、スレーブサーバ上で「HOME=/var/lib/zabbix mysql -e "SHOW SLAVE STATUS\G" | grep " ""＜フィールド名＞": | awk '{print $2}'」コマンドが実行され、フィールドの値をZabbixサーバに返します。

また、ユーザーパラメータ末尾の「awk '{print $$2}'」の $記号がダブっているのは、いつものタイプミス(^^;)ではありません。ユーザパラメータ内で $1〜$9 はアイテムキーの引数として解釈されてしまうため、コマンドに $記号を使いたい場合は、このように表記します。

位置基準を変更せずに使用するには、awk '{print $$2}'というように、ドル記号を入れてください。この場合、実際には$$2はコマンドを実行した時に$2に変わります。

4 ユーザーパラメータ | Zabbix Documentation 2.2 より引用

ユーザーパラメータを追加したら Zabbixエージェントを再起動します。

systemctl restart zabbix-agent

マスターサーバ

vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
最終行に下記を追加

UserParameter=mysql.master.status[*],HOME=/var/lib/zabbix mysql -e "SHOW MASTER STATUS\G" | grep " ""$1": | awk '{print $$2}'

Zabbixエージェントを再起動します。

systemctl restart zabbix-agent

確認

Zabbixサーバ上で zabbix_get コマンドを使って、追加したユーザーパラメータの動作を確認をします。ユーザーパラメータで指定したコマンドと、同じ出力結果が返ってくればOKです。

zabbix_get -s 172.16.1.22 -k "mysql.slave.status[Slave_IO_Running]"
Yes

zabbix_get -s 172.16.1.21 -k "mysql.master.status[File]"
mysqld-bin.000004

MySQL スレーブサーバ監視テンプレートの作成

前置きと下準備が、かなり長くなってしまいましたが、いよいよ Zabbix の設定です。

テンプレート名など、各種の名称は任意です。ご自身が分かりやすい名称を設定してください。

テンプレートの作成

[設定]→[テンプレート]→[テンプレートの作成]をクリックします。

[テンプレート]タブを選択、テンプレート名に「A_Templates_MySQL_Slave」を入力し、適当な所属グループを選択して「追加」をクリックすればテンプレートが作成されます。

↓

アプリケーションの作成

[設定]→[テンプレート]　「A_Template_App_MySQL_Slave」行の「アプリケーション」をクリックします。

「アプリケーションの作成」をクリックします。

名前に「mysql-slave」を入力し「追加」をクリックすればアプリケーションが作成されます。

アイテムの作成

[設定]→[テンプレート]　「A_Template_App_MySQL_Slave」行の「アイテム」をクリックします。

「アイテムの作成」をクリックします。

下記を入力/選択し「追加」をクリックすれば、スレーブ I/0スレッド監視のアイテムが作成されます。名前に入力しているマクロ「$1」には、キーの引数で指定しているフィールド名「Slave_IO_Running」が代入されます。

---(設定箇所)---------------------------
名前：3100_MySQL $1
キー：mysql.slave.status[Slave_IO_Running]
データ型：文字列
更新間隔(秒)：60
アプリケーション：mysql-slave
------------------------------

同様に、以下のスレーブサーバ監視用のアイテムを作成します。

---(設定箇所)---------------------------
名前：3101_MySQL $1
キー：mysql.slave.status[Slave_SQL_Running]
データ型：文字列
更新間隔(秒)：60
アプリケーション：mysql-slave
------------------------------

---(設定箇所)---------------------------
名前：3102_MySQL $1
キー：mysql.slave.status[Master_Log_File]
データ型：文字列
更新間隔(秒)：60
アプリケーション：mysql-slave
------------------------------

---(設定箇所)---------------------------
名前：3103_MySQL $1
キー：mysql.slave.status[Read_Master_Log_Pos]
データ型：数値（整数）
更新間隔(秒)：60
アプリケーション：mysql-slave
------------------------------

---(設定箇所)---------------------------
名前：3104_MySQL $1
キー：mysql.slave.status[Seconds_Behind_Master]
データ型：数値（整数）
更新間隔(秒)：60
アプリケーション：mysql-slave
------------------------------

最終的に５つのアイテムが作成されていることを、確認してください。

トリガーの作成

スレーブ I/0スレッド、スレーブ SQLスレッドが停止している場合は、通知するようにトリガーを設定します。

[設定]→[テンプレート]　「A_Template_App_MySQL_Slave」行の「トリガー」をクリックします。

「トリガーの作成」をクリックします。

下記を設定し「追加」をクリックすればトリガーが作成されます。

---(設定箇所)---------------------------
名前：{HOST.NAME} MySQL Slave_IO_Running
条件式：{A_Templates_MySQL_Slave:mysql.slave.status[Slave_IO_Running].regexp(Yes)}=0
深刻度：重度の障害
------------------------------
条件式末尾の「.regexp(Yes)}=0」は、MySQL Slave_IO_Running フィールドの値が「Yes」以外（停止している）ことを示しています。詳細は「1 サポートされているトリガー関数 | Zabbix Documentation 2.2」をご参照ください。

また、トリガーの名前にはマクロが使用できます。上の設定 {HOST.NAME} はホストの表示名がトリガー名に表示されます。

同様に、スレーブ SQLスレッド停止時に通知するトリガーも作成します。

---(設定箇所)---------------------------
名前：{HOST.NAME} Slave_SQL_Running
条件式：{A_Templates_MySQL_Slave:mysql.slave.status[Slave_SQL_Running].regexp(Yes)}=0
深刻度：重度の障害
------------------------------

MySQL マスターサーバ監視テンプレートの作成

スレーブサーバ監視テンプレートと同様に、以下のテンプレートとアプリケーションを作成します。

テンプレート名「A_Templates_MySQL_Master」
アプリケーション名「mysql-master」

アイテムの作成

[設定]→[テンプレート]　「A_Templates_MySQL_Master」行の「アイテム」をクリック → 「アイテムの作成」をクリックします。

下記を入力/選択し「追加」をクリックすれば、バイナリログファイル名の監視アイテムが作成されます。

---(設定箇所)---------------------------
名前：3200_MySQL $1
キー：mysql.master.status[File]
データ型：文字列
更新間隔(秒)：60
アプリケーション：mysql-master
------------------------------

同様に、バイナリログ位置の監視用のアイテムを作成します。

---(設定箇所)---------------------------
名前：3201_MySQL $1
キー：mysql.master.status[Position]
データ型：数値（整数）
更新間隔(秒)：60
アプリケーション：mysql-master
------------------------------

２つのアイテムが作成されていることを、確認してください。

ホストの作成

スレーブサーバとマスターサーバのホストを作成し、作成したテンプレートを適用すれば、設定完了です。

[設定]→[ホスト]→[ホストの作成]　をクリックします。

「ホスト」タブを選択します。ホスト名は、他のサーバと重複しないような名前を設定します。ホスト名に使える文字は、アルファベットの大文字小文字と半角数字、記号はアンダースコアとハイフンが使えます。表示名には使える文字の制限がありませんので、分かりやすい名前を付けておきます。

所属グループの選択は必須ですので、今回は「MySQL」という新規グループを作成しました。最後にMySQLスレーブサーバのIPアドレスを入力します。※まだ「追加」ボタンはクリックしません。

続いて「テンプレート」タブを選択し「Slave」をキーにして、先ほど作成したテンプレートを検索します。検索結果の「A_Template_App_MySQL_Slave」を選択します。

新規テンプレートをリンクの枠内の「追加」をクリックします。

テンプレートとのリンクに、先ほど選択したテンプレートが表示されていることを確認し「追加」をクリックすれば、ホストが作成されます。

同様にマスターサーバのホストを作成して「A_Templates_MySQL_Master」テンプレートを適用します。

---(設定箇所)---------------------------
ホスト名：mysql-master01
表示名：MySQLマスター01
グループ：MySQL
IPアドレス：172.16.1.21
テンプレート：A_Templates_MySQL_Master
------------------------------

作成した監視テンプレートの動作確認

[監視データ]→[概要]　グループ「MySQL」、タイプ「データ」を選択します。各種レプリケーション状態の値が表示されていればOKです。

スレーブ I/Oスレッド(Slave_IO_Running)の値は「YES」、スレーブ SQLスレッド(Slave_SQL_Running) の値も「Yes」なので、正常にレプリケーションが動作していることが確認できますね。

遅延状態は、現在のバイナリログファイル名が一致し(Master_Log_File = File) 読込位置も一致(Read_Master_Log_Pos = Position)していますので、スレーブ I/Oスレッドによる遅延はありません、また、Master_Log_File スレーブ SQLスレッドによる遅延(Seconds_Behind_Master)も「0」なので、遅延なくデータがレプリケーションされていることも確認できます。

トリガーの動作確認

いざ障害が発生したけれど、トリガーの設定ミスで、通知メールが送信されない(^^;) なんてことが無いように、可能であれば、MySQLスレーブサーバの、スレーブ I/Oスレッドとスレーブ SQLスレッドを停止して、トリガーの動作確認をすることをオススメします。

スレーブ I/Oスレッドの開始と停止

mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;

スレーブ SQLスレッドの開始と停止

mysql> STOP SLAVE SQL_THREAD;
mysql> START SLAVE SQL_THREAD;

上記のコマンドを実行して、障害通知メールや、復旧通知メールが届いていればOKです。

日	月	火	水	木	金	土
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31