MySQLのレプリケーション遅延が発生する場合に確認すべき事




投稿日:

MySQLのレプリケーション遅延が発生した場合に原因を素早く特定する為に、見るべきところをしっかりと把握しておく必要があります。今回はこの確認すべきポイントをまとめておこうと思います。

そもそもMySQLのレプリケーションって?

引用元が文章が分かりやすかったので最後に載せた参照より引用させて頂きました。

MySQL のレプリケーションは、安定稼働やバックアップ、負荷分散などの目的に利用できる優れた機能です。 bin-log (バイナリログ) を利用して Master サーバから Slave サーバに更新を伝播させ、データの複製を行います。Slave サーバでは、2 つのスレッドが動作しています。

IO_THREAD – Master から送られてきたデータを受け取り、relay-log (リレーログ) として書き出す
SQL_THREAD – relay-log を読み出し、DB を更新する


レプリケーション遅延の状態を確認しよう

最初に見るべきは、スレーブ側です。MySQLにログインし「show slave status \G」コマンドで確認します。

スレーブ側

show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.12.2
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000024
          Read_Master_Log_Pos: 198822658
               Relay_Log_File: relay-bin.000048
                Relay_Log_Pos: 198730937
        Relay_Master_Log_File: mysql-bin.000024
             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: 1980774
              Relay_Log_Space: 1983035
              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: 265
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: 
             Master_Info_File: 
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: invalidating query cache entries (table)
           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

Seconds_Behind_Masterが0でなければその分の秒数だけ遅延しています。
「Seconds_Behind_Master」の値が、「現在 SQL_THREAD が実行しているクエリの実行時刻」と「Slave サーバが保持しているリレーログの時刻」の差となり、遅延を表しています。

どれだけ遅延しているかチェックしよう

SHOW SLAVE STATUS の結果の「Master_Log_File」&「Relay_Log_Pos」と「Relay_Master_Log_File」&「Exec_Master_Log_Pos」をそれぞれ比較します。

Master_Log_File: mysql-bin.000024
Read_Master_Log_Pos: 198822658

この2行は、スレーブ側でI/Oスレッドにより転送されたリレーログに関する記述。
転送されて受け取っているファイルとそのポジションが記載されています。

Relay_Master_Log_File: mysql-bin.000024
Exec_Master_Log_Pos: 198730774

こちらの2行は、スレーブ側のSQLスレッドがどこまでログ内のSQLを実行したかを示しています。
そのファイルとポジションを指しています。


この2つのFileやPosの乖離がSeconds_Behind_Masterなわけですが、どこでまで止まっているのかを確かめることも可能です。

バイナリログファイルの中身を確認する場合は、mysqlbinlog コマンドで確認ができます。

# mysqlbinlog mysql-bin.000024

オプションもかなりあります。


Seconds_Behind_Masterに遅れはないが、遅延が発生している場合

そんな場合は、マスター側でもコマンドを叩いて以下を確認します。

show master status \G"
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
             File: mysql-bin.000024
         Position: 198937070
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

発行されている「File」と「Position」から、スレーブ側とどれだけずれているかを確認できます。

つまり、
マスター側 SHOW MASTER STATUS での 「File」 と 「Position」
スレーブ側 SHOW SLAVE STATUS での 「Master_Log_File」と「Read_Master_Log_Pos」 を比較します。

乖離がある場合は、マスターからスレーブへのバイナリログの転送が遅延しています。
つまり、ネットワークで遅延していることが分かります。この場合はネットワーク遅延の原因を探ります。

以上のように状態把握ができるので遅延の場合も焦らず対応しましょう。


参考

ありがとうございます。
引用 MySQLのレプリケーション遅延が発生する場合に確認すべき所とその対策