Posted inMySQL

MySQL Master-Slaveレプリケーションの設定：実際のトラブルから解決策へ

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

午前3時に起きたこと

以前、午前3時にデータベースの破損トラブルに遭遇し、バックアップからリストアする羽目になった。それ以来、毎日バックアップを確認するようになった。しかし、どれだけしっかりバックアップを取っていても、致命的な弱点がある。リストア中はウェブサイトが完全に停止してしまうのだ。顧客は商品を購入できず、SEOにも影響が出る。そして自分は黒い画面を見つめながら、冷めたコーヒーを飲み続けるしかない。

あのトラブルをきっかけに、MySQLレプリケーションを本格的に調べ始めた。これは数年前に知っておけばよかった技術だと今でも思う。

なぜデータベースが最大の弱点になるのか？

中小規模のWebアプリの9割を見てみると、同じ構成になっている：Webサーバー1台＋データベースサーバー1台だ。このセットアップはトラフィックが小さいうちは問題なく動く。しかしユーザーが増えてくると、真っ先に限界を迎えるのは常にデータベースだ。

具体的な問題点：

  • Read-heavy workload：一般的なWebアプリのクエリの80〜90%はSELECT（データ読み取り）だ。それがすべて1台のサーバーに集中する。
  • 単一障害点（SPOF）：データベースが落ちる＝アプリ全体が落ちる。代替手段がない。
  • バックアップではダウンタイムを防げない：データ量によってリストアに30分〜数時間かかる。
  • メンテナンスがダウンタイムを引き起こす：MySQLのバージョンアップが必要？大きなテーブルの最適化が必要？すべての処理を止めなければならない。

解決策とそれぞれの問題点

スケールアップ：ハードウェアの増強

最もシンプルな方法は、より高性能なサーバーを購入することだ。RAMを増やし、SSDを高速化し、コア数を増やす。しかしスケールアップは時間を稼ぐだけで、永遠にアップグレードし続けることはできない。AWSのr6g.8xlargeインスタンス（64GB RAM、32 vCPU）は月額約1,000ドルかかるが、それでも単一障害点には変わりない。

キャッシュレイヤーの追加（Redis/Memcached）

RedisやMemcachedを使えば、データベースへのクエリを60〜70%削減できる。特にリスティングページ、検索結果、静的コンテンツに効果的だ。しかし、すべてをキャッシュできるわけではない。更新中のカート、口座残高、リアルタイム通知などは、データベースから直接読み取る必要がある。

MySQLレプリケーション

データベースが自動的に別のサーバーへ継続的かつほぼリアルタイムで複製される。読み書きの負荷分散と冗長化を同時に実現できる。これこそが根本的な解決策だ。

MySQLレプリケーションの仕組み

仕組みはシンプルだ。Masterサーバーがすべての書き込み操作（INSERT、UPDATE、DELETE）を受け付ける。すべての変更はBinary Logに記録される。SlaveサーバーはMasterに接続し、Binary Logを読み取って同じ処理を自分自身で再実行する。

その結果、Slaveは常にMasterとほぼ同じデータを保持し、負荷に応じてわずか数ミリ秒〜数秒の遅延しか生じない。

この構成により、アプリケーションは：

  • すべての書き込みクエリをMasterへ送信
  • すべての読み取りクエリをSlaveへ送信

これだけでMasterの負荷を80%削減でき、常に最新状態の「ホットバックアップ」を手に入れられる。

MySQL Master-Slaveレプリケーションのステップバイステップ設定

デモ環境：Ubuntu 22.04、MySQL 8.0、サーバー2台。

  • Master：IP 192.168.1.10
  • Slave：IP 192.168.1.20

ステップ1：MasterのMySQL設定

MySQLの設定ファイルを開く：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

以下の行を追加または編集する：

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database_name
bind-address = 0.0.0.0

MySQLを再起動する：

sudo systemctl restart mysql

ステップ2：Masterでレプリケーションユーザーを作成

MySQLにログインする：

mysql -u root -p

ユーザーを作成して権限を付与する：

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

現在のBinary Log情報を取得する。この値は後のステップで必要になるので控えておくこと：

SHOW MASTER STATUS;

出力は以下のようになる：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      154  | mydb         |                  |
+------------------+----------+--------------+------------------+

FileとPositionの値を控えておく。

ステップ3：Masterからデータをエクスポート

テーブルをロックし、データをダンプしてからアンロックする：

# MySQLセッションでテーブルをロックする
FLUSH TABLES WITH READ LOCK;

# 別のターミナルを開いてデータベースをダンプする
mysqldump -u root -p --databases your_database_name > /tmp/db_dump.sql

# 最初のセッションに戻り、アンロックする
UNLOCK TABLES;

ダンプファイルをSlaveにコピーする：

scp /tmp/db_dump.sql [email protected]:/tmp/

ステップ4：SlaveのMySQL設定

設定ファイルを開く：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

以下を追加する：

[mysqld]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database_name
relay-log = /var/log/mysql/mysql-relay-bin.log

MySQLを再起動してデータをインポートする：

sudo systemctl restart mysql
mysql -u root -p < /tmp/db_dump.sql

ステップ5：SlaveをMasterに接続

SlaveのMySQLにログインする：

STOP SLAVE;

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;

START SLAVE;

mysql-bin.000003と154は、ステップ2で控えた値に置き換えること。

ステップ6：動作確認

Slave上で以下のコマンドを実行する：

SHOW SLAVE STATUS\G

確認すべき重要な2行：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

両方がYesであればレプリケーションが正常に動作している。Masterで新しいテーブルを作成したりデータをINSERTしたりして、Slaveに反映されるか確認してみよう。

運用時の注意点

レプリケーション遅延（Replication lag）：SlaveのデータはMasterと常に100%一致しているとは限らない。書き込み直後にデータを読み取る必要がある場合（例：ユーザー登録直後にプロフィールを表示する場合）は、読み取りリクエストをMasterにルーティングすること。

SlaveはRead-only：アプリケーションが誤ってSlaveにデータを書き込まないよう注意が必要だ。安全のために、Slaveの設定に以下の行を追加する：

read_only = ON

レプリケーションの監視：レプリケーションはエラー（主にデータの競合）により停止することがある。定期チェックのスクリプトを用意しておくとよい：

mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "Running|Error|Seconds_Behind"

ファイアウォール：Masterのポート3306で、SlaveのIPからの接続を許可すること。インターネット全体には開放しないこと。

sudo ufw allow from 192.168.1.20 to any port 3306

実際の導入結果

1日約5万セッションのECプロジェクトにレプリケーションを導入したところ、MasterのCPU負荷が85%から30%に低下した。商品リストページの平均クエリタイムは800msから200msに短縮された。読み取りクエリをSlaveにルーティングするだけでこの効果だ。

さらに重要なのは、次回のデータベースメンテナンス時にSlaveを一時的にMasterに昇格させるだけで、一切のダウンタイムなしに対応できるようになった。

複雑でもなく、コストもかからない。月額5〜10ドルのVPS1台と30分の設定作業があれば十分だ。その代わりに得られるものは大きい：データベースの負荷軽減、常に最新状態の冗長バックアップ、そして次回以降のメンテナンスでダウンタイムを心配しなくてよくなる。今すぐ取り組む価値がある。

Share:
Tags: