DATABASE
MySQL 分類

MySQL Replication(レプリケーション) - 単方向二重化【1】

投稿者情報

  • 運営局 投稿
  • 投稿日

コンテンツ情報

本文内容

MySQL Replicationを利用してDBMSを片方向に二重化する。

PsNU97a.jpg

 

 

ウェブサーバーの負荷がかかり、L4 を利用してロードバランシングを利用してウェブサーバーの負荷を解決しましたが、DBサーバーの負荷でサイトがスムーズに開く現象が発生しました。

 

という訳で、DBサーバーを二重化する方法はないかと思って探してみたらMySQLのレプリケーションという機能がありました。 この機能を利用するとDBを二重化できるということを知りました。

 

今回は、MySQLのレプリケーションとは何か、レプリケーションを利用してDBを二重化する方法について解説したいと思います。

 

 

そもそも、MySQL Replication(レプリケーション)とは?

 

レプリケーション(Replication)は複製を意味し、2つ以上のDBMSを分けてデータを保存する方法で、使用するための最小構成はMasterSlave構成になります。

 

Master DBMS役割:

ウェブサーバーからデータの書込み·修正·削除を要請があった場合、バイナリログ(Binarylog)を生成し、Slaveサーバーに転送します。

(ウェブサーバーから要請されたデータの書込み·修正·削除の機能をするDBMSとして多用されます。)

 

Slave DBMS役割:

Master DBMSから受け取ったバイナリログ(Binarylog)をデータとして反映します。
(ウェブサーバーからの要請を通じてデータを読み込むDBMSとして多用されます。)

 

MySQL Replication(レプリケーション)の使用目的

 

MySQLリプリケーション(Replication)の使用目的は①リアルタイムのDataバックアップと②複数台のDBサーバーの負荷を分散させる目的で使用されます。

①データのバックアップ

 

例として、Masterサーバーをデータのオリジナルのサーバー、Slaveサーバーをバックアップサーバーといいましょう。

まず、MasterサーバーにDBMSの登録修正アップデータができる次第、Slaveサーバーへ変更されたデータを転送します。 このような過程で、データバックアップができ、また、Masterサーバーの障害が生じた場合、Slaveサーバーに変更して使用することができます。

 

pZgq4ME.png

図で表現すると、まずユーザが使用する際に、発生するクエリをMasterサーバに要請、Masterサーバで発生したクエリをSlaveサーバに転送することになり、バックアップの用途として使うことができます。

 

2.DBMSの負荷分散

サーバがユーザの集中により1台のDBサーバーで対応できない時、MySQLレプリケーション(Replication)を利用して同じDBデータを複数作ることができるので、負荷を分散させることが可能です。

 

CpgNiku.png

図で表現すると、Masterサーバーを登録修正削除で利用されるサーバーとして使用し、Slaveサーバーをデータを読み込む用途に使用すると、DBMSの負荷を分散させることができます。

 


MySQL Replication(レプリケーション) 注意事項

 

MySQL Replication(レプリケーション)を使用するとき、次のような注意事項があります。

必ずお読んだ後に行ってください。

1.互換性のためにReplication(レプリケーション)を使用するMySQLを等しく合わせる(DBバージョンなど)のがいいです。

2.Replication(レプリケーション)を使用するのにやむを得ずMySQLバージョンが異なる場合は、

   Slaveサーバーが上位バージョンでなければなりません。

3.Replication(レプリケーション)稼動時にMasterサーバー、Slaveの順に稼動させなければなりません。

 

 

MySQL Replication(レプリケーション)定 - (Masterサーバー)

 

 

これからMySQL Replication(レプリケーション)を設定し、まずMasterサーバの設定から行います。

MySQLレプリケーションを使用するためには、まずDB、アカウント、レプリケーションアカウントを作成します。
構成情報は以下のとおりとします。

[MasterサーバーDB、アカウント情報]

 

IP : 192.168.143.1(Master), 192.168.143.2(Slave)

DadaBases : repl_db

ID : user1

PW : test123

 

[Replicationアカウント情報]

 

IP : 192.168.143.1 - (Master)

ID : repl_user

PW : test456

 

- MasterサーバーのデータをSlaveサーバーに複製するためにはMySQLアカウントが必要です。

- MySQL root アカウントで使用するのはセキュリティ上よくないので、複製アカウントを作成することをお勧めします。

 

1. MySQL DB、アカウント作成および権限設定

