はじめに

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 にしてユーザーからの更新を防ぐ

参考