Posted inMySQL

MySQL Active-Active レプリケーション設定：双方向データ同期と同時書き込み競合の解決

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Quick Start — 10分でMaster-Masterをセットアップ

Ubuntu 22.04のサーバー2台を使ってデモします。どちらもMySQL 8.0を実行しています。IPアドレスはご自身の環境に合わせて変更してください：

  • Node1: 192.168.1.10
  • Node2: 192.168.1.20

ステップ1：両方のノードにレプリケーションユーザーを作成

Node1で以下のコマンドを実行します：

CREATE USER 'repl_user'@'192.168.1.20' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.20';
FLUSH PRIVILEGES;

Node2でも同様に実行します — IPアドレスを192.168.1.10に変更するだけです：

CREATE USER 'repl_user'@'192.168.1.10' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.10';
FLUSH PRIVILEGES;

ステップ2：my.cnfを設定

Node1上（/etc/mysql/mysql.conf.d/mysqld.cnf）：

[mysqld]
server-id                = 1
log_bin                  = /var/log/mysql/mysql-bin.log
binlog_format            = ROW
relay_log                = /var/log/mysql/relay-bin.log
log_slave_updates        = ON
auto_increment_increment = 2
auto_increment_offset    = 1

Node2上 — server-idとauto_increment_offsetのみ異なります：

[mysqld]
server-id                = 2
log_bin                  = /var/log/mysql/mysql-bin.log
binlog_format            = ROW
relay_log                = /var/log/mysql/relay-bin.log
log_slave_updates        = ON
auto_increment_increment = 2
auto_increment_offset    = 2

sudo systemctl restart mysql

ステップ3：binlog positionの取得とレプリケーションの有効化

Node1上で、正確なbinlog positionを取得するために一時的にテーブルをロックします：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

以下のような結果が返されます — FileとPositionをメモしておきます：

+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000001 |      154 |
+------------------+----------+

次にNode2をNode1に向けて設定します：

-- Node2で実行
CHANGE MASTER TO
  MASTER_HOST     = '192.168.1.10',
  MASTER_USER     = 'repl_user',
  MASTER_PASSWORD = 'StrongPassword123!',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS  = 154;

START SLAVE;
SHOW SLAVE STATUS\G

逆方向も同様に設定します — Node2のbinlog positionを取得し、Node1をNode2に向けて設定します。先にテーブルをアンロックすることを忘れずに：

-- Node1上：Node2のpositionを取得後にunlock
UNLOCK TABLES;

-- Node1をNode2のslaveとして設定
CHANGE MASTER TO
  MASTER_HOST     = '192.168.1.20',
  MASTER_USER     = 'repl_user',
  MASTER_PASSWORD = 'StrongPassword123!',
  MASTER_LOG_FILE = 'mysql-bin.000001',  -- Node2から取得したposition
  MASTER_LOG_POS  = 154;

START SLAVE;

状態を確認します — 以下の2行がどちらもYesになっている必要があります：

SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes

内部の仕組みを理解する

本質的に、Master-Masterとは2つのMaster-Slave関係を逆方向に組み合わせたものです。Node1はmaster（binlogを書き込む）であり、同時にslave（Node2からbinlogを受け取る）でもあります。Node2も同様です。どちらのノードに書き込んでも、もう一方が受け取ります — 区別はありません。

auto_increment_incrementとauto_increment_offsetがなぜ重要なのか

ここは最も見落とされやすい部分であり、設定を怠ると最も頭を悩ませる箇所でもあります。この2つのパラメータを設定しないと、両方のノードが1から始まるAUTO_INCREMENTを生成します。Node1がID=1を作成し、Node2も同じID=1を作成してしまい、duplicate keyエラーでレプリケーションが即座に停止します。

auto_increment_increment=2の場合：

  • Node1（offset=1）が生成する値: 1, 3, 5, 7, 9…
  • Node2（offset=2）が生成する値: 2, 4, 6, 8, 10…

この2つの数列が衝突することはありません。3ノードに拡張する場合は、increment=3に設定し、offsetをそれぞれ1、2、3にします。

応用 — 同時書き込み競合の処理

書き込み競合はActive-Activeで最も難しい問題です。Node1とNode2が同時に同じ行をUPDATEしようとする場合を考えてみましょう：

-- Node1で実行
UPDATE products SET stock = 10 WHERE id = 100;

-- Node2が同時に実行
UPDATE products SET stock = 5 WHERE id = 100;