1)  DB生成

mysql> create database repl_db default character set utf8;

 

2)  アカウント生成

mysql> create user user1@'%' identified by 'test123';

 

3)権限設定
 

mysql> grant all privileges on repl_db.* to user1@'%' identified by 'test123';


2. レプリケーションアカウント生成

 

mysql> grant replication slave on *.* to 'repl_user'@'%' identified by 'test456';

 

3. MySQL 設定 - my.cnf

 

vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

 

最初の設置の際と同じ設定になっており、ない場合は新たに追加してください。

 


4. MySQL 再起動

 

# service mysqld restart

 

5. Masterサーバー情報確認

 

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 1487 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 

 

*File:MySQLログファイル
* Position:ログファイル内の読み込み場所
* Binlog_Do_DB:バイナリー(Binary)ログファイル(イベント情報が変更されるファイル)
* Binlog_Ignore_DB:複製除外情報

 

MySQL Replication設定 - (SLaveサーバー)

Slaveサーバーを設置する前にまずアカウントを作成します。

MySQL DB、アカウント作成および権限設定

 

 

1)  DB生成

 

mysql> create database repl_db default character set utf8;

 

2)  アカウント作成

 

mysql> create user user1@'%' identified by 'test123';

 

3)  権限設定

 

mysql> grant all privileges on repl_db.* to user1@'%' identified by 'test123';

 

Slaveサーバ設定をする方法、2つがあります。
mysqlに入って設定する方法とmysql設定ファイル(my.cnf)で設定する方法がありますが、まずmysqlで設定する方法から解説します。

 

1. MySQLに接続して設定

1) MySQL 設定 - my.cnf

 

# vi /etc/my.cnf

[mysqld]
server-id=2
replicate-do-db='repl_db'

 

Server-id:Masterサーバーのserver-idを除いて1~(2^32)-1内の数字で設定してください。


replicate-do-db:複製したいデータベースを意味し、2つ以上のデータベースをする場合、replicate-do-dbを追加します。

 

2)  MySQL復元

  Master DBMSでコピーするデータベースをdumpして復元します。

 

3)  Masterサーバーに接続するための設定

 

mysql> change master to
master_host='192.168.65.148',
master_user='repl_user',
master_password='test456',
master_log_file='mysql-bin.000010',
master_log_pos=1487;

 

MASTER_HOST:MsterサーバーIP入力
MASTER_USER : レプリケーションID
MASTER_PASSWORD:レプリケーションPW
MASTER_LOG_FILE:MASTER STATUS ログファイル名
MASTER_LOG_POS : MASTER STATUSでのposition値

 

 

4)  MySQL再起動

 

# service mysqld restart

 

2.MySQLのmy.cnfで設定する

 

1)  MySQL 設定 - my.cnf

 

[mysqld]
replicate-do-db='repl_db'
master-host=192.168.143.1
master-user=repl_user
master-password=test456
master-port=3306
server-id=2

 

replicate-do-db:

複製したいデータベースを意味し、2つ以上のデータベースをする場合、replicate-do-dbを追加します。

 

master-host:MasterサーバーのIPを入力
master-user:Masterサーバーに生成したレプリケーション(Replication)IDを入力
master-password:Masterサーバーに生成したレプリケーション(Replication)PW入力
master-port:MySQLで使用するポート入力
Server-id:Masterサーバーのserver-idを除いて1~(2^32)-1内の数字で設定してください。

 

2)  MySQL再起動

 

# service mysqld restart

 

MySQL Replicationステータスを確認する

 

MySQLレプリケーション(Replication)が正常完了したら、その状態を確認する必要があります。

次のようにご確認ください。

 

1. Masterサーバーの状態を見る

  スレッドの状態を確認

 

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: repl_user
   Host: 192.168.143.1:38488
     db: NULL
Command: Binlog Dump
   Time: 2434
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
2 rows in set (0.00 sec)

Masterサーバーで上記の内容のようにコマンドを入力すると、Id:1スレッドのSlaveサーバー(192.168.143.1)のrepl_userアカウントに接続されていることが確認できます。

 

2. Slaveサーバーの状態を確認

スレッドの状態を確認

 

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 4294967261
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 90
  State: Waiting for master to send event
   Info: NULL

スレッド1(ld:1)ではMasterサーバーと通信するためのスレッドで、スレッド2(ld:2)はアップデートされた内容を処理するためのSQLスレッドです。 この2つのスレッドでは、エラーが発生してはいけません。

 

スレッドの主要因子の状態を確認

 

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.143.1
                Master_User: repl_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000012
        Read_Master_Log_Pos: 434
             Relay_Log_File: slave-relay-bin.000042
              Relay_Log_Pos: 419
      Relay_Master_Log_File: mysql-bin.000012
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: repl_db,repl_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: 434
            Relay_Log_Space: 419
            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
1 row in set (0.00 sec)

 

Slave_IO_State : Masterサーバーの接続を試み、Masterサーバーからイベントを待機、再接続するかどうかを知らせます。

Master_Host : 接続されたMasterサーバーホストです

Master_User : Masterサーバーを接続する際に使用されるユーザーです。

Master_Port:Masterサーバーを接続する際に使用されるポートです。

Connect_Retry : --master-connect-retry オプションの現在値です。

Master_Log_File:I/Oスレッドで現在読んでいるバイナリログファイル名です。
Read_Master_Log_Pos:I/Oスレッドで現在のMasterサーバーのバイナリログで読んだ場所までの位置です。

Relay_Log_File:SQLスレッドで現在のrelayログファイル名です。

Relay_Log_Pos:SQLスレッドによってRelayログから読み込んで実行した場所までの位置です。

Relay_Master_Log_File : SQLスレッドによって実行された最近のMaster サーバーのバイナリログファイル名です。

Slave_IO_Running:I/Oスレッドが開始し、Masterサーバーが成功的に接続されているかどうかです。

SLave_SQL_Running:SQLスレッドが開始されているかどうかです。

Replicate_Do_DB:Masterサーバーでアップデートされたデータが反映されるDBです。

Replicate_Ignore_DB :省略

Replicate_Do_Table :省略

Replicate_Ignore_Table :省略

Replicate_Wild_Do_Table :省略

Replicate_Wild_Ignore_Table :省略

Last_Errno:直近に使用されたクエリのエラーメッセージの番号にリターンされます。

Last_Error:直近に使用されたクエリのエラーメッセージの番号にリターンされます。

Skip_Counter : 省略。

Exec_Master_Log_Pos : MasterサーバーのバイナリログのRelay_Master_Log_FileからSQLスレッドによって最後のイベント位置です。

Relay_Log_Space:存在するすべてのRelayで構成されています。

Until_Condition :省略

Until_Log_File :省略

Until_Log_Pos:省略

Master_SSL_Allowed:Masterサーバーに接続するためにSlaveによって使用されたSSLパラメータです。

Master_SSL_CA_File : Masterサーバーに接続するためにSlaveによって使用されたSSLパラメータです。

Master_SSL_CA_Path: Masterサーバーに接続するためにSlaveによって使用されたSSLパラメータです。

Master_SSL_Cert:Masterサーバーに接続するためにSlaveによって使用されたSSLパラメータです。

Master_SSL_Cipher:Masterサーバーに接続するためにSlaveによって使用されたSSLパラメータです。

Master_SSL_Key : Masterサーバーに接続するためにSlaveによって使用されたSSLパラメータです。

Seconds_Behind_Master : Masterサーバーで実行されたイベントのタイムスタンプ以降経過時間(秒単位)の数です。

 

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.143.1
                Master_User: repl_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000010
        Read_Master_Log_Pos: 98
             Relay_Log_File: slave-relay-bin.000010
              Relay_Log_Pos: 370
      Relay_Master_Log_File: mysql-bin.000009
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: repl_db,repl_db,repl_db,repl_db
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1007
                 Last_Error: Error 'Can't create database 'repl_db'; database exists' on query. Default database: 'repl_db'. Query: 'create database repl_db default character set utf8'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 233
            Relay_Log_Space: 1123
            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
1 row in set (0.00 sec)

ERROR: 
No query specified

たまに上のようにエラーが発生して、Slaveサーバーが作動しない場合があります。
上のような場合は、Slaveサーバーでエラーが発生すると、エラーが発生した時点からMasterサーバーから更新されたクエリが実行されなくなり、この場合、エラーを渡した後、次のクエリが実行されます。

 

vi /etc/my.cnf
[mysqld]
slave-skip-errors=all

上記のようなオプションを設定して反映してください。 my.cnfで設定を反映した後はMySQLを再起動してください。

関連資料

新着投稿


  • 投稿がありません。

新着書込み


  • 書込みがありません。
アラート 0