MySQLでレプリケーション

こんにちは松田です。

MySQLのレプリケーション機能を使用することによって

マスターと同じデータを持ったスレーブ（主に読み込み用）を作成することができます。

各バージョン

  • CentOS 6.8
  • MySQL 5.6

仕組み

レプリケーションの処理はスレーブ側のIOスレッドとSQLスレッドが行います。

  • スレーブIOスレッドがマスター側に接続する。
  • マスター側のBinlogダンプスレッドがバイナリログに書かれたSQLをスレーブIOスレッドに送信する。
  • スレーブIOスレッドは受け取ったSQLをローカルのリレーログに書き込む。
  • スレーブSQLスレッドがリレーログのSQLを読み取り実行する。

このようにしてマスター側で実行されたSQLと同じSQLがスレーブ側でも実行される為、データの同期が実現できます。



注意点としてこのレプリケーションは非同期です。

そのためマスターが更新されてからスレーブに反映されるまでタイムラグがあります。

（通常は一瞬でスレーブに反映されますが、重いSQLや大量のSQLを実行したりするとレプリケーション遅延が発生します。）

レプリケーションに必要な設定

マスター側にレプリケーション用アカウントを用意します。

# 192.168.0.*からアクセスできるrep1ユーザを追加
grant replication slave on *.* to rep1@'192.168.0.%' identified by 'パスワード';

マスター側ではバイナリログを出力する必要がある為、その設定をします。（my.cnf）

log_bin    = mysql-bin

マスター/スレーブのserver_idを異なる値に設定する必要があります。（my.cnf）

server_id  = 10   # マスター側
server_id  = 20   # スレーブ側

レプリケーション手順

レプリケーション前にマスター/スレーブのデータは同じ状態にする必要がある為、

マスター側からフルダンプを取得して、スレーブ側にリストアします。



マスター側からダンプ

mysqldump --single-transaction --all-databases --events --master-data=2 > mysql1.dump

スレーブ側にリストア

mysql < mysql1.dump
※リストア前にスレーブ側のデータは初期化しておいてください。

現在のマスター側のバイナリログのポジションを取得します。

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000197
         Position: 120
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

取得したポジションからレプリケーションをスタートします。

mysql> CHANGE MASTER TO MASTER_HOST='マスターのIP',MASTER_USER='rep1', MASTER_PASSWORD='パスワード', MASTER_LOG_FILE='mysql-bin.000197', MASTER_LOG_POS=120;
mysql> start slave;

もしダンプ取得以降にマスター側のデータを更新している場合は、

ダンプファイル（mysql1.dump）に書かれたポジションを使用します。

※mysqldumpでmaster-data=2を指定するとダンプ時のポジションがdumpファイルの先頭付近にコメントで記載されます。

確認

レプリケーションの設定が完了したらうまくいっているか確認します。

スレーブ側でshow slave statusを打つとレプリケーションの状態が確認できます。

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 【マスターのIP】
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000197
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000197
             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: 457
              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: 10
                  Master_UUID: f0c3f79f-5d3b-11e6-b2d2-005056823b88
             Master_Info_File: mysql.slave_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)

Slave_IO_RunningとSlave_SQL_RunningがYesで、Errorに何も出ていなければ成功です！

終わり

無事にレプリケーションすることができました。

レプリケーションによって負荷分散や高可用性が実現できますのでぜひ活用してみてください。

関連記事

TOP
TOP