MySQL レプリケーションの完全ガイド｜セットアップ手順、トラブルシューティング、運用管理│MySQL & MariaDBの世界

1. MySQL レプリケーションとは？その概要と用途

MySQL レプリケーションは、データベースのコピーをリアルタイムで他のサーバに同期させる機能です。これにより、データベースの冗長性やパフォーマンスを高めることが可能になります。以下に、MySQL レプリケーションがどのようなシーンで利用されるか、またその仕組みについて詳しく解説します。

MySQL レプリケーションの概要

MySQL レプリケーションは、マスターサーバーとスレーブサーバーの構成により、データベースの内容を複数のサーバー間で共有します。具体的には、マスターサーバーがバイナリログに記録したデータ更新を、スレーブサーバーが読み取り反映することでデータの同期が行われます。これにより、マスターサーバーに障害が発生してもスレーブサーバーに切り替えることでサービスの継続が可能です。

MySQL レプリケーションの用途

MySQL レプリケーションは、以下のような用途で広く活用されています。

高可用性の確保：万が一の障害時にスレーブサーバーを利用することでダウンタイムを最小限に抑えることができます。
負荷分散：読み取り専用のクエリをスレーブサーバーに振り分けることで、マスターサーバーの負荷を分散します。
データ保全とバックアップ：レプリケーションはリアルタイムにデータを複製するため、バックアップとしての利用も可能です。

レプリケーションの種類

MySQL レプリケーションには、データの同期方式により以下の種類があります。

非同期レプリケーション：マスターがスレーブに更新情報を送信するタイミングを待たずに処理を進めるため、高速な応答が可能です。しかし、障害時にはデータの一部がスレーブに届かないことがあります。
準同期レプリケーション：スレーブ側にデータが反映されることを確認してから処理を進めるため、非同期よりも信頼性が高いですが、応答速度はやや遅くなります。

次のセクションでは、MySQLレプリケーションの基本概念であるバイナリログやGTIDについて説明します。

2. MySQL レプリケーションの基本概念

MySQL レプリケーションを理解するためには、レプリケーションにおいて重要な役割を果たすバイナリログやGTID（Global Transaction ID）の役割について把握することが重要です。これらの要素は、データが正確に複製されるための基盤となります。

マスターとスレーブの役割

MySQLレプリケーションでは、マスターサーバーとスレーブサーバーがそれぞれ異なる役割を担います。マスターサーバーは、データの更新内容をバイナリログに記録し、その内容をスレーブに配信します。スレーブサーバーは、マスターから受け取ったログを適用し、データを更新します。これにより、スレーブはマスターの最新のデータと同じ内容を保持できるのです。

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

MySQLレプリケーションの基盤には、次の2つのログが利用されます。

バイナリログ（Binary Log）

バイナリログは、マスターサーバー上でのデータ更新（INSERT、UPDATE、DELETEなど）を記録したものです。これにより、スレーブサーバーがマスターと同様のデータ状態を保てるようになります。

リレーログ（Relay Log）

リレーログは、スレーブサーバーがマスターから受け取ったバイナリログを自分のシステム上に保存したものです。スレーブのSQLスレッドはこのリレーログを順に実行し、データの変更を反映します。

GTID（Global Transaction ID）とは

GTIDは、各トランザクションに一意のIDを割り当てる仕組みで、複数のスレーブでの同期の整合性を保つのに役立ちます。GTIDを使用することで、バイナリログの位置指定が不要になり、マスターから取得されていないトランザクションのみを自動的にスレーブに適用できるため、管理が大幅に簡略化されます。

GTID の利点

一意の識別：各トランザクションには一意のGTIDが付与されるため、どのトランザクションが適用済みであるかが明確になります。
復旧が容易：GTIDを用いることで、マスターやスレーブが再起動しても、適用されていないトランザクションのみが再適用されます。
運用管理の効率化：複数のスレーブサーバーが存在する大規模環境でも、トランザクションの整合性を保ちながら簡単に管理が可能です。

GTIDを使用するには、gtid_mode=ONおよびenforce_gtid_consistency=ONの設定が必須です。マスターおよびスレーブにおいて、これらの設定を行うことで、GTIDによるレプリケーションを有効にできます。

次のセクションでは、具体的なMySQLレプリケーションのセットアップ手順について説明します。

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

ここでは、MySQLレプリケーションをセットアップするための手順について詳しく解説します。以下の手順に従うことで、マスターとスレーブの基本的な構成を行い、データのリアルタイム同期を実現します。

マスターサーバーの設定

まず、マスターサーバーの設定ファイル（通常はmy.cnfまたはmy.ini）を編集して、バイナリログの有効化とサーバーIDの設定を行います。

設定ファイルの編集

以下の設定を[mysqld]セクションに追加し、サーバーIDを一意の値（例：1）に設定します。

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

server-idは各サーバーで異なる一意の番号を指定する必要があり、log-binはバイナリログの有効化を意味します。

レプリケーションユーザーの作成

マスターサーバーにレプリケーション専用のユーザーを作成し、必要な権限を付与します。

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

このユーザーはスレーブサーバーからマスターのデータにアクセスするために必要です。

マスターの状態確認

現在のバイナリログファイルとポジション（ログの位置）を確認します。この情報はスレーブサーバーの設定で必要になります。

   SHOW MASTER STATUS;

このコマンドで表示されるFile（ログファイル名）とPosition（位置）は、スレーブ側の設定で使用します。

スレーブサーバーの設定

次に、スレーブサーバーの設定ファイルを編集し、サーバーIDとマスターの情報を設定します。

設定ファイルの編集

スレーブサーバーもserver-idを一意に設定します（例：2）。サーバーIDはマスターサーバーと異なる番号を指定します。

   [mysqld]
   server-id=2

スレーブサーバーでのデータ書き込みを防ぐために、read_only=ONを設定することも一般的です。

マスターの情報をスレーブに設定

スレーブサーバーで以下のコマンドを実行し、マスターのホスト名、ユーザー、バイナリログファイル名、ポジションを指定します。

   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には、先ほどマスターで確認した値を入力します。

レプリケーションの開始

スレーブサーバーで以下のコマンドを実行し、レプリケーションを開始します。

   START SLAVE;

レプリケーションの状態確認

マスターとスレーブ間でレプリケーションが正しく設定されているか確認します。

マスターの状態確認

  SHOW MASTER STATUS;

スレーブの状態確認

  SHOW SLAVE STATUS\G;

Slave_IO_RunningとSlave_SQL_RunningがYesと表示されていれば、レプリケーションが正常に稼働しています。

次のセクションでは、MySQLレプリケーションの応用的な設定方法について解説します。非同期と準同期レプリケーションの違いや、GTIDを活用した設定手順に触れていきます。

4. レプリケーションの種類と応用

MySQLレプリケーションには、データの同期方式によって非同期レプリケーションと準同期レプリケーションの2種類があります。それぞれの特徴と利用シーンに応じた選択基準を理解することで、システムの性能と信頼性を高めることが可能です。また、ここではGTID（Global Transaction Identifier）を活用したレプリケーション設定の利点についても説明します。

非同期レプリケーションと準同期レプリケーションの違い

1. 非同期レプリケーション

非同期レプリケーションは、マスターサーバーがトランザクションを完了した時点で、すぐにクライアントに応答を返します。つまり、スレーブサーバーへのデータの同期が遅延している間にも、マスターは新しいリクエストを処理することができます。そのため、応答性能に優れており、負荷分散を目的としたシステムに適しています。しかし、障害発生時には、スレーブサーバーに反映されていないデータが失われる可能性がある点に注意が必要です。

2. 準同期レプリケーション

準同期レプリケーションは、マスターサーバーがスレーブサーバーへのデータ転送が完了したことを確認した後に、クライアントへ応答を返します。これにより、データの整合性が向上しますが、スレーブへの反映を待つ分、トランザクションの応答時間が長くなる可能性があります。準同期レプリケーションは、高いデータ整合性が求められるシステムや、データの信頼性を最優先にしたい環境に適しています。

GTIDを活用したレプリケーション

GTID（Global Transaction Identifier）は、各トランザクションに一意のIDを付与し、マスターおよびスレーブでトランザクションの整合性を保つ仕組みです。GTIDを有効にすることで、従来のバイナリログ位置指定型のレプリケーションと比べ、レプリケーションの管理が容易になります。

GTID の利点

データ整合性の向上：GTIDにより、スレーブ側で適用されていないトランザクションを自動的に認識できるため、データ整合性が保たれやすくなります。
レプリケーション管理の簡略化：GTIDを使用すると、マスターやスレーブの切り替えや復旧作業が効率的に行えます。バイナリログの位置を指定する必要がなくなるため、管理がシンプルになります。

GTID レプリケーションの設定

GTIDを活用するには、マスターとスレーブの設定ファイルに以下のオプションを追加し、有効化する必要があります。

マスターサーバーの設定

[mysqld]
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency=ON

スレーブサーバーの設定

[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
read_only=ON

GTIDを有効にした環境では、スレーブに対してCHANGE MASTER TOコマンドを使用してマスターの情報を設定するだけで、GTIDによるレプリケーションが自動的に行われます。

次のセクションでは、MySQLレプリケーションのメンテナンス方法と運用管理における監視のポイントについて解説します。

5. レプリケーションのメンテナンスと監視

MySQLレプリケーションを適切に運用するためには、定期的なメンテナンスと監視が欠かせません。このセクションでは、レプリケーションが正常に動作しているかを確認するためのコマンドや、一般的なエラーに対する対処方法について解説します。

レプリケーションのステータス確認方法

レプリケーションの状態を把握するために、以下のコマンドを使用して、マスターとスレーブ間の同期状況を確認します。

マスターの状態確認

マスターサーバーでのレプリケーション状態は、SHOW MASTER STATUSコマンドで確認できます。このコマンドにより、現在のバイナリログファイル名やポジション（位置）が表示され、スレーブに渡されるべき最新の更新内容を確認できます。

SHOW MASTER STATUS;

このコマンドの出力には、以下のような項目が含まれます。

File：マスターが出力している現在のバイナリログファイル名
Position：バイナリログ内での現在の位置
Binlog_Do_DBとBinlog_Ignore_DB：レプリケーション対象のデータベース

スレーブの状態確認

スレーブサーバーのレプリケーション状況は、SHOW SLAVE STATUSコマンドで確認できます。このコマンドの結果には、スレーブサーバーが正常に動作しているかを判断するための情報が含まれます。

SHOW SLAVE STATUS\G;

重要な項目として、以下が挙げられます。

Slave_IO_RunningとSlave_SQL_Running：どちらもYesであれば、スレーブが正常に稼働していることを示します。
Seconds_Behind_Master：スレーブがマスターにどれだけ遅れているか（秒数）を示す。通常、この値が0であることが理想です。

レプリケーションのトラブルシューティング

レプリケーションの運用中に発生しやすい問題には、接続エラーやデータ不整合などが含まれます。以下は、一般的なエラーメッセージとその対処法です。

1. 接続エラー

Slave_IO_RunningがNoとなっている場合、スレーブがマスターに接続できていないことを意味します。以下の対処法を試してください。

マスターサーバーのホスト名やIPアドレスの確認：マスターのアドレスが正しいか確認します。
ファイアウォールの設定確認：必要なポート（通常は3306）が開いていることを確認します。

2. データ不整合

Last_Errorにエラー内容が記載されている場合、マスターとスレーブ間のデータ不整合が発生している可能性があります。データ不整合が発生した際は、スレーブを一旦停止して修正が必要です。

STOP SLAVE;
# 修正後に再開
START SLAVE;

3. 遅延の解消

スレーブが遅延する原因には、スレーブのハードウェア性能やネットワークの問題が考えられます。必要に応じて、スレーブの構成を強化することで改善できる場合もあります。

次のセクションでは、レプリケーションにおけるトラブルの詳細とその解決策についてさらに掘り下げます。

6. よくあるトラブルとその対処方法

MySQLレプリケーションでは、運用中にさまざまなトラブルが発生することがあります。ここでは、よくあるトラブルとその対処方法について詳しく説明します。問題を早期に発見し、適切に対処することで、システムの安定稼働を維持することが可能です。

1. Slave_IO_Running が停止している場合

現象：SHOW SLAVE STATUSコマンドの出力で、Slave_IO_RunningがNoになっている場合、スレーブがマスターに接続できない状態を示しています。

原因と対策：

ネットワークの問題：ネットワーク接続に問題がある場合、スレーブがマスターにアクセスできなくなります。ファイアウォールの設定を確認し、マスターにアクセス可能な状態かを確認します。
マスターのホスト名またはIPアドレスの設定ミス：CHANGE MASTER TOで指定したホスト名またはIPアドレスが間違っていないか確認してください。
ユーザー権限の問題：マスター側で設定したレプリケーションユーザーに十分な権限がない場合も接続に失敗します。GRANT REPLICATION SLAVEで正しい権限が付与されているか確認しましょう。

2. スレーブのデータ不整合

現象：スレーブとマスターでデータが一致しない場合、スレーブのデータが不整合な状態になることがあります。

原因と対策：

データの手動修正：不整合が発生した場合、スレーブを停止し、問題のトランザクションを手動で修正します。修正後にスレーブを再開することで、レプリケーションを正常に戻せます。
STOP SLAVE; # 必要に応じてデータを修正 START SLAVE;
データの再同期：大規模な不整合が発生している場合、マスターからデータのフルバックアップを取得し、スレーブに再同期を行うことで解決できます。

3. レプリケーションの遅延

現象：SHOW SLAVE STATUSの出力でSeconds_Behind_Masterが0でない場合、スレーブがマスターから遅延していることを示します。通常、この値が小さいほど理想的です。

原因と対策：

スレーブのハードウェア性能：スレーブのサーバースペックが低い場合、処理が追いつかずに遅延が発生することがあります。ハードウェアのアップグレードが効果的です。
クエリの最適化：マスターから送られるクエリがスレーブ上で実行する際に時間がかかる場合、遅延が発生します。インデックスの追加やクエリの最適化を行い、処理時間を短縮することが有効です。

4. レプリケーションユーザーの権限エラー

現象：Last_Errorに権限に関するエラーメッセージが表示される場合、スレーブがマスターへの接続に必要な権限を持っていない可能性があります。

原因と対策：

再設定による権限付与：マスター上で適切な権限を持つユーザーが作成されているか確認し、必要であれば再設定を行います。
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'スレーブのIPアドレス'; FLUSH PRIVILEGES;

5. バイナリログの肥大化

現象：マスターのバイナリログが肥大化し、サーバーのディスク容量が圧迫されることがあります。

原因と対策：

バイナリログのローテーション：定期的にバイナリログを削除またはアーカイブすることで、肥大化を防ぎます。expire_logs_daysを設定することで、一定期間経過したログを自動削除することが可能です。
SET GLOBAL expire_logs_days = 7; # 7日以上のログを削除

このように、MySQLレプリケーションでよくあるトラブルとその解決策を把握することで、スムーズな運用管理が可能になります。次のセクションでは、記事のまとめとして、レプリケーション運用のポイントを振り返ります。

7. まとめ

MySQL レプリケーションは、データの整合性やシステムの信頼性を高めるために重要な機能です。本記事では、MySQLレプリケーションの基本概念からセットアップ手順、運用管理における監視やトラブルシューティングまでを詳しく解説しました。最後に、レプリケーションの運用管理における重要なポイントを以下にまとめます。

重要なポイントの振り返り

レプリケーションの種類と選択

非同期レプリケーションは応答速度に優れ、負荷分散に最適ですが、信頼性を求める場合には準同期レプリケーションが適しています。システムの要件に応じて適切な方式を選択しましょう。

GTIDの有効活用

GTIDを活用することで、バイナリログの位置指定を必要とせず、スムーズなトランザクション管理が可能です。特に、複数のスレーブがある環境や障害復旧が重要なシステムにおいて役立ちます。

定期的なステータスの確認

SHOW MASTER STATUSやSHOW SLAVE STATUSコマンドを用いて、マスターおよびスレーブの稼働状況を定期的に監視することが重要です。異常が検知された場合には迅速に対処することで、データ不整合や遅延のリスクを最小限に抑えられます。

一般的なトラブルシューティングの習得

スレーブの接続エラー、データ不整合、遅延など、MySQLレプリケーションには特有のトラブルが発生しがちです。それぞれの問題に対する基本的な解決方法を理解しておくことで、運用中のトラブル対応がスムーズに行えます。

バイナリログの管理

バイナリログが肥大化するとサーバーのディスク容量が圧迫されるため、expire_logs_days設定を活用して自動削除を設定し、定期的にメンテナンスを行うことが推奨されます。

MySQL レプリケーションは一度設定すれば終わりではなく、日々の監視と適切なメンテナンスが欠かせません。定期的に状態を確認し、必要に応じて設定の見直しを行うことで、信頼性の高いデータベースシステムを構築・維持することができます。

本記事がMySQLレプリケーションの理解と実装に役立つものとなれば幸いです。今後のレプリケーション運用がスムーズであることを願っています。

MySQL レプリケーションの完全ガイド｜セットアップ手順、トラブルシューティング、運用管理

1. MySQL レプリケーションとは？その概要と用途

MySQL レプリケーションの概要

MySQL レプリケーションの用途

レプリケーションの種類

2. MySQL レプリケーションの基本概念

マスターとスレーブの役割

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

GTID（Global Transaction ID）とは

GTID の利点

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

マスターサーバーの設定

スレーブサーバーの設定

レプリケーションの状態確認

4. レプリケーションの種類と応用

非同期レプリケーションと準同期レプリケーションの違い

1. 非同期レプリケーション

2. 準同期レプリケーション

GTIDを活用したレプリケーション

GTID の利点

GTID レプリケーションの設定

5. レプリケーションのメンテナンスと監視

レプリケーションのステータス確認方法

マスターの状態確認

スレーブの状態確認

レプリケーションのトラブルシューティング

1. 接続エラー

2. データ不整合

3. 遅延の解消

6. よくあるトラブルとその対処方法

1. Slave_IO_Running が停止している場合

2. スレーブのデータ不整合

3. レプリケーションの遅延

4. レプリケーションユーザーの権限エラー

5. バイナリログの肥大化

7. まとめ

重要なポイントの振り返り

MySQLのrootパスワード変更方法｜手順・セキュリティ対策を徹底解説

MySQLでSQLファイルを実行する方法｜効率的なデータ操作と開発の秘訣