レンタルサーバー・自宅サーバー設定・構築のヒント
レンタルサーバー・自宅サーバー設定・構築のヒント - レンタルサーバー・自宅サーバーの設定・構築情報を公開しています。

mysqlのレプリケーションを使う(3) エラー1062の対処

2011年4月6日 2015年5月16日
mysql troubleshooting

以前に

で、mysqlのレプリケーション(replication) について記事にしました。
今回は、そのmysqlのレプリケーションでエラーストップしたときの対処についてです。

特に、今回は、単純なレプリケーションの取扱の誤りであるスレーブ側への書き込みをしてしまった場合のエラーストップの対処を考えてみたいと思います。

レプリケーション(replication)とは、
直訳のとおり複製を意味します。(レプリカ(replica)と語源は一緒なので、こちらがピンとくるかもしれません)

mysqlでレプリケーションと言うと、マスター、スレーブのそれぞれのmysqlサーバーを構築することに他なりません。
マスター1台に対して、スレーブ複数台というのが一般的なmysqlサーバーの構成になります。

一般的には、マスター mysql サーバー でデータベースの更新処理(書き込み)を受け持ち、スレーブ側で参照処理(読み込み)を受け持つことで負荷を分散させたり、バックアップデータベースを作成するなど行うことができます。
レプリケーション
上の図は、その典型的なレプリケーションの例を示したものです。
ユーザは、1台のサーバーにアクセスしているつもりですが、実際のデータベースへの書き込み処理は、バックにあるマスターサーバー側で処理され、データベースの読み込み処理は、フロントのスレーブサーバーで処理されます。
特に参照がメインのサーバーでは、かなりの負荷分散が期待されます。

レプリケーションの基本

MySQLに限らずレプリケーションの基本を少しおさらいしておきます。
以下の図は、簡単なレプリケーションの関係図を表現しています。

mysqlのレプリケーションの構成

ここで注意すべき点は、
書き込みは、マスターデータベースへ行うこと。
読み込みは、すべてのデータベースで行うことができること。

図では、スレーブデータベースでの読み込みを意図していますが、設定によりマスターでも読み込めます。(一般的にそうします。)

肝心なのは、書き込みです。
書き込みは、常にマスターデータベースにのみ実施されることに注意する必要があることです。

マスターデータベースへ書き込むことで、マスターデータベースは、登録されているスレーブデータベースへコピー(レプリケーション)しようとします。
スレーブデータベースが多ければ、それだけ、ユーザ(アプリケーション)側ですべてのデータベースへの変更を意識する必要がなくなり、効率的でもあります。

一番ありがちなエラーは、スレーブ側に書き込んでしまうケースです。自分もよく失敗しました。今でもレプリケーションの設定をしているのを忘れて書き込んでしまうことも・・・・。

スレーブデータベースでもデータベースに変わりはありませんから、書き込み処理を受け付けますし、SQLに誤りがなければ正常に書きこめてしまいます。

レプリケーションでエラーを発生させてみる

先に書いたようにレプリケーションのスレーブ側に書き込みをすると、マスターとスレーブにデータの不一致が検知されスレーブ側では、同期(レプリケーション)処理を停止します。
これを簡単に再現してみます。

マスターデータベースで以下のようにテーブルを作成して、データを設定してみます。

mysql> CREATE TABLE TEST_DATA (
    -> NO INTEGER PRIMARY KEY AUTO_INCREMENT,
    -> TITLE VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO TEST_DATA (TITLE) VALUES('test1');
Query OK, 1 row affected (0.03 sec)
CREATE TABLE TEST_DATA ( NO INTEGER PRIMARY KEY AUTO_INCREMENT, TITLE VARCHAR(255) );

次に、
スレーブデータベースでマスタで更新された情報が反映されたか確認してみます。

mysql> SELECT * FROM TEST_DATA;
+----+-------+
| NO | TITLE |
+----+-------+
|  1 | test1 |
+----+-------+
1 row in set (0.00 sec)

こんな感じで情報がちゃんとスレーブ側に伝わっていればOK。

もし、情報が表示できない場合は、スレーブが停止している可能性があります。まずは、動作しているかshow slave status \G;で確認しましょう。
スレーブデータベースで以下のように表示されればちゃんと動作しています。
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxx.xxx.xxx.xxx
                  Master_User: slave-user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.000001
          Read_Master_Log_Pos: 394
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 537
        Relay_Master_Log_File: bin-log.000001
             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: 394
              Relay_Log_Space: 693
              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)