レプリケーションが遅れた方のノードが勝ちます。この動作はlast-write-winsと呼ばれ、エラーも出ず、ロールバックもなく、データは静かに上書きされます。MySQLは介入しません。

レプリケーションのエラーを検出する

# 両方のノードで素早く確認する
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "Slave_SQL_Running|Last_Error|Seconds_Behind"

Slave_SQL_Running: Noが表示されていますか？レプリケーションが停止しています。Last_Errorを読んで原因を特定し、必要であれば現在のエラーをスキップしてください：

-- 現在のエラーをスキップして続行（慎重に使用すること）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

duplicate keyエラーを自動スキップする設定

レプリケーションを完全に停止させる代わりにduplicate keyエラー（エラーコード1062）をスキップしたい場合は、my.cnfに追加します：

[mysqld]
# trade-offを十分理解した上で使用すること — データの整合性が失われる可能性あり
slave_skip_errors = 1062

アーキテクチャ的な解決策：競合を根本から防ぐ

私の本番データベースはMySQL 8.0で約50GBのデータを扱っています。競合への対処はdetect-and-fixではなく、最初からスマートなルーティングを行うことが重要です。具体的には：ProxySQLを組み合わせて、すべての書き込みを1つのアクティブなノードだけにルーティングします。もう一方のノードは読み取り専用です。アクティブなノードに障害が発生した場合、数秒以内に書き込みを別のノードへ切り替えます。競合が発生した後に処理するよりもはるかにシンプルです。

競合を防ぐための他の一般的なパターン：

  • テナント/リージョンによる分割：ユーザーAは常にNode1に書き込み、ユーザーBは常にNode2に書き込む。2つのノードが同じ行に同時に書き込むことはない。
  • アプリケーションレベルのロック：書き込む前にRedis SETNXで分散ロックを取得し、同時に1つのwriterしか存在しないことを保証する。

実践的なTips

GTIDで管理を簡単にする

MySQL 5.6以降にはGTID（Global Transaction Identifier）があり、binlogファイルとpositionを手動で管理する必要がなくなります。フェイルオーバーとリカバリーがはるかに簡単になります：

[mysqld]
gtid_mode                = ON
enforce_gtid_consistency = ON

GTIDを使用する場合、MASTER_LOG_FILEとMASTER_LOG_POSを完全に省略します：

CHANGE MASTER TO
  MASTER_HOST          = '192.168.1.10',
  MASTER_USER          = 'repl_user',
  MASTER_PASSWORD      = 'StrongPassword123!',
  MASTER_AUTO_POSITION = 1;  -- MySQLが適切なpositionを自動的に探す

レプリケーションラグを継続的に監視する

# リアルタイムでlagを確認、5秒ごとに更新
watch -n 5 'mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master'

Seconds_Behind_Masterが継続的に増加している場合、slaveがmasterに追いついていないサインです。一般的な原因：ディスクI/Oのボトルネック、CPUが非力、または同時書き込みが多すぎること — ラグが30秒を超えたら、すぐに調査が必要です。

本番環境に移行する前のチェックリスト

  • ✅ 両方のノードが同じMySQLバージョンを使用している
  • ✅ binlog_format = ROW — STATEMENTは使用しない（NOW()、RAND()などの関数で不整合が生じやすいため）
  • ✅ ノード間でauto_increment_incrementとauto_increment_offsetが異なる値に設定されている
  • ✅ ファイアウォールが2つのノード間のポート3306を双方向で許可している
  • ✅ Slave_SQL_Running = Noのときに監視アラートが設定されている
  • ✅ 2つのノード間のネットワークが切断された場合のsplit-brainに対処するrunbookが用意されている

Master-Masterを使うべきでない場合

Master-Masterが常に正しい選択とは限りません。以下の場合は避けてください：

  • アプリケーションが複数のノードから同じ行を頻繁にUPDATEする場合 — 競合が絶えず発生する
  • strong consistencyが必要な場合 — Active-Activeは本質的にeventually consistent
  • チームがレプリケーションの経験に乏しい場合 — Master-MasterのトラブルシューティングはMaster-Slaveよりもはるかに難しい

そのような場合は、MySQL Group ReplicationまたはGalera Clusterの方が適しています。どちらも組み込みの競合検出と解決機能を備えています。トレードオフとして：ノード間で同期コンセンサスが必要なため、書き込みレイテンシーがわずかに高くなります。

Share:
Tags: