MySQLでレプリケーション同期がとれなくなった場合の対処

•
このエントリーをはてなブックマークに追加

昨日久しぶりにブログを更新したら、下書きが突然消えたりパーマリンクが消えてしまったり、はたまた下書きが勝手に公開されてしまったりと酷い目にあってしまった。どうにか投稿ができた後も、リロードするたびに最新の投稿がランダムに見えたり見えなかったりしたりして困ったちゃんである。2台のサーバでnginxの設定ズレやキャッシュの有無などを疑ったりしたが、どうやらMySQLのレプリケーションが失敗しているようだ。そこでスレーブ側のサーバでステータスを見てみることにした。

[nullpopopo@SLAVE ~]$ mysql -u root -p

mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
(略)
                Relay_Log_Pos: 121815113
        Relay_Master_Log_File: mysqld-bin.000035
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
(略)
                   Last_Errno: 1396
                   Last_Error: Error 'Operation DROP USER failed for ''@'master.example.com'' on query. Default database: 'mysql'. Query: 'DROP USER ''@'master.example.com''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 121934861
              Relay_Log_Space: 151988270
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
(略)
               Last_SQL_Errno: 1396
               Last_SQL_Error: Error 'Operation DROP USER failed for ''@'master.example.com'' on query. Default database: 'mysql'. Query: 'DROP USER ''@'master.example.com''
1 row in set (0.00 sec)

mysql>

Slave_IO_Running: と Slave_SQL_Running: が両方とも YES になっていると、スレーブは問題なく動作していることになるのだが、ご覧のありさまだ。


Slave_IO_Running: Yes
Slave_SQL_Running: No

Slave_SQL_Running: が No になっていると、クライアントがスレーブサーバに更新系の SQL を発行したなどの原因でSQL スレッドに問題が発生しているとのこと。先ほどのエラー原因からすると、これはブログ記事を投稿する前に、マスター側でもともとスレーブ側にいなかった匿名ユーザを削除したのが原因のようだ。片方のサーバにしかいないユーザだったので、スキップしてもよいだろうと判断。SQLスレッドが停止する原因となったクエリを1つスキップしてSQLスレッドを再開することにしてみる。

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

再度確認してみる。

mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
(略)
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
(略)
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1008
                   Last_Error: Error 'Can't drop database 'test'; database doesn't exist' on query. Default database: ''. Query: 'DROP DATABASE `test`'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 121935573
              Relay_Log_Space: 152015711
(略)

今度はtestデータベースを削除できなかったって言ってる・・・(；´Д`)
これきっと両方のサーバで「せーの」で削除したからか。またもやスキップしてしまおう。

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

さあまた確認だ。

mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.1
(略)
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
(略)
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1008
                   Last_Error: Error 'Can't drop database 'himitsu_no_db'; database doesn't exist' on query. Default database: ''. Query: 'DROP DATABASE `himitsu_no_db`'
(略)

あばばばば(；´Д`)
今度は使わなくなったテスト用DBを削除したときのだ。マスターからもスレーブからもDROPしてあるので、これまたスキップしていい。

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000037
          Read_Master_Log_Pos: 2630950
               Relay_Log_File: mysqld-relay-bin.000163
                Relay_Log_Pos: 2631096
        Relay_Master_Log_File: mysqld-bin.000037
             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: 2630950
              Relay_Log_Space: 2631398
              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: 
1 row in set (0.00 sec)

ｷﾀ━━━━(ﾟ∀ﾟ)━━━━ !!!!!なおったー！そこには元気に走り回るmysql-serverの姿が。「もうSLAVEでDBのDROPなんてしないよ！」と元気に語るnullpopopoであった。

※参考サイト
MySQL レプリケーションの設定 - maruko2 Note.
現場指向のレプリケーション詳説

More from my site