上記表示項目のSlave_SQL_RunningYesとなっていればOK。

さあ次にエラーを発生させてみましょう。
スレーブデータベースで先に作成したテーブルを更新してしまえばエラーになります。

mysql> INSERT INTO TEST_DATA (TITLE) VALUES('test2');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM TEST_DATA;
+----+-------+
| NO | TITLE |
+----+-------+
|  1 | test1 |
|  2 | test2 |
+----+-------+
2 row in set (0.00 sec)

こんな感じで情報がちゃんとスレーブ側でレコードの追加はできてしまいますね。
同じようにマスターデータベースでもレコードの追加を行います。

mysql> INSERT INTO TEST_DATA (TITLE) VALUES('test3');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM TEST_DATA;
+----+-------+
| NO | TITLE |
+----+-------+
|  1 | test1 |
|  2 | test3 |
+----+-------+
2 row in set (0.00 sec)

これで、マスターとスレーブで完全に不一致が発生します。
スレーブデータベースの状態を確認します。

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxx.xxx.xxx.xxx
                  Master_User: slave-user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.000001
          Read_Master_Log_Pos: 531
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 537
        Relay_Master_Log_File: bin-log.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '2' for key 'PRIMARY'' on query. Default database: 'bgcom'. Query: 'INSERT INTO TEST_DATA (TITLE) VALUES('test3')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 394
              Relay_Log_Space: 830
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '2' for key 'PRIMARY'' on query. Default database: 'bgcom'. Query: 'INSERT INTO TEST_DATA (TITLE) VALUES('test3')'
1 row in set (0.01 sec)

上記表示項目のSlave_SQL_RunningNoとなって完全に止まってますね。
Errno : 1062は、レコードが重複していてアップデートできない状態であることを意味します。
つまり、マスターで更新されたレコードがスレーブに既にあるのでエラーとなっています。

レプリケーションでエラーを対処してみる

上記のエラー対処方法は、以下のような手順で行います。

  1. まずは、マスターデータベースを正しい情報に設定する

    これは、上記に例でいうとスレーブ側で更新したレコードとマスター側で更新したレコードがあります。
    この更新した情報が、いずれも正しい情報であるなら、マスター側を再設定する必要があるでしょう。
    また、誤ってスレーブ側を更新してしまったというだけなら、スレーブ側を該当するレコードを削除すれば良いでしょう。

    まず大事なことは、マスターデータベースを正しい情報に設定することです。

    ここでは、誤ってスレーブにデータを設定してしまったという例なので、マスター側は、そのままで正しいことになります。
    スレーブ側の(誤って追加した)データを削除すればOKのはずですね。
    AUTO_INCREMENTが気になるところですが、まず問題になることはありません。

  2. スレーブを起動する

    エラーで止まっているスレーブを起動します。

上記のレプリケーションでエラーを発生させてみるで具体的なエラーについて、どのようにエラーを対処したかは、以下のような感じです。
これは、すべてスレーブ側で実施した様子です。

mysql> DELETE  FROM TEST_DATA WHERE NO=2;
Query OK, 1 row affected (0.00 sec)

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

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxx.xxx.xxx.xxx
                  Master_User: slave-user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.000001
          Read_Master_Log_Pos: 531
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 674
        Relay_Master_Log_File: bin-log.000001
             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: 531
              Relay_Log_Space: 830
              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)

ERROR:
No query specified

mysql> SELECT * FROM TEST_DATA;
+----+-------+
| NO | TITLE |
+----+-------+
|  1 | test1 |
|  2 | test3 |
+----+-------+
2 rows in set (0.00 sec)

mysql>

最初にスレーブ側で誤って設定してしてしったデータを削除しています。

その後、エラーで停止してしまったスレーブを起動しています。

あとは、確認しているだけなので、手順としては、上の2つだけです。

よくある対処方法の誤った使用方法

MySQLのレプリケーションのエラー1062を対処する方法として、以下のような記載があります。

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

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
Note:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;は、エラーを1回無視しなさいということです。
※START SLAVE は、START SLAVE SQL_THREAD; としていることも多い。


これは、とりあえずエラーを回避する上だけで、問題の本質を無視した対処です。
ここでの例では、この対処は誤りになります。

ここで誤って設定してしまったスレーブ側のデータを対処せずに、エラーをスキップさせていることが大きな問題です。

つまり、この対処では、誤って設定してしまったスレーブ側のデータはそのまま残ります。
マスター側とスレーブ側は、アンマッチとなるわけです。

SET GLOBAL SQL_SLAVE_SKIP_COUNTER は、非常に便利なものですが、よく理解した上で利用しましょう。

レプリケーションでエラーをスレーブのリセットで対処してみる

先のエラー対処方法は、自力でデータを修復するようなものです。
実際のトラブルでは、こんなに簡単なパターンは、ほとんどありません。

とりあえず、このスレーブ側のエラーを回避するのは、マスター側にデータを合わせてしまうのは、最も手っ取り早い手順の場合があります。

この手順は、mysqlのレプリケーションを使う のスレーブの設定とほとんど同じです。
その手順を簡単に解説してみます。


  1. マスターのデータバックアップ

    データの書き込みを一時停止します。

    mysql> FLUSH TABLES WITH READ LOCK;
    Query OK, 0 rows affected (0.01 sec)

    データのバックアップをtarで1ファイルに固めます。

    $ tar cvfz mysql-bin-backup.tar.gz /var/lib/maysql
           :

    バックアップしたデータベースの状態を確認しておきます。

    mysql> SHOW MASTER STATUS;
    +----------------+----------+--------------+------------------+
    | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +----------------+----------+--------------+------------------+
    | bin-log.000001 |      901 |              |                  |
    +----------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

    データの書き込みを一時停止を解除します。

    mysql> UNLOCK TABLES;
    Query OK, 0 rows affected (0.00 sec)
  2. スレーブでリストア

    MySQLを停止します。

    $ /etc/init.d/mysqld stop
    mysqld を停止中:                                           [  OK  ]

    マスターでバックアップしたデータを展開(リストア)します。

    $ tar xvfz mysql-bin-backup.tar.gz /var/lib/maysql
           :

    MySQLを起動します。

    $ /etc/init.d/mysqld start
    mysqld を起動中:                                           [  OK  ]

    MySQLのスレーブをリセットし起動します。

    mysql> stop slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> reset slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='slave-user', MASTER_PASSWORD='slave-password',  MASTER_LOG_FILE='bin-log.000001', MASTER_LOG_POS=901;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show slave status \G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: xxx.xxx.xxx.xxx
                      Master_User: slave-user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: bin-log.000001
              Read_Master_Log_Pos: 901
                   Relay_Log_File: mysqld-relay-bin.000002
                    Relay_Log_Pos: 249
            Relay_Master_Log_File: bin-log.000001
                 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: 901
                  Relay_Log_Space: 405
                  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)
    
    ERROR:
    No query specified
手順は、mysqlのレプリケーションを使う のスレーブの設定とほとんど同じなのが理解いただけたと思います。
ただ、RESET SLAVE をお忘れなく。
RESET SLAVE を実行しないとエラーが取れません。
エラーの対処は、ざっとこんな感じです。

エラーを無視するだけなら、

my.conf ( [mysqld]セクション )に

slave-skip-errors = 1062

を追記して、MySQLをリスタートすれば、すべての1062エラーは無視されます。

いろいろな対処がありますが、エラーを発生させないのが一番です。
ご利用のブラウザは、広告ブロック(AdBlockなど) が適用となっていませんか?
このサイトでは、コンテンツの一部が非表示、あるいは、コメント、お問い合わせの投稿ができない検索ができないことがあります。


関連記事 :

mysqlのレプリケーションを使う(5) エラー1236の対処

以前に mysqlのレプリケーションを使う mysqlのレプリケーションを使う(3) エラー1062の対処 で、my ...

mysqlのレプリケーションを使う

今回は、mysqlのレプリケーション(replication)です。 レプリケーション(replication)とは、 直訳 ...

mysqlでマルチマスター レプリケーションを設定してみる

以前の記事 mysqlのレプリケーションを使う で mysqlを使ったレプリケーションの設定を行ってみました。 今回は、そのmysqlのレプリ ...

mysqlの準同期レプリケーションを使ってみる

以前の記事 mysqlのレプリケーションを使う で mysqlを使った 簡単なマスタースレーブ型のレプリケーションの設定を行ってみました。 今 ...

MySQLのバイナリログを使ったロールバック(リストア)を行ってみる

以前の記事 MySQLのログの種類とログの仕方を調べてみた(実施例) で、MySQLのログの採取方法について簡単に解説してみました。 その中の ...



コメントを投稿 :

お名前 *

メールアドレス *
( メールアドレスが公開されることはありません。)

サイトアドレス

コメント *

* 印の項目は、入力が必要な項目です。




最近投稿の記事

[ 画像提供元 : Amazon ] 先日、1TBのディスクの入れ替え時にバックアップをとろうとディスクコピーを行いました。 その際 ...

Windows で Linux ファイルシステム Ext4 のディスクをマウントするには? Ext3Fsd が、おそらく、最も簡単なツール ...

今回は、Windows で Compass を使ってみました。 Compass とは、 Sass(サス、Syntactica ...

今回は、Anti Adblock を使ってみました。 Anti Adblock とは、 そもそも Adblock という ウェブ ...

デスクトップ環境でない サーバーで、Webページのキャプチャー画像をコマンドで撮る には、wkhtmltoimage, CutyCapt ...


さくらのVPS 全プラン リニューアルです。(石狩(北海道)も選択可)


root権限ありで ¥685 / 月 ~ と非常にリーズナブルな CPU 1(core)の 512 プランから、 最高 CPU 10(core), メモリ 32(GB), SSD容量 800(GB) までとプランが充実。
ディスクは、SSDとHDDの選択が可能になった他、データセンターは人気の東京、石狩(北海道)となりました。

また、どのプランでも好きなOSが選べます
( CentOS, Fedora, Scientific Linux, FreeBSD, Ubutu, Debian )

管理人もおすすめのVPSです。
試用期間がありますから、一度、お試しを!!

詳しくは、http://vps.sakura.ad.jp/さくらのVPSのサイトへ へどうぞ!!

カテゴリ


Serverman@VPS 完全1ヶ月無料 キャンペーン実施中です。


Serverman@VPS 完全1ヶ月無料 キャンペーン実施中です。
最近、スワップにも対応した Serverman@VPS は、かなりリーズナブルかもです。

  • メモリ1GB~2GBのEntryプラン :月額:490円
  • メモリ2MB~4GBのStandardプラン :月額:980円
  • メモリ4GB~8GBのProプラン :月額:1,980円

新規申し込みで1ヶ月間完全無料となるキャンペーンを実施中です。
Serverman@VPSの特徴は、安さとIPv6対応です。また、初期設定費0円もポイントです。

IPv6でちょっと遊んでみたい方には、おすすめかもしれませんね。最低利用期間もありませんから、気に入らないときは即解約もできます。

Serverman@VPSの詳細については、 http://dream.jp/vps/ Serverman@VPSのサイトへへどうぞ。



KVM採用 ConoHa VPSは、時間単位で借りれる便利なVPSです。


ConoHa VPS は、初期設定費0円最低利用期間無し時間単位で清算可能、 さらに、Web APIを使って自動化を図ることもできる便利なVPSです。

海外サーバー設置も可能で、ローカル接続にも対応と、かなり、機能豊富なサーバーです。

新規ユーザ登録で、クーポンもらえますから、まずは、お試しですね。

ConoHa VPSの詳細については、
http://www.conoha.jp/ へどうぞ。

KVM採用 お名前.com VPS(KVM) 2G プラン 初期設定費無料 キャンペーン 実施です。


メモリ2GBプラン CPU:3core、Disk:200GB
月額:1,153円から (初期設定:1,680円0円)

さくらのVPSがリニューアルされてもなんのその。
1GBメモリ / 2Core を ¥834 – の格安価格で提供中です!
間違いなくスペックからすると割安感ありです。
年間割引時の途中解約で返金がないのは、 ちょっと残念ですが、それでもこの割安感は魅力です。

まずは、お試しですね。

お名前.com VPS(KVM)の詳細については、
http://www.onamae-server.com/vps/ へどうぞ。(お試し期間が15日あります。)



  • ソーシャルブックマーク

  • はてなブックマークへ追加するはてな登録数
ページトップへ
Time : 0.2215 [s]