はじめに
RDSのリードレプリカはAWSが自動で構築してくれますが、オンプレや自前のMySQLサーバーでレプリケーションを構築する機会もあります。
今回は『MySQL運用管理 [実践] 入門』第5章を読んで、ポジションレプリケーションとGTIDレプリケーションの両方の構築手順を整理しました。
前提
| 役割 | IPアドレス | MySQLバージョン |
|---|---|---|
| ソース | 192.168.0.1 | MySQL 8.0 |
| レプリカ | 192.168.0.2 | MySQL 8.0 |
構築の流れ
1. 設定の確認（ソース）
2. ユーザーアカウントの作成（ソース）
3. フルバックアップの取得（ソース）
4. リストア（レプリカ）
5. レプリケーションの設定（レプリカ）
6. レプリケーションの開始（レプリカ）
7. レプリケーションの状態確認（レプリカ）
8. 読み取り専用に設定（レプリカ）
1. 設定の確認（ソース）
server_id を設定します。ソースとレプリカで異なる値にする必要があります。デフォルトは1なのでどちらかを必ず変更します。
# my.cnf
[mysqld]
server_id = 1000
GTIDレプリケーションを構築する場合は追加でGTIDを有効にします。
[mysqld]
server_id = 1000
gtid_mode = ON
enforce_gtid_consistency = ON
設定後はMySQLサーバーを再起動します。
2. ユーザーアカウントの作成（ソース）
レプリケーション専用のユーザーアカウントを作成します。必要な権限は REPLICATION SLAVE 権限です。
CREATE USER 'repl_user'@'192.168.0.2' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.0.2';
必須ではありませんが、管理面・セキュリティ面からレプリケーション専用ユーザーを作成することをお勧めします。
3. フルバックアップの取得（ソース）
mysqldumpでフルバックアップを取得します。ポジションレプリケーションとGTIDレプリケーションでオプションが異なります。
# ポジションレプリケーションの場合
mysqldump -h 192.168.0.1 -P 3306 -u root -p \
--single-transaction \
--default-character-set=utf8mb4 \
--source-data=2 \
--routines --triggers --events --hex-blob \
--all-databases > source.dump
# GTIDレプリケーションの場合
mysqldump -h 192.168.0.1 -P 3306 -u root -p \
--single-transaction \
--default-character-set=utf8mb4 \
--set-gtid-purged=COMMENTED \
--routines --triggers --events --hex-blob \
--all-databases > source.dump
オプションの説明
| オプション | 説明 |
|---|---|
--single-transaction |
トランザクションを使ってバックアップを取得（InnoDB向け） |
--source-data=2 |
バイナリログのファイル名とポジションをコメントとして記録（ポジション用） |
--set-gtid-purged=COMMENTED |
GTID_PURGEDをコメントとして記録（GTID用） |
--routines |
ストアドプロシージャ・ファンクションを含める |
--triggers |
トリガーを含める |
--events |
イベントスケジューラを含める |
--hex-blob |
BLOBデータを16進数で出力（文字化け防止） |
4. リストア（レプリカ）
mysqldumpで取得したダンプファイルをレプリカにインポートします。
cat source.dump | mysql -h 192.168.0.2 -P 3306 -u root -p
5. レプリケーションの設定（レプリカ）
まずレプリカの server_id がソースと異なることを確認します。
SELECT @@server_id;
-- 99 など、ソースと異なる値であることを確認
ポジションレプリケーションの場合
ダンプファイルからレプリケーションを開始するポジションを確認します。
more source.dump
# 以下のような行を探す
# -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=157;
確認したポジションを CHANGE REPLICATION SOURCE TO に設定します。
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.0.1',
SOURCE_PORT=3306,
SOURCE_USER='repl_user',
SOURCE_PASSWORD='repl_password',
SOURCE_LOG_FILE='binlog.000004',
SOURCE_LOG_POS=157;
GTIDレプリケーションの場合
ダンプファイルからGTID_PURGEDの値を確認します。
more source.dump
# 以下のような行を探す
# -- SET @@GLOBAL.GTID_PURGED='+00022132-1111-1111-1111-111111111111:1-20';
GTIDをレプリカに設定してからレプリケーションを設定します。
-- GTID_PURGEDを設定（先頭の+はGTIDセットの追記を意味する）
SET @@GLOBAL.GTID_PURGED='+00022132-1111-1111-1111-111111111111:1-20';
-- GTIDレプリケーションはSOURCE_AUTO_POSITION=1だけでOK（ポジション指定不要）
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.0.1',
SOURCE_PORT=3306,
SOURCE_USER='repl_user',
SOURCE_PASSWORD='repl_password',
SOURCE_AUTO_POSITION=1;
GTIDレプリケーションは SOURCE_AUTO_POSITION=1 を指定するだけでポジションの指定が不要になります。ソースに接続するときにGTIDセットを送信して、適用済みのGTID以降のイベントを受け取ることが保証されます。
6. レプリケーションの開始（レプリカ）
START REPLICA;
7. レプリケーションの状態確認（レプリカ）
SHOW REPLICA STATUS\G
確認すべきカラムは以下の通りです。
| カラム名 | 確認内容 |
|---|---|
Replica_IO_Running |
Yes であればI/Oスレッドが正常に動作 |
Replica_SQL_Running |
Yes であればSQLスレッドが正常に動作 |
Seconds_Behind_Source |
0 に近づいていればレプリケーション遅延が解消されつつある |
Last_IO_Error |
エラーがあれば原因を確認 |
Last_SQL_Error |
エラーがあれば原因を確認 |
Replica_IO_Running と Replica_SQL_Running が両方 Yes であればレプリケーションは正常に動作しています。No であれば Last_IO_Error または Last_SQL_Error を確認します。
8. 読み取り専用に設定（レプリカ）
ユーザーからレプリカへの更新を防ぐために読み取り専用に設定します。
-- 通常ユーザーからの更新を禁止（SUPERユーザーは更新可能）
SET GLOBAL read_only = ON;
-- SUPERユーザーも含めて完全に読み取り専用にする場合
SET GLOBAL super_read_only = ON;
以上でレプリケーションの構築は完了です。
ポジション vs GTID の比較
| ポジションレプリケーション | GTIDレプリケーション | |
|---|---|---|
| 設定の複雑さ | バイナリログファイル名＋ポジションの指定が必要 |
SOURCE_AUTO_POSITION=1 だけでOK |
| レプリカ追加時 | バイナリログとポジションを確認する必要あり | GTIDセットが自動で管理される |
| フェイルオーバー時 | バイナリログとポジションを確認する必要あり | 不要 |
| リスク | 誤ったポジション指定でデータズレが気づかず進む可能性 | 制限事項あり（トランザクション内でのDDLなど） |
| 推奨 | - | ✅ 運用が楽 |
GTIDレプリケーションの方が運用が楽です。新規構築の場合はGTIDレプリケーションを選ぶことをお勧めします。
一時停止・リセット
-- レプリケーションを一時停止
STOP REPLICA;
-- 再開
START REPLICA;
-- レプリケーション情報をすべてリセット（リレーログも削除される）
STOP REPLICA;
RESET REPLICA ALL;
-- RELOAD権限が必要
まとめ
- レプリケーションの構築は8ステップ。mysqldumpでバックアップを取得してリストアし、CHANGE REPLICATION SOURCE TOで設定する
- ポジションレプリケーションはバイナリログのファイル名とポジションを指定する
- GTIDレプリケーションは
SOURCE_AUTO_POSITION=1だけでOK。運用が楽なので新規構築はGTIDを推奨 - 構築後は
SHOW REPLICA STATUSでReplica_IO_RunningとReplica_SQL_Runningが両方Yesであることを確認する - レプリカは
read_only = ONにしてユーザーからの更新を防ぐ