MySQL レプリケーションの基本

MySQL レプリケーションは、データベースのデータを自動的に複製するプロセスで、高可用性や負荷分散、バックアップの目的で利用されます。この記事では、初心者向けに MySQL レプリケーションの基本を説明し、簡単なセットアップ手順を紹介します。

目次

MySQL レプリケーションとは
マスター・スレーブ構成
レプリケーションの流れ
スレッドの役割
master.info と relay-log.info
バイナリログとリレーログ
レプリケーションのセットアップ手順
レプリケーションの状況確認
トラブルシューティング

1. MySQL レプリケーションとは

MySQL レプリケーションは、マスターサーバーからスレーブサーバーへデータを複製するプロセスです。これにより、データの冗長性や高可用性を確保し、読み取り操作の負荷分散を実現します。

2. マスター・スレーブ構成

レプリケーションでは、一つのマスターサーバーと一つ以上のスレーブサーバーを設定します。マスターサーバーはデータの更新を処理し、その変更をスレーブサーバーに伝えます。スレーブサーバーはマスターサーバーのデータ変更を受け取り、データを同期します。

3. レプリケーションの流れ

マスターとスレーブのやり取りは次のように行われます：

マスターで処理された更新系のクエリが逐次、マスターのバイナリログに記録される。
スレーブのI/OスレッドがマスターのBinlog Dumpスレッドに接続する。
マスターのBinlog Dumpスレッドは、バイナリログの内容を送信する。Binlog Dumpスレッドはこの処理を繰り返す。
スレーブのI/Oスレッドは、受け取ったバイナリログをスレーブ内に保存する。これをリレーログと呼ぶ。I/Oスレッドはこの処理を繰り返す。
スレーブのSQLスレッドは、リレーログからクエリを読み取り実行する。SQLスレッドはこの処理を繰り返す。

4. スレッドの役割

スレーブではI/OスレッドとSQLスレッドの2つが稼働しています。

I/Oスレッド：マスターからデータを取得し、リレーログに記録する。
SQLスレッド：リレーログを読み取り、クエリを実行する。

この分離により、データの取得とクエリの実行が独立して行われ、効率的なレプリケーションが可能になります。

5. master.info と relay-log.info

スレーブにはレプリケーションの状態を保持するファイルが2つ作られます。

master.info：マスターのホスト名、接続ユーザー名、マスターのバイナリログファイル名、スレーブのI/Oスレッドがどこまでバイナリログを取得したかを記録します。
relay-log.info：リレーログのファイル名や位置情報を記録し、スレーブのSQLスレッドがどこまでリレーログを処理したかを確認します。

6. バイナリログとリレーログ

バイナリログ：マスターでのデータ更新が記録される。フルバックアップやレプリケーションに使用。
リレーログ：スレーブのI/Oスレッドがマスターのバイナリログをスレーブに保存したもの。必要がなくなると自動的に削除されます。

7. レプリケーションのセットアップ手順

以下に、マスターとスレーブの基本的な設定手順を示します。

マスターサーバーの設定

1.設定ファイルの編集

MySQL の設定ファイル（通常は my.cnf または my.ini）を編集し、以下の設定を追加します：

[mysqld]
server-id=1
log-bin=mysql-bin

server-id は各サーバーで一意の値を指定します。log-bin はバイナリログを有効にする設定です。

2.データベースとユーザーの作成

レプリケーション用のユーザーを作成し、必要な権限を付与します：

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

3.マスターの状態を確認

マスターの現在のバイナリログファイルとポジションを確認します：

SHOW MASTER STATUS;

ここで得られる File と Position の値をメモしておきます。

スレーブサーバーの設定

1.設定ファイルの編集

スレーブサーバーの設定ファイルに以下の設定を追加します：

[mysqld]
server-id=2
server-id はマスターとは異なる一意の値を指定します。

2.レプリケーションの開始

スレーブサーバーで以下のコマンドを実行し、マスターの情報を設定します：

CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS= 123;

MASTER_LOG_FILE と MASTER_LOG_POS には、先ほどマスターで確認した File と Position の値を指定します。

3.レプリケーションの開始

スレーブサーバーでレプリケーションを開始します：

START SLAVE;

8. レプリケーションの状況確認

レプリケーションの状況を確認する方法をいくつか紹介します。

マスターの状況確認

SHOW MASTER STATUS

SHOW MASTER STATUS\G

例：


*************************** 1. row ***************************
            File: mymaster-bin.004
        Position: 135
    Binlog_do_db: 
Binlog_ignore_db:

SHOW MASTER LOGS

SHOW MASTER LOGS;

例：

+------------------+
| Log_name         |
+------------------+
| mymaster-bin.001 |
| mymaster-bin.002 |
| mymaster-bin.003 |
| mymaster-bin.004 |
+------------------+

スレーブの状況確認

SHOW SLAVE STATUS

SHOW SLAVE STATUS\G

例：

*************************** 1. row ***************************
          Master_Host: mymaster
          Master_User: repl
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: mymaster-bin.004
  Read_Master_Log_Pos: 135
       Relay_Log_File: myslave-relay-bin.002
        Relay_Log_Pos: 668
Relay_Master_Log_File: mymaster-bin.004
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db:
  Replicate_ignore_db:
           Last_errno: 0
           Last_error:
         Skip_counter: 0
  Exec_master_log_pos: 135
      Relay_log_space: 668

このコマンドの出力で Slave_IO_Running と Slave_SQL_Running が Yes であることを確認します。

上記のようにして、マスターとスレーブの状態を確認し、レプリケーションが正常に動作しているかを監視することができます。

9. トラブルシューティング

レプリケーションが停止した場合：

SHOW SLAVE STATUS\G; の出力を確認し、エラーメッセージを元に対処します。
ネットワーク接続や権限設定、バイナリログのポジションを再確認します。

データ不整合が発生した場合：

スレーブサーバーを再同期する必要があります。そのためには、スレーブサーバーのデータベースを一度削除し、マスターから最新のデータを再度インポートします。

マスタが停止した場合:

マスタのmysqldが異常終了した、マスタのサーバがOSごとフリーズしたなどなどいろいろな場合が考えられますが、どの場合でも、MySQLのデータが無事であればマスタでmysqldを起動しなおすだけで自動的にスレーブと接続し、レプリケーションが再開されます。

スレーブが停止した場合:

スレーブのmysqldが何らかの原因で停止した場合は、マスタと同じように、データに破損がない限りはmysqldを起動しなおせばレプリケーションが再開されます。

mysqldは正常に稼働しているが、スレーブでのレプリケート処理が停止した場合は、基本的に停止した原因を取り除いてからスレーブでSTART SLAVE文を実行して再開します。

まとめ

以上で、MySQL レプリケーションの基本的な設定と確認方法について説明しました。レプリケーションを正しく設定し、定期的に状態を監視することで、データベースの高可用性と信頼性を向上させることができます。ぜひ参考にしてみてください